Rule L042 creates CTE in incorrect position

[nakamichiworks]

Search before asking

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

What Happened

Fixing the rule L042 violation in the following query creates the CTE t2 in incorrect position, which cannot be referenced from the CTE cte1

• Before sqlfluff fix

with

cte1 as (
    select t1.x, t2.y
    from tbl1 t1
    join (select x, y from tbl2) t2
        on t1.x = t2.x
)

, cte2 as (
    select x, y from tbl2 t2
)

select x, y from cte1
union all
select x, y from cte2
;

• After sqlfluff fix

with cte1 as (
    select
        tbl1.x,
        t2.y
    from tbl1
    inner join t2
        on tbl1.x = t2.x
),

cte2 as (
    select
        x,
        y
    from tbl2
),

t2 as (select
    x,
    y
    from tbl2)
select
    x,
    y
from cte1
union all
select
    x,
    y
from cte2;

Expected Behaviour

t2 should be placed before cte1.

with
t2 as (select
    x,
    y
    from tbl2
),

cte1 as (
    select
        tbl1.x,
        t2.y
    from tbl1
    inner join t2
        on tbl1.x = t2.x
),

cte2 as (
    select
        x,
        y
    from tbl2
)

select
    x,
    y
from cte1
union all
select
    x,
    y
from cte2;

Observed Behaviour

Described above.

How to reproduce

Described above.

Dialect

Redshift

Version

sqlfluff: 1.4.2 python: 3.10.6

Configuration

Default configuration is used.

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

[pdebelak]

This appears to be an interaction with L036 and L042:

$ echo 'with

cte1 as (
    select t1.x, t2.y
    from tbl1 t1
    join (select x, y from tbl2) t2
        on t1.x = t2.x
)

, cte2 as (
    select x, y from tbl2 t2
)

select x, y from cte1
union all
select x, y from cte2
;' | sqlfluff fix --dialect redshift -
with cte1 as (                                                                                                                                                      
    select                                                                                                                                                          
        tbl1.x,
        t2.y
    from tbl1
    inner join t2
        on tbl1.x = t2.x
),

cte2 as (
    select
        x,
        y
    from tbl2
),

t2 as (select
    x,
    y
    from tbl2)
select
    x,
    y
from cte1
union all
select
    x,
    y
from cte2;

But disabling L036:

echo 'with

cte1 as (
    select t1.x, t2.y
    from tbl1 t1
    join (select x, y from tbl2) t2
        on t1.x = t2.x
)

, cte2 as (
    select x, y from tbl2 t2
)

select x, y from cte1
union all
select x, y from cte2
;' | sqlfluff fix --dialect redshift --exclude-rules L036 -
with t2 as (select x, y from tbl2),                                                                                                                                 
cte1 as (                                                                                                                                                           
    select tbl1.x, t2.y
    from tbl1
    inner join t2
        on tbl1.x = t2.x
),

cte2 as (
    select x, y from tbl2
)

select x, y from cte1
union all
select x, y from cte2;