Exists2In: Wrong result (extra or missing rows) with exists_to_in + materialization, EXISTS subquery

SELECT * FROM t1 AS alias

WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );

bzr version-info

revision-id: sanja@montyprogram.com-20120430093355-4yrpta86g97g6rhb

date: 2012-04-30 12:33:55 +0300

build-date: 2012-05-02 17:50:48 +0400

revno: 3390

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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,extended_keys=off,exists_to_in=on

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	alias	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

Warnings:

Note	1276	Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1

Note	1276	Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1

Note	1003	select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` where <in_optimizer>(`test`.`alias`.`b`,`test`.`alias`.`b` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` > `test`.`alias`.`a`) ), <primary_index_lookup>(`test`.`alias`.`b` in <temporary table> on distinct_key where ((`test`.`alias`.`b` = `<subquery2>`.`a`)))))

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	alias	ALL	NULL	NULL	NULL	NULL	2	100.00	

1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	Using where

2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	

Warnings:

Note	1276	Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1

Note	1276	Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1

Note	1003	select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where ((`test`.`t1`.`a` > `test`.`alias`.`a`))

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	alias	ALL	NULL	NULL	NULL	NULL	2	100.00	

1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	Using where

2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	

Warnings:

Note	1276	Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1

Note	1276	Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1

Note	1003	select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where ((`test`.`t1`.`a` > `test`.`alias`.`a`))

SET optimizer_switch='exists_to_in=on';

CREATE TABLE t1 ( a VARCHAR(1), b VARCHAR(1) );

INSERT INTO t1 VALUES ('v','v'),('s','v');

SELECT * FROM t1 AS alias

WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );

# a   b

# -----

# s   v