MySQL Bugs: #32074: Columns of query containing subqueries is checked after execution of subquery

Bug #32074	Columns of query containing subqueries is checked after execution of subquery
Submitted:	3 Nov 2007 12:43	Modified:	16 Dec 2007 19:57
Reporter:	Bryan Tong Minh	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.42-enterprise-gpl, 5.0.52-BK	OS:	Any
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
When a query that contains a subquery is executed, the columns in the field list of the parent query are checked after the sub query has executed.

How to repeat:
SELECT test FROM (SELECT SLEEP(10)) AS t;

Thank you for a bug report. Verified just as described with latest 5.0.52-BK on Linux.

This is the output I got from the test query:

mysql> SELECT test FROM (SELECT SLEEP(10)) AS t;
ERROR 1054 (42S22): Unknown column 'test' in 'field list'

I don't see any bug here - the virtual table 't' has no
column 'test', hence we get an error. Perhaps you oversimplified
some more complex query that failed for you?

Please provide a complete example that reproduces the problem
you believe is a bug.

The problem is that the error that the main query generates is only thrown _after_ the sub query has executed. What I would expect is that first the existence of a field is checked and then the query is executed, but it appears that first the subquery is executed, then the fields are checked and then the main query is executed.

SQL is declarative language, and generally the SQL standard
doesn'r prescribe any specific order of evaluation of a
statment, the standard only prescribes the semantics of
statements. Any system that implements SQL is free to evaluate
statements as it wishes, as long as the result is correct.
Therefore SQL statements that call functions with side-effects
are particularly dangerous and may result in unpredictable
results. That's why I believe this is not a bug.

All that said, the effect of the test query is due to the fact
that currently MySQL evaluates subqueries in the FROM clause very
early (at parse time). This will be fixed in the near future for
performance reasons. In general you should not rely on the order
of evaluation of SQL clauses in any DBMS.