Bug #36561 Server doesn't report a syntax error in subquery
Submitted: 7 May 2008 11:51 Modified: 7 May 2008 14:56
Reporter: Sergey Kudriavtsev Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1 OS:Linux (Ubuntu 7.10)
Assigned to: CPU Architecture:Any
Tags: error handling, subquery, syntax error

[7 May 2008 11:51] Sergey Kudriavtsev
Description:
When I use a subquery containing syntax error (typo in field name) with outer DELETE statement the server doesn't return any error.
For example:

DELETE FROM table1 WHERE field1 IN (SELECT field FROM table2);

In my case table2 didn't contain the field named `field` (it contained `field1`), but the server didn't report an error. Moreover, server has just deleted ALL the records from table1 (I'd expect it to at least leave table1 untouched).

This happens only when table2 is derived (CREATE TABLE ... SELECT ...) from a table which actually had field named 'field'. See more details in "How to repeat"

How to repeat:
create table t1 (ix int, data char(255));
Query OK, 0 rows affected (0,06 sec)

mysql> insert into t1 values(1,"a");
Query OK, 1 row affected (0,00 sec)

mysql> insert into t1 values(2,"b");
Query OK, 1 row affected (0,00 sec)

mysql> insert into t1 values(3,"c");
Query OK, 1 row affected (0,00 sec)

mysql> create table t2 (ix int);
Query OK, 0 rows affected (0,08 sec)

mysql> insert into t2 values(1);
Query OK, 1 row affected (0,00 sec)

mysql> insert into t2 values(3);
Query OK, 1 row affected (0,01 sec)

mysql> select * from t1;
+------+------+
| ix   | data |
+------+------+
|    1 | a    | 
|    2 | b    | 
|    3 | c    | 
+------+------+
3 rows in set (0,00 sec)

mysql> select * from t2;
+------+
| ix   |
+------+
|    1 | 
|    3 | 
+------+
2 rows in set (0,00 sec)

mysql> create table t3 select ix as xi from t2;//t3 has only `xi` field,not `ix`
Query OK, 2 rows affected (0,02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from t1 where ix in (select ix from t3); //error must be here
Query OK, 3 rows affected (0,01 sec)

mysql> select * from t1;
Empty set (0,00 sec)
[7 May 2008 14:56] Valeriy Kravchuk
I think this is not a bug. In your DELETE:

delete from t1 where ix in (select ix from t3);

server can not find ix column in the t3 table, but then it searches this column in table from outer query. And, surely, there is ix column in t1. Hence the results...

This is a documented behaviour. Please, read http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html:

"Scoping rule: MySQL evaluates from inside to outside."

MySQL server does NOT know if your use of ix was intentional or a misprint, and process subquery as correlated, referring to ix column in the t1.