Repository: sqlfluff/sqlfluff

Issue:
aliasing.unused false positive when a table alias is used while unnesting
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

[redshift] sqlfluff 2.0.2 reports (and fixes) aliasing.unused where the alias is (correctly) not being used in a select statement, but is used unnesting an array in the from statement.

Example:

```sql
-- redshift
SELECT
    my_column,
    my_array_value
FROM my_schema.my_table AS t, t.super_array AS my_array_value
```
 
```
L:   4 | P:  28 | AL05 | Alias 't' is never used in SELECT statement.
                       | [aliasing.unused]
```

### Expected Behaviour

The alias is being validly used in the FROM clause of this SELECT statement, and this rule should not trigger.

### Observed Behaviour

The table alias is reported as unused, and `sqlfluff fix` removes it, making the query invalid.



### How to reproduce

Reproducer included in _What Happened_

### Dialect

Redshift

### Version

2.0.2 (python 3.10.6)

### Configuration

Default configuration (aside from dialect).

### Are you willing to work on and submit a PR to address the issue?

- [X] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)


Discussion:
github-actions[bot]: # Coverage Results ✅
```
Name    Stmts   Miss  Cover   Missing
-------------------------------------
TOTAL   18595      0   100%

236 files skipped due to complete coverage.

```


Patch:
diff --git a/src/sqlfluff/core/templaters/jinja.py b/src/sqlfluff/core/templaters/jinja.py
index d6ea7841a..42c8733d6 100644
--- a/src/sqlfluff/core/templaters/jinja.py
+++ b/src/sqlfluff/core/templaters/jinja.py
@@ -82,10 +82,20 @@ class UndefinedRecorder:
         self.undefined_set.add(self.name)
         return UndefinedRecorder(f"{self.name}.{item}", self.undefined_set)
 
+    def __getitem__(self, item: str) -> "UndefinedRecorder":
+        """Don't fail when called, remember instead."""
+        self.undefined_set.add(self.name)
+        return UndefinedRecorder(f"{self.name}.{item}", self.undefined_set)
+
     def __call__(self, *args: Any, **kwargs: Any) -> "UndefinedRecorder":
         """Don't fail when called unlike parent class."""
         return UndefinedRecorder(f"{self.name}()", self.undefined_set)
 
+    def __iter__(self) -> Iterator["UndefinedRecorder"]:
+        """Don't fail when iterated, remember instead."""
+        self.undefined_set.add(self.name)
+        yield UndefinedRecorder(f"iter({self.name})", self.undefined_set)
+
 
 class JinjaTemplater(PythonTemplater):
     """A templater using the jinja2 library.
diff --git a/src/sqlfluff/rules/aliasing/AL05.py b/src/sqlfluff/rules/aliasing/AL05.py
index 9920e8515..5fd5ae7d5 100644
--- a/src/sqlfluff/rules/aliasing/AL05.py
+++ b/src/sqlfluff/rules/aliasing/AL05.py
@@ -154,6 +154,25 @@ class Rule_AL05(BaseRule):
                 and self._followed_by_qualify(context, alias)
             ):
                 continue
+            # If the alias is for a _function_ rather than just a table, it's possible
+            # that it's an array function, like `unnest` or `jsonb_array_elements_text`
+            # So if that alias appears as what looks like a _column reference_ then
+            # also skip it.
+            # https://github.com/sqlfluff/sqlfluff/issues/4623
+            _table_expression = alias.from_expression_element.get_child(
+                "table_expression"
+            )
+            if _table_expression and _table_expression.get_child("function"):
+                # Case insensitive match for conservatism
+                if alias.ref_str.strip("\"'`[]").upper() in [
+                    seg.raw_upper.strip("\"'`[]")
+                    for seg in select_info.reference_buffer
+                ]:
+                    self.logger.debug(
+                        f"Alias for function {alias.ref_str} found as apparent "
+                        "column reference in select. Skipping"
+                    )
+                    continue
 
             if (
                 alias.aliased
@@ -195,8 +214,14 @@ class Rule_AL05(BaseRule):
     ) -> bool:
         """Given an alias, is it REQUIRED to be present?
 
