Repository: sqlfluff/sqlfluff

Issue:
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
```


Discussion:

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index f25ad70e6..655614979 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -12,6 +12,8 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 - Respect XDG base dirs on Mac OS ([#889](https://github.com/sqlfluff/sqlfluff/issues/889)).
 - Fix bug [#1082](https://github.com/sqlfluff/sqlfluff/issues/1082), adding
   support for BigQuery `select as struct '1' as bb, 2 as aa` syntax
+- Fix bug [#1079](https://github.com/sqlfluff/sqlfluff/issues/1079), addressing
+  issues with L025 and L026 with BigQuery column references involving `STRUCT`
 
 Contributors:
 - [@GitHub-Username](Link to GitHub profile) ([#PR-Number](Link to PR))
@@ -22,6 +24,7 @@ Contributors:
   ANSI dialect which defaults to the semicolon, but allows it to be more intuitive 
   when overriden in a specific child dialect (mysql) [#901](https://github.com/sqlfluff/sqlfluff/issues/901))
 - Added support for the DELIMITER statement in the mysql dialect [#901](https://github.com/sqlfluff/sqlfluff/issues/901))
+
 ## [0.6.0a2] - 2021-05-27
 ### Changed
 - Better exception handling for the simple parsing API (`sqlfluff.parse`)
@@ -30,7 +33,7 @@ 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
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index d407fe351..fd313aeb1 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -12,7 +12,8 @@ grammar. Check out their docs, they're awesome.
 https://www.cockroachlabs.com/docs/stable/sql-grammar.html#select_stmt
 """
 
-from typing import Generator, List, Tuple, NamedTuple, Optional
+from enum import Enum
+from typing import Generator, List, Tuple, NamedTuple, Optional, Union
 
 from sqlfluff.core.parser import (
     Matchable,
@@ -606,18 +607,48 @@ class ObjectReferenceSegment(BaseSegment):
         """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.
+    class ObjectReferenceLevel(Enum):
+        """Labels for the "levels" of a reference.
+
+        Note: Since SQLFluff does not have access to database catalog
+        information, interpreting references will often be ambiguous.
+        Typical example: The first part *may* refer to a schema, but that is
+        almost always optional if referring to an object in some default or
+        currently "active" schema. For this reason, use of this enum is optional
+        and intended mainly to clarify the intent of the code -- no guarantees!
+        Additionally, the terminology may vary by dialect, e.g. in BigQuery,
+        "project" would be a more accurate term than "schema".
+        """
+
+        OBJECT = 1
+        TABLE = 2
+        SCHEMA = 3
+
+    def extract_possible_references(
+        self, level: Union[ObjectReferenceLevel, int]
+    ) -> List[ObjectReferencePart]:
+        """Extract possible references of a given level.
 
-        e.g. level 1 = the object.
-        level 2 = the table
-        level 3 = the schema
-        etc...
+        "level" may be (but is not required to be) a value from the
+        ObjectReferenceLevel enum defined above.
+
+        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.
         """
+        level = self._level_to_int(level)
         refs = list(self.iter_raw_references())
         if len(refs) >= level:
-            return refs[-level]
-        return None
+            return [refs[-level]]
+        return []
+
+    @staticmethod
+    def _level_to_int(level: Union[ObjectReferenceLevel, int]) -> int:
+        # If it's an ObjectReferenceLevel, get the value. Otherwise, assume it's
+        # an int.
+        level = getattr(level, "value", level)
+        assert isinstance(level, int)
+        return level
 
 
 @ansi_dialect.segment()
diff --git a/src/sqlfluff/dialects/dialect_bigquery.py b/src/sqlfluff/dialects/dialect_bigquery.py
index c30a22499..a0a2b0ba1 100644
--- a/src/sqlfluff/dialects/dialect_bigquery.py
+++ b/src/sqlfluff/dialects/dialect_bigquery.py
@@ -409,8 +409,37 @@ class LiteralCoercionSegment(BaseSegment):
     )
 
 
+# Dialects should not use Python "import" to access other dialects. Instead,
+# get a reference to the ANSI ObjectReferenceSegment this way so we can inherit
+# from it.
+ObjectReferenceSegment = ansi_dialect.get_segment("ObjectReferenceSegment")
+
+
+@bigquery_dialect.segment(replace=True)
+class ColumnReferenceSegment(ObjectReferenceSegment):  # type: ignore
+    """A reference to column, field or alias."""
+
+    type = "column_reference"
+
+    def extract_possible_references(self, level):
+        """Extract possible references of a given level."""
+        level = self._level_to_int(level)
+        refs = list(self.iter_raw_references())
+        if level == self.ObjectReferenceLevel.SCHEMA.value and len(refs) >= 3:
+            return [refs[0]]
+        if level == self.ObjectReferenceLevel.TABLE.value and len(refs) >= 3:
+            # Ambiguous case: The table could be the first or second part, so
+            # return both.
+            return [refs[0], refs[1]]
+        if level == self.ObjectReferenceLevel.OBJECT.value and len(refs) >= 3:
+            # Ambiguous case: The object (i.e. column) could be the first or
+            # second part, so return both.
+            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):  # type: ignore
     """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 98ecf72fb..903f7497b 100644
--- a/src/sqlfluff/rules/L025.py
+++ b/src/sqlfluff/rules/L025.py
@@ -51,9 +51,12 @@ class Rule_L025(Rule_L020):
         # 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=r.ObjectReferenceLevel.TABLE
+                )
+            )
 
         alias: AliasInfo
         for alias in table_aliases:
diff --git a/src/sqlfluff/rules/L026.py b/src/sqlfluff/rules/L026.py
index 9d826e1d8..ff175fda7 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,25 @@ class Rule_L026(Rule_L025):
 
     """
 
+    @staticmethod
+    def _is_bad_tbl_ref(table_aliases, parent_select, tbl_ref):
+        """Given a table reference, try to find what it's referring to."""
+        # Is it referring to one of the table aliases?
+        if tbl_ref[0] in [a.ref_str for a in table_aliases]:
+            # Yes. Therefore okay.
+            return False
+
+        # Not a table alias. It it referring to a correlated subquery?
+        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]:
+                # Yes. Therefore okay.
+                return False
+
+        # It's not referring to an alias or a correlated subquery. Looks like a
+        # bad reference (i.e. referring to something unknown.)
+        return True
+
     def _lint_references_and_aliases(
         self,
         table_aliases,
@@ -42,22 +61,18 @@ class Rule_L026(Rule_L025):
 
         # 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=r.ObjectReferenceLevel.TABLE)
+            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=f"Reference {r.raw!r} refers to table/view "
-                        f"{tbl_ref.part!r} not found in the FROM clause or "
-                        "found in parent subquery.",
+                        "not found in the FROM clause or found in parent "
+                        "subquery.",
                     )
                 )
         return violation_buff or None


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L025.yml b/test/fixtures/rules/std_rule_cases/L025.yml
index 625ea5e7d..042e8d8a3 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 abb737fdc..1ab671db6 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