Bug #83138 Delete with CTE reports unknown column
Submitted: 25 Sep 2016 9:55 Modified: 25 Sep 2016 11:02
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.0-labs-opt-log OS:Any
Assigned to: CPU Architecture:Any
Tags: cte

[25 Sep 2016 9:55] Daniël van Eeden
Description:
This works:
with t1_ids as (select id from t1)
select id from t1_ids;

This doesn't
with t1_ids as (select id from t1)
delete from t1 where t1.id=t1_ids.id;

How to repeat:
mysql [localhost] {msandbox} (test) > with t1_ids as (select id from t1) select id from t1_ids;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > with t1_ids as (select id from t1) delete from t1 where t1.id=t1_ids.id;
ERROR 1054 (42S22): Unknown column 't1_ids.id' in 'where clause'
[25 Sep 2016 11:02] Daniël van Eeden
Not a bug, the correct query is:

with t1_ids as (select id from t1)
delete from t1 using t1 inner join t1_ids using(id) where t1.id=t1_ids.id;