The word CROSS in cross joins is parsed as table alias for the table coming before it

[Nazaniiin]

Search before asking

• [X] I searched the issues and found no similar issues.

What Happened

I saw a couple of previous bug reports on issues with CROSS JOIN (https://github.com/sqlfluff/sqlfluff/issues/871 and https://github.com/sqlfluff/sqlfluff/issues/77) but both bugs seem to be fixed. I could not find an open issue on this, so I'm opening a new one.

SQLFluff sees the word CROSS in a cross join as the alias of the table coming before the CROSS JOIN statement.

Expected Behaviour

I expected SQLFluff to parse CROSS JOIN as a join statement similar to INNER JOIN and LEFT JOIN.

Observed Behaviour

Example code snippet:

final AS (
    SELECT
        dim_dates.date,
        users.user_id
    FROM dim_dates
    CROSS JOIN users
)

SELECT * FROM final

sqlfluff parse

from_expression_element:
[L: 66, P: 10]      |                                    table_expression:
[L: 66, P: 10]      |                                        table_reference:
[L: 66, P: 10]      |                                            naked_identifier:  'dim_dates'
[L: 66, P: 19]      |                                    newline:                  '\n'
[L: 67, P:  1]      |                                    whitespace:               '    '
[L: 67, P:  5]      |                                    alias_expression:
[L: 67, P:  5]      |                                        [META] indent:
[L: 67, P:  5]      |                                        naked_identifier:     'CROSS'
[L: 67, P: 10]      |                                        [META] dedent:
[L: 67, P: 10]      |                                whitespace:                   ' '
[L: 67, P: 11]      |                                [META] dedent:
[L: 67, P: 11]      |                                join_clause:
[L: 67, P: 11]      |                                    keyword:                  'JOIN'
[L: 67, P: 15]      |                                    [META] indent:
[L: 67, P: 15]      |                                    whitespace:               ' '

sqlfluff lint

L:  67 | P:   5 | AL01 | Implicit/explicit aliasing of table.
                       | [aliasing.table]
L:  67 | P:   5 | AL05 | Alias 'CROSS' is never used in SELECT statement.
                       | [aliasing.unused]
L:  67 | P:   5 | CP02 | Unquoted identifiers must be lower case.
                       | [capitalisation.identifiers]
L:  67 | P:   5 | RF04 | WARNING: Keywords should not be used as identifiers.
                       | [references.keywords]
L:  67 | P:  10 | LT02 | Expected line break and no indent before 'JOIN'.
                       | [layout.indent]
L:  67 | P:  11 | AM05 | Join clauses should be fully qualified.
                       | [ambiguous.join]

sqlfluff fix

Code is fixed as:

final AS (
    SELECT
        dim_dates.date,
        users.user_id
    FROM dim_dates INNER JOIN users
)

SELECT * FROM final

How to reproduce

Create a simple statement with a cross join in it and run sqlfluff fix on it:

SELECT
    table_1.col_1,
    table_2.col_2
FROM table_1
CROSS JOIN table_2

Dialect

Snowflake

Version

sqlfluff==2.1.3 sqlfluff-templater-dbt==2.1.3 dbt-core==1.5.0 dbt-snowflake==1.5.0

Configuration

.sqlfluff

[sqlfluff]
templater = dbt
dialect = snowflake
warnings = RF04
max_line_length = 80
sql_file_exts = .sql

[sqlfluff:templater]
unwrap_wrapped_queries = True

[sqlfluff:templater:jinja]
apply_dbt_builtins = True

[sqlfluff:templater:dbt]
project_dir = .
profile = default
target = sqlfluff

[sqlfluff:indentation]
tab_space_size = 4
indent_unit = space
indented_then = False
indented_joins = False
indented_ctes = False
template_blocks_indent = True
allow_implicit_indents = True
indented_using_on = False
indented_on_contents = False
trailing_comments = before

[sqlfluff:rules]
allow_scalar = True
single_table_references = consistent
unquoted_identifiers_policy = all
ambiguous_distinct = error

[sqlfluff:rules:ambiguous.join]
fully_qualify_join_types = inner

[sqlfluff:rules:references.consistent]
force_enable = True
single_table_references = unqualified

[sqlfluff:rule:layout.select_targets]
wildcard_policy = single

[sqlfluff:rules:structure.subquery]
forbid_subquery_in = both

.sqlfluffignore

dbt_packages/
target/
macros/
analysis/

Are you willing to work on and submit a PR to address the issue?

• [X] Yes I am willing to submit a PR!

Code of Conduct

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

[keraion]

This has been fixed in #4996

[Nazaniiin]

This has been fixed in #4996

Thank you! Confirming that this is now fixed!