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) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[22 Jan 2020 20:35] Daniël van Eeden
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.