Repository: sqlfluff/sqlfluff
Pull request: L028 fix - Allow SELECT column alias in WHERE clauses for certain dialects
### Brief summary of the change made
Fixes #1710

### Are there any other side effects of this change that we should be aware of?
Sets Snowflake and Redshift as only two dialects that allow this

### Pull Request checklist
- [x] Please confirm you have completed any of the necessary steps below.

- Included test cases to demonstrate any code changes, which may be one or more of the following:
  - `.yml` rule test cases in `test/fixtures/rules/std_rule_cases`.
  - `.sql`/`.yml` parser test cases in `test/fixtures/dialects` (note YML files can be auto generated with `python test/generate_parse_fixture_yml.py` or by running `tox` locally).
  - Full autofix test cases in `test/fixtures/linter/autofix`.
  - Other.
- Added appropriate documentation for the change.
- Created GitHub issues for any relevant followup/future enhancements if appropriate.


Problem statement:
Rule L028 flags false positive when using aliased columns
```sql
select
    t.col0,
    t.col1 + 1 as alias_col1
from table1 as t
where alias_col1 > 5
```

## Expected Behaviour
Passes

## Observed Behaviour
```
L:   5 | P:   7 | L028 | Unqualified reference 'alias_col1' found in single table select which is inconsistent with previous references.
```

## Steps to Reproduce
`sqlfluff lint` on above code

## Dialect
postgres

## Version
sqlfluff:                0.7.0 python:                  3.7.6

## Configuration
```
[sqlfluff]
verbose = 0
nocolor = False
dialect = postgres
templater = jinja
rules = None
exclude_rules = L009,L011,L019,L031,L034,L044
recurse = 0
output_line_length = 160
runaway_limit = 10
ignore_templated_areas = True
encoding = autodetect
# 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
indented_using_on = True
template_blocks_indent = True

[sqlfluff:templater]
unwrap_wrapped_queries = True

[sqlfluff:templater:jinja]
apply_dbt_builtins = True

[sqlfluff:templater:jinja:macros]
# Macros provided as builtins for dbt projects
dbt_ref = {% macro ref(model_ref) %}{{model_ref}}{% endmacro %}
dbt_source = {% macro source(source_name, table) %}{{source_name}}_{{table}}{% endmacro %}
dbt_config = {% macro config() %}{% for k in kwargs %}{% endfor %}{% endmacro %}
dbt_var = {% macro var(variable) %}item{% endmacro %}
dbt_is_incremental = {% macro is_incremental() %}True{% endmacro %}

# Some rules can be configured directly from the config common to other rules.
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 160
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:L007]  # Keywords
operator_new_lines = after

[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
prefer_count_0 = False

```

Hints from issue discussion:
Is that valid SQL in Postgres?

Usually `WHERE` clause is evaluated before the `SELECT` so can’t use column aliases in `WHERE` clause: https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause
I'm unsure. The [Postgres documentation](https://www.postgresql.org/docs/9.5/sql-select.html#SQL-WHERE) doesn't specifically talk about aliases but says
> A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.

It doesn't seem to be valid in all Postgres implementations based on this [question on Stackoverflow](https://stackoverflow.com/questions/3241352/using-an-alias-column-in-the-where-clause-in-postgresql).

It is valid syntax in Redshift.

If I remember correctly, it was a more recent addition (as in the last 1-2 years)—which just means that it wasn't in Postgres 10.x on which Redshift is based.
At the top of that page it gives the steps used in executing a SQL statement including processing of the `WHERE` clause in step 3:

> 3. If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output. (See WHERE Clause below.)

**before** the `SELECT` clause in step 5:

> 5. The actual output rows are computed using the SELECT output expressions for each selected row or row group. (See SELECT List below.)

So that suggests it's not valid syntax in Postgres.

To be honest I'm really surprised it's valid syntax in Redshift. Do you have a reference for this? If so it's a relatively easy addition to rule L028 to have it ignore column alias's for the new `redshift` dialect only. But it sounds against every other SQL implementation I've ever dealt with, so would really like some official documentation on this before we go and add that to the rule.
FWIW, using aliases in the `WHERE` clause is definitely allowed in Snowflake.
We're using it in a ton of production models, and it makes our lives so much easier. It's so awkward that you have to repeat a whole `CASE WHEN` or other computation in the `WHERE` clause in other implementations.

