|
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;
|
|