MySQL Bugs: #13726: "Unknown column" error is not reported for sub-query in where clause

Bug #13726	"Unknown column" error is not reported for sub-query in where clause
Submitted:	3 Oct 2005 22:03	Modified:	3 Oct 2005 23:02
Reporter:	Jian Wu	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (Linux)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
MySQL doesn't report "Error Code : 1054 Unknown column 'column_name' in 'field list'" when "column_name" is referenced in a sub-query in where clause.

How to repeat:
CREATE TABLE A (a_id INT, col_1 INT, col_2 INT);

CREATE TABLE B (b_id INT, a_id INT);

SELECT b_id FROM B WHERE a_id IN (SELECT id FROM A);

Which version of 4.1 are you using ? I was unable to repeat with
current source server:

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE A (a_id INT, col_1 INT, col_2 INT);
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> CREATE TABLE B (b_id INT, a_id INT);
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> SELECT b_id FROM B WHERE a_id IN (SELECT id FROM A);
ERROR 1054 (42S22): Unknown column 'id' in 'field list'
mysql>

I'm sorry that it was actually my mistake in the query but not a MySQL bug.  The repeat steps should really be this:

CREATE TABLE A (a_id INT, col_1 INT, col_2 INT);

CREATE TABLE B (b_id INT, a_id INT);

SELECT b_id FROM B WHERE b_id IN (SELECT b_id FROM A);

The select query won't generate an error saying unknown column b_id in field list because it thinks b_id is from table B not table A.  If the sub-query gets executed separately, the error does occur.  It was really my own bug in the query.  I apologize for causing any trouble or inconvenience.  Thanks for all the help.