Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.1, 5.5.29
-
None
-
None
Description
The following test case produces COUNT = 23712 when it is executed with exists_to_in=off, and 23713 when it is executed with exists_to_in=on.
Postgres, MySQL 5.6 and older versions of MariaDB all return 23712, so I assume it is the correct result.
Test case:
set optimizer_switch='exists_to_in=on'; |
|
|
CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES |
(4),(8),(0),(0),(0),(7),(7),(5),(3),(4),(9),(6),(1),(5),(6),(2),(4),(4),
|
(4),(3),(3),(7),(6),(7),(9),(4),(4),(2),(1),(2),(2),(3),(8),(4),(1),(7),
|
(9),(4),(5),(5),(9),(3),(8),(0),(3),(1),(0),(8),(3),(3),(9),(6),(1),(0),
|
(8),(3),(9),(5),(9),(2),(5),(9),(1),(8),(7),(6),(2),(4),(7),(3),(8),(6);
|
|
|
CREATE TABLE t2 (i2 INT, j2 INT) ENGINE=MyISAM; |
INSERT INTO t2 VALUES |
(7,1),(0,7),(9,4),(3,7),(4,0),(2,2),(5,9),(3,4),(1,0),(3,9),
|
(5,8),(1,8),(204,18),(224,84),(9,6),(5,3),(0,6),(6,1),(7,3);
|
|
|
CREATE TABLE t3 (i3 INT, KEY(i3)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES |
(0),(8),(1),(8),(9),(24),(6),(1),(6),
|
(2),(4),(8),(4),(4),(7),(4),(1),(9),(4);
|
|
|
SELECT COUNT(*) FROM t1 outer_t1, t2 outer_t2, t3 |
WHERE EXISTS ( |
SELECT 1 FROM t2 LEFT JOIN t3 ON ( i3 = j2 ) |
WHERE j2 = outer_t1.i1 AND i2 <> outer_t2.j2 |
);
|
Result with exists_to_in=off:
COUNT(*)
|
23712
|
Result with exists_to_in=on:
COUNT(*)
|
23713
|
EXPLAIN with exists_to_in=on, otherwise default optimizer_switch:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY outer_t2 ALL NULL NULL NULL NULL 19 100.00
|
1 PRIMARY t3 index NULL i3 5 NULL 19 100.00 Using index; Using join buffer (flat, BNL join)
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 19 100.00 Using where; Start temporary; Using join buffer (incremental, BNL join)
|
1 PRIMARY t3 ref i3 i3 5 test.t2.j2 2 100.00 Using where; Using index
|
1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 72 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1276 Field or reference 'test.outer_t1.i1' of SELECT #2 was resolved in SELECT #1
|
Note 1276 Field or reference 'test.outer_t2.j2' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select count(0) AS `COUNT(*)` from `test`.`t1` `outer_t1` semi join (`test`.`t2` left join `test`.`t3` on(((`test`.`t3`.`i3` = `test`.`t2`.`j2`) and (`test`.`t2`.`j2` is not null)))) join `test`.`t2` `outer_t2` join `test`.`t3` where ((`test`.`outer_t1`.`i1` = `test`.`t2`.`j2`) and (`test`.`t2`.`i2` <> `test`.`outer_t2`.`j2`))
|