Wrong result upon query with DISTINCT, subqueries and extended_keys=on

--source include/have_innodb.inc

set optimizer_switch='extended_keys=on';

CREATE TABLE t (id INT PRIMARY KEY, a INT, KEY (a)) ENGINE=InnoDB;

INSERT INTO t VALUES (3,2),(105,3),(127,3),(17,127);

SELECT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

SELECT DISTINCT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

EXPLAIN EXTENDED SELECT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

EXPLAIN EXTENDED SELECT DISTINCT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

set optimizer_switch='extended_keys=off';

SELECT DISTINCT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

EXPLAIN EXTENDED SELECT DISTINCT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

# Cleanup

DROP TABLE t;

SELECT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

a

2

3

SELECT DISTINCT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

a

2

set optimizer_switch='extended_keys=off';

SELECT DISTINCT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

a

2

3

EXPLAIN EXTENDED SELECT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	outer_t	index	NULL	a	5	NULL	4	100.00	Using where; Using index

2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.outer_t.id	1	100.00	Using index

2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.outer_t.id	2	100.00	Using where; Using index; FirstMatch(t1)

2	DEPENDENT SUBQUERY	t	index	NULL	PRIMARY	4	NULL	4	100.00	Using index; Using join buffer (flat, BNL join)

Warnings:

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

Note	1003	/* select#1 */ select `test`.`outer_t`.`a` AS `a` from `test`.`t` `outer_t` where <in_optimizer>(1,<expr_cache><`test`.`outer_t`.`id`>(exists(/* select#2 */ select 1 from `test`.`t` `t1` semi join (`test`.`t` `t2`) join `test`.`t` where `test`.`t2`.`a` = `test`.`t1`.`id` and `test`.`outer_t`.`id` = `test`.`t1`.`id` limit 1)))

EXPLAIN EXTENDED SELECT DISTINCT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	outer_t	range	NULL	a	5	NULL	3	100.00	Using where; Using index for group-by

2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.outer_t.id	1	100.00	Using index

2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.outer_t.id	2	100.00	Using where; Using index; FirstMatch(t1)

2	DEPENDENT SUBQUERY	t	index	NULL	PRIMARY	4	NULL	4	100.00	Using index; Using join buffer (flat, BNL join)

Warnings:

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

Note	1003	/* select#1 */ select distinct `test`.`outer_t`.`a` AS `a` from `test`.`t` `outer_t` where <in_optimizer>(1,<expr_cache><`test`.`outer_t`.`id`>(exists(/* select#2 */ select 1 from `test`.`t` `t1` semi join (`test`.`t` `t2`) join `test`.`t` where `test`.`t2`.`a` = `test`.`t1`.`id` and `test`.`outer_t`.`id` = `test`.`t1`.`id` limit 1)))

EXPLAIN EXTENDED SELECT DISTINCT a FROM t AS outer_t WHERE EXISTS ( SELECT * FROM t WHERE outer_t.id IN ( SELECT t1.id FROM t AS t1 INNER JOIN t AS t2 ON (t2.a = t1.id ) ) ) ;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	outer_t	index	NULL	a	5	NULL	4	100.00	Using where; Using index

2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.outer_t.id	1	100.00	Using index

2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.outer_t.id	2	100.00	Using where; Using index; FirstMatch(t1)

2	DEPENDENT SUBQUERY	t	index	NULL	PRIMARY	4	NULL	4	100.00	Using index; Using join buffer (flat, BNL join)

Warnings:

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

Note	1003	/* select#1 */ select distinct `test`.`outer_t`.`a` AS `a` from `test`.`t` `outer_t` where <in_optimizer>(1,<expr_cache><`test`.`outer_t`.`id`>(exists(/* select#2 */ select 1 from `test`.`t` `t1` semi join (`test`.`t` `t2`) join `test`.`t` where `test`.`t2`.`a` = `test`.`t1`.`id` and `test`.`outer_t`.`id` = `test`.`t1`.`id` limit 1)))