Repository: sqlfluff/sqlfluff
Issue category: Feature Request

Issue:
DuckDB needs to be added to list of dialects supporting dot-based struct access
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

The following query

```sql
SELECT ex.x.hi
FROM (SELECT { 'hi': 'there' } AS x) AS ex
```

returns an `RF01` with

```
Reference 'ex.x.hi' refers to table/view not found in the FROM clause or found in ancestor statement.
```

### Expected Behaviour

The query above is valid DuckDB, and should return `'there'`, so there should be no errors. 

### Observed Behaviour

See above.

### How to reproduce

```python
import sqlfluff; from sqlfluff.core import FluffConfig, Linter
Linter(config=FluffConfig(overrides={"dialect": "duckdb"})).lint_string("SELECT ex.x.hi\nFROM (SELECT { 'hi': 'there' } AS x) AS ex\n")
```

The following patch would fix the problem, I tried to directly open a PR but I guess I need to be a "Collaborator" first!

```diff
diff --git a/src/sqlfluff/rules/references/RF01.py b/src/sqlfluff/rules/references/RF01.py
index 3ef60bbd8..71717aea6 100644
--- a/src/sqlfluff/rules/references/RF01.py
+++ b/src/sqlfluff/rules/references/RF01.py
@@ -269,6 +269,8 @@ class Rule_RF01(BaseRule):
         # 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
+        # DuckDB:
+        # https://duckdb.org/docs/sql/data_types/struct#retrieving-from-structs
         # Redshift:
         # https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
         # TODO: all doc links to all referenced dialects
@@ -276,6 +278,7 @@ class Rule_RF01(BaseRule):
             "athena",
             "bigquery",
             "databricks",
+            "duckdb",
             "hive",
             "redshift",
             "soql",
diff --git a/test/fixtures/rules/std_rule_cases/RF01.yml b/test/fixtures/rules/std_rule_cases/RF01.yml
index 01253a089..10276c065 100644
--- a/test/fixtures/rules/std_rule_cases/RF01.yml
+++ b/test/fixtures/rules/std_rule_cases/RF01.yml
@@ -36,7 +36,7 @@ test_pass_object_referenced_3:
     SELECT * FROM db.sc.tbl2
     WHERE a NOT IN (SELECT tbl2.a FROM db.sc.tbl1)

-test_pass_object_referenced_4:
+test_pass_object_referenced_4a:
   # Test ambiguous column reference caused by use of BigQuery structure fields.
   # Here, 'et2' could either be a schema name or a table name.
   # https://github.com/sqlfluff/sqlfluff/issues/1079
@@ -50,6 +50,17 @@ test_pass_object_referenced_4:
       references.from:
         force_enable: true

+test_pass_object_referenced_4b:
+  # DuckDB allows dot-access to its MAP objects, which requires special handling
+  # to ensure `ex.x` is not interpreted as `{table}.{field}` instead of
+  # `{schema}.{table}`. The following returns `'there'` if executed.
+  pass_str: |
+    SELECT ex.x.hi
+    FROM (SELECT { 'hi': 'there' } AS x) AS ex
+  configs:
+    core:
+      dialect: duckdb
+
 test_pass_object_referenced_5a:
   # Test ambiguous column reference caused by use of BigQuery structure fields.
   # Here, column,field should not trigger the rule as by default this rule is
```

### Dialect

duckdb

### Version

```
$ sqlfluff --version
sqlfluff, version 3.3.0

$ python --version
Python 3.12.8

$ git rev-parse HEAD
4649363f0282d8ff8220b8929a2c1d9f9bb64774
```

### Configuration

N/A

### 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:

Patch:
diff --git a/src/sqlfluff/rules/references/RF01.py b/src/sqlfluff/rules/references/RF01.py
index 3ef60bbd8ed..ef9fe2c4955 100644
--- a/src/sqlfluff/rules/references/RF01.py
+++ b/src/sqlfluff/rules/references/RF01.py
@@ -37,7 +37,7 @@ class Rule_RF01(BaseRule):
 
     .. note::
 
-       This rule is disabled by default for BigQuery, Databricks, Hive,
+       This rule is disabled by default for Athena, BigQuery, Databricks, DuckDB, Hive,
        Redshift, SOQL and SparkSQL due to the support of things like
        structs and lateral views which trigger false positives. It can be
        enabled with the ``force_enable = True`` flag.
@@ -269,6 +269,8 @@ def _dialect_supports_dot_access(dialect: Dialect) -> bool:
         # 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
+        # DuckDB:
+        # https://duckdb.org/docs/sql/data_types/struct#retrieving-from-structs
         # Redshift:
         # https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
         # TODO: all doc links to all referenced dialects
@@ -276,6 +278,7 @@ def _dialect_supports_dot_access(dialect: Dialect) -> bool:
             "athena",
             "bigquery",
             "databricks",
+            "duckdb",
             "hive",
             "redshift",
             "soql",


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/RF01.yml b/test/fixtures/rules/std_rule_cases/RF01.yml
index 01253a0895b..10276c06530 100644
--- a/test/fixtures/rules/std_rule_cases/RF01.yml
+++ b/test/fixtures/rules/std_rule_cases/RF01.yml
@@ -36,7 +36,7 @@ test_pass_object_referenced_3:
     SELECT * FROM db.sc.tbl2
     WHERE a NOT IN (SELECT tbl2.a FROM db.sc.tbl1)
 
-test_pass_object_referenced_4:
+test_pass_object_referenced_4a:
   # Test ambiguous column reference caused by use of BigQuery structure fields.
   # Here, 'et2' could either be a schema name or a table name.
   # https://github.com/sqlfluff/sqlfluff/issues/1079
@@ -50,6 +50,17 @@ test_pass_object_referenced_4:
       references.from:
         force_enable: true
 
+test_pass_object_referenced_4b:
+  # DuckDB allows dot-access to its MAP objects, which requires special handling
+  # to ensure `ex.x` is not interpreted as `{table}.{field}` instead of
+  # `{schema}.{table}`. The following returns `'there'` if executed.
+  pass_str: |
+    SELECT ex.x.hi
+    FROM (SELECT { 'hi': 'there' } AS x) AS ex
+  configs:
+    core:
+      dialect: duckdb
+
 test_pass_object_referenced_5a:
   # Test ambiguous column reference caused by use of BigQuery structure fields.
   # Here, column,field should not trigger the rule as by default this rule is