MySQL Bugs: #27749: No error message is shown when wrong query is fired

Bug #27749	No error message is shown when wrong query is fired
Submitted:	11 Apr 2007 6:24	Modified:	12 Apr 2007 6:56
Reporter:	Aijaz Hassan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.0.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	forgiving nature of mysql, In Clause problem

Description:
When i fire the query:

select * from table1 where col in (select col from table2)

Structure of table1:

col, col1, col2,....

Structure of table2:

col3, col4, .... [but no col]

Still the query gets executed with no warnings or errors! Trying different sql modes for getting an error message also did not worked

How to repeat:
Have table structures as with any datatypes:

Table 1: Student ( rollno, name)       
Table 2: StudentMarks ( rollno, marks)
Table 3(dummy): Dummy (address)
fire the query:

select * from student where rollno in (select rollno from dummy)

Query gets executed!

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

Please read about correlated subqueries at http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html

what you did: Fired a wrong query where the column name does not exists in the table.

what you wanted to happen: Show an error message

what actually happened: Returned an empty result set

Quote from provided link:

Notice that the subquery contains a reference to a column of t1, even though the subquery's FROM clause does not mention a table t1. So, MySQL looks outside the subquery, and finds t1 in the outer query.

The problem is not related with correlated sub-queries, but any simple sub-query. 
Even when the sub-query used in the IN clause is referencing a non-existing column there is no error message shown, and the query executes with null result-set. Please refer to my example in the description before.
>
select * from student where rollno in (select rollno from dummy)
> rollno column does not exist in dummy table, but still the query executes.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

> rollno column does not exist in dummy table, but still the query executes.
Yes. But it exists in the student table, so the subquery is treated as correlated.

> rollno column does not exist in dummy table, but still the query
executes.
Yes. But it exists in the student table, so the subquery is treated as
correlated.

This is not right. Then what is the meaning of the brackets around the sub
query. With brackets if the externals table has to be referenced then it
should have corresponding qualifier without that the column names should
only be searched in the columns of tables in the sub-query. I still feel it
is bug.

Please re-read manual page link to I provided before.

Also read Sergei Golubchik comment to bug #21276.

See also many similar bug reports: Bug #26801, Bug #25204, Bug #23420, Bug #17881