Idk when this was added, but I found some [documentation](https://docs.aws.amazon.com/redshift/latest/dg/r_WHERE_clause.html) that says
> You can use aliases in the WHERE clause to reference select list expressions.

And [here](https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html) it says
> The alias is recognized right after it is defined in the target list. You can use an alias in other expressions defined after it in the same target list. The following example illustrates this.

Based on [this announcement](https://aws.amazon.com/about-aws/whats-new/2018/08/amazon-redshift-announces-support-for-lateral-column-alias-reference/) this was added in 2018 to Redshift. (Somewhat of a guess, as the announcement doesn't mention `WHERE` clauses).
> Posted On: Aug 7, 2018
> Amazon Redshift now enables you to write queries that refer to a column alias within the same query immediately after it is declared, improving the readability of complex SQL queries.
> The support for lateral column alias reference enables you to write queries without repeating the same expressions in the SELECT list. For example, you can define the alias 'probability' and use it within the same select statement:

Commit message: Merge branch 'main' into l028-allow-select-alias-for-redshift

Patch to review:
diff --git a/src/sqlfluff/rules/L028.py b/src/sqlfluff/rules/L028.py
index 5be833698bb..4ebb4dce447 100644
--- a/src/sqlfluff/rules/L028.py
+++ b/src/sqlfluff/rules/L028.py
@@ -43,6 +43,7 @@ class Rule_L028(Rule_L025):
     """
 
     config_keywords = ["single_table_references", "force_enable"]
+    _allow_select_alias = False
 
     def _lint_references_and_aliases(
         self,
@@ -72,6 +73,10 @@ def _lint_references_and_aliases(
             # entirely rather than trying to enforce anything.
             if ref.raw in standalone_aliases:
                 continue
+
+            # Certain dialects allow use of SELECT alias in WHERE clauses
+            if self._allow_select_alias and ref.raw in col_aliases:
+                continue
             this_ref_type = ref.qualification()
             if self.single_table_references == "consistent":
                 if seen_ref_types and this_ref_type not in seen_ref_types:
@@ -97,15 +102,17 @@ def _lint_references_and_aliases(
         return violation_buff or None
 
     def _eval(self, context: RuleContext) -> EvalResultType:
-        """Override Rule L025 for dialects that use structs.
-
-        Some dialects use structs (e.g. column.field) which look like
-        table references and so incorrectly trigger this rule.
-        """
+        """Override Rule L025 for dialects that use structs."""
         # Config type hints
         self.force_enable: bool
 
+        # Some dialects use structs (e.g. column.field) which look like
+        # table references and so incorrectly trigger this rule.
         if context.dialect.name in ["bigquery"] and not self.force_enable:
             return LintResult()
 
+        # Certain dialects allow use of SELECT alias in WHERE clauses
+        if context.dialect.name in ["snowflake", "redshift"]:
+            self._allow_select_alias = True
+
         return super()._eval(context=context)
diff --git a/test/fixtures/rules/std_rule_cases/L028.yml b/test/fixtures/rules/std_rule_cases/L028.yml
index 9d67bf72063..e427bc3b201 100644
--- a/test/fixtures/rules/std_rule_cases/L028.yml
+++ b/test/fixtures/rules/std_rule_cases/L028.yml
@@ -103,3 +103,39 @@ test_date_functions_are_excluded:
       dialect: tsql
 
 
+test_select_alias_in_where_clause_1:
+  # This should pass for certain dialects
+  pass_str: |
+    select
+        t.col0,
+        t.col1 + 1 as alias_col1
+    from table1 as t
+    where alias_col1 > 5
+  configs:
+    core:
+      dialect: redshift
+
+
+test_select_alias_in_where_clause_2:
+  # This should pass for certain dialects
+  pass_str: |
+    select
+        t.col0,
+        t.col1 + 1 as alias_col1
+    from table1 as t
+    where alias_col1 > 5
+  configs:
+    core:
+      dialect: snowflake
+
+
+test_select_alias_in_where_clause_3:
+  # This should fail for ansi
+  fail_str: |
+    select
+        t.col0,
+        t.col1 + 1 as alias_col1
+    from table1 as t
+    where alias_col1 > 5
+
+


Code review comments:
diff_hunk: @@ -97,15 +102,17 @@ def _lint_references_and_aliases(
         return violation_buff or None
 
     def _eval(self, context: RuleContext) -> EvalResultType:
-        """Override Rule L025 for dialects that use structs.
-
-        Some dialects use structs (e.g. column.field) which look like
-        table references and so incorrectly trigger this rule.
-        """
+        """Override Rule L025 for dialects that use structs."""
path: src/sqlfluff/rules/L028.py
text: @author:
```suggestion
        """Override Rule L025 for dialects that use structs, or SELECT aliases."""
```