MySQL Bugs: #21276: Subqueries are being interpreted incorrectly

Bug #21276	Subqueries are being interpreted incorrectly
Submitted:	25 Jul 2006 14:54	Modified:	26 Aug 2006 8:56
Reporter:	Erica Moss	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.0.25 -log	OS:	Linux (Fedora core 5)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
In the below script there doesn't seem to be a good reason why the subquery was resolved as a dependent subquery.  It appears from the interpretation that t2 is being disregarded completely, and the entirety of t1 is returned to the outer query.  

It seems that the output from this should either be no rows with a warning, or an actual error since there is no column 'a' in table t2, therefore it's most likely a syntax error.

How to repeat:
CREATE DATABASE fooDB;
use fooDB;

CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES (1), (2), (3);

CREATE TABLE t2 (a INT, PRIMARY KEY(a));
INSERT INTO t2 VALUES (1), (2), (3);

SELECT a FROM t2 WHERE a IN (SELECT a FROM t1);

EXPLAIN EXTENDED SELECT a FROM t2
    WHERE a IN (SELECT a FROM t1);

DROP TABLE t1, t2;
DROP DATABASE fooDB;

##############
PARTIAL OUTPUT
SELECT a FROM t2 WHERE a IN (SELECT a FROM t1);
+ a
+ 1
+ 2
+ 3
EXPLAIN EXTENDED SELECT a FROM t2
 WHERE a IN (SELECT a FROM t1);
+ id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+ 1     PRIMARY t2      index   NULL    PRIMARY 4       NULL    3       Using where; Using index
+ 2     DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       Using where
+ Warnings:
+ Note  1276    Field or reference 'a' of SELECT #2 was resolved in SELECT #1
+ Note  1003    select `fooDB`.`t2`.`a` AS `a` from `fooDB`.`t2` where <in_optimizer>(`fooDB`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `fooDB`.`t1` where (<cache>(`fooDB`.`t2`.`a`) = `fooDB`.`t2`.`a`)))

Correcting the description since there is a typo in it:
   "...there is no column 'a' in table t2, therefore..." 
should have said:
   "...there is no column 'a' in table t1, therefore...

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/.

Where is a bug here ? According to the name resolution rules, a column reference is first looked up in a subquery, then in an outer query. Thus, as 'a' in the subquery is not found in a t1, it is looked up in t2. The query becomes

  SELECT t2.a FROM t2 WHERE t2.a IN (SELECT t2.a FROM t1);

here WHERE condition is always true, and the query does simply

  SELECT a FROM t2;

as expected