Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
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';
Launchpad bug id: 825095
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.