Wrong result with materialization and NOT IN with 2 expressions

[Philip Stoev]

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.

[Timour Katchaounov (timour)]

I have no idea how did I reproduce this bug. I tried with several
revisions of 5.3 all the way back to the end of June, and was not able
to reproduce it.

Philip, please let me know if I missed something.

[Philip Stoev (pstoev-askmonty)]

New test case:

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( pk int(11) NOT NULL , col_int_nokey int(11), col_int_key int(11), col_varchar_key varchar(1) COLLATE latin1_swedish_ci) ;
INSERT IGNORE INTO t2 VALUES (1,9,1,NULL),(2,6,5,'r');

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( pk int(11) NOT NULL , col_int_nokey int(11), col_int_key int(11), col_varchar_key varchar(1) COLLATE latin1_swedish_ci) ;
INSERT IGNORE INTO t1 VALUES (2,5,4,'d');

SET SESSION 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';
SELECT alias2.col_int_nokey , alias1.pk
FROM t2
 AS alias1 , t1 AS alias2
WHERE
( alias2.col_int_nokey , alias1.pk )
 NOT IN
(
SELECT col_int_key , pk
FROM t2 )

 bzr version-info
revision-id: <email address hidden>
date: 2011-08-17 14:10:32 +0300
build-date: 2011-08-18 10:51:07 +0300
revno: 3163
branch-nick: maria-5.3

[Timour Katchaounov (timour)]

This is a bug in the rowid-merge partial matching algorithm.
Even the first test case can be reproduced with the following
optimizer switch:

SET @optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';