Comment 2 for bug 944706

Unfortunately the analysis in comment #1 is incorrect as shown below.

The real reason for the difference in execution times between 5.2 and 5.3 is a result of re-engineering subquery optimization to be done during the optimization phase. One consequence of this change is that subqueries can no longer be executed during optimization of the outer query. The reason for this is two-fold:

(1) A subquery may be arbitrarily expensive, thus it may make optimization and explain arbitrarily slow.
(2) Execution of subqueries during the optimization phase has side-effects that permanently change some data structures that represent a subquery. These side-effects later may lead to crashes.

In 5.2 a subquery of the type "const1 IN (SELECT inner_expr FROM ... )" is evaluated during the optimize_cond phase in JOIN::optimize, the result is substituted in the containing condition, and the condition is simplified. In the case of this example, the optimizer finds that the HAVING clause is FALSE, and produces an empty result without even executing the query.

In 5.3 subquery predicates are not evaluated during optimization, therefore such subsitution cannot be performed, and the query is executed as a normal JOIN.

Further investigation of the query plan in 5.3 shows that if we use the default join_cache_level=2, execution is very slow ~1.8 sec, while with join_cache_level=0, execution takes ~0.07 sec. This problem can be shown without a subquery, thus it is not related to this bug.

The attachment below contains detailed EXPLAINs, and execution times for 5.2/5.3 with various combinaitons of materialization=on/off, and join_cache_level=0/2.