sql: CTE name resolution can't pass through VALUES or UNION clauses

[jordanlewis]

WITH outermost(x) AS (
  SELECT 1
  UNION (WITH innermost as (SELECT 2)
         SELECT * FROM innermost
         UNION SELECT 3)
)
SELECT * FROM outermost ORDER BY 1;

expected:

 x 
---
 1
 2
 3
(3 rows)

actual:

relation "innermost" does not exist

[jseldess]

Documented as a known limitation in cockroachdb/docs#2823.

[jseldess]

@jordanlewis, I'm trying to document this for 2.0, but I can't quite understand it. Does CTE name resolution can't pass through VALUES or UNION clauses mean CTEs can't be used VALUES or UNION clauses? Or is it more specific? Can you perhaps draft up a sentence or two for me?

[jseldess]

Ah, I think this was documented by @knz here? https://www.cockroachlabs.com/docs/dev/common-table-expressions.html#use-ctes-at-most-once

[knz]

No this is a different issue.

Jesse I can volunteer to document this known limitation, together with the other one you found yesterday.

[jseldess]

Thanks, @knz! To save extra git work, please just add your writeup as a comment on this PR: cockroachdb/docs#2823

[knz]

done

[andreimatei]

@knz, @jseldess the workaround documented in the "known limitation" doesn't work for me. It says:
https://www.cockroachlabs.com/docs/dev/common-table-expressions.html#using-ctes-with-set-operations

It is not yet possible to use a common table expression defined outside of a set expression in the right operand of a set operator, for example:

WITH a AS (SELECT 1)
SELECT * FROM users UNION SELECT * FROM a; -- "a" used on the right, not yet supported.

For UNION, you can work around this limitation by swapping the operands. For the other set operators, you can inline the definition of the CTE inside the right operand.

Swapping the operands results in the same error:

WITH a AS (SELECT 1)
  SELECT * FROM a UNION SELECT 2;

pq: relation "a" does not exist

[sploiselle]

@jordanlewis Can I get a quick blurb describing this known limitation w/r/t the impact to user experience? Ideally, we need it by Friday 10/26 for the 2.1 Known Limitations page. Posting it on this issue and/or pinging me would be great.

[knz]

This has been fixed in the new opt code -- it's only a limitation in the heuristic planner.

"""
title: Using common table expressions in VALUES and UNION clauses

When the cost-based optimizer is disabled, or when it does not support a query, a common table expression defined outside of a VALUES or UNION clause will not be available inside it. For example WITH a AS (...) SELECT ... FROM (VALUES(SELECT * FROM a)). This limitation will be lifted when the cost-based optimizer covers all queries. Until then applications can work around this limitation by including the entire CTE query in the place where it is used.
"""

[jordanlewis]

Closing due to fixed on CBO status.

[jseldess]

@jordanlewis, can I resolve/remove this known limitation from the 20.1 docs? https://www.cockroachlabs.com/docs/dev/known-limitations.html#using-common-table-expressions-in-values-and-union-clauses

[jordanlewis]

Yes!

[jseldess]

Thanks, @jordanlewis.