LP:944706 - Query with impossible or constant subquery in WHERE or HAVING is not precomputed and thus not part of optimization

The following query

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 );

works almost instantly on MariaDB 5.2, but takes quite long, depending on the amount of data in t1, on MariaDB 5.3.

bzr version-info
revision-id: <email address hidden>
date: 2012-02-29 23:28:16 -0800
build-date: 2012-03-02 14:57:35 +0400
revno: 3451

bzr version-info
revision-id: <email address hidden>
date: 2012-02-28 13:50:30 +0200
build-date: 2012-02-29 03:39:46 +0400
revno: 3116
branch-nick: maria-5.2

EXPLAIN in 5.3:

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
Warnings:
Note 1003 select max(`test`.`alias2`.`a`) AS `field` from `test`.`t1` `alias1` join `test`.`t1` `alias2` join `test`.`t1` `alias3` where ((`test`.`alias1`.`a` = `test`.`alias2`.`a`) or (`test`.`alias1`.`a` = 'y')) having ((`field` > 'B') and <expr_cache><'Moscow'>(<in_optimizer>('Moscow','Moscow' in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>('Moscow' in <temporary table> on distinct_key where (('Moscow' = `<subquery2>`.`a`)))))))

optimizer_switch in 5.3 (default):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
join_cache_level=2 (default)

EXPLAIN in 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
Warnings:
Note 1003 select max(`test`.`alias2`.`a`) AS `field` from `test`.`t1` `alias1` join `test`.`t1` `alias2` join `test`.`t1` `alias3` where (multiple equal(`test`.`alias1`.`a`, `test`.`alias2`.`a`) or multiple equal('y', `test`.`alias1`.`a`)) having 0

optimizer_switch in 5.2 (default):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,table_elimination=on

Test case:

CREATE TABLE t1 ( a VARCHAR(16), KEY (a) );
INSERT INTO t1 VALUES
('Abilene'),('Akron'),('Albany'),('Albuquerque'),
('Alexandria'),('Allentown'),('Amarillo'),('Anaheim'),
('Anchorage'),('Ann Arbor'),('Arden-Arcade'),
('Arlington'),('Arlington'),('Arvada'),
('Athens-Clarke County'),('Atlanta'),
('Augusta-Richmond County'),('Aurora'),('Aurora'),
('Austin'),('Bakersfield'),('Baltimore'),
('Baton Rouge'),('Beaumont'),('Bellevue'),
('Berkeley'),('Billings'),('Birmingham'),
('Boise City'),('Boston'),('Boulder'),('Bridgeport'),
('Brockton'),('Brownsville'),('Buffalo'),('Burbank'),
('Cambridge'),('Cape Coral'),('Carrollton'),
('Carson'),('Cary'),('Cedar Rapids'),('Chandler'),
('Charleston'),('Charlotte'),('Chattanooga'),
('Chesapeake'),('Chicago'),('Chula Vista'),
('Cincinnati'),('Citrus Heights'),('Clarksville'),
('Clearwater'),('Cleveland'),('Colorado Springs'),
('Columbia'),('Columbus'),('Columbus'),('Compton'),
('Concord'),('Coral Springs'),('Corona'),
('Corpus Christi'),('Costa Mesa'),('Dallas'),
('Daly City'),('Davenport'),('Dayton'),('Denver'),
('Des Moines'),('Detroit'),('Downey'),('Durham'),
('East Los Angeles'),('El Cajon'),('El Monte'),
('El Paso'),('Elgin'),('Elizabeth'),('Erie'),
('Escondido'),('Eugene'),('Evansville'),('Fairfield'),
('Fall River'),('Fayetteville'),('Flint'),('Fontana'),
('Fort Collins'),('Fort Lauderdale'),('Fort Wayne'),
('Fort Worth'),('Fremont'),('Fresno'),('Fullerton'),
('Gainesville'),('Garden Grove'),('Garland'),('Gary'),
('Gilbert'),('Glendale'),('Glendale'),
('Grand Prairie'),('Grand Rapids'),('Green Bay'),
('Greensboro'),('Hampton'),('Hartford'),('Hayward'),
('Henderson'),('Hialeah'),('Hollywood'),('Honolulu'),
('Houston'),('Huntington Beach'),('Huntsville'),
('Independence'),('Indianapolis'),('Inglewood'),
('Irvine'),('Irving'),('Jackson'),('Jacksonville'),
('Jersey City'),('Joliet'),('Kansas City'),
('Kansas City'),('Kenosha'),('Knoxville'),
('Lafayette'),('Lakewood'),('Lancaster'),('Lansing')
;
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 );

1. End of test case