Repository: sqlfluff/sqlfluff

Issue:
Rule ST09 not working correctly with Jinja templating
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

Rule ST09 is not able to properly asses order of tables when Jinja parameters are present.

### Expected Behaviour

For the order of tables to be assessed correctly.

### Observed Behaviour

#### False positive

This example passes when it should fail as the last join sets `ON t3.id = t1.id`.

```sql
SELECT
  t1.a
  , t1.b
  , t2.c
FROM `{{ params.project_id }}.my_dataset.table_1` AS t1
LEFT JOIN `{{ params.project_id }}.my_dataset.table_2` AS t2
  ON t1.id = t2.id
LEFT JOIN `{{ params.project_id }}.my_dataset.table_3` AS t3
  ON t3.id = t1.id
```

![image](https://github.com/sqlfluff/sqlfluff/assets/16988051/66f0790a-f820-45f3-a9e9-e4c4da0bb01a)

#### Expected failure

If we now replace the Jinja params for their values, the check fails as expected.

```sql
SELECT
  t1.a
  , t1.b
  , t2.c
FROM `my_project.my_dataset.table_1` AS t1
LEFT JOIN `my_project.my_dataset.table_2` AS t2
  ON t1.id = t2.id
LEFT JOIN `my_project.my_dataset.table_3` AS t3
  ON t3.id = t1.id
```

![image](https://github.com/sqlfluff/sqlfluff/assets/16988051/2b0d04c8-c7fd-4b37-9c81-324b500ad175)


### How to reproduce

The command I've run is

```shell
sqlfluff lint --config .sqlfluff dags/sql/sqlfluff_test.sql   
```


### Dialect

bigquery

### Version

![image](https://github.com/sqlfluff/sqlfluff/assets/16988051/8b1efc31-b836-4d41-b3a8-b7a6f12edcea)


### Configuration

```cfg
[sqlfluff]
dialect = bigquery
disable_noqa = False
encoding = utf-8
fix_even_unparsable = False
# ignore = templating, parsing
ignore_templated_areas = True
large_file_skip_byte_limit = 0
large_file_skip_char_limit = 0
max_line_length = 120
nocolor = False
output_line_length = 80
processes = 1
runaway_limit = 10
sql_file_exts = .sql
templater = jinja
verbose = 2
warn_unused_ignores = False

rules = ST09

[sqlfluff:templater:jinja]
apply_dbt_builtins = False

[sqlfluff:templater:jinja:context]
params = {"project_id": "my_project"}

[sqlfluff:rules:structure.join_condition_order]
preferred_first_table_in_join_clause = earlier
```

### 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)

Hints from issue comments:

Solution patch:
diff --git a/src/sqlfluff/api/simple.py b/src/sqlfluff/api/simple.py
index 0f24f88d7..f50b2b56e 100644
--- a/src/sqlfluff/api/simple.py
+++ b/src/sqlfluff/api/simple.py
@@ -38,11 +38,24 @@ def get_simple_config(
 
     # Instantiate a config object.
     try:
-        return FluffConfig.from_root(
+        config = FluffConfig.from_root(
             extra_config_path=config_path,
             ignore_local_config=True,
             overrides=overrides,
+            require_dialect=False,
         )
+
+        # If no dialect was specified, set it to ansi. This allows for the legacy
+        # behavior of the simple API to be maintained, where the dialect is not
+        # required to be specified, but defaults to ansi.
+        if not config.get("dialect"):
+            overrides["dialect"] = "ansi"
+            config = FluffConfig.from_root(
+                extra_config_path=config_path,
+                ignore_local_config=True,
+                overrides=overrides,
+            )
+        return config
     except SQLFluffUserError as err:  # pragma: no cover
         raise SQLFluffUserError(f"Error loading config: {str(err)}")
 
@@ -60,7 +73,7 @@ class APIParsingError(ValueError):
 
 def lint(
     sql: str,
-    dialect: str = "ansi",
+    dialect: Optional[str] = None,
     rules: Optional[list[str]] = None,
     exclude_rules: Optional[list[str]] = None,
     config: Optional[FluffConfig] = None,
@@ -70,7 +83,7 @@ def lint(
 
     Args:
         sql (:obj:`str`): The SQL to be linted.
-        dialect (:obj:`str`, optional): A reference to the dialect of the SQL
+        dialect (:obj:`Optional[str]`, optional): A reference to the dialect of the SQL
             to be linted. Defaults to `ansi`.
         rules (:obj:`Optional[list[str]`, optional): A list of rule
             references to lint for. Defaults to None.
@@ -101,7 +114,7 @@ def lint(
 
 def fix(
     sql: str,
-    dialect: str = "ansi",
+    dialect: Optional[str] = None,
     rules: Optional[list[str]] = None,
     exclude_rules: Optional[list[str]] = None,
     config: Optional[FluffConfig] = None,
@@ -112,7 +125,7 @@ def fix(
 
     Args:
         sql (:obj:`str`): The SQL to be fixed.
-        dialect (:obj:`str`, optional): A reference to the dialect of the SQL
+        dialect (:obj:`Optional[str]`, optional): A reference to the dialect of the SQL
             to be fixed. Defaults to `ansi`.
         rules (:obj:`Optional[list[str]`, optional): A subset of rule
             references to fix for. Defaults to None.
@@ -154,7 +167,7 @@ def fix(
 
 def parse(
     sql: str,
-    dialect: str = "ansi",
+    dialect: Optional[str] = None,
     config: Optional[FluffConfig] = None,
     config_path: Optional[str] = None,
 ) -> dict[str, Any]:
@@ -162,7 +175,7 @@ def parse(
 
     Args:
         sql (:obj:`str`): The SQL to be parsed.
-        dialect (:obj:`str`, optional): A reference to the dialect of the SQL
+        dialect (:obj:`Optional[str]`, optional): A reference to the dialect of the SQL
             to be parsed. Defaults to `ansi`.
         config (:obj:`Optional[FluffConfig]`, optional): A configuration object
             to use for the operation. Defaults to None.
diff --git a/src/sqlfluff/core/parser/segments/raw.py b/src/sqlfluff/core/parser/segments/raw.py
index 929643dbb..b6676ed43 100644
--- a/src/sqlfluff/core/parser/segments/raw.py
+++ b/src/sqlfluff/core/parser/segments/raw.py
@@ -77,7 +77,7 @@ class RawSegment(BaseSegment):
         self.quoted_value = quoted_value
         self.escape_replacements = escape_replacements
         self.casefold = casefold
-        self._raw_value: str = self._raw_normalized()
+        self._raw_value: str = self.normalize()
 
     def __repr__(self) -> str:
         # This is calculated at __init__, because all elements are immutable
@@ -180,15 +180,16 @@ class RawSegment(BaseSegment):
             return raw_buff
         return raw_buff
 
-    def _raw_normalized(self) -> str:
-        """Returns the string of the raw content's value.
+    def normalize(self, value: Optional[str] = None) -> str:
+        """Returns the normalized version of a string using the segment's rules.
 
+        By default this uses the raw value of the segment.
         E.g. This removes leading and trailing quote characters, removes escapes
 
         Return:
-        str: The raw content's value
+        str: The normalized value
         """
-        raw_buff = self.raw
+        raw_buff = value or self.raw
         if self.quoted_value:
             _match = re.match(self.quoted_value[0], raw_buff)
             if _match:
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index b6bd98b70..5836b2839 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -1627,7 +1627,7 @@ class FromExpressionElementSegment(BaseSegment):
         ),
     )
 
-    def get_eventual_alias(self) -> AliasInfo:
+    def get_eventual_alias(self) -> Generator[AliasInfo, None, None]:
         """Return the eventual table name referred to by this table expression.
 
         Returns:
@@ -1656,17 +1656,20 @@ class FromExpressionElementSegment(BaseSegment):
                 ref = cast(ObjectReferenceSegment, _ref)
 
         # Handle any aliases
-        alias_expression = self.get_child("alias_expression")
-        if not alias_expression:  # pragma: no cover
-            _bracketed = self.get_child("bracketed")
-            if _bracketed:
-                alias_expression = _bracketed.get_child("alias_expression")
-        if alias_expression:
+        has_alias = False
+        alias_expressions = self.get_children("alias_expression", "bracketed")
+        for alias_expression in alias_expressions:
+            if alias_expression.is_type("bracketed"):  # pragma: no cover
+                _alias_expression = alias_expression.get_child("alias_expression")
+                if _alias_expression is None:
+                    continue
+                alias_expression = _alias_expression
             # If it has an alias, return that
+            has_alias = True
             segment = alias_expression.get_child("identifier")
             if segment:
                 segment = cast(IdentifierSegment, segment)
-                return AliasInfo(
+                yield AliasInfo(
                     segment.raw_normalized(casefold=False),
                     segment,
                     True,
@@ -1674,6 +1677,8 @@ class FromExpressionElementSegment(BaseSegment):
                     alias_expression,
                     ref,
                 )
+        if has_alias:
+            return
 
         # If not return the object name (or None if there isn't one)
         if ref:
@@ -1683,7 +1688,7 @@ class FromExpressionElementSegment(BaseSegment):
                 penultimate_ref: ObjectReferenceSegment.ObjectReferencePart = (
                     references[-1]
                 )
-                return AliasInfo(
+                yield AliasInfo(
                     penultimate_ref.part,
                     penultimate_ref.segments[0],
                     False,
@@ -1691,8 +1696,9 @@ class FromExpressionElementSegment(BaseSegment):
                     None,
                     ref,
                 )
+                return
         # No references or alias
-        return AliasInfo(
+        yield AliasInfo(
             "",
             None,
             False,
@@ -1955,12 +1961,12 @@ class JoinClauseSegment(BaseSegment):
         from_expression = self.get_child("from_expression_element")
         # As per grammar above, there will always be a FromExpressionElementSegment
         assert from_expression
-        alias: AliasInfo = cast(
+        from_aliases = cast(
             FromExpressionElementSegment, from_expression
         ).get_eventual_alias()
         # Only append if non-null. A None reference, may
         # indicate a generator expression or similar.
-        if alias:
+        for alias in from_aliases:
             buff.append((from_expression, alias))
 
         # In some dialects, like TSQL, join clauses can have nested join clauses
@@ -2039,7 +2045,7 @@ class FromClauseSegment(BaseSegment):
 
         # Iterate through the potential sources of aliases
         for clause in direct_table_children:
-            alias: AliasInfo = cast(
+            direct_table_aliases = cast(
                 FromExpressionElementSegment, clause
             ).get_eventual_alias()
             # Only append if non-null. A None reference, may
@@ -2049,7 +2055,7 @@ class FromClauseSegment(BaseSegment):
                 if clause in direct_table_children
                 else clause.get_child("from_expression_element")
             )
-            if alias:
+            for alias in direct_table_aliases:
                 assert table_expr
                 buff.append((table_expr, alias))
         for clause in join_clauses:
diff --git a/src/sqlfluff/dialects/dialect_mariadb.py b/src/sqlfluff/dialects/dialect_mariadb.py
index b7c057f81..4deb9c251 100644
--- a/src/sqlfluff/dialects/dialect_mariadb.py
+++ b/src/sqlfluff/dialects/dialect_mariadb.py
@@ -6,7 +6,6 @@ https://mariadb.com/kb/en/sql-statements-structure/
 from sqlfluff.core.dialects import load_raw_dialect
 from sqlfluff.core.parser import (
     AnyNumberOf,
-    AnySetOf,
     BaseSegment,
     Bracketed,
     Dedent,
@@ -483,17 +482,18 @@ class InsertStatementSegment(BaseSegment):
             optional=True,
         ),
         Ref("BracketedColumnReferenceListGrammar", optional=True),
-        AnySetOf(
-            OneOf(
-                Ref("ValuesClauseSegment"),
-                Ref("SetClauseListSegment"),
-                Ref("SelectStatementSegment"),
-                optional=False,
+        OneOf(
+            Ref("ValuesClauseSegment"),
+            Ref("SetClauseListSegment"),
+            Ref(
+                "SelectableGrammar",
+                terminators=[Ref("ReturningClauseSegment")],
             ),
-            Ref("InsertRowAliasSegment", optional=True),
-            Ref("UpsertClauseListSegment", optional=True),
-            Ref("ReturningClauseSegment", optional=True),
+            optional=True,
         ),
+        Ref("InsertRowAliasSegment", optional=True),
+        Ref("UpsertClauseListSegment", optional=True),
+        Ref("ReturningClauseSegment", optional=True),
     )
 
 
diff --git a/src/sqlfluff/rules/references/RF01.py b/src/sqlfluff/rules/references/RF01.py
index cd0dd44bf..795364ba9 100644
--- a/src/sqlfluff/rules/references/RF01.py
+++ b/src/sqlfluff/rules/references/RF01.py
@@ -9,7 +9,8 @@ from sqlfluff.core.parser import BaseSegment
 from sqlfluff.core.rules import BaseRule, LintResult, RuleContext
 from sqlfluff.core.rules.crawlers import SegmentSeekerCrawler
 from sqlfluff.core.rules.reference import object_ref_matches_table
-from sqlfluff.utils.analysis.query import Query
+from sqlfluff.dialects.dialect_ansi import ObjectReferenceSegment
+from sqlfluff.utils.analysis.query import Query, Selectable
 
 _START_TYPES = [
     "delete_statement",
@@ -70,7 +71,7 @@ class Rule_RF01(BaseRule):
 
     def _eval(self, context: RuleContext) -> list[LintResult]:
         violations: list[LintResult] = []
-        dml_target_table: Optional[tuple[str, ...]] = None
+        dml_target_table: Optional[list[tuple[str, ...]]] = None
         self.logger.debug("Trigger on: %s", context.segment)
         if not context.segment.is_type("select_statement"):
             # Extract first table reference. This will be the target
@@ -79,7 +80,9 @@ class Rule_RF01(BaseRule):
                 context.segment.recursive_crawl("table_reference"), None
             )
             if table_reference:
-                dml_target_table = self._table_ref_as_tuple(table_reference)
+                dml_target_table = self._table_ref_as_tuple(
+                    cast(ObjectReferenceSegment, table_reference)
+                )
 
         self.logger.debug("DML Reference Table: %s", dml_target_table)
         # Verify table references in any SELECT statements found in or
@@ -91,23 +94,32 @@ class Rule_RF01(BaseRule):
         )
         return violations
 
-    @classmethod
-    def _alias_info_as_tuples(cls, alias_info: AliasInfo) -> list[tuple[str, ...]]:
+    def _alias_info_as_tuples(self, alias_info: AliasInfo) -> list[tuple[str, ...]]:
         result: list[tuple[str, ...]] = []
         if alias_info.aliased:
             result.append((alias_info.ref_str,))
         if alias_info.object_reference:
-            result.append(cls._table_ref_as_tuple(alias_info.object_reference))
+            result += self._table_ref_as_tuple(
+                cast(ObjectReferenceSegment, alias_info.object_reference)
+            )
         return result
 
-    @staticmethod
-    def _table_ref_as_tuple(table_reference) -> tuple[str, ...]:
-        return tuple(ref.part for ref in table_reference.iter_raw_references())
+    def _table_ref_as_tuple(
+        self,
+        table_reference: ObjectReferenceSegment,
+    ) -> list[tuple[str, ...]]:
+        return [
+            tuple(ref.part for ref in table_reference.iter_raw_references()),
+            tuple(
+                ref.segments[0].normalize(ref.part)
+                for ref in table_reference.iter_raw_references()
+            ),
+        ]
 
     def _analyze_table_references(
         self,
         query: RF01Query,
-        dml_target_table: Optional[tuple[str, ...]],
+        dml_target_table: Optional[list[tuple[str, ...]]],
         dialect: Dialect,
         violations: list[LintResult],
     ) -> None:
@@ -145,8 +157,9 @@ class Rule_RF01(BaseRule):
                 cast(RF01Query, child), dml_target_table, dialect, violations
             )
 
-    @staticmethod
-    def _should_ignore_reference(reference, selectable) -> bool:
+    def _should_ignore_reference(
+        self, reference: ObjectReferenceSegment, selectable: Selectable
+    ) -> bool:
         ref_path = selectable.selectable.path_to(reference)
         # Ignore references occurring in an "INTO" clause:
         # - They are table references, not column references.
@@ -158,10 +171,13 @@ class Rule_RF01(BaseRule):
         else:
             return False  # pragma: no cover
 
-    @staticmethod
-    def _get_table_refs(ref, dialect):
+    def _get_table_refs(
+        self, ref: ObjectReferenceSegment, dialect: Dialect
+    ) -> list[tuple[ObjectReferenceSegment.ObjectReferencePart, tuple[str, ...]]]:
         """Given ObjectReferenceSegment, determine possible table references."""
-        tbl_refs = []
+        tbl_refs: list[
+            tuple[ObjectReferenceSegment.ObjectReferencePart, tuple[str, ...]]
+        ] = []
         # First, handle any schema.table references.
         for sr, tr in ref.extract_possible_multipart_references(
             levels=[
@@ -170,6 +186,15 @@ class Rule_RF01(BaseRule):
             ]
         ):
             tbl_refs.append((tr, (sr.part, tr.part)))
+            tbl_refs.append(
+                (
+                    tr,
+                    (
+                        sr.segments[0].normalize(sr.part),
+                        tr.segments[0].normalize(tr.part),
+                    ),
+                )
+            )
         # Maybe check for simple table references. Two cases:
         # - For most dialects, skip this if it's a schema+table reference -- the
         #   reference was specific, so we shouldn't ignore that by looking
@@ -186,20 +211,31 @@ class Rule_RF01(BaseRule):
                 level=ref.ObjectReferenceLevel.TABLE
             ):
                 tbl_refs.append((tr, (tr.part,)))
+                tbl_refs.append((tr, (tr.segments[0].normalize(tr.part),)))
         return tbl_refs
 
     def _resolve_reference(
-        self, r, tbl_refs, dml_target_table: Optional[tuple[str, ...]], query: RF01Query
+        self,
+        r: ObjectReferenceSegment,
+        tbl_refs: list[
+            tuple[ObjectReferenceSegment.ObjectReferencePart, tuple[str, ...]]
+        ],
+        dml_target_table: Optional[list[tuple[str, ...]]],
+        query: RF01Query,
     ) -> Optional[LintResult]:
         # Does this query define the referenced table?
         possible_references = [tbl_ref[1] for tbl_ref in tbl_refs]
-        targets = []
+        targets: list[tuple[str, ...]] = []
         for alias in query.aliases:
             targets += self._alias_info_as_tuples(alias)
         for standalone_alias in query.standalone_aliases:
             targets.append((standalone_alias.raw,))
+            targets.append((standalone_alias.raw_normalized(False),))
+        distinct_targets = set(tuple(s.upper() for s in t) for t in targets)
 
-        if len(targets) == 1 and self._dialect_supports_dot_access(query.dialect):
+        if len(distinct_targets) == 1 and self._dialect_supports_dot_access(
+            query.dialect
+        ):
             self.force_enable: bool
             if self.force_enable:
                 # Backwards compatibility.
@@ -220,7 +256,7 @@ class Rule_RF01(BaseRule):
             # No parent query. If there's a DML statement at the root, check its
             # target table or alias.
             elif not dml_target_table or not object_ref_matches_table(
-                possible_references, [dml_target_table]
+                possible_references, dml_target_table
             ):
                 return LintResult(
                     # Return the first segment rather than the string
@@ -232,8 +268,7 @@ class Rule_RF01(BaseRule):
 
         return None
 
-    @staticmethod
-    def _get_implicit_targets(query: RF01Query) -> list[tuple[str, ...]]:
+    def _get_implicit_targets(self, query: RF01Query) -> list[tuple[str, ...]]:
         if query.dialect.name == "sqlite":
             maybe_create_trigger: Optional[BaseSegment] = next(
                 (
@@ -246,19 +281,18 @@ class Rule_RF01(BaseRule):
             if not maybe_create_trigger:
                 return []
             for seg in maybe_create_trigger.segments:
-                if seg.is_type("keyword") and seg.raw_normalized() == "INSERT":
+                if seg.is_type("keyword") and seg.raw_upper == "INSERT":
                     return [("new",)]
-                elif seg.is_type("keyword") and seg.raw_normalized() == "UPDATE":
+                elif seg.is_type("keyword") and seg.raw_upper == "UPDATE":
                     return [("new",), ("old",)]
-                elif seg.is_type("keyword") and seg.raw_normalized() == "DELETE":
+                elif seg.is_type("keyword") and seg.raw_upper == "DELETE":
                     return [("old",)]
                 else:
                     pass  # pragma: no cover
 
         return []
 
-    @staticmethod
-    def _dialect_supports_dot_access(dialect: Dialect) -> bool:
+    def _dialect_supports_dot_access(self, dialect: Dialect) -> bool:
         # Athena:
         # https://docs.aws.amazon.com/athena/latest/ug/filtering-with-dot.html
         # BigQuery:
diff --git a/src/sqlfluff/rules/structure/ST09.py b/src/sqlfluff/rules/structure/ST09.py
index fd67ff346..51e0f24bd 100644
--- a/src/sqlfluff/rules/structure/ST09.py
+++ b/src/sqlfluff/rules/structure/ST09.py
@@ -109,14 +109,12 @@ class Rule_ST09(BaseRule):
             return None
 
         # the first alias comes from the from clause
-        from_expression_alias: str = (
+        from_expression_alias: str = next(
             cast(
                 FromExpressionElementSegment,
                 children.recursive_crawl("from_expression_element")[0],
-            )
-            .get_eventual_alias()
-            .ref_str
-        )
+            ).get_eventual_alias()
+        ).ref_str
 
         table_aliases.append(from_expression_alias)
 
@@ -171,6 +169,7 @@ class Rule_ST09(BaseRule):
 
         # STEP 4.
         fixes: list[LintFix] = []
+        anchor_segment = context.segment  # Default anchor
 
         for subcondition in column_operator_column_subconditions:
             comparison_operator = subcondition[1]
@@ -206,6 +205,17 @@ class Rule_ST09(BaseRule):
                 table_aliases.index(first_table) < table_aliases.index(second_table)
                 and self.preferred_first_table_in_join_clause == "later"
             ):
+                # Use the first column reference as anchor if it has a literal
+                # position marker. This ensures the violation is anchored to
+                # a literal segment which won't be filtered out in templated
+                # code.
+                if (
+                    not fixes
+                    and first_column_reference.pos_marker
+                    and first_column_reference.pos_marker.is_literal()
+                ):
+                    anchor_segment = first_column_reference
+
                 fixes = (
                     fixes
                     + [
@@ -249,7 +259,7 @@ class Rule_ST09(BaseRule):
         # STEP 5.b.
         else:
             return LintResult(
-                anchor=context.segment,
+                anchor=anchor_segment,
                 fixes=fixes,
                 description=(
                     "Joins should list the table referenced "
diff --git a/src/sqlfluff/utils/analysis/select.py b/src/sqlfluff/utils/analysis/select.py
index 3ce04258f..9f83be91f 100644
--- a/src/sqlfluff/utils/analysis/select.py
+++ b/src/sqlfluff/utils/analysis/select.py
@@ -147,7 +147,7 @@ def get_aliases_from_select(
     # We only want table aliases, so filter out aliases for value table
     # functions, lambda parameters and pivot columns.
     standalone_aliases: list[BaseSegment] = []
-    standalone_aliases += _get_pivot_table_columns(segment, dialect)
+    standalone_aliases += _get_pivot_table_aliases(segment, dialect)
     standalone_aliases += _get_lambda_argument_columns(segment, dialect)
 
     table_aliases = []
@@ -179,7 +179,7 @@ def _has_value_table_function(
     return False
 
 
-def _get_pivot_table_columns(
+def _get_pivot_table_aliases(
     segment: BaseSegment, dialect: Optional[Dialect]
 ) -> list[BaseSegment]:
     if not dialect:
@@ -187,20 +187,15 @@ def _get_pivot_table_columns(
         # we don't have it, assume the clause does not have a pivot table
         return []  # pragma: no cover
 
-    fc = segment.recursive_crawl("from_pivot_expression")
-    if not fc:
-        # If there's no pivot clause then just abort.
-        return []  # pragma: no cover
-
-    pivot_table_column_aliases: list[BaseSegment] = []
-
-    for pivot_table_column_alias in segment.recursive_crawl("pivot_column_reference"):
-        if pivot_table_column_alias.raw not in [
-            a.raw for a in pivot_table_column_aliases
-        ]:
-            pivot_table_column_aliases.append(pivot_table_column_alias)
+    pivot_table_aliases: list[BaseSegment] = []
+    for fc in segment.recursive_crawl("from_pivot_expression"):
+        for pivot_table_alias in fc.recursive_crawl(
+            "pivot_column_reference", "table_reference"
+        ):
+            if pivot_table_alias.raw not in [a.raw for a in pivot_table_aliases]:
+                pivot_table_aliases.append(pivot_table_alias)
 
-    return pivot_table_column_aliases
+    return pivot_table_aliases
 
 
 # Lambda arguments,


Test patch:
diff --git a/test/api/simple_test.py b/test/api/simple_test.py
index 75627c7a4..e24127a15 100644
--- a/test/api/simple_test.py
+++ b/test/api/simple_test.py
@@ -1,6 +1,7 @@
 """Tests for simple use cases of the public api."""
 
 import json
+from contextlib import nullcontext
 
 import pytest
 
@@ -523,6 +524,107 @@ def test__api__config_path():
     assert parsed == expected_parsed
 
 
+@pytest.mark.parametrize(
+    "dialect,config_path,expectation",
+    [
+        (None, "test/fixtures/api/config_dialect/.sqlfluff", nullcontext()),
+        (None, None, pytest.raises(APIParsingError)),
+        ("duckdb", None, nullcontext()),
+        ("ansi", None, pytest.raises(APIParsingError)),
+    ],
+)
+def test__api__parse_dialect_config_path(dialect, config_path, expectation):
+    """Test that we can load a dialect from a config file in the Simple API parse."""
+    # Load test SQL file.
+    with open("test/fixtures/api/config_dialect/config_dialect.sql", "r") as f:
+        sql = f.read()
+
+    # Load in expected result.
+    with open("test/fixtures/api/config_dialect/config_dialect_parse.json", "r") as f:
+        expected_parsed = json.load(f)
+
+    was_parsed = False
+    with expectation:
+        # Pass a config path to the Simple API.
+        parsed = sqlfluff.parse(
+            sql,
+            dialect=dialect,
+            config_path=config_path,
+        )
+        was_parsed = True
+        # Compare JSON from parse to expected result.
+        assert parsed == expected_parsed
+
+    if isinstance(expectation, nullcontext):
+        assert was_parsed
+    else:
+        assert not was_parsed
+
+
+@pytest.mark.parametrize(
+    "dialect,config_path,fails",
+    [
+        (None, "test/fixtures/api/config_dialect/.sqlfluff", False),
+        (None, None, True),
+        ("duckdb", None, False),
+        ("ansi", None, True),
+    ],
+)
+def test__api__lint_dialect_config_path(dialect, config_path, fails):
+    """Test that we can load a dialect from a config file in the Simple API lint."""
+    # Load test SQL file.
+    with open("test/fixtures/api/config_dialect/config_dialect.sql", "r") as f:
+        sql = f.read()
+
+    # Load in expected result.
+    issue_type = "prs" if fails else "lt01"
+    with open(
+        f"test/fixtures/api/config_dialect/config_dialect_lint_{issue_type}.json", "r"
+    ) as f:
+        expected_lint = json.load(f)
+
+    # Pass a config path to the Simple API.
+    linted = sqlfluff.lint(
+        sql,
+        dialect=dialect,
+        config_path=config_path,
+    )
+    # Compare JSON from lint to expected result.
+    assert linted == expected_lint
+
+
+@pytest.mark.parametrize(
+    "dialect,config_path,fails",
+    [
+        (None, "test/fixtures/api/config_dialect/.sqlfluff", False),
+        (None, None, True),
+        ("duckdb", None, False),
+        ("ansi", None, True),
+    ],
+)
+def test__api__fix_dialect_config_path(dialect, config_path, fails):
+    """Test that we can load a dialect from a config file in the Simple API fix."""
+    # Load test SQL file.
+    with open("test/fixtures/api/config_dialect/config_dialect.sql", "r") as f:
+        sql = f.read()
+
+    # Load in expected result.
+    if fails:
+        expected_fix = sql
+    else:
+        with open("test/fixtures/api/config_dialect/config_dialect_fix.sql", "r") as f:
+            expected_fix = f.read()
+
+    # Pass a config path to the Simple API.
+    fixed = sqlfluff.fix(
+        sql,
+        dialect=dialect,
+        config_path=config_path,
+    )
+    # Compare to expected result.
+    assert fixed == expected_fix
+
+
 @pytest.mark.parametrize(
     "kwargs,expected",
     [
diff --git a/test/cli/commands_test.py b/test/cli/commands_test.py
index 1efe4de9f..396ea61a7 100644
--- a/test/cli/commands_test.py
+++ b/test/cli/commands_test.py
@@ -2256,7 +2256,7 @@ class TestProgressBars:
 multiple_expected_output = """==== finding fixable violations ====
 == [test/fixtures/linter/multiple_sql_errors.sql] FAIL
 L:  12 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
-L:  40 | P:  10 | ST09 | Joins should list the table referenced earlier first.
+L:  44 | P:  12 | ST09 | Joins should list the table referenced earlier first.
                        | [structure.join_condition_order]
 ==== fixing violations ====
 2 fixable linting violations found
diff --git a/test/fixtures/api/config_dialect/config_dialect.sql b/test/fixtures/api/config_dialect/config_dialect.sql
new file mode 100644
index 000000000..a9ba3d6e3
--- /dev/null
+++ b/test/fixtures/api/config_dialect/config_dialect.sql
@@ -0,0 +1,2 @@
+FROM  tab
+WHERE a = 1;
diff --git a/test/fixtures/api/config_dialect/config_dialect_fix.sql b/test/fixtures/api/config_dialect/config_dialect_fix.sql
new file mode 100644
index 000000000..2f08f0fd2
--- /dev/null
+++ b/test/fixtures/api/config_dialect/config_dialect_fix.sql
@@ -0,0 +1,2 @@
+FROM tab
+WHERE a = 1;
diff --git a/test/fixtures/api/config_dialect/config_dialect_lint_lt01.json b/test/fixtures/api/config_dialect/config_dialect_lint_lt01.json
new file mode 100644
index 000000000..fc93a6fde
--- /dev/null
+++ b/test/fixtures/api/config_dialect/config_dialect_lint_lt01.json
@@ -0,0 +1,26 @@
+[
+    {
+        "code": "LT01",
+        "description": "Expected only single space before naked identifier. Found '  '.",
+        "end_file_pos": 6,
+        "end_line_no": 1,
+        "end_line_pos": 7,
+        "fixes": [
+            {
+                "edit": " ",
+                "end_file_pos": 6,
+                "end_line_no": 1,
+                "end_line_pos": 7,
+                "start_file_pos": 4,
+                "start_line_no": 1,
+                "start_line_pos": 5,
+                "type": "replace"
+            }
+        ],
+        "name": "layout.spacing",
+        "start_file_pos": 4,
+        "start_line_no": 1,
+        "start_line_pos": 5,
+        "warning": false
+    }
+]
diff --git a/test/fixtures/api/config_dialect/config_dialect_lint_prs.json b/test/fixtures/api/config_dialect/config_dialect_lint_prs.json
new file mode 100644
index 000000000..326eab091
--- /dev/null
+++ b/test/fixtures/api/config_dialect/config_dialect_lint_prs.json
@@ -0,0 +1,14 @@
+[
+    {
+        "code": "PRS",
+        "description": "Line 1, Position 1: Found unparsable section: 'FROM  tab\\nWHERE a = 1;'",
+        "end_file_pos": 22,
+        "end_line_no": 2,
+        "end_line_pos": 13,
+        "name": "",
+        "start_file_pos": 0,
+        "start_line_no": 1,
+        "start_line_pos": 1,
+        "warning": false
+    }
+]
diff --git a/test/fixtures/api/config_dialect/config_dialect_parse.json b/test/fixtures/api/config_dialect/config_dialect_parse.json
new file mode 100644
index 000000000..f9b565609
--- /dev/null
+++ b/test/fixtures/api/config_dialect/config_dialect_parse.json
@@ -0,0 +1,49 @@
+{
+    "file": {
+        "statement": {
+            "select_statement": {
+                "from_clause": {
+                    "keyword": "FROM",
+                    "whitespace": "  ",
+                    "from_expression": {
+                        "from_expression_element": {
+                            "table_expression": {
+                                "table_reference": {
+                                    "naked_identifier": "tab"
+                                }
+                            }
+                        }
+                    }
+                },
+                "newline": "\n",
+                "where_clause": {
+                    "keyword": "WHERE",
+                    "whitespace": " ",
+                    "expression": [
+                        {
+                            "column_reference": {
+                                "naked_identifier": "a"
+                            }
+                        },
+                        {
+                            "whitespace": " "
+                        },
+                        {
+                            "comparison_operator": {
+                                "raw_comparison_operator": "="
+                            }
+                        },
+                        {
+                            "whitespace": " "
+                        },
+                        {
+                            "numeric_literal": "1"
+                        }
+                    ]
+                }
+            }
+        },
+        "statement_terminator": ";",
+        "newline": "\n"
+    }
+}
diff --git a/test/fixtures/dialects/mariadb/insert.sql b/test/fixtures/dialects/mariadb/insert.sql
index 66fd84e32..7964d9024 100644
--- a/test/fixtures/dialects/mariadb/insert.sql
+++ b/test/fixtures/dialects/mariadb/insert.sql
@@ -25,3 +25,5 @@ ON DUPLICATE KEY UPDATE c = t2.a+t2.b;
 
 INSERT INTO t1 SET a=1,b=2,c=3 AS t2(m,n,p)
 ON DUPLICATE KEY UPDATE c = m+n, b = n+p;
+
+INSERT INTO T1 (A) WITH X AS (SELECT 1 AS A) SELECT A FROM X;
diff --git a/test/fixtures/dialects/mariadb/insert.yml b/test/fixtures/dialects/mariadb/insert.yml
index 088955659..3d48d12dc 100644
--- a/test/fixtures/dialects/mariadb/insert.yml
+++ b/test/fixtures/dialects/mariadb/insert.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: 651a0d14bda891e258a0bb7da14c4054db6c1661870c16fe2ad13aeafa5c3b8a
+_hash: b33afa54b9ce472012b80da761ff903d28f8107cf176b7d19ecf4e4f2cb747db
 file:
 - statement:
     insert_statement:
@@ -528,3 +528,44 @@ file:
           - column_reference:
               naked_identifier: p
 - statement_terminator: ;
+- statement:
+    insert_statement:
+    - keyword: INSERT
+    - keyword: INTO
+    - table_reference:
+        naked_identifier: T1
+    - bracketed:
+        start_bracket: (
+        column_reference:
+          naked_identifier: A
+        end_bracket: )
+    - with_compound_statement:
+        keyword: WITH
+        common_table_expression:
+          naked_identifier: X
+          keyword: AS
+          bracketed:
+            start_bracket: (
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  numeric_literal: '1'
+                  alias_expression:
+                    keyword: AS
+                    naked_identifier: A
+            end_bracket: )
+        select_statement:
+          select_clause:
+            keyword: SELECT
+            select_clause_element:
+              column_reference:
+                naked_identifier: A
+          from_clause:
+            keyword: FROM
+            from_expression:
+              from_expression_element:
+                table_expression:
+                  table_reference:
+                    naked_identifier: X
+- statement_terminator: ;
diff --git a/test/fixtures/rules/std_rule_cases/RF01.yml b/test/fixtures/rules/std_rule_cases/RF01.yml
index aec0377b0..47e9d57da 100644
--- a/test/fixtures/rules/std_rule_cases/RF01.yml
+++ b/test/fixtures/rules/std_rule_cases/RF01.yml
@@ -467,3 +467,52 @@ test_postgres_pass_dot_inside_quotes:
   configs:
     core:
       dialect: postgres
+
+test_tsql_bracketed_identifiers_pass:
+  pass_str: |
+    select
+        [t1].[field1],
+        [t2].[field2],
+        t1.field3,
+        "t1".field4,
+        [t3].field5,
+        "t3".field6,
+        t3.field7
+    from [table1] as [t1]
+    inner join [table2] as [t2]
+        on [t2].[pk] = [t1].[pk]
+    inner join [table3] as t3
+        on t3.pk = t1.pk
+  configs:
+    core:
+      dialect: tsql
+
+test_bigquery_unnest_with_offset_pass:
+  pass_str: |
+    select
+        ix,
+        v
+    from t
+    left join
+        unnest(t.value_list) as v
+        with offset as ix
+  configs:
+    core:
+      dialect: bigquery
+
+test_pass_tsql_pivot_table_alias:
+  pass_str: |
+    select
+        pvt.cl1
+        , pvt.cl2
+        , pvt.cl3
+        , [1] as lvl_1
+        , [2] as lvl_2
+        , [3] as lvl_3
+    from
+        levels as lvl
+    pivot
+    (max(value) for rn in([1], [2], [3]) ) as pvt
+  configs:
+    core:
+      dialect: tsql
diff --git a/test/fixtures/rules/std_rule_cases/ST05.yml b/test/fixtures/rules/std_rule_cases/ST05.yml
index 09d13e81f..2b59f4453 100644
--- a/test/fixtures/rules/std_rule_cases/ST05.yml
+++ b/test/fixtures/rules/std_rule_cases/ST05.yml
@@ -1083,3 +1083,61 @@ test_pass_nested_table_function_with_subquery:
   configs:
     core:
       dialect: snowflake
+
+test_fail_mariadb_insert_cte_select:
+  fail_str: |
+    INSERT INTO test_hold (
+        uid,
+        date_created,
+        date_updated,
+        earliest_activity,
+        latest_activity,
+        reason
+    )
+    SELECT
+        uid,
+        sub.earliest_date_created AS date_created,
+        sub.latest_date_updated AS date_updated,
+        earliest_activity,
+        latest_activity,
+        sub.reason
+    FROM test AS t
+    INNER JOIN (
+        SELECT
+            reason,
+            MIN(date_created) AS earliest_date_created,
+            MAX(date_updated) AS latest_date_updated
+        FROM test
+        GROUP BY reason
+    ) AS sub ON t.reason = sub.reason
+    GROUP BY t.reason;
+  fix_str: |
+    INSERT INTO test_hold (
+        uid,
+        date_created,
+        date_updated,
+        earliest_activity,
+        latest_activity,
+        reason
+    )
+    WITH sub AS (
+        SELECT
+            reason,
+            MIN(date_created) AS earliest_date_created,
+            MAX(date_updated) AS latest_date_updated
+        FROM test
+        GROUP BY reason
+    )
+    SELECT
+        uid,
+        sub.earliest_date_created AS date_created,
+        sub.latest_date_updated AS date_updated,
+        earliest_activity,
+        latest_activity,
+        sub.reason
+    FROM test AS t
+    INNER JOIN sub ON t.reason = sub.reason
+    GROUP BY t.reason;
+  configs:
+    core:
+      dialect: mariadb
diff --git a/test/fixtures/rules/std_rule_cases/ST09.yml b/test/fixtures/rules/std_rule_cases/ST09.yml
index 56d18583d..d0a3a15da 100644
--- a/test/fixtures/rules/std_rule_cases/ST09.yml
+++ b/test/fixtures/rules/std_rule_cases/ST09.yml
@@ -421,3 +421,56 @@ test_fail_sparksql_lt_eq_gt_operator:
   configs:
     core:
       dialect: sparksql
+
+test_pass_jinja_templated_tables_correct_order:
+  pass_str: |
+    select
+        t1.a,
+        t2.b
+    from `{{ params.project_id }}.my_dataset.table_1` as t1
+    left join `{{ params.project_id }}.my_dataset.table_2` as t2
+        on t1.id = t2.id
+  configs:
+    core:
+      dialect: bigquery
+    templater:
+      jinja:
+        context:
+          params:
+            project_id: my_project
+
+test_fail_jinja_templated_issue_5506_reproduction:
+  # This test reproduces the exact issue from GitHub #5506
+  fail_str: |
+    SELECT
+      t1.a
+      , t1.b
+      , t2.c
+    FROM `{{ params.project_id }}.my_dataset.table_1` AS t1
+    LEFT JOIN `{{ params.project_id }}.my_dataset.table_2` AS t2
+      ON t1.id = t2.id
+    LEFT JOIN `{{ params.project_id }}.my_dataset.table_3` AS t3
+      ON t3.id = t1.id
+
+  fix_str: |
+    SELECT
+      t1.a
+      , t1.b
+      , t2.c
+    FROM `{{ params.project_id }}.my_dataset.table_1` AS t1
+    LEFT JOIN `{{ params.project_id }}.my_dataset.table_2` AS t2
+      ON t1.id = t2.id
+    LEFT JOIN `{{ params.project_id }}.my_dataset.table_3` AS t3
+      ON t1.id = t3.id
+
+  configs:
+    core:
+      dialect: bigquery
+    templater:
+      jinja:
+        context:
+          params:
+            project_id: my_project
+    rules:
+      structure.join_condition_order:
+        preferred_first_table_in_join_clause: earlier