|
--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;
|
The first query, without DISTINCT, returns two values (good):
|
10.5 2776635c
|
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
|
The second query, same but with DISTINCT, returns one value (bad):
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
|
The same DISTINCT query but run with extended_keys=off (non-default) returns two values again (good):
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
|
Execution plans for three queries accordingly:
|
No distinct, extended_keys=on
|
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)))
|
|
DISTINCT, extended_keys=on
|
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)))
|
|
DISTINCT, extended_keys=off
|
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)))
|
Two values is the expected result.
Reproducible on 10.5+ with InnoDB.
Not reproducible on 10.4.
Not reproducible with MyISAM.
|