MySQL Bugs: #77952: Subquery gives result even if Column mentioned in the subquery is not present

Bug #77952	Subquery gives result even if Column mentioned in the subquery is not present
Submitted:	6 Aug 2015 0:16	Modified:	13 Aug 2015 8:08
Reporter:	Sajeendra Das	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.5.40	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	subquery

Description:
The Subquery gives results even if the column mentioned in the subquery is not present in the second table at all.
It should have thrown an error saying Column Name incorrect or Column Not found.

For example. Table1 (PersonID, Name, ManagerID). Table2(ID_Manager, Name). Table1 has 30 records, and Table2 has 5 records.

Select * from Table1 where ManagerID in (Select ManagerID from Table2).

In this case, the user is not sure what the column name for Manager in the Table2 is. So, he assumes it to be ManagerID and types the above command.

Typically, it should have thrown an error saying ManagerID not found in Table2.

But, that is not happening. All 30 records from Table1 are being displayed without any erorrs. This might mean to the user that all Managers present in Table1 are present in Table2 also, but that might not be the case.
The user is unaware that the command didnt actually run properly and all records from Table1 are just displayed.

How to repeat:
Run any Subquery, as mentioned above, wherein the Column Name provided in the subquery should not be present in the second table.

Hi Sajeendra,

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php
Imho this is not a bug, it is by design. Please see the explanation provided in Bug #25204

Thanks,
Umesh

Hi Umesh,
I checked the resolution provided in http://bugs.mysql.com/bug.php?id=25204.
I do not agree that the Bug 25204 is related to my bug, 77952.
In Bug 25204, the user is getting message 0 records found for the same subquery.
In my case, I am getting ALL records, which is incorrect and will give wrong impression to the user.

Taking Same Example: Table1 (PersonID, Name, ManagerID). Table2(ID_Manager, Name). Table1 has 30 records, and Table2 has 5 records.

Select * from Table1 where ManagerID in (Select ManagerID from Table2).

User in Bug 25204 is getting 0 records found, which is fine.
I am getting result output which has all 30 rows. This might mean to the user that the query was successful and All records in Table1 have ManagerID in Table2, which is not correct. The ManagerID column is not present in Table2.

Please take a look.

Hi Sajeendra,

I didn't say it is duplicate but the reason for such behavior is explained in there. Imho it is a valid query and not that the resulting query makes much sense in this case, but it is perfectly valid SQL.  See, below examples from MySQL as well as from Postgres. In the below case 'id' is taken from the outer context of the query.

mysql> create table tt1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table tt2(no_id int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tt1 where id in (select id from tt2);
Empty set (0.00 sec)

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

mysql> insert into tt2 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt1 where id in (select id from tt2);
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql> explain extended select * from tt1 where id in (select id from tt2);
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                             |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
|  1 | SIMPLE      | tt2   | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Start temporary                                                   |
|  1 | SIMPLE      | tt1   | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where; End temporary; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                  |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'test.tt1.id' of SELECT #2 was resolved in SELECT #1                                                                  |
| Note  | 1003 | /* select#1 */ select `test`.`tt1`.`id` AS `id` from `test`.`tt1` semi join (`test`.`tt2`) where (`test`.`tt1`.`id` = `test`.`tt1`.`id`) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

// Posrgres

postgres=# select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)

postgres=# 
postgres=#  create table tt1(id int);
CREATE TABLE
postgres=#  create table tt2(no_id int);
CREATE TABLE
postgres=# select * from tt1 where id in (select id from tt2);
 id 
----
(0 rows)

postgres=# insert into tt1 values(1);
INSERT 0 1
postgres=# insert into tt1 values(2);
INSERT 0 1
postgres=# insert into tt1 values(3);
INSERT 0 1
postgres=# insert into tt1 values(4);
INSERT 0 1
postgres=# insert into tt1 values(5);
INSERT 0 1
postgres=# insert into tt2 values(1);
INSERT 0 1
postgres=# select * from tt1 where id in (select id from tt2);
 id 
----
  1
  2
  3
  4
  5
(5 rows)

Thanks,
Umesh