--------------------------------------------------------------------------------
-- Original test case
--------------------------------------------------------------------------------
EXPLAIN EXTENDED
SELECT MAX( alias2.a ) AS field
FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
WHERE alias1.a = alias2.a OR alias1.a = 'y'
HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );

-- 5.2:
+----+-------------+-------+----------------+---------------+------+---------+-------+------+----------+--------------------------+
| id | select_type | table | type           | possible_keys | key  | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+----------------+---------------+------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | NULL  | NULL           | NULL          | NULL | NULL    | NULL  | NULL |     NULL | Impossible HAVING        |
|  2 | SUBQUERY    | t1    | index_subquery | a             | a    | 19      | const |    1 |   100.00 | Using index; Using where |
+----+-------------+-------+----------------+---------------+------+---------+-------+------+----------+--------------------------+
-- rewritten to:
select max(alias2.a) AS field
from t1 alias1 join t1 alias2 join t1 alias3
where (multiple equal(alias1.a, alias2.a) or multiple equal('y', alias1.a))
having 0
-- time: 0 sec


-- 5.3, join_cache_level=2 (default), materialization=off
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------+
| 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                                     |
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------+
-- rewritten to:
select max(alias2.a) AS field
from t1 alias1 join t1 alias2 join t1 alias3
where ((alias1.a = alias2.a) or (alias1.a = 'y'))
having ((field > 'B') and <expr_cache><'Moscow'>(<in_optimizer>('Moscow',<exists>(<index_lookup>('Moscow' in t1 on a where ('Moscow' = t1.a))))))
-- time: 1.81 sec

-- 5.3, join_cache_level=2 (default), materialization=on
+----+--------------+--------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| 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 | MATERIALIZED | t1     | index | a             | a    | 19      | NULL |  133 |   100.00 | Using index                                                  |
+----+--------------+--------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
-- rewritten to:
select max(alias2.a) AS field
from t1 alias1 join t1 alias2 join t1 alias3
where ((alias1.a = alias2.a) or (alias1.a = 'y'))
having ((field > 'B') and <expr_cache><'Moscow'>(<in_optimizer>('Moscow','Moscow' in ( <materialize> (select t1.a from t1 ), <primary_index_lookup>('Moscow' in <temporary table> on distinct_key where (('Moscow' = <subquery2>.a)))))))
-- time: 1.79 sec


-- 5.3, join_cache_level=0, materialization=off/on
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------+
| 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 |
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+--------------------------+
-- rewritten to:
select max(alias2.a) AS field
from t1 alias1 join t1 alias2 join t1 alias3
where ((alias2.a = alias1.a) or (alias1.a = 'y'))
having ((field > 'B') and <expr_cache><'Moscow'>(<in_optimizer>('Moscow',<exists>(<index_lookup>('Moscow' in t1 on a where ('Moscow' = t1.a))))))
-- time (in-exists):       0.07 sec
-- time (materialization): 0.07 sec

--------------------------------------------------------------------------------
-- Simplified test case
--------------------------------------------------------------------------------
EXPLAIN EXTENDED
SELECT count(*)
FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );

-- 5.2
+----+-------------+--------+----------------+---------------+------+---------+--------------------+------+----------+--------------------------------+
| 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 | ref            | a             | a    | 19      | lpb944706.alias1.a |    1 |   100.00 | Using where; Using index       |
|  1 | PRIMARY     | alias3 | index          | NULL          | a    | 19      | NULL               |  133 |   100.00 | Using index; Using join buffer |
|  2 | SUBQUERY    | t1     | index_subquery | a             | a    | 19      | const              |    1 |   100.00 | Using index; Using where       |
+----+-------------+--------+----------------+---------------+------+---------+--------------------+------+----------+--------------------------------+
select count(0) AS `count(*)` from `lpb944706`.`t1` `alias1` join `lpb944706`.`t1` `alias2` join `lpb944706`.`t1` `alias3` where (`lpb944706`.`alias2`.`a` = `lpb944706`.`alias1`.`a`)
-- time: 0.0 sec

-- 5.3, join_cache_level=2 (default), materialization=off
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+---------------------------------------------------------------------+
| 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 (incremental, BNL join) |
|  2 | DEPENDENT SUBQUERY | t1     | index_subquery | a             | a    | 19      | const |   10 |   100.00 | Using index; Using where                                            |
+----+--------------------+--------+----------------+---------------+------+---------+-------+------+----------+---------------------------------------------------------------------+
select count(0) AS `count(*)` from `lpb944706`.`t1` `alias1` join `lpb944706`.`t1` `alias2` join `lpb944706`.`t1` `alias3` where ((`lpb944706`.`alias1`.`a` = `lpb944706`.`alias2`.`a`) or <expr_cache><'Moscow'>(<in_optimizer>('Moscow',<exists>(<index_lookup>('Moscow' in t1 on a where ('Moscow' = `lpb944706`.`t1`.`a`))))))
-- time: 3.19 sec
   
-- 5.3, join_cache_level=2 (default), materialization=on
+----+--------------+--------+-------+---------------+------+---------+------+------+----------+---------------------------------------------------------------------+
| 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 (incremental, BNL join) |
|  2 | MATERIALIZED | t1     | index | a             | a    | 19      | NULL |  133 |   100.00 | Using index                                                         |
+----+--------------+--------+-------+---------------+------+---------+------+------+----------+---------------------------------------------------------------------+
select count(0) AS `count(*)` from `lpb944706`.`t1` `alias1` join `lpb944706`.`t1` `alias2` join `lpb944706`.`t1` `alias3` where ((`lpb944706`.`alias1`.`a` = `lpb944706`.`alias2`.`a`) or <expr_cache><'Moscow'>(<in_optimizer>('Moscow','Moscow' in ( <materialize> (select `lpb944706`.`t1`.`a` from `lpb944706`.`t1` ), <primary_index_lookup>('Moscow' in <temporary table> on distinct_key where (('Moscow' = `<subquery2>`.`a`)))))))
--time: 3.15 sec

-- 5.3, join_cache_level=0 (default), materialization=off/on
+----+--------------+--------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 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 | MATERIALIZED | t1     | index | a             | a    | 19      | NULL |  133 |   100.00 | Using index              |
+----+--------------+--------+-------+---------------+------+---------+------+------+----------+--------------------------+
-- time (in-exists):       0.08 sec
-- time (materialization): 0.07 sec