Bug #111559 View with CTE syntax error
Submitted: 26 Jun 2023 9:10 Modified: 26 Jun 2023 9:27
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, VIEW

[26 Jun 2023 9:10] Pedro Ferreira 
Description:
Run these statements:

CREATE VIEW v0 (c0) AS (WITH x(x) AS (SELECT 1), y(y) AS (SELECT 1) SELECT 1 FROM x);
SELECT 1 FROM v0;

The SELECT statement will give a syntax error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 1 AS `1` from `x`' at line 1

The create view statement was successful, plus the view definition runs without errors outside of it. I suspect this is a bug.

How to repeat:
Run the statements above.
[26 Jun 2023 9:21] Pedro Ferreira 
Fixed typo in title
[26 Jun 2023 9:27] MySQL Verification Team 
Hello Pedro Ferreira,

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

regards,
Umesh
[3 Jul 2023 13:36] Dag Wanvik 
Posted by developer:
 
The view text stored is:

   with `x` (`x`) as (select 1 AS `1`),  select 1 AS `1` from `x`

which is obviously wrong, hence the syntax error. The second common_table_expr is missing; only the comma after the first is present.