Repository: sqlfluff/sqlfluff
Pull request: Issue 1079: L026 and BigQuery record fields
Resolves #1079 

Problem statement:
L026 and BigQuery record fields
## Expected Behaviour

The linter only recognises tables from the before the first`.` in the column name when using the bigquery dialect.

## Observed Behaviour

The linter sees names in nested fields which are separated by dots as table names which are not referenced in the query. Maybe it is assuming that the first name in the column string is a bigquery project?

```
== [/example.sql] FAIL
L:   5 | P:   9 | L026 | Reference 'et2.transaction.amount' refers to table/view
                       | 'transaction' not found in the FROM clause or found in
                       | parent subquery.

```

## Steps to Reproduce

SQL:
```
CREATE TABLE `example_dataset1.example_table1`
AS
SELECT
    et2._partitiontime AS partitiontime,
    et2.transaction.amount AS amount
FROM `example_dataset2.example_table2` AS et2
```

example_table2 has the following structure:

```
{
  name: 'transaction',
  type: 'RECORD',
  mode: 'REQUIRED',
  fields: [
    {
      name: 'amount',
      type: 'INTEGER',
      mode: 'REQUIRED'
    }
  ]
}
```
(date partitioned)

## Version
```
% python --version 
Python 3.8.8

% sqlfluff --version
sqlfluff, version 0.5.3
```

## Configuration
```
[sqlfluff]
verbose = 0
nocolor = False
dialect = bigquery
templater = jinja
rules = None

exclude_rules = L031
# L031 - Aliases in joins 

recurse = 0
output_line_length = 80
runaway_limit = 10
ignore_templated_areas = True
# Comma separated list of file extensions to lint.

# NB: This config will only apply in the root folder.
sql_file_exts = .sql,.sql.j2,.dml,.ddl

[sqlfluff:indentation]
indented_joins = False
template_blocks_indent = True

[sqlfluff:templater]
unwrap_wrapped_queries = True

[sqlfluff:templater:jinja:context]
ds=2020-01-01

# Some rules can be configured directly from the config common to other rules.
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 120
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
unquoted_identifiers_policy = all

# Some rules have their own specific config.
[sqlfluff:rules:L003]
lint_templated_tokens = True

[sqlfluff:rules:L010]  # Keywords
capitalisation_policy = consistent

[sqlfluff:rules:L014]  # Unquoted identifiers
extended_capitalisation_policy = consistent

[sqlfluff:rules:L016]
ignore_comment_lines = False

[sqlfluff:rules:L029]  # Keyword identifiers
unquoted_identifiers_policy = aliases

[sqlfluff:rules:L030]  # Function names
capitalisation_policy = consistent

[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid

[sqlfluff:rules:L040]  # Null & Boolean Literals
capitalisation_policy = consistent

[sqlfluff:rules:L042]
# By default, allow subqueries in from clauses, but not join clauses.
forbid_subquery_in = join

[sqlfluff:rules:L047]  # Consistent syntax to count all rows
prefer_count_1 = False
```

Hints from issue discussion:

Commit message: PR review, run Black on L026.py

