Bug #25204 No Error messages for Subselects in IN/ANY/SOME statements

• Submitted: 20 Dec 2006 8:49
• Modified: 20 Dec 2006 13:38
• Reporter: Peter Volk (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S2 (Serious)
• Version: 5.0.30
• CPU Architecture: Any
• Tags: ANY, error message, IN, SOME, subquery

[20 Dec 2006 8:49] Peter Volk

Description:
For statements within a IN/ANY/SOME the table catalog is not analysed correctly. If the subquery contains invalid column names then the server does not return an error. The page http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html does not list this restriction. 

How to repeat:
Create tables:

/*Table structure for table `tab1` */

CREATE TABLE `tab1` (
  `a` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `tab2` */

CREATE TABLE `tab2` (
  `b` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Correct behaviour (error message):

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

False behaviour (No error message although above select in the IN statement):

mysql> INSERT INTO tab1 (a)
    -> SELECT b
    -> FROM tab2 WHERE b = ANY(SELECT b FROM tab1);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Also:

mysql> SELECT b
    -> FROM tab2 WHERE b IN(SELECT b FROM tab1);
Empty set (0.00 sec)

Suggested fix:
Analyse the subquery correctly. It seems as if the parser does not check all subqueries correctly or the errors are not propageted correctly to the root node of the parsetree.

[20 Dec 2006 13:38] Hartmut Holzgraefe

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

SELECT b FROM tab2 WHERE b IN(SELECT b FROM tab1);

actually comes down to 

SELECT tab2.b FROM tab2 WHERE tab2.b IN (SELECT tab2.b FROM tab1)

so 'b' is taken from the outer context of the query. Not that
the resulting query makes much sense in this case, but it is
perfectly valid SQL.