[MDEV-3686] LP:825095 - Wrong result with materialization and NOT IN with 2 expressions Created: 2011-08-12  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug825095.xml    

 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.

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-08-17 ]

Re: Wrong result with materialization and NOT IN with 2 expressions
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.

Comment by Philip Stoev (Inactive) [ 2011-08-18 ]

Re: Wrong result with materialization and NOT IN with 2 expressions
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: timour@askmonty.org-20110817111032-nbegqxzq30iq7bim
date: 2011-08-17 14:10:32 +0300
build-date: 2011-08-18 10:51:07 +0300
revno: 3163
branch-nick: maria-5.3

Comment by Timour Katchaounov (Inactive) [ 2011-08-19 ]

Re: Wrong result with materialization and NOT IN with 2 expressions
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';

Comment by Rasmus Johansson (Inactive) [ 2011-08-23 ]

Launchpad bug id: 825095

Generated at Thu Feb 08 06:50:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.