Patch to review:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index bce968f93ba..8a4ce4d8a37 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -19,12 +19,14 @@ Contributors:
 - Fix bug [#1037](https://github.com/sqlfluff/sqlfluff/issues/1037), in which fix 
   logging had been sent to stdout when reading data from stdin.
 - Add a little bit of fun on CLI exit 🎉!
-- Disabled models in the dbt templater are now skipped enitrely rather than
+- Disabled models in the dbt templater are now skipped entirely rather than
   returning an untemplated file.
 - Add a changelog check to SQLFluff continuous integration.
 - Fix bug [#1083](https://github.com/sqlfluff/sqlfluff/issues/1083), adding
   support for BigQuery named function arguments, used with functions such as
   [ST_GEOGFROMGEOJSON()](https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogfromgeojson)
+- Fix bug [#1079](https://github.com/sqlfluff/sqlfluff/issues/1079), addressing
+  issues with L025 and L026 with BigQuery column references involving `STRUCT`
 
 ## [0.6.0a1] - 2021-05-15
 ### Added
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index 240a6541baa..1c0d420d57f 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -605,8 +605,12 @@ def qualification(self):
         """Return the qualification type of this reference."""
         return "qualified" if self.is_qualified() else "unqualified"
 
-    def extract_reference(self, level: int) -> Optional[ObjectReferencePart]:
-        """Extract a reference of a given level.
+    def extract_possible_references(self, level: int) -> List[ObjectReferencePart]:
+        """Extract possible references of a given level.
+
+        NOTE: The base implementation here returns at most one part, but
+        dialects such as BigQuery that support nesting (e.g. STRUCT) may return
+        multiple reference parts.
 
         e.g. level 1 = the object.
         level 2 = the table
@@ -615,8 +619,8 @@ def extract_reference(self, level: int) -> Optional[ObjectReferencePart]:
         """
         refs = list(self.iter_raw_references())
         if len(refs) >= level:
-            return refs[-level]
-        return None
+            return [refs[-level]]
+        return []
 
 
 @ansi_dialect.segment()
diff --git a/src/sqlfluff/dialects/dialect_bigquery.py b/src/sqlfluff/dialects/dialect_bigquery.py
index 38948f833c8..b6e067ff2fd 100644
--- a/src/sqlfluff/dialects/dialect_bigquery.py
+++ b/src/sqlfluff/dialects/dialect_bigquery.py
@@ -417,8 +417,33 @@ class LiteralCoercionSegment(BaseSegment):
     )
 
 
+@bigquery_dialect.segment(replace=True)
+class ObjectReferenceSegment(ansi_dialect.get_segment("ObjectReferenceSegment")):  # type: ignore
+    """A reference to an object."""
+
+    pass
+
+
+@bigquery_dialect.segment(replace=True)
+class ColumnReferenceSegment(ObjectReferenceSegment):
+    """A reference to column, field or alias."""
+
+    type = "column_reference"
+
+    def extract_possible_references(self, level: int):
+        """Extract possible references of a given level."""
+        refs = list(self.iter_raw_references())
+        if level == 3 and len(refs) >= 3:
+            return [refs[0]]
+        if level == 2 and len(refs) >= 3:
+            return [refs[0], refs[1]]
+        if level == 1 and len(refs) >= 3:
+            return [refs[1], refs[2]]
+        return super().extract_possible_references(level)
+
+
 @bigquery_dialect.segment()
-class HyphenatedObjectReferenceSegment(ansi_dialect.get_segment("ObjectReferenceSegment")):  # type: ignore
+class HyphenatedObjectReferenceSegment(ObjectReferenceSegment):
     """A reference to an object that may contain embedded hyphens."""
 
     type = "hyphenated_object_reference"
diff --git a/src/sqlfluff/rules/L025.py b/src/sqlfluff/rules/L025.py
index 5aa8c23be52..d68025cfe79 100644
--- a/src/sqlfluff/rules/L025.py
+++ b/src/sqlfluff/rules/L025.py
@@ -51,9 +51,7 @@ def _lint_references_and_aliases(
         # Check all the references that we have, keep track of which aliases we refer to.
         tbl_refs = set()
         for r in references:
-            tbl_ref = r.extract_reference(level=2)
-            if tbl_ref:
-                tbl_refs.add(tbl_ref.part)
+            tbl_refs.update(tr.part for tr in r.extract_possible_references(level=2))
 
         alias: AliasInfo
         for alias in table_aliases:
diff --git a/src/sqlfluff/rules/L026.py b/src/sqlfluff/rules/L026.py
index acb527cf4ba..14d435bb297 100644
--- a/src/sqlfluff/rules/L026.py
+++ b/src/sqlfluff/rules/L026.py
@@ -2,10 +2,10 @@
 
 from sqlfluff.core.rules.analysis.select import get_aliases_from_select
 from sqlfluff.core.rules.base import LintResult
-from sqlfluff.rules.L025 import Rule_L025
+from sqlfluff.rules.L025 import Rule_L020
 
 
-class Rule_L026(Rule_L025):
+class Rule_L026(Rule_L020):
     """References cannot reference objects not present in FROM clause.
 
     | **Anti-pattern**
@@ -28,6 +28,17 @@ class Rule_L026(Rule_L025):
 
     """
 
+    @staticmethod
+    def _is_bad_tbl_ref(table_aliases, parent_select, tbl_ref):
+        if tbl_ref and tbl_ref[0] not in [a.ref_str for a in table_aliases]:
+            # Last check, this *might* be a correlated subquery reference.
+            if parent_select:
+                parent_aliases, _ = get_aliases_from_select(parent_select)
+                if parent_aliases and tbl_ref[0] in [a[0] for a in parent_aliases]:
+                    return False
+            return True
+        return False
+
     def _lint_references_and_aliases(
         self,
         table_aliases,
@@ -42,22 +53,18 @@ def _lint_references_and_aliases(
 
         # Check all the references that we have, do they reference present aliases?
         for r in references:
-            tbl_ref = r.extract_reference(level=2)
-            # Check whether the string in the list of strings
-            if tbl_ref and tbl_ref[0] not in [a.ref_str for a in table_aliases]:
-                # Last check, this *might* be a correlated subquery reference.
-                if parent_select:
-                    parent_aliases, _ = get_aliases_from_select(parent_select)
-                    if parent_aliases and tbl_ref[0] in [a[0] for a in parent_aliases]:
-                        continue
-
+            tbl_refs = r.extract_possible_references(level=2)
+            if tbl_refs and all(
+                self._is_bad_tbl_ref(table_aliases, parent_select, tbl_ref)
+                for tbl_ref in tbl_refs
+            ):
                 violation_buff.append(
                     LintResult(
                         # Return the first segment rather than the string
-                        anchor=tbl_ref.segments[0],
+                        anchor=tbl_refs[0].segments[0],
                         description="Reference {0!r} refers to table/view "
-                        "{1!r} not found in the FROM clause or "
-                        "found in parent subquery.".format(r.raw, tbl_ref.part),
+                        "not found in the FROM clause or found in parent"
+                        "subquery.".format(r.raw),
                     )
                 )
         return violation_buff or None
diff --git a/test/fixtures/rules/std_rule_cases/L025.yml b/test/fixtures/rules/std_rule_cases/L025.yml
index 625ea5e7de1..042e8d8a3cb 100644
--- a/test/fixtures/rules/std_rule_cases/L025.yml
+++ b/test/fixtures/rules/std_rule_cases/L025.yml
@@ -42,3 +42,14 @@ test_6:
   configs:
     core:
       dialect: bigquery
+
+test_7:
+  # 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
+  pass_str: |
+    SELECT et2.txn.amount
+    FROM `example_dataset2.example_table2` AS et2
+  configs:
+    core:
+      dialect: bigquery
diff --git a/test/fixtures/rules/std_rule_cases/L026.yml b/test/fixtures/rules/std_rule_cases/L026.yml
index abb737fdc42..1ab671db61f 100644
--- a/test/fixtures/rules/std_rule_cases/L026.yml
+++ b/test/fixtures/rules/std_rule_cases/L026.yml
@@ -28,3 +28,14 @@ test_5:
   pass_str: |
     SELECT * FROM db.sc.tbl2
     WHERE a NOT IN (SELECT tbl2.a FROM db.sc.tbl1)
+
+test_6:
+  # 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
+  pass_str: |
+    SELECT et2.txn.amount
+    FROM `example_dataset2.example_table2` AS et2
+  configs:
+    core:
+      dialect: bigquery
\ No newline at end of file


Code review comments:
diff_hunk: @@ -417,8 +417,33 @@ class LiteralCoercionSegment(BaseSegment):
     )
 
 
+@bigquery_dialect.segment(replace=True)
+class ObjectReferenceSegment(ansi_dialect.get_segment("ObjectReferenceSegment")):  # type: ignore
+    """A reference to an object."""
+
+    pass
+
+
+@bigquery_dialect.segment(replace=True)
+class ColumnReferenceSegment(ObjectReferenceSegment):
+    """A reference to column, field or alias."""
+
+    type = "column_reference"
+
+    def extract_possible_references(self, level: int):
+        """Extract possible references of a given level."""
+        refs = list(self.iter_raw_references())
+        if level == 3 and len(refs) >= 3:
path: src/sqlfluff/dialects/dialect_bigquery.py
text: @user1:
I think I need a little help (or comment) to understand which level corresponds to which reference, column / field / alias?

@user1:
If each level has a meaning, could it be turned into a named enumerator?

@author:
I had the same thought -- I'll look into it for this PR. Until I do that, this [section of`dialect_ansi.py`](https://github.com/sqlfluff/sqlfluff/blob/master/src/sqlfluff/dialects/dialect_ansi.py#L612L614) documents the meaning of the values.

diff_hunk: @@ -19,12 +19,14 @@ Contributors:
 - Fix bug [#1037](https://github.com/sqlfluff/sqlfluff/issues/1037), in which fix 
   logging had been sent to stdout when reading data from stdin.
 - Add a little bit of fun on CLI exit 🎉!
-- Disabled models in the dbt templater are now skipped enitrely rather than
+- Disabled models in the dbt templater are now skipped entirely rather than
   returning an untemplated file.
 - Add a changelog check to SQLFluff continuous integration.
 - Fix bug [#1083](https://github.com/sqlfluff/sqlfluff/issues/1083), adding
   support for BigQuery named function arguments, used with functions such as
   [ST_GEOGFROMGEOJSON()](https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogfromgeojson)
+- Fix bug [#1079](https://github.com/sqlfluff/sqlfluff/issues/1079), addressing
path: CHANGELOG.md
text: @user1:
The CHANGELOG is new to me but shouldn't this be added after ## Unreleased?

@author:
Yes. I'll move it. I think a PR yesterday incorrectly added a 0.6.1 section, which is not an actual release. I like the CHANGELOG, but I'm starting to wonder if there's a technical solution that would automate it a bit more. Manual editing is a bit error prone and creates lots of merge "conflicts".

diff_hunk: @@ -417,8 +417,33 @@ class LiteralCoercionSegment(BaseSegment):
     )
 
 
+@bigquery_dialect.segment(replace=True)
+class ObjectReferenceSegment(ansi_dialect.get_segment("ObjectReferenceSegment")):  # type: ignore
path: src/sqlfluff/dialects/dialect_bigquery.py
text: @user1:
not sure I understand why you need to use the ansi_dialect segment as a base and then not overwrite anything?

@user1:
Can't you just import the ObjectReferenceSegment from the ansi_dialect package as a class?

@author:
I'll take a look at that. I'm in unfamiliar territory here -- replacing a subclass of `ObjectReferenceSegment` in a child dialect (BigQuery). 🤯 

@author:
Done! IIUC, Alan did some refactoring a few months back, and dialects shouldn't directly `import` from other dialects, so I did this:
```
ObjectReferenceSegment = ansi_dialect.get_segment("ObjectReferenceSegment")
```

This seems to work fine -- I can use `ObjectReferenceSegment` as a base class, which was why I had created this unnecessary new class. 👍

diff_hunk: @@ -28,6 +28,17 @@ class Rule_L026(Rule_L025):
 
     """
 
+    @staticmethod
+    def _is_bad_tbl_ref(table_aliases, parent_select, tbl_ref):
+        if tbl_ref and tbl_ref[0] not in [a.ref_str for a in table_aliases]:
+            # Last check, this *might* be a correlated subquery reference.
+            if parent_select:
+                parent_aliases, _ = get_aliases_from_select(parent_select)
+                if parent_aliases and tbl_ref[0] in [a[0] for a in parent_aliases]:
path: src/sqlfluff/rules/L026.py
text: @user1:
could do
```suggestion
                return not (parent_aliases and tbl_ref[0] in [a[0] for a in parent_aliases])
```

@author:
Makes sense, but I think I'd like to keep the existing structure -- now that I have added more comments as you suggested, I find that the various `return` statements are good places to provide context on the significance and context of reaching various lines of code. I'll push up this changes in a few minutes.