Bug #98330 Inconsitent behaviour for DELETE on CTE

• Submitted: 22 Jan 2020 20:35
• Modified: 28 Jan 2020 19:18
• Reporter: Daniël van Eeden (OCA)
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.18
• OS: Any
• CPU Architecture: Any

[22 Jan 2020 20:35] Daniël van Eeden

Description:
If you use a CTE then I would expect that SELECT, UPDATE and DELETE would always work the same. 

It is expected that DELETE and UPDATE on a table that is fully virtual won't work and doesn't make much sense.

However if there is a CTE called t1 and a real table t1 it looks like SELECT works on the CTE, UPDATE works on the CTE and expectedly fails but DELETE works on the real table instead.

How to repeat:
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> with t1 as (select 1) select * from t1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> with t1 as (select 1) delete from t1;
Query OK, 3 rows affected (0.01 sec)

mysql> with t1 as (select 1) update t1 set `1`=0;
ERROR 1288 (HY000): The target table t1 of the UPDATE is not updatable

Suggested fix:
Ensure DELETE behaves similarly to SELECT and UPDATE for CTEs

[23 Jan 2020 0:25] MySQL Verification Team

Thank you for the bug report.

[28 Jan 2020 19:18] Paul DuBois

Posted by developer:
 
Fixed in 8.0.20.

Common table expressions with a single-table DELETE statement did not
work properly.

[29 Jan 2020 2:35] Paul DuBois

Posted by developer:
 
Corrected changelog entry:

Statements did not work properly when the target of a DELETE was a
common table expression.