Repository: sqlfluff/sqlfluff

Issue:
BigQuery: Accessing `STRUCT` elements evades triggering L027
### Search before asking

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


### What Happened

Accessing unreferenced `STRUCT` elements using BigQuery dot notation in a multi table query does not trigger L027.

### Expected Behaviour

L027 gets triggered.

### Observed Behaviour

L027 does not get triggered.

### How to reproduce

```sql
SELECT
    t1.col1,
    t2.col2,
    events.id
FROM t_table1 AS t1
LEFT JOIN t_table2 AS t2
    ON TRUE
```

### Dialect

BigQUery

### Version

`0.11.2` using online.sqlfluff.com

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

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


Discussion:
codecov[bot]: # [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) Report
> Merging [#2998](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (0b17a24) into [main](https://codecov.io/gh/sqlfluff/sqlfluff/commit/f136ed2c3086f6b4973e7b587660148b72106d68?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (f136ed2) will **not change** coverage.
> The diff coverage is `100.00%`.

```diff
@@            Coverage Diff            @@
##              main     #2998   +/-   ##
=========================================
  Coverage   100.00%   100.00%           
=========================================
  Files          164       164           
  Lines        11973     11987   +14     
=========================================
+ Hits         11973     11987   +14     
```


| [Impacted Files](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) | Coverage Δ | |
|---|---|---|
| [src/sqlfluff/rules/L027.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL3J1bGVzL0wwMjcucHk=) | `100.00% <100.00%> (ø)` | |
| [src/sqlfluff/dialects/dialect\_bigquery.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2RpYWxlY3RzL2RpYWxlY3RfYmlncXVlcnkucHk=) | `100.00% <0.00%> (ø)` | |
| [src/sqlfluff/dialects/dialect\_bigquery\_keywords.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2RpYWxlY3RzL2RpYWxlY3RfYmlncXVlcnlfa2V5d29yZHMucHk=) | `100.00% <0.00%> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Last update [f136ed2...0b17a24](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2998?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).

tunetheweb: I think we should go with this. Doesn't look too hacky to me.

It might lead to a few double reporting of issues as per https://github.com/sqlfluff/sqlfluff/issues/2992#issuecomment-1088106451 but think that's OK.

@barrywhart could you have a review and a read of above comment and see what you think?
dmohns: Ok, cool. I'm changing this from Draft -> Ready for Review. Let me know what you think about making the check for valid references a configurable option to L027 which is disabled by default, as part of the review.
barrywhart: @dmohns: What do you mean by this?
>I'm not sure if it is within SQLFluff design to allow a single rule to trigger different `LintResult`s.

The `_eval()` function can return multiple `LintResult` objects. See rule L034, for example.
barrywhart: Looks good to me!

Patch:
diff --git a/src/sqlfluff/rules/L027.py b/src/sqlfluff/rules/L027.py
index f8a65a563..d9c929e5c 100644
--- a/src/sqlfluff/rules/L027.py
+++ b/src/sqlfluff/rules/L027.py
@@ -73,4 +73,21 @@ class Rule_L027(Rule_L020):
                     )
                 )
 
+            all_table_aliases = [t.ref_str for t in table_aliases] + standalone_aliases
+
+            # For qualified references, we want to check that the alias is actually
+            # valid
+            if (
+                this_ref_type == "qualified"
+                and list(r.iter_raw_references())[0].part not in all_table_aliases
+            ):
+                violation_buff.append(
+                    LintResult(
+                        anchor=r,
+                        description=f"Qualified reference {r.raw!r} not found in "
+                        f"available tables/view aliases {all_table_aliases} in select "
+                        "with more than one referenced table/view.",
+                    )
+                )
+
         return violation_buff or None


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L027.yml b/test/fixtures/rules/std_rule_cases/L027.yml
index 88dd9a03c..8ad46d33e 100644
--- a/test/fixtures/rules/std_rule_cases/L027.yml
+++ b/test/fixtures/rules/std_rule_cases/L027.yml
@@ -220,3 +220,40 @@ test_pass_rowtype_with_join:
   configs:
     core:
       dialect: hive
+
+test_fail_column_name_not_found_in_table_aliases_bigquery:
+  # qualified reference should actually exists in table aliases
+  fail_str: |
+    SELECT
+        a.bar,
+        b.foo,
+        this_is.some_struct.id
+    FROM
+        a LEFT JOIN b ON TRUE
+  configs:
+    core:
+      dialect: bigquery
+
+test_pass_column_name_is_a_struct_bigquery:
+  # check structs work as expected
+  pass_str: |
+    SELECT
+        a.bar,
+        b.this_is.some_struct.id
+    FROM
+        a LEFT JOIN b ON TRUE
+  configs:
+    core:
+      dialect: bigquery
+
+test_pass_column_name_from_unnest_bigquery:
+  # Check that we allow an table alias come from UNNEST statement
+  pass_str: |
+    SELECT
+        a.bar,
+        e.foo
+    FROM
+        a LEFT JOIN UNEST(a.events) AS e
+  configs:
+    core:
+      dialect: bigquery
diff --git a/test/rules/std_test.py b/test/rules/std_test.py
index 0afc54319..af7a52208 100644
--- a/test/rules/std_test.py
+++ b/test/rules/std_test.py
@@ -68,7 +68,7 @@ from sqlfluff.testing.rules import assert_rule_raises_violations_in_file
         ),
         ("L016", "block_comment_errors_2.sql", [(1, 85), (2, 86)]),
         # Column references
-        ("L027", "column_references.sql", [(1, 8)]),
+        ("L027", "column_references.sql", [(1, 8), (1, 11)]),
         ("L027", "column_references_bare_function.sql", []),
         ("L026", "column_references.sql", [(1, 11)]),
         ("L025", "column_references.sql", [(2, 11)]),