Comment 5 for bug 944706

If subqueries are not expensive (5.2, or changed 5.3) the query is optimized as follows:

- JOIN::optimize for the outer query calls:
  having= optimize_cond(this, having, join_list, &having_value, &having_equal);
- optimize_cond() calls remove_eq_conds()
- remove_eq_conds() calls eval_const_cond() as follows:
  else if (cond->const_item() && !cond->is_expensive())
  {
    *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
    return (COND*) 0;
  }
- eval_const_cond() calls Item_in_optimizer::val_int(), which in turn
  optimizes and executes the subquery, and returns FALSE to remove_eq_conds().
- remove_eq_conds() returns a NULL item, and sets JOIN::having_value = Item::COND_FALSE.
- JOIN::optimize checks that having_value is Item::COND_FALSE, and sets
  zero_result_cause= "Impossible HAVING"
- JOIN::exec checks for zero_result_cause and returns empty result without executing.
  EXPLAIN shows "Impossible HAVING".

If subqueries are expensive (5.3), the query is processed as follows:
- optimize_cond for the outer JOIN doesn't remove the HAVING clause.
- The optimizer optimizes both the query and the subquery.
- Execution proceeds normally by executing the three-way join in the
  query until end_send_group evaluates the HAVING clause once, which
  results in a single subquery execution.

Since the subquery is evaluated only once, one may wonder why this query
takes so much time. The reason is an inefficient JOIN plan that is chosen
with the default join_cache_level=2. The plan is:
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------+
| 1 | PRIMARY | alias3 | index | NULL | a | 19 | NULL | 133 | 100.00 | Using index |
| 1 | PRIMARY | alias2 | index | a | a | 19 | NULL | 133 | 100.00 | Using index; Using join buffer (flat, BNL join) |
| 1 | PRIMARY | alias1 | index | a | a | 19 | NULL | 133 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
| 2 | DEPENDENT SUBQUERY | t1 | index_subquery | a | a | 19 | const | 10 | 100.00 | Using index; Using where |
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------+

This plan takes 1.8 sec.

With join_cache_level=0 there is a much better plan:
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | alias1 | index | a | a | 19 | NULL | 133 | 100.00 | Using index |
| 1 | PRIMARY | alias2 | index | a | a | 19 | NULL | 133 | 100.00 | Using where; Using index |
| 1 | PRIMARY | alias3 | index | NULL | a | 19 | NULL | 133 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | t1 | index_subquery | a | a | 19 | const | 10 | 100.00 | Using index; Using where |
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------+

This plan takes 0.07 sec.

If we allow subqueries to be used for constant optimization we mask the problem
with the inneficient JOIN plan.