Bug #76583 Wrong columns in subquery , but doesn't throw exception

• Submitted: 3 Apr 2015 3:26
• Modified: 5 Apr 2015 11:45
• Reporter: QIANG ZHANG
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.6.23
• OS: Any
• CPU Architecture: Any

[3 Apr 2015 3:26] QIANG ZHANG

Description:
Error in subquery when subquery use main table columns which does't exist in subquery table.
For example:
table A has c1 and c2
table B has c3 and c4

select c1,c2 from A where c1 in ( select c1 from B );

This Clause DOES NOT RETURN EXCEPTION. Many times  it will return error data... 

How to repeat:
create sample table in description and run the sample sql.

[3 Apr 2015 3:27] QIANG ZHANG

change Severity Label

[3 Apr 2015 13:04] Peter Laursen

Exactly the same on MySQL 5.1, 5.5 and 5.6:

SELECT c1,c2 FROM A WHERE c1 IN ( SELECT c1 FROM B ); -- no error
SELECT c1 FROM B -- Error Code: 1054 - Unknown column 'c1' in 'field list'
(with empty tables.  All columns are INT) 

-- Peter
-- not a MySQL/Oracle person

[5 Apr 2015 11:45] MySQL Verification Team

Hello Zhang,

Thank you for the report.
Imho this is an expected behavior because subquery can reference columns of outer table(e.g c1,c2 etc). Please reference http://dev.mysql.com/doc/refman/5.6/en/correlated-subqueries.html

// MySQL, PostgreSQL ( couldn't check others)

mysql> create table A(c1 int,c2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table B(c3 int,c4 int);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT c1,c2 FROM A WHERE c1 IN ( SELECT c1 FROM B );
Empty set (0.03 sec)

postgres=# create table A(c1 int,c2 int);
CREATE TABLE
postgres=# create table B(c3 int,c4 int);
CREATE TABLE
postgres=# SELECT c1,c2 FROM A WHERE c1 IN ( SELECT c1 FROM B );
 c1 | c2 
----+----
(0 rows)

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

Thanks,
Umesh