Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
Description
CREATE TABLE t (a DATE NOT NULL, b INT) ENGINE=MyISAM; |
INSERT INTO t VALUES ('1980-01-23',1); |
|
|
SELECT * FROM t WHERE (a, b) IN ( SELECT a, b FROM t HAVING b = 1 ); |
SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 ); |
|
|
DROP TABLE t; |
With the default optimizer switch (all of in_to_exists, semijoin and matherialization ON), IN query correctly returns the row, while NOT IN query incorrectly returns the same row:
|
10.3 be99d0dd |
SELECT * FROM t WHERE (a, b) IN ( SELECT a, b FROM t HAVING b = 1 ); |
a b
|
1980-01-23 1
|
SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 ); |
a b
|
1980-01-23 1
|
Plans with the default optimizer switch:
EXPLAIN EXTENDED SELECT * FROM t WHERE (a, b) IN ( SELECT a, b FROM t HAVING b = 1 ); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t system NULL NULL NULL NULL 1 100.00 |
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 const,const 1 100.00 |
2 MATERIALIZED t system NULL NULL NULL NULL 1 100.00 |
Warnings:
|
Note 1003 /* select#1 */ select '1980-01-23' AS `a`,1 AS `b` from <materialize> (/* select#2 */ select '1980-01-23',1 from dual having 1) where `<subquery2>`.`a` = '1980-01-23' and `<subquery2>`.`b` = 1 |
EXPLAIN EXTENDED SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 ); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t system NULL NULL NULL NULL 1 100.00 |
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables |
Warnings:
|
Note 1003 /* select#1 */ select '1980-01-23' AS `a`,1 AS `b` from dual where !<expr_cache><'1980-01-23',1>(<in_optimizer>(('1980-01-23',1),<exists>(/* select#2 */ select '1980-01-23',1 from dual having 0))) |
Without semijoin or materialization the result is even worse, then the IN query incorrectly returns an empty result set, while NOT IN query incorrectly returns a row:
SET optimizer_switch='semijoin=off'; |
SELECT * FROM t WHERE (a, b) IN ( SELECT a, b FROM t HAVING b = 1 ); |
a b
|
SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 ); |
a b
|
1980-01-23 1
|
With in_to_exists=off the result is correct:
SET optimizer_switch='in_to_exists=off'; |
SELECT * FROM t WHERE (a, b) IN ( SELECT a, b FROM t HAVING b = 1 ); |
a b
|
1980-01-23 1
|
SELECT * FROM t WHERE (a, b) NOT IN ( SELECT a, b FROM t HAVING b = 1 ); |
a b
|
DROP TABLE t; |