Repository: sqlfluff/sqlfluff
Issue category: Bug Report

Issue:
Support Request: RF01 rule
### Search before asking

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


### Description

It seems sqlfluff can’t handle the dot notation we use for Redshift SUPER data type. RF01 rule (https://docs.sqlfluff.com/en/stable/rules.html#rule-references.from).

Usage example:

```
w_extract as (
    select
        pj.hash_key,
        pj.snapshot_date,
        trim(cast(pj.metadata_super."amount" as numeric))                                       as amount,
        trim(cast(pj.metadata_super."leadAge" as numeric))                                      as lead_age,
        trim(cast(pj.metadata_super."leadType" as varchar))                                     as lead_type,
        trim(cast(pj.metadata_super."leadValue" as numeric))                                    as lead_value,
        trim(cast(pj.metadata_super."randValue" as numeric))                                    as rand_value,
        trim(cast(pj.metadata_super."callReason" as varchar))                                   as call_reason
    from w_parse_json as pj
)
```
Failures:
```
L:  49 | P:  22 | RF01 | Reference 'pj.metadata_super."amount"' refers to
                       | table/view not found in the FROM clause or found in
                       | ancestor statement. [references.from]
L:  50 | P:  22 | RF01 | Reference 'pj.metadata_super."leadAge"' refers to
                       | table/view not found in the FROM clause or found in
                       | ancestor statement. [references.from]
L:  51 | P:  22 | RF01 | Reference 'pj.metadata_super."leadType"' refers to
                       | table/view not found in the FROM clause or found in
                       | ancestor statement. [references.from]
L:  52 | P:  22 | RF01 | Reference 'pj.metadata_super."leadValue"' refers to
                       | table/view not found in the FROM clause or found in
                       | ancestor statement. [references.from]
L:  53 | P:  22 | RF01 | Reference 'pj.metadata_super."randValue"' refers to
                       | table/view not found in the FROM clause or found in
                       | ancestor statement. [references.from]
L:  54 | P:  22 | RF01 | Reference 'pj.metadata_super."callReason"' refers to
                       | table/view not found in the FROM clause or found in
                       | ancestor statement. [references.from]
```

Can we improve this so this is automatically handled via ```sqlfluff fix```? Currently, this is an unfixable violation and we simply have to ignore it. 

### Use case

_No response_

### Dialect

-

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

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

### Code of Conduct

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



Hints:
We also see this, for us I suspect this is partially due to casing mattering for some of our keys within the SUPER field. If the identifiers are all unquoted I believe they are interpreted as lower case which means the data is not found
same for `athena` dialect when accessing nesting for `struct` datatype 
```sql
select
    a.complex.key,
    a.complex.structure.val
from sch.t1
```
causes
```verilog
L:   2 | P:   7 | RF01 | Reference 'a.complex.key' refers to table/view not found in the FROM clause or found in ancestor statement. [references.from]
L:   3 | P:  15 | RF01 | Reference 'a.complex.structure.val' refers to table/view not found in the FROM clause or found in ancestor statement. [references.from]
```

Patch:
diff --git a/src/sqlfluff/rules/references/RF01.py b/src/sqlfluff/rules/references/RF01.py
index b20bcf3f22a..3ef60bbd8ed 100644
--- a/src/sqlfluff/rules/references/RF01.py
+++ b/src/sqlfluff/rules/references/RF01.py
@@ -29,6 +29,7 @@ class RF01Query(Query):
 
     aliases: List[AliasInfo] = field(default_factory=list)
     standalone_aliases: List[BaseSegment] = field(default_factory=list)
+    parent_stack: Tuple[BaseSegment, ...] = field(default_factory=tuple)
 
 
 class Rule_RF01(BaseRule):
@@ -70,25 +71,8 @@ class Rule_RF01(BaseRule):
     # If any of the parents would have also triggered the rule, don't fire
     # because they will more accurately process any internal references.
     crawl_behaviour = SegmentSeekerCrawler(set(_START_TYPES), allow_recurse=False)
-    _dialects_disabled_by_default = [
-        "bigquery",
-        "databricks",
-        "hive",
-        "redshift",
-        "soql",
-        "sparksql",
-    ]
 
     def _eval(self, context: RuleContext) -> List[LintResult]:
-        # Config type hints
-        self.force_enable: bool
-
-        if (
-            context.dialect.name in self._dialects_disabled_by_default
-            and not self.force_enable
-        ):
-            return []
-
         violations: List[LintResult] = []
         dml_target_table: Optional[Tuple[str, ...]] = None
         self.logger.debug("Trigger on: %s", context.segment)
@@ -104,7 +88,8 @@ def _eval(self, context: RuleContext) -> List[LintResult]:
         self.logger.debug("DML Reference Table: %s", dml_target_table)
         # Verify table references in any SELECT statements found in or
         # below context.segment in the parser tree.
-        query = RF01Query.from_segment(context.segment, context.dialect)
+        query: RF01Query = RF01Query.from_segment(context.segment, context.dialect)
+        query.parent_stack = context.parent_stack
         self._analyze_table_references(
             query, dml_target_table, context.dialect, violations
         )
@@ -209,7 +194,7 @@ def _get_table_refs(ref, dialect):
 
     def _resolve_reference(
         self, r, tbl_refs, dml_target_table: Optional[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 = []
@@ -217,6 +202,19 @@ def _resolve_reference(
             targets += self._alias_info_as_tuples(alias)
         for standalone_alias in query.standalone_aliases:
             targets.append((standalone_alias.raw,))
+
+        if len(targets) == 1 and self._dialect_supports_dot_access(query.dialect):
+            self.force_enable: bool
+            if self.force_enable:
+                # Backwards compatibility.
+                # Nowadays "force_enable" is more of "strict" mode,
+                # for dialects with dot access.
+                pass
+            else:
+                return None
+
+        targets += self._get_implicit_targets(query)
+
         if not object_ref_matches_table(possible_references, targets):
             # No. Check the parent query, if there is one.
             if query.parent:
@@ -235,3 +233,51 @@ def _resolve_reference(
                     "not found in the FROM clause or found in ancestor "
                     "statement.",
                 )
+
+        return None
+
+    @staticmethod
+    def _get_implicit_targets(query: RF01Query) -> List[Tuple[str, ...]]:
+        if query.dialect.name == "sqlite":
+            maybe_create_trigger: Optional[BaseSegment] = next(
+                (
+                    seg
+                    for seg in reversed(query.parent_stack)
+                    if seg.is_type("create_trigger")
+                ),
+                None,
+            )
+            if not maybe_create_trigger:
+                return []
+            for seg in maybe_create_trigger.segments:
+                if seg.is_type("keyword") and seg.raw_normalized() == "INSERT":
+                    return [("new",)]
+                elif seg.is_type("keyword") and seg.raw_normalized() == "UPDATE":
+                    return [("new",), ("old",)]
+                elif seg.is_type("keyword") and seg.raw_normalized() == "DELETE":
+                    return [("old",)]
+                else:
+                    pass  # pragma: no cover
+
+        return []
+
+    @staticmethod
+    def _dialect_supports_dot_access(dialect: Dialect) -> bool:
+        # Athena:
+        # https://docs.aws.amazon.com/athena/latest/ug/filtering-with-dot.html
+        # BigQuery:
+        # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#field_access_operator
+        # Databricks:
+        # https://docs.databricks.com/en/sql/language-manual/functions/dotsign.html
+        # Redshift:
+        # https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
+        # TODO: all doc links to all referenced dialects
+        return dialect.name in (
+            "athena",
+            "bigquery",
+            "databricks",
+            "hive",
+            "redshift",
+            "soql",
+            "sparksql",
+        )


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/RF01.yml b/test/fixtures/rules/std_rule_cases/RF01.yml
index 93eed5482d1..01253a0895b 100644
--- a/test/fixtures/rules/std_rule_cases/RF01.yml
+++ b/test/fixtures/rules/std_rule_cases/RF01.yml
@@ -328,3 +328,99 @@ test_pass_trino_lambda_expression:
   configs:
     core:
       dialect: trino
+
+test_athena_ignore_rule_if_single_table:
+  pass_str: |
+    select
+        a.complex.key,
+        a.complex.structure.val
+    from sch.t1
+  configs:
+    core:
+      dialect: athena
+
+test_athena_apply_rule_if_single_table_and_force_enable:
+  fail_str: |
+    select
+        a.complex.key,
+        a.complex.structure.val
+    from sch.t1
+  configs:
+    core:
+      dialect: athena
+    rules:
+      references.from:
+        force_enable: true
+
+test_athena_apply_rule_if_multiple_tables:
+  fail_str: |
+    select
+        a.complex.key,
+        a.complex.structure.val
+    from sch.table1 as t1
+    left join sch.table2 as t2
+    on t1.id = t2.id
+  configs:
+    core:
+      dialect: athena
+
+test_athena_apply_rule_if_one_aliased_table:
+  fail_str: |
+    select
+        a.complex.key,
+        a.complex.structure.val
+    from sch.table1 as t1
+  configs:
+    core:
+      dialect: athena
+
+test_sqlite_create_trigger_after_insert_reference:
+  # https://github.com/sqlfluff/sqlfluff/issues/6402
+  # https://www.sqlite.org/lang_createtrigger.html
+  pass_str: |
+    CREATE TRIGGER x
+    AFTER INSERT
+    ON y
+    BEGIN
+    SELECT 1 WHERE new.xyz = 3;
+    END
+  configs:
+    core:
+      dialect: sqlite
+
+test_sqlite_create_trigger_after_delete_reference:
+  # https://github.com/sqlfluff/sqlfluff/issues/6402
+  # https://www.sqlite.org/lang_createtrigger.html
+  pass_str: |
+    CREATE TRIGGER x
+    AFTER DELETE
+    ON y
+    BEGIN
+    SELECT 1 WHERE old.xyz = 3;
+    END
+  configs:
+    core:
+      dialect: sqlite
+
+test_sqlite_create_trigger_after_update_reference:
+  # https://github.com/sqlfluff/sqlfluff/issues/6402
+  # https://www.sqlite.org/lang_createtrigger.html
+  pass_str: |
+    CREATE TRIGGER x
+    AFTER UPDATE
+    ON y
+    BEGIN
+    SELECT 1 WHERE old.xyz != new.xyz;
+    END;
+  configs:
+    core:
+      dialect: sqlite
+
+test_sqlite_outside_of_trigger:
+  # https://github.com/sqlfluff/sqlfluff/issues/6402
+  # https://www.sqlite.org/lang_createtrigger.html
+  fail_str: |
+    SELECT foo FROM bar WHERE old.xyz != new.xyz;
+  configs:
+    core:
+      dialect: sqlite