MySQL Bugs: #111559: View with CTE syntax error

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

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.

Fixed typo in title

Hello Pedro Ferreira,

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

regards,
Umesh

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.

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.

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.

I can confirm it reproducible in both 8.0.45 and 8.4.8.

Steps I used:
```
mysql> CREATE TABLE t1 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>   CREATE VIEW v_repro AS
    ->   WITH used_cte AS (SELECT id FROM t1),
    ->        unused_cte AS (SELECT id FROM t1)
    ->   SELECT * FROM used_cte;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>   SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
    ->   WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'v_repro';
+--------------------------------------------------------------------------------------------------------------------------+
| VIEW_DEFINITION                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------+
| with `used_cte` as (select `mysql`.`t1`.`id` AS `id` from `mysql`.`t1`),  select `used_cte`.`id` AS `id` from `used_cte` |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql>   SHOW CREATE VIEW v_repro;
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 `used_cte`.`id` AS `id` from `used_cte`' at line 1
```

fix cte syntax error agains 8.4.8 branch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix-cte-view-serialization-trailing-coma.patch (application/octet-stream, text), 778 bytes.

Fix with integration tests against 8.0.45 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix-cte-view-serialization.patch (application/octet-stream, text), 8.01 KiB.