Common Table Expressions (CTE) inside WITH should have their own identifier scope

[stumc]

Common Table Expressions inside WITH should have their own identifier scope. Currently they share the same scope as all other identifiers.

[grandinj]

Are you planning on submitting a fix for this? :-)

[katzyn]

I wrote a some basic implementation of separate identifier scopes for CTE views and it fixes problem from #1374, but our current implementation of CTE has a lot of tricks. Some queries don't work because such tricks cannot work with my changes. When I'm trying to resolve failure in one place something else becomes broken.

[katzyn]

I recently resumed my work on this issue, but I had to remove support of non-standard constructions like WITH something INSERT / UPDATE / DELETE / CREATE TABLE / etc., they are too problematic.

[manticore-projects]

Thank you @katzyn,

I may be overly biased here since we use WITH ... a lot as part of our financial reporting queries but I would say: a simple robust WITH ... SELECT ... (no recursion) was much better than an unreliable cover-all approach.

Please feel very welcome to push any kind of testing and support towards me, I will want to help you as good as I can.

Allow me one question please: Why is the WITH ... clause not just executed (first) into a temporary table, which is then used for the query itself? This is how I would have tried to implement it eventually. It even would allow for indexing? (sorry when this is too naive here)

[katzyn]

We have multiple problems with CTEs:

1. Wrong scope (this one).
2. Problem with parameters (possibly a parser bug).
3. Common problem with deeply nested references in derived tables, they are simply not supported at all and were never supported.
4. Implementation of recursive CTEs is tricky and unreliable.

Materialization of derived tables, CTEs, etc. is another question. And it can't be performed unconditionally, we don't want to materialize something with 1,000,000,000 rows to use only 100 of them.