sqlfluff: L044: CTE in CTE doesn't pass linter

Observed Behaviour

Rule L044 fails when there’s a CTE inside another CTE.

Steps To Reproduce

Example SQL:

with a as (
    with b as (
        select 1
    )

    select * from b
)

select * from a

Linter output:

== [example/test.sql] FAIL
L:   9 | P:   1 | L044 | Query produces an unknown number of result columns.

Link to SQLFluff Online output.

Expected Behaviour

Linter rule L044 should PASS on this, as there’s just 1 column expected in the output.

Debug Output

When ran with -vvvvv, here’s the output:

DEBUG      [L044] Analyzing query: select * from a
DEBUG      [L044] Wildcard: * has target a
DEBUG      [L044] Analyzing query: select 1
DEBUG      [L044] Analyzing query: select * from b
DEBUG      [L044] Wildcard: * has target b
DEBUG      [L044] Query target b is external. Generating warning.
DEBUG      [L044] !! Violation Found: 'Query produces an unknown number of result columns.'

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 19 (17 by maintainers)

Most upvoted comments

Hi all, it’s great to see this level of engagement! Happy to provide more context. First of all, the example in the issue was an attempt to create the minimal, reproducible example.

Would be interested to see a real query to see where this would make sense in case I’m missing something.

I stumbled upon that when optimising a complex long SQL file, that has multiple steps of preparing data. The 1st level CTEs are the main building blocks of the logic. Some of these 1st level CTEs have smaller multi-step prep that is too small to move out into separate dbt models, but a bit more complex than a a couple joins all done in one query. That was probably the reason for with inside the 1st level CTEs.

pif on Nov 27, 2021

IIRC, this issue arose partly from the fact that SQLFluff probably didn’t support nested CTEs at the time L044 was added. (Also, do all dialects support nested CTEs?)

Anyway, I’m 100% in favor of fixing this, very much against reducing functionality of L044 and making it (IMHO) too simplistic and restrictive. I also think this will be a pretty quick fix – note that L044 already handles recursion (i.e. following a chain of dependent queries); here, we’re just adding support for a different kind of recursion (nesting, not just a chain of dependencies in a flat structure).

barrywhart on Nov 27, 2021

Thanks for the additional context @pif — and most of all, for taking the positives out of above discussion as all too easy to take offence at discussions like this as a personal attack on your issue/code when that is definitely not the intention!

Even if you want rules for the majority of your code base, some of them just won’t be enforcable for some queries. Hence why we provide inline options to disable for those cases, and also a .sqlfluffignore option to exclude a whole directories or individual file without adding messy inline commands.

But let’s see what @barrywhart can come up with there. Sounds like he’s a plan and he knows SQLFluff better than me so interested to see the results.

tunetheweb on Nov 27, 2021