Repository: sqlfluff/sqlfluff

Issue:
LT01 & ST02 interaction adds a space between coalesce keyword and opening parenthesis
### Search before asking

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


### What Happened

I have a classic unnecessary case statement:
```
case when ended_at is null or date(ended_at) > current_date() then true else false end as is_active,
```

Which is correctly being flagged by ST02:
```
=== [dbt templater] Sorting Nodes...
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
== [my_redacted_model.sql] FAIL
L:  31 | P:  23 | ST02 | Unnecessary CASE statement. Use COALESCE function
                       | instead. [structure.simple_case]
```

Applying `sqlfluff fix` "fixes" the offending case statement with malformed SQL:
```
coalesce (ended_at is null or date(ended_at) > current_date(), false) as is_active,
        
        ^- shouldn't be there
```

which fails a subsequent lint due to the space. Running `sqlfluff` fix twice avoides the error because the offending space is picked up by LT01.


### Expected Behaviour

I would expect ST02 to not add a space between the `coalesce` keyword and the opening parenthesis.

### Observed Behaviour

ST02 adds a space between the `coalesce` keyword and the opening parenthesis.

### How to reproduce

This query reproduces the error for me:

```
with
    subscriptions as (
        select *
        from {{ ref('stg__subscriptions') }}
    ),

    club_subscriptions as (
        select
            case
                when ended_at is null or date(ended_at) > current_date()
                    then true
                else false
            end as is_active
        from subscriptions
    )

select *
from club_subscriptions
```

### Dialect

bigquery, with the dbt templater

### Version

Python: Python 3.10.15

- **sqlfluff**
	```
	"sqlfluff==3.2.3",
    "sqlfluff-templater-dbt==3.2.3",
	```

- **dbt**
	```
    "dbt-adapters==1.7.0",
    "dbt-bigquery==1.8.2",
    "dbt-core==1.8.7",
	```

### Configuration

```
[sqlfluff]
templater = dbt
dialect = bigquery
max_line_length = 120
exclude_rules =
    structure.column_order,
    structure.using,
    references.qualification,
    ambiguous.column_count
warnings = RF04

[sqlfluff:templater:dbt]
project_dir = ./dbt
profiles_dir = ./dbt

[sqlfluff:templater:jinja]
load_macros_from_path = ./dbt/macros

[sqlfluff:indentation]
indented_ctes = true
allow_implicit_indents = true

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.identifiers]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower

```

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

- [ ] 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   18594      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
diff --git a/src/sqlfluff/rules/structure/ST02.py b/src/sqlfluff/rules/structure/ST02.py
index de4c16802..aef10af36 100644
--- a/src/sqlfluff/rules/structure/ST02.py
+++ b/src/sqlfluff/rules/structure/ST02.py
@@ -14,6 +14,24 @@ from sqlfluff.core.rules.crawlers import SegmentSeekerCrawler
 from sqlfluff.utils.functional import FunctionalContext, Segments, sp
 
 
+class FunctionNameSegment(BaseSegment):
+    """Function name, including any prefix bits, e.g. project or schema.
+
+    NOTE: This is a minimal segment definition to allow appropriate fixing.
+    """
+
+    type = "function_name"
+
+
+class FunctionContentsSegment(BaseSegment):
+    """Function Contents.
+
+    NOTE: This is a minimal segment definition to allow appropriate fixing.
+    """
+
+    type = "function_contents"
+
+
 class Rule_ST02(BaseRule):
     """Unnecessary ``CASE`` statement.
 
@@ -92,13 +110,19 @@ class Rule_ST02(BaseRule):
         """Generate list of fixes to convert CASE statement to COALESCE function."""
         # Add coalesce and opening parenthesis.
         edits = [
-            WordSegment("coalesce", type="function_name_identifier"),
-            SymbolSegment("(", type="start_bracket"),
-            coalesce_arg_1,
-            SymbolSegment(",", type="comma"),
-            WhitespaceSegment(),
-            coalesce_arg_2,
-            SymbolSegment(")", type="end_bracket"),
+            FunctionNameSegment(
+                segments=(WordSegment("coalesce", type="function_name_identifier"),)
+            ),
+            FunctionContentsSegment(
+                segments=(
+                    SymbolSegment("(", type="start_bracket"),
+                    coalesce_arg_1,
+                    SymbolSegment(",", type="comma"),
+                    WhitespaceSegment(),
+                    coalesce_arg_2,
+                    SymbolSegment(")", type="end_bracket"),
+                )
+            ),
         ]
 
         if preceding_not:


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
diff --git a/test/rules/std_LT01_ST02_test.py b/test/rules/std_LT01_ST02_test.py
new file mode 100644
index 000000000..f57efada0
--- /dev/null
+++ b/test/rules/std_LT01_ST02_test.py
@@ -0,0 +1,49 @@
+"""Tests the python routines within LT04 and ST06."""
+
+import pytest
+
+from sqlfluff.core import FluffConfig, Linter
+
+
+@pytest.mark.parametrize(
+    ["in_sql", "out_sql"],
+    [
+        (
+            """
+select
+    case
+        when ended_at is null or date(ended_at) > current_date()
+        then true else false
+    end as is_active
+from foo
+""",
+            """
+select
+    coalesce(ended_at is null or date(ended_at) > current_date(), false) as is_active
+from foo
+""",
+        ),
+    ],
+)
+def test_rules_std_LT01_and_ST02_interaction(in_sql, out_sql) -> None:
+    """Test interaction between LT04 and ST06.
+
+    Test sql with two newlines with leading commas expecting trailing.
+    """
+    # Lint expected rules.
+    cfg = FluffConfig.from_string(
+        """[sqlfluff]
+dialect = ansi
+rules = LT01,ST02
+"""
+    )
+    linter = Linter(config=cfg)
+
+    # Return linted/fixed file.
+    linted_file = linter.lint_string(in_sql, fix=True)
+
+    # Check expected lint errors are raised.
+    assert set([v.rule.code for v in linted_file.violations]) == {"ST02"}
+
+    # Check file is fixed.
+    assert linted_file.fix_string()[0] == out_sql