MySQL Bugs: #79286: Unexpected ERROR 1093 of delete w/ subquery in from stmt

Bug #79286	Unexpected ERROR 1093 of delete w/ subquery in from stmt
Submitted:	16 Nov 2015 3:34	Modified:	16 Nov 2015 12:36
Reporter:	Su Dylan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0/5.1/5.5/5.6/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 int, c2 int);
sert into t1 values(1,1),(2,2Query OK, 0 rows affected (0.00 sec)

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

mysql> delete from t1 where c1 in (select max(c1) from t1 t2);
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
mysql> delete from t1 where c1 in (select min(c2) from (select max(c1) as c2 from t1 ) t2);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
The behavior should be consistent.
"delete from t1 where c1 in (select max(c1) from t1 t2)" is expected to succeed.

How to repeat:

drop table if exists t1;
create table t1(c1 int, c2 int);
insert into t1 values(1,1),(2,2);
delete from t1 where c1 in (select max(c1) from t1 t2);
delete from t1 where c1 in (select min(c2) from (select max(c1) as c2 from t1 ) t2);

Suggested fix:
The behavior should be consistent.
"delete from t1 where c1 in (select max(c1) from t1 t2)" is expected to succeed.

Thank you for the bug report. Same behavior since 5.0.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.10 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > drop table if exists t1;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.03 sec)

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

mysql 5.7 > delete from t1 where c1 in (select max(c1) from t1 t2);
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)

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

mysql 5.0 > delete from t1 where c1 in (select max(c1) from t1 t2);
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

MariaDB 10.1 returns same error number but another and IMO better error message:

Error Code: 1093
Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data.

If it is supposed to work (according to standards), I don't know. But it may make sense as the MariaDB error message describes it. Such statement may change the condition in the middle of statement execution (as rows get deleted) and result could be non-deterministic. An alias does not change that. But the error message of Oracle/MySQL here is nonsense and  confusing IMO.

The longer query "delete from t1 where c1 in (select min(c2) from (select max(c1) as c2 from t1 ) t2);" builds a derived temporary table (t2) so that t1 is not (directly) a "source for data" anymore, and that is why it works. 

In the short form t2 is an *alias* only: in the long statement it is a *table* in my understanding. 

-- Peter
-- not a MySQL/Oracle person

Hi Peter,

Here is result for oracle 11.1 for reference:
===============================
SQL> drop table t1;

Table dropped.

SQL> create table t1(c1 int, c2 int);

Table created.

SQL> insert into t1 values(1,1);

1 row created.

SQL> insert into t1 values(2,2);

1 row created.

SQL> delete from t1 where c1 in (select max(c1) from t1 t2);

1 row deleted.

SQL> delete from t1 where c1 in (select min(c2) from (select max(c1) as c2 from t1 ) t2);

1 row deleted.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

OK, but some MySQL internals may make such statement unsafe with MySQL.

I don't claim to know what is right/wrong | standard/non-standard | possible/not possible here. I only ran your testcase in MariaDB 10.1 and noticed the differently framed error message here and thought it provides some sort of explanation (unlike the Oracle/MySQL error message) why such statement cannot be allowed to execute. At least I think the MariaDB error message shows that it is intentional that the parser refuses the statement with an error.

Hi Peter,

Cool! Thanks for your feedback.
It is quite valuable!

Dylan