Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
This query:
SELECT *
FROM t1 , t2
WHERE ( t1.f2 , t2.f3 ) NOT IN (
SELECT t3.f3 , t4.f3
FROM t3 STRAIGHT_JOIN t4
ON ( t4.f1 = t3.f1 )
);
returns 1 row when run with in_to_exists:
| g | c |
and 2 rows otherwise:
| NULL | c |
| g | c |
explain:
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 17 | Using where |
| 2 | DEPENDENT SUBQUERY | t4 | ALL | NULL | NULL | NULL | NULL | 43 | Using where |
minimal optimizer switch: in_to_Exists=on,materialization=off
full 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=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,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
also affects mysql 5.5
test case:
CREATE TABLE t1 ( f2 varchar(1) ) ;
INSERT INTO t1 VALUES (NULL),('g');
CREATE TABLE t2 ( f3 varchar(1) ) ;
INSERT INTO t2 VALUES ('c');
CREATE TABLE t3 ( f1 int(11), f3 varchar(1) ) ;
INSERT INTO t3 VALUES (4,'v'),(7,'c'),(0,'x'),(7,'i'),(7,'e'),(1,'p'),(7,'s'),(1,'j'),(5,'z'),(2,'c'),(0,'a'),(1,'q'),(8,'y'),(1,'r'),(9,'v'),(1,NULL),(5,'r');
DROP TABLE IF EXISTS t4;
CREATE TABLE t4 ( f1 int(11), f3 varchar(1) ) ;
INSERT INTO t4 VALUES (1,'x'),(4,'l'),(1,'i'),(0,'i'),(0,'e'),(9,'h'),(5,'f'),(9,NULL),(0,'p'),(2,'n'),(5,'x'),(1,'d'),(4,'t'),(5,'u'),(5,'p'),(1,'o'),(7,'v'),(8,'x'),(2,'b'),(7,'m'),(4,'v'),(0,'j'),(8,NULL),(5,NULL),(8,'h'),(2,'k'),(9,'k'),(7,NULL),(5,'n'),(7,'e'),(0,'s'),(4,'w'),(1,'z'),(0,'b'),(5,'i'),(1,'t'),(2,'g'),(1,'q'),(7,'l'),(1,'n'),(9,'z'),(9,'n'),(8,'r');
SET SESSION optimizer_switch='in_to_exists=on,materialization=off';
SELECT *
FROM t1 , t2
WHERE ( t1.f2 , t2.f3 ) NOT IN (
SELECT t3.f3 , t4.f3
FROM t3 STRAIGHT_JOIN t4
ON ( t4.f1 = t3.f1 )
);
bzr version-info:
revision-id: <email address hidden>
date: 2011-10-06 01:21:15 +0400
build-date: 2011-10-06 13:02:50 +0300
revno: 3213
branch-nick: maria-5.3
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with in_to_exists=ON and NOT IN
There is a partial match between the outer row (NULL, 'c') and the inner row ('v', null).
Therefore the correct result of the IN predicate is NULL, and the strategy that produces
an incorrect result is partial_match_rowid_merge.
Launchpad bug id: 869036
Re: Wrong result with in_to_exists=ON and NOT IN
The problem can be demonstrate with the following very simple test case:
create table outer_sq (f1 char(1), f2 char(1));
insert into outer_sq values (NULL, 'c'), ('g', 'c');
create table inner_sq (f3 char(1), f4 char(1));
insert into inner_sq values(null, 'i'), ('v', null);
set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';
SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq;
SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq);
set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on';
SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq;
SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq);
set @@optimizer_switch='in_to_exists=on,materialization=off';
SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq;
SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq);