Search before asking

• I searched the issues and found no similar issues.

What Happened

Redshift documentation states:

If you're using the QUALIFY clause directly after the FROM clause, the FROM relation name must have an alias specified before the QUALIFY clause.

https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_clause.html

However, this is incompatible with the autofix for aliasing.unused rule as it will remove the alias and break the query.

Expected Behaviour

Don't automatically remove any table alias that directly precedes a Redshift QUALIFY clause

Observed Behaviour

autofix for aliasing.unused rule removed the table alias directly preceding the QUALIFY clause and broke the query.

How to reproduce

Example taken from Redshift docs
https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_clause.html

run this through sql autofix with aliasing.unused enabled and have the query fail.

create table #store_sales (ss_sold_date date, ss_sold_time time,
                          ss_item text, ss_sales_price float);

insert into #store_sales values ('2022-01-01', '09:00:00', 'Product 1', 100.0),
                               ('2022-01-01', '11:00:00', 'Product 2', 500.0),
                               ('2022-01-01', '15:00:00', 'Product 3', 20.0),
                               ('2022-01-01', '17:00:00', 'Product 4', 1000.0),
                               ('2022-01-01', '18:00:00', 'Product 5', 30.0),
                               ('2022-01-02', '10:00:00', 'Product 6', 5000.0),
                               ('2022-01-02', '16:00:00', 'Product 7', 5.0);
SELECT *
FROM #store_sales as ss
QUALIFY row_number() OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2

Dialect

redshift

Version

sqlfluff, version 2.3.5

Configuration

dialect = redshift

exclude_rules = references.consistent,aliasing.length,references.keywords,references.special_chars,structure.subquery,ambiguous.column_references,structure.column_order

max_line_length = 150
output_line_length = 1000
processes = -1
large_file_skip_byte_limit = 300000

[sqlfluff:rules:aliasing.table]
aliasing = explicit

[sqlfluff:rules:aliasing.column]
aliasing = explicit

[sqlfluff:rules:capitalisation.keywords]
ignore_words = table_type,external_location,is_external,location,field_delimiter,format,write_compression,bucketed_by,bucket_count,partitioned_by,partitioning,write_target_data_file_size_bytes,optimize_rewrite_min_data_file_size_bytes,optimize_rewrite_max_data_file_size_bytes,optimize_rewrite_data_file_threshold,optimize_rewrite_delete_file_threshold,vacuum_min_snapshots_to_keep,vacuum_max_snapshot_age_seconds,write_compression,orc_compression,parquet_compression,compression_level
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.identifiers]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower

[sqlfluff:rules:references.quoting]
ignore_words = state,time,year

[sqlfluff:rules:convention.terminator]
multiline_newline = True

[sqlfluff:layout:type:alias_expression]
spacing_before = align
align_within = select_clause
align_scope = bracketed

[sqlfluff:layout:type:dot]
spacing_before = touch
spacing_after = touch

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

• I agree to follow this project's Code of Conduct