Diverging results with partial_match_rowid_merge=on

[Philip Stoev]

Even after the fix for mysql bug 51070, the following 2 queries return different results for no apparent reason when executed with partial_match_rowid_merge=on

SELECT * FROM t1 WHERE ( 6 , 4 ) NOT IN ( SELECT b , a FROM t2 );
SELECT * FROM t1 WHERE ( 6 , 4 ) NOT IN ( SELECT a , b FROM t2 );

Either the two queries must return no rows because NOT IN involving NULLs is NULL and thus FALSE, or they should both return rows because (6, 4) is actually not present in t2.

test case:

CREATE TABLE t1 (c int) ;
INSERT INTO t1 VALUES (0),(0);

CREATE TABLE t2 (a int, b int) ;
INSERT INTO t2 VALUES (6,3), (9,NULL);

SET SESSION optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
SELECT * FROM t1 WHERE ( 6 , 4 ) NOT IN ( SELECT b , a FROM t2 );
SELECT * FROM t1 WHERE ( 6 , 4 ) NOT IN ( SELECT a , b FROM t2 );

The counters show that a different strategy is chosen for each query. Explain is identical:

| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | SUBQUERY | t2 | system | NULL | NULL | NULL | NULL | 1 | |

minimal optimizer switch:partial_match_rowid_merge=on,partial_match_table_scan=off

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=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=on,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=on,table_elimination=on

bzr version-info
revision-id: <email address hidden>
date: 2011-07-11 10:56:48 -0700
build-date: 2011-07-12 14:20:57 +0300
revno: 3100
branch-nick: maria-5.3

[Timour Katchaounov (timour)]

The second query produces wrong result. The needed switch is:
SET SESSION optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off';