Skip to content

DuckDB needs to be added to list of dialects supporting dot-based struct access #6551

@bruno-digitbio

Description

@bruno-digitbio

Search before asking

  • I searched the issues and found no similar issues.

What Happened

The following query

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

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 --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?

  • Yes I am willing to submit a PR!

Code of Conduct

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingduckdbIssue relating to the dbduck dialect

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions