MySQL Bugs: #111511: CTE missing scope in set queries

Bug #111511	CTE missing scope in set queries
Submitted:	21 Jun 2023 8:38	Modified:	21 Jun 2023 9:52
Reporter:	Pedro Ferreira	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	cte, UNION

Description:
While testing something I found this error:

WITH x AS (SELECT 1) (WITH y AS (SELECT 1) SELECT 1) UNION (SELECT 1 FROM x);
ERROR 1146 (42S02): Table 'sys.x' doesn't exist

I see the CTE x is still in scope and it should be found by the second subquery in the UNION, so I think the error is invalid.

How to repeat:
Run the query above.

Hello Pedro Ferreira,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

I believe I have found a related issue: 

Here is what I have in t2
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

Resolution of the following query seems to work well:
mysql> (with y(y) as (select 9 )select * from y) union   (WITH y(y)  as (SELECT *  from t2) SELECT * from  y);
+---+
| y |
+---+
| 9 |
| 1 |
| 2 |
| 3 |
+---+
4 rows in set (0.00 sec)

as the results are convincing, however printing such a query will yield wrong results, see:
mysql> create view v as (with y(y) as (select 9 )select * from y) union   (WITH y(y)  as (SELECT *  from t2) SELECT * from  y);

mysql> select * from v;
+---+
| y |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

It is likely because: 

bool PT_with_clause::contextualize(Parse_context *pc) {
  if (super::contextualize(pc)) return true; /* purecov: inspected */
  // WITH complements a query expression (a unit).
  pc->select->master_unit()->m_with_clause = this;
  return false;
}

 pc->select->master_unit()->m_with_clause will first point to the cte on the right, and when looking at the rhs, we overwrite with the cte on the right.

During print, we rely on m_with_clause of the select_lex_unit to print the cte, which will be missing the information about the cte on the left.

first point to the cte on the right -> I meant the cte on the left!

Looking at the Standard, and also from what I remember when I implemented CTEs in MySQL, the syntaxes above are non-compliant and should be blocked. A CTE is to be attached to a query_expression, i.e. a SELECT_LEX_UNIT, i.e. the UNION here ; there's no place in the standard's grammar which would allow it to be inside a parenthesised SELECT part of a UNION, IMHO.