Bug #89410 delete from ...where not exists with table alias creates an ERROR 1064 (42000)
Submitted: 25 Jan 14:35 Modified: 29 Jan 12:01
Reporter: Peter Lorenz Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.21 OS:Ubuntu (17.10)
Assigned to: CPU Architecture:Any

[25 Jan 14:35] Peter Lorenz
Description:
mysql is reporting an ERROR 1064 (42000) in a special case (see how to repeat) but syntax is correct. 
Working with table alias is working in select statement but fails on delete

use test;

How to repeat:
# simplified testcase, two tables with the following content:

mysql> select * from test;
+---------+
| testcol |
+---------+
| 1       |
| 2       |
| x       |
+---------+
3 rows in set (0.00 sec)

mysql> select * from test2;
+---------+
| testcol |
+---------+
| 1       |
| 2       |
+---------+
2 rows in set (0.00 sec)

# select test with table alias and where (no) exists

mysql> select * from test t1 where exists (select * from test2 t2 where t1.testcol = t2.testcol);
+---------+
| testcol |
+---------+
| 1       |
| 2       |
+---------+
2 rows in set (0.00 sec)

mysql> select * from test t1 where not exists (select * from test2 t2 where t1.testcol = t2.testcol);
+---------+
| testcol |
+---------+
| x       |
+---------+
1 row in set (0.00 sec)

# try to delete with table alias fails

mysql> delete from test t1 where not exists (select * from test2 t2 where t1.testcol = t2.testcol);
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 't1 where not exists (select * from test2 t2 where t1.testcol = t2.testcol)' at line 1

# try to delete without first table alias is working

mysql> delete from test where not exists (select * from test2 t2 where test.testcol = t2.testcol);
Query OK, 1 row affected (0.00 sec)

Suggested fix:
no idea, workaround is using no table alias in that case
[25 Jan 16:22] Miguel Solorzano
Please provide a complete test case with create table statements. Thanks.
[26 Jan 9:32] Peter Lorenz
create database bug8410;
use bug8410;

create table test1 (testcol char(1));
create table test2 (testcol char(1));

insert into test1 (testcol) values ('1');
insert into test1 (testcol) values ('2');
insert into test1 (testcol) values ('x');

insert into test2 (testcol) values ('1');
insert into test2 (testcol) values ('2');

select * from test1;
select * from test2;

select * from test1 t1 where exists (select * from test2 t2 where t1.testcol = t2.testcol);
select * from test1 t1 where not  exists (select * from test2 t2 where t1.testcol = t2.testcol);

Wrong error:

delete from test1 t1 where not exists (select * from test2 t2 where t1.testcol = t2.testc
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 't1 where not exists (select * from test2 t2 where t1.testcol = t2.testcol)' at line 1

Working without Alias:

delete from test1 where not exists (select * from test2 t2 where test1.testcol = t2.testcol);
Query OK, 1 row affected (0.00 sec)

Same Testcase is working on PostgreSQL and Oracle.

regards,
Peter
[29 Jan 12:01] Bogdan Kecman
Hi,
Thanks for the report, verified as stated.
bogdan
[29 Jan 13:52] Roy Lyseng
This is documented syntax in SQL standard, so it is clearly an omission.

MySQL does however support multi-table delete, where it is possible to add correlation names for tables, e.g

  delete t1
  from test as t1
  where not exists (select * from test2 t2 where t1.testcol = t2.testcol);