Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
None
-
None
Description
The following query:
SELECT *
FROM t1
WHERE (a, b ) NOT IN (
SELECT a , b
FROM t2
);
does not return any rows when executed with materialization, even though it should return
a | b
--+--
4 | 4
4 | 2
(2 rows)
explain:
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 |
minimal optimizer switch: in_to_exists=off,materialization=on
full optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
test case:
CREATE TABLE t1 (a int,b int);
INSERT INTO t1 VALUES (4,4),(4,2);
CREATE TABLE t2 (b int, a int);
INSERT INTO t2 VALUES (4,3),(8,4);
SET SESSION optimizer_switch='in_to_exists=off,materialization=on';
SELECT *
FROM t1
WHERE (a, b ) NOT IN (
SELECT a , b
FROM t2
);
Repeatable in maria-5.3. Not repeatable in maria-5.2, mysql-5.5 . Does not involve NULLs, empty tables, empty subqueries or constant tables. Seems the particular values in the tables are significant.