Repository: sqlfluff/sqlfluff

Issue:
Fixes for ST05 not applied, as it would result in an unparsable file. Please report this as a bug with a minimal query which demonstrates this warning.
### Search before asking

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


### What Happened

See title for output of running sqlfluff over a complex query, looks like I should report this as a bug

### Expected Behaviour

Not sure; should this query be fixable with sqlfluff?

### Observed Behaviour

Modified query that would cause this:

```
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;
```

### How to reproduce

See the above box

### Dialect

mariadb

### Version

sqlfluff - 3.4.0
python - 3.11.13

### Configuration

```
[sqlfluff]
dialect = mariadb
exclude_rules = CV08, RF01, RF02, RF04, RF05
fix_even_unparsable = True
large_file_skip_byte_limit = 0
max_line_length = 120

[sqlfluff:indentation]
allow_implicit_indents = True

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper
```

### 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:

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/dialects/dialect_tsql.py b/src/sqlfluff/dialects/dialect_tsql.py
index 3800032af..63fe15504 100644
--- a/src/sqlfluff/dialects/dialect_tsql.py
+++ b/src/sqlfluff/dialects/dialect_tsql.py
@@ -260,7 +260,7 @@ tsql_dialect.patch_lexer_matchers(
             ),
         ),
         RegexLexer(
-            "word", r"[0-9a-zA-Z_#@]+", WordSegment
+            "word", r"[0-9a-zA-Z_#@\p{L}]+", WordSegment
         ),  # overriding to allow hash mark and at-sign in code
     ]
 )
@@ -418,7 +418,7 @@ tsql_dialect.replace(
     NakedIdentifierSegment=SegmentGenerator(
         # Generate the anti template from the set of reserved keywords
         lambda dialect: RegexParser(
-            r"[A-Z_][A-Z0-9_@$#]*",
+            r"[A-Z_\p{L}][A-Z0-9_@$#\p{L}]*",
             IdentifierSegment,
             type="naked_identifier",
             anti_template=r"^("
@@ -3573,13 +3573,16 @@ class AlterTableStatementSegment(BaseSegment):
                     Ref("TableConstraintSegment"),
                 ),
                 Sequence(
-                    OneOf(
-                        "CHECK",
-                        "DROP",
-                    ),
+                    "CHECK",
                     "CONSTRAINT",
                     Ref("ObjectReferenceSegment"),
                 ),
+                Sequence(
+                    "DROP",
+                    "CONSTRAINT",
+                    Ref("IfExistsGrammar", optional=True),
+                    Ref("ObjectReferenceSegment"),
+                ),
                 # Rename
                 Sequence(
                     "RENAME",
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..418cf952a 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)
 
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/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/dialects/tsql/alter_table.sql b/test/fixtures/dialects/tsql/alter_table.sql
index 8b8bd3d25..928e64846 100644
--- a/test/fixtures/dialects/tsql/alter_table.sql
+++ b/test/fixtures/dialects/tsql/alter_table.sql
@@ -41,6 +41,9 @@ GO
 ALTER TABLE Production.TransactionHistoryArchive
 DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
 
+ALTER TABLE Production.TransactionHistoryArchive
+DROP CONSTRAINT IF EXISTS PK_TransactionHistoryArchive_TransactionID
+
 ALTER TABLE [Production].[ProductCostHistory]
 WITH CHECK ADD CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID])
 REFERENCES [Production].[Product] ([ProductID])
diff --git a/test/fixtures/dialects/tsql/alter_table.yml b/test/fixtures/dialects/tsql/alter_table.yml
index 7532d4c0d..0271bc178 100644
--- a/test/fixtures/dialects/tsql/alter_table.yml
+++ b/test/fixtures/dialects/tsql/alter_table.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: 2a0d700bd5dc5418ed964e623a378ff5053d0abcadd537d2e9fb58905c6321c0
+_hash: 48376aa83f4f699ff9589a73506d9489f3d1d10da0ed466406965e333d5688a7
 file:
 - batch:
     statement:
@@ -318,6 +318,20 @@ file:
       - keyword: CONSTRAINT
       - object_reference:
           naked_identifier: PK_TransactionHistoryArchive_TransactionID
+  - statement:
+      alter_table_statement:
+      - keyword: ALTER
+      - keyword: TABLE
+      - table_reference:
+        - naked_identifier: Production
+        - dot: .
+        - naked_identifier: TransactionHistoryArchive
+      - keyword: DROP
+      - keyword: CONSTRAINT
+      - keyword: IF
+      - keyword: EXISTS
+      - object_reference:
+          naked_identifier: PK_TransactionHistoryArchive_TransactionID
   - statement:
       alter_table_statement:
       - keyword: ALTER
diff --git a/test/fixtures/dialects/tsql/select.sql b/test/fixtures/dialects/tsql/select.sql
index 2b6e9dbc4..5c13def98 100644
--- a/test/fixtures/dialects/tsql/select.sql
+++ b/test/fixtures/dialects/tsql/select.sql
@@ -121,3 +121,6 @@ select
     'Tabellen' as Objekt,
     Count(*) as Anzahl
 from dbo.sql_modules;
+
+-- naked identifier with extended Unicode characters
+select field1 AS 日期差多少天;
diff --git a/test/fixtures/dialects/tsql/select.yml b/test/fixtures/dialects/tsql/select.yml
index a2905534d..074bcb0ac 100644
--- a/test/fixtures/dialects/tsql/select.yml
+++ b/test/fixtures/dialects/tsql/select.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: 753fa17f824e34805cf8b5bf8664984d8769fcd2c1f3e316ff534e58b12f71af
+_hash: a6ed2e145300008a101e47d3425f7c7b05d44b49ab2a940d00a630bb8974086e
 file:
   batch:
   - statement:
@@ -981,3 +981,14 @@ file:
                 - dot: .
                 - naked_identifier: sql_modules
           statement_terminator: ;
+  - statement:
+      select_statement:
+        select_clause:
+          keyword: select
+          select_clause_element:
+            column_reference:
+              naked_identifier: field1
+            alias_expression:
+              keyword: AS
+              naked_identifier: 日期差多少天
+        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