CTE with column names is handled incorrectly in lint and fix

[mrichards42]

CTEs with column name definitions can be parsed, but linting rules aren't able to handle the column names, and instead seem to treat them as if they were the actual query / table expression. There's some discussion in #345 -- it looks like that issue was about sqlfluff not being able to parse this construction.

Expected Behaviour

Given the following input (this is cleaned up from the recursive example in the postgresql docs)

with recursive t(n) as (
    select 1
    union all
    select n + 1 from t
)

select n from t limit 100;

sqlfluff lint should report no errors, and sqlfluff fix should make no changes.

Observed Behaviour

$ sqlfluff lint sample.sql
== [sample.sql] FAIL
L:   1 | P:  21 | L022 | Blank line expected but not found after CTE closing
                       | bracket.

$ sqlfluff fix sample.sql
with recursive t(n)

as (
    select 1
    union all
    select n + 1 from t
)

select n from t limit 100;

sqlfluff parse output

[L:  1, P:  1]      |file:
[L:  1, P:  1]      |    statement:
[L:  1, P:  1]      |        with_compound_statement:
[L:  1, P:  1]      |            keyword:                                          'with'
[L:  1, P:  5]      |            whitespace:                                       ' '
[L:  1, P:  6]      |            keyword:                                          'recursive'
[L:  1, P: 15]      |            whitespace:                                       ' '
[L:  1, P: 16]      |            common_table_expression:
[L:  1, P: 16]      |                identifier:                                   't'
[L:  1, P: 17]      |                bracketed:
[L:  1, P: 17]      |                    start_bracket:                            '('
[L:  1, P: 18]      |                    [META] indent:
[L:  1, P: 18]      |                    identifier_list:
[L:  1, P: 18]      |                        identifier:                           'n'
[L:  1, P: 19]      |                    [META] dedent:
[L:  1, P: 19]      |                    end_bracket:                              ')'
[L:  1, P: 20]      |                whitespace:                                   ' '
[L:  1, P: 21]      |                keyword:                                      'as'
[L:  1, P: 23]      |                whitespace:                                   ' '
[L:  1, P: 24]      |                bracketed:
[L:  1, P: 24]      |                    start_bracket:                            '('
[L:  1, P: 25]      |                    [META] indent:
[L:  1, P: 25]      |                    newline:                                  '\n'
[L:  2, P:  1]      |                    whitespace:                               '    '
[L:  2, P:  5]      |                    set_expression:
[L:  2, P:  5]      |                        select_statement:
[L:  2, P:  5]      |                            select_clause:
[L:  2, P:  5]      |                                keyword:                      'select'
[L:  2, P: 11]      |                                [META] indent:
[L:  2, P: 11]      |                                whitespace:                   ' '
[L:  2, P: 12]      |                                select_clause_element:
[L:  2, P: 12]      |                                    literal:                  '1'
[L:  2, P: 13]      |                            [META] dedent:
[L:  2, P: 13]      |                        newline:                              '\n'
[L:  3, P:  1]      |                        whitespace:                           '    '
[L:  3, P:  5]      |                        set_operator:
[L:  3, P:  5]      |                            keyword:                          'union'
[L:  3, P: 10]      |                            whitespace:                       ' '
[L:  3, P: 11]      |                            keyword:                          'all'
[L:  3, P: 14]      |                        newline:                              '\n'
[L:  4, P:  1]      |                        whitespace:                           '    '
[L:  4, P:  5]      |                        select_statement:
[L:  4, P:  5]      |                            select_clause:
[L:  4, P:  5]      |                                keyword:                      'select'
[L:  4, P: 11]      |                                [META] indent:
[L:  4, P: 11]      |                                whitespace:                   ' '
[L:  4, P: 12]      |                                select_clause_element:
[L:  4, P: 12]      |                                    expression:
[L:  4, P: 12]      |                                        column_reference:
[L:  4, P: 12]      |                                            identifier:       'n'
[L:  4, P: 13]      |                                        whitespace:           ' '
[L:  4, P: 14]      |                                        binary_operator:      '+'
[L:  4, P: 15]      |                                        whitespace:           ' '
[L:  4, P: 16]      |                                        literal:              '1'
[L:  4, P: 17]      |                            whitespace:                       ' '
[L:  4, P: 18]      |                            [META] dedent:
[L:  4, P: 18]      |                            from_clause:
[L:  4, P: 18]      |                                keyword:                      'from'
[L:  4, P: 22]      |                                whitespace:                   ' '
[L:  4, P: 23]      |                                from_expression:
[L:  4, P: 23]      |                                    [META] indent:
[L:  4, P: 23]      |                                    from_expression_element:
[L:  4, P: 23]      |                                        table_expression:
[L:  4, P: 23]      |                                            table_reference:
[L:  4, P: 23]      |                                                identifier:   't'
[L:  4, P: 24]      |                                    [META] dedent:
[L:  4, P: 24]      |                    newline:                                  '\n'
[L:  5, P:  1]      |                    [META] dedent:
[L:  5, P:  1]      |                    end_bracket:                              ')'
[L:  5, P:  2]      |            newline:                                          '\n'
[L:  6, P:  1]      |            newline:                                          '\n'
[L:  7, P:  1]      |            select_statement:
[L:  7, P:  1]      |                select_clause:
[L:  7, P:  1]      |                    keyword:                                  'select'
[L:  7, P:  7]      |                    [META] indent:
[L:  7, P:  7]      |                    whitespace:                               ' '
[L:  7, P:  8]      |                    select_clause_element:
[L:  7, P:  8]      |                        column_reference:
[L:  7, P:  8]      |                            identifier:                       'n'
[L:  7, P:  9]      |                whitespace:                                   ' '
[L:  7, P: 10]      |                [META] dedent:
[L:  7, P: 10]      |                from_clause:
[L:  7, P: 10]      |                    keyword:                                  'from'
[L:  7, P: 14]      |                    whitespace:                               ' '
[L:  7, P: 15]      |                    from_expression:
[L:  7, P: 15]      |                        [META] indent:
[L:  7, P: 15]      |                        from_expression_element:
[L:  7, P: 15]      |                            table_expression:
[L:  7, P: 15]      |                                table_reference:
[L:  7, P: 15]      |                                    identifier:               't'
[L:  7, P: 16]      |                        [META] dedent:
[L:  7, P: 16]      |                whitespace:                                   ' '
[L:  7, P: 17]      |                limit_clause:
[L:  7, P: 17]      |                    keyword:                                  'limit'
[L:  7, P: 22]      |                    [META] indent:
[L:  7, P: 22]      |                    whitespace:                               ' '
[L:  7, P: 23]      |                    literal:                                  '100'
[L:  7, P: 26]      |                    [META] dedent:
[L:  7, P: 26]      |    statement_terminator:                                     ';'
[L:  7, P: 27]      |    newline:                                                  '\n'

