Bug #81146 Executing a Query with a bad Subselect

• Submitted: 19 Apr 2016 20:20
• Modified: 19 Apr 2016 21:53
• Reporter: Thomas Wnnsche
• Status: Duplicate
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.5
• OS: Ubuntu
• CPU Architecture: Any

[19 Apr 2016 20:20] Thomas Wnnsche

Description:
i wrote a DELETE Query with a mispelled subselect. 
I am expected  not delete a row but all rows was deleted.

How to repeat:
mysql> CREATE TABLE a (ident INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE b (ident2 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO a VALUES (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO b VALUES (2),(3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT ident FROM b;
ERROR 1054 (42S22): Unknown column 'ident' in 'field list'

mysql> DELETE FROM a WHERE ident IN (SELECT ident FROM b);
Query OK, 2 rows affected (0.00 sec)

[19 Apr 2016 21:53] MySQL Verification Team

Thank you for the bug report. Not a bug as explained on bug: https://bugs.mysql.com/bug.php?id=66663. See the behavior of Oracle below too:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> CREATE TABLE a (ident INT);

Table created.

SQL> CREATE TABLE b (ident2 INT);

Table created.

SQL> INSERT INTO a VALUES (1);

1 row created.

SQL> INSERT INTO a VALUES (2);

1 row created.

SQL> INSERT INTO b VALUES (2);

1 row created.

SQL> INSERT INTO b VALUES (3);

1 row created.

SQL> SELECT ident FROM b;
SELECT ident FROM b
       *
ERROR at line 1:
ORA-00904: "IDENT": invalid identifier

SQL> DELETE FROM a WHERE ident IN (SELECT ident FROM b);

2 rows deleted.

[7 May 2017 1:31] MySQL Verification Team

https://bugs.mysql.com/bug.php?id=86200 marked as duplicate of this one.