MySQL Bugs: #89410: delete from ...where not exists with table alias creates an ERROR 1064 (42000)

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

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

Please provide a complete test case with create table statements. Thanks.

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

Hi,
Thanks for the report, verified as stated.
bogdan

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);

Documented fix in the MySQL 8.0.16 changelog as follows:

    For consistency with the SQL standard and other RDBMS, table
    aliases are now supported in single-table as well as multi-table
    DELETE statements.

Also noted the change in the DELETE Syntax section of the 8.0 Manual.

Closed.