Dialect

postgres, but as noted in #345 this isn't postgres-specific

Version

$ sqlfluff --version
sqlfluff, version 0.9.0
$ python --version
Python 3.10.1

Configuration

default

Thanks!

[jpy-git]

coolio, can fix this pretty easily 👍

[enote-kane]

Sorry to report on a closed ticket, but this doesn't seem to have made it into 1.0.0:

$ sqlfluff lint --nocolor --verbose --dialect postgres 2022-06-21.cte-newline.sql 
==== sqlfluff ====
sqlfluff:                1.0.0 python:                 3.8.10
implementation:        cpython verbosity:                   1
dialect:              postgres templater:               jinja
rules:                                all
==== readout ====

=== [ path: 2022-06-21.cte-newline.sql ] ===

== [2022-06-21.cte-newline.sql] FAIL                                                                                                                                              
L:   6 | P:  13 | L022 | Blank line expected but not found after CTE closing
                       | bracket.
==== summary ====
violations:        1 status:         FAIL
All Finished!

Test file 2022-06-21.cte-newline.sql:

WITH
cte_1 AS (
    SELECT 1 AS var
),

cte_2 (var) AS (
    SELECT 2
)

SELECT
    cte_1.var,
    cte_2.var
FROM cte_1, cte_2;

[tunetheweb]

It looks related but slightly different.

This issue fixed the original test case, and also fixes yours if the (var) is set on cte_1 but not for cte_2.

Let's keep this closed, and can you open a new issue for that? Link to this Issue and PR #2139 in that.