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

[21 Jun 2023 8:38] Pedro Ferreira
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.
[21 Jun 2023 9:52] MySQL Verification Team
Hello Pedro Ferreira,

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

regards,
Umesh
[12 Apr 14:51] Maxime Conjard
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.
[12 Apr 14:52] Maxime Conjard
first point to the cte on the right -> I meant the cte on the left!
[12 Apr 15:00] Guilhem Bichot
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.