-        Aliases are required in SOME, but not all dialects when there's a VALUES
-        clause.
+        There are a few circumstances where an alias is either required by the
+        dialect, or recommended by SQLFluff:
+
+        * Aliases are required in SOME, but not all dialects when there's a
+          VALUES clause.
+
+        * In the case of a nested SELECT, all dialect checked (MySQL, Postgres,
+          T-SQL) require an alias.
         """
         # Look for a table_expression (i.e. VALUES clause) as a descendant of
         # the FROM expression, potentially nested inside brackets. The reason we


Test patch:
diff --git a/test/core/templaters/jinja_test.py b/test/core/templaters/jinja_test.py
index 30e69597e..73237b490 100644
--- a/test/core/templaters/jinja_test.py
+++ b/test/core/templaters/jinja_test.py
@@ -1715,29 +1715,32 @@ def test__templater_jinja_large_file_check():
 
 
 @pytest.mark.parametrize(
-    "ignore, expected_violation",
+    "in_str, ignore, expected_violation",
     [
         (
+            """WITH a AS ({{  b(c=d, e=f) }}) SELECT * FROM final""",
             "",
-            SQLTemplaterError(
-                "Undefined jinja template variable: 'test_event_cadence'"
-            ),
+            SQLTemplaterError("Undefined jinja template variable: 'b'"),
+        ),
+        ("""WITH a AS ({{  b(c=d, e=f) }}) SELECT * FROM final""", "templating", None),
+        (
+            # https://github.com/sqlfluff/sqlfluff/issues/6360
+            """{% for tbl in tbl_list %}SELECT a FROM {{ tbl }};{% endfor %}""",
+            "",
+            SQLTemplaterError("Undefined jinja template variable: 'tbl_list'"),
+        ),
+        (
+            """SELECT a FROM {{ tbl['name'] }};""",
+            "",
+            SQLTemplaterError("Undefined jinja template variable: 'tbl'"),
         ),
-        ("templating", None),
     ],
 )
-def test_jinja_undefined_callable(ignore, expected_violation):
+def test_jinja_undefined_callable(in_str, ignore, expected_violation):
     """Test undefined callable returns TemplatedFile and sensible error."""
     templater = JinjaTemplater()
     templated_file, violations = templater.process(
-        in_str="""WITH streams_cadence_test AS (
-{{  test_event_cadence(
-    model= ref('fct_recording_progression_stream'),
-    grouping_column='archive_id', time_column='timestamp',
-    date_part='minute', threshold=1) }}
-)
-SELECT * FROM final
-""",
+        in_str=in_str,
         fname="test.sql",
         config=FluffConfig(overrides={"dialect": "ansi", "ignore": ignore}),
     )
diff --git a/test/fixtures/rules/std_rule_cases/AL05.yml b/test/fixtures/rules/std_rule_cases/AL05.yml
index e9fa9b0d0..ae55cf096 100644
--- a/test/fixtures/rules/std_rule_cases/AL05.yml
+++ b/test/fixtures/rules/std_rule_cases/AL05.yml
@@ -1177,3 +1177,50 @@ test_fail_redshift_qualify_does_not_follows_from:
   configs:
     core:
       dialect: redshift
+
+test_pass_postgres_jsonb_array_elements_text:
+  # https://github.com/sqlfluff/sqlfluff/issues/4623
+  pass_str: |
+    SELECT t
+    FROM jsonb_array_elements_text('["orange","banana","watermelon"]') AS t;
+  configs:
+    core:
+      dialect: postgres
+
+test_pass_postgres_json_array_elements:
+  # https://github.com/sqlfluff/sqlfluff/issues/4623
+  pass_str: |
+    SELECT a
+    FROM json_array_elements('[{"id": "a1"}]') AS a,
+        json_array_elements('[{"id": "A1"}, {"id": "A2"}]') AS b
+    WHERE lower(a ->> 'id') = lower(b ->> 'id');
+  configs:
+    core:
+      dialect: postgres
+
+test_pass_bigquery_unnest:
+  # https://github.com/sqlfluff/sqlfluff/issues/4623
+  pass_str: |
+    with sequences as (
+        select [0, 1, 1, 2] as some_numbers
+        union all
+        select [2, 4, 8]
+    )
+
+    select num
+    from sequences as s
+    cross join unnest(s.some_numbers) as num
+  configs:
+    core:
+      dialect: bigquery
+
+test_pass_redshift_array_value:
+  # https://github.com/sqlfluff/sqlfluff/issues/4623
+  pass_str: |
+    SELECT
+        my_column,
+        my_array_value
+    FROM my_schema.my_table AS t, t.super_array AS my_array_value
+  configs:
+    core:
+      dialect: redshift