Bug #111559 View with CTE syntax error
Submitted: 26 Jun 9:10 Modified: 26 Jun 9:27
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
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 9:10] Pedro Ferreira
Run these statements:

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

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 9:21] Pedro Ferreira
Fixed typo in title
[26 Jun 9:27] MySQL Verification Team
Hello Pedro Ferreira,

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

[3 Jul 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.