Bug #111559 View with CTE syntax error

• Submitted: 26 Jun 2023 9:10
• Modified: 26 Jun 2023 9:27
• Reporter: Pedro Ferreira
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Ubuntu (22.04)
• 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.

[22 Jan 2:21] YEN-CHENG Wu

in version 9.1.0, this issue still exists. Do you have plan to fix this?
Multiple CTE is quite common. I can use it in MSSQL, Oracle, Postgresql, BigQuery.

[22 Jan 7:36] YEN-CHENG Wu

I already know the root cause of the problem.

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

Working:
CREATE VIEW v0 (c0) AS (WITH x(x) AS (SELECT 1), y(y) AS (SELECT 1) SELECT 1 FROM x union select 1 from y);
SELECT 1 FROM v0;

It seems that MySQL will try to remove unuse CTE part, which is good to increase performance. However, it forgot to remove the comma as well.

So the workaround is to remove all unuse CTEs by ourself. Then the view will be correct now.