Bug #100599 Query with invalid column name in subquery is executed (it should fail)

• Submitted: 21 Aug 2020 10:52
• Modified: 21 Aug 2020 14:13
• Reporter: Jukka Merinen
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.31
• OS: Any
• CPU Architecture: Any

[21 Aug 2020 10:52] Jukka Merinen

Description:
Query with invalid column name is subquery is successfully executed.

How to repeat:
CREATE TABLE table1 (
   column1 INT,
   column2 INT
);

CREATE TABLE table2 (
   column3 INT,
   column4 INT
);

SELECT * FROM table1 WHERE column1 IN ( SELECT column2 FROM table2 );

The query is successfully executed and it returns zero rows. The query should fail because column2 does not exist in table2.

The issue can be reproduced in 5.7.31 and 8.0.21.

[21 Aug 2020 12:56] MySQL Verification Team

Hi Mr. Merinen,

Thank you for your bug report.

However, this is not a bug.

This behaviour is the same with most relational databases, and it is mentioned in paragraph 8.4 of the latest SQL standard.

Simply, nested query which has no values or no columns of the given name is optimised away.

Not a bug.

[21 Aug 2020 13:43] Jukka Merinen

Thanks for your comment.

It seems that the nested query is not completely optimized away.

Here is another example:

CREATE TABLE table1 (
   column1 INT,
   column2 INT
   
);  

CREATE TABLE table2 (
   column4 INT,
   column5 INT
);  

INSERT INTO table1 VALUES (1, 1);
INSERT INTO table1 VALUES (2, 2);
INSERT INTO table1 VALUES (3, 4);
INSERT INTO table1 VALUES (5, 6);
INSERT INTO table2 VALUES (100, 100);

SELECT * FROM table1 WHERE column1 IN ( select column1 FROM table2 );

The query returns all rows from table1. In my opinion, the correct behavior would be to return an error because column1 does not exist in table2.

Tested with 5.7.31 and 8.0.21.

[21 Aug 2020 13:52] MySQL Verification Team

Hi Mr. Merinen,

Do note that in your second example, a nested query is dependent nested query, unlike in your first example.

Hence, a totally changed behaviour.

[21 Aug 2020 13:57] MySQL Verification Team

Here the behavior of Oracle database, same as MySQL:

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> CREATE TABLE table1 (
  2     column1 INT,
  3     column2 INT
  4  );

Table created.

SQL> CREATE TABLE table2 (
  2     column3 INT,
  3     column4 INT
  4  );

Table created.

SQL> SELECT * FROM table1 WHERE column1 IN ( SELECT column2 FROM table2 );

no rows selected

SQL>

[21 Aug 2020 14:13] Jukka Merinen

Thanks for your quick reply.

I agree that the behavior is as expected and it's not a bug.

[24 Aug 2020 12:07] MySQL Verification Team

You are welcome, Mr. Merinen !!!

[8 Sep 2023 7:08] MySQL Verification Team

Bug #112289 marked as duplicate of this one