Repository: sqlfluff/sqlfluff

Issue:
L027: outer-level table not found in WHERE clause sub-select
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

Outer-level table/view referenced in sub-select inside `WHERE` clause is not being detected.

This error seems to only occur when the sub-select contains joins.

### Expected Behaviour

No error

### Observed Behaviour

```
L:   7 | P:  32 | L027 | Qualified reference 'my_table.kind' not found in
                       | available tables/view aliases ['other_table',
                       | 'mapping_table'] in select with more than one referenced
                       | table/view.
```

### How to reproduce

```sql
SELECT my_col
FROM my_table
WHERE EXISTS (
    SELECT 1
    FROM other_table
    INNER JOIN mapping_table ON (mapping_table.other_fk = other_table.id_pk)
    WHERE mapping_table.kind = my_table.kind
);
```

### Dialect

postgres

### Version

sqlfluff, version 0.12.0

### Configuration

```
[sqlfluff]
nocolor = True
dialect = postgres
```

### 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)

L027 error finding table aliases when mixing how table aliases are used in joins
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

This valid (at least for redshift) query raises an L027 error when linting because sqlfluff can't resolve the mixed aliases.

```sql
SELECT
    account.id AS account_id,
    account.isdeleted AS is_deleted
FROM salesforce_sd.account
INNER JOIN salesforce_sd."user" ON salesforce_sd."user".id = account.ownerid

LIMIT 100
```

```text
❯ sqlfluff lint
== [/Users/alexkoch/repos/sqlfluff-test/test.sql] FAIL
L:   5 | P:  36 | L027 | Qualified reference 'salesforce_sd."user".id' not found
                       | in available tables/view aliases ['account', '"user"']
                       | in select with more than one referenced table/view.
All Finished 📜 🎉!
```

### Expected Behaviour

Query passes linting or is shown as a style type error.

### Observed Behaviour

Query fails linting with the "Qualified reference..." message when using different ways of aliasing how tables/columns are referenced with variations of `<<database>>.<<schema>>.<<table>>.<<column>>`

I've also seen a few other variations of L027 errors, particularly with DBT refs/source, but this seems the most general example I can come up with.

### How to reproduce

1. Install sqlfluff, create sqfluff config with:
```
[sqlfluff]
dialect = redshift
```
2. Create test.sql file:
```
SELECT
    account.id AS account_id,
    account.isdeleted AS is_deleted
FROM salesforce_sd.account
INNER JOIN salesforce_sd."user" ON salesforce_sd."user".id = account.ownerid

LIMIT 100
```
3. Run `sqlfluff lint`

### Dialect

redshift

### Version

❯ sqlfluff version
0.13.2
❯ python --version
Python 3.9.7

### Configuration

.sqlfluff:
```ini
[sqlfluff]
dialect = redshift
````

### 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/3435?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) Report
> Merging [#3435](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3435?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (3b01fee) into [main](https://codecov.io/gh/sqlfluff/sqlfluff/commit/8e724ef8906eecce7179f4b7c52d4fc0672e4bd9?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (8e724ef) will **not change** coverage.
> The diff coverage is `n/a`.

```diff
@@            Coverage Diff            @@
##              main     #3435   +/-   ##
=========================================
  Coverage   100.00%   100.00%           
=========================================
  Files          170       170           
  Lines        12862     12859    -3     
=========================================
- Hits         12862     12859    -3     
```


| [Impacted Files](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3435?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/3435/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% <ø> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3435?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/3435?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Last update [8e724ef...3b01fee](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3435?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).


Patch:
diff --git a/src/sqlfluff/rules/L027.py b/src/sqlfluff/rules/L027.py
index a3535a4d9..66afd0dbb 100644
--- a/src/sqlfluff/rules/L027.py
+++ b/src/sqlfluff/rules/L027.py
@@ -99,23 +99,6 @@ 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
 
     def _init_ignore_words_list(self):


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L027.yml b/test/fixtures/rules/std_rule_cases/L027.yml
index 69aab33a4..8740f1b59 100644
--- a/test/fixtures/rules/std_rule_cases/L027.yml
+++ b/test/fixtures/rules/std_rule_cases/L027.yml
@@ -221,43 +221,6 @@ test_pass_rowtype_with_join:
     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
-
 test_fail_table_plus_flatten_snowflake_1:
   # FLATTEN() returns a table, thus there are two tables, thus lint failure.
   fail_str: |
@@ -328,3 +291,48 @@ test_pass_ignore_words_regex_bigquery_declare_example:
     rules:
       L027:
         ignore_words_regex: ^_
+
+test_pass_redshift:
+  # This was failing in issue 3380.
+  pass_str:
+    SELECT account.id
+    FROM salesforce_sd.account
+    INNER JOIN salesforce_sd."user" ON salesforce_sd."user".id = account.ownerid
+  configs:
+    core:
+      dialect: redshift
+
+test_pass_tsql:
+  # This was failing in issue 3342.
+  pass_str:
+    select
+        psc.col1
+    from
+        tbl1 as psc
+    where
+        exists
+        (
+            select 1 as data
+            from
+                tbl2 as pr
+            join tbl2 as c on c.cid = pr.cid
+            where
+                c.col1 = 'x'
+                and pr.col2 <= convert(date, getdate())
+                and pr.pid = psc.pid
+        )
+  configs:
+    core:
+      dialect: tsql
+
+test_pass_ansi:
+  # This was failing in issue 3055.
+  pass_str: |
+    SELECT my_col
+    FROM my_table
+    WHERE EXISTS (
+        SELECT 1
+        FROM other_table
+        INNER JOIN mapping_table ON (mapping_table.other_fk = other_table.id_pk)
+        WHERE mapping_table.kind = my_table.kind
+    )
diff --git a/test/rules/std_test.py b/test/rules/std_test.py
index af7a52208..0afc54319 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), (1, 11)]),
+        ("L027", "column_references.sql", [(1, 8)]),
         ("L027", "column_references_bare_function.sql", []),
         ("L026", "column_references.sql", [(1, 11)]),
         ("L025", "column_references.sql", [(2, 11)]),