Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Repeatable on maria-5.3-mwl90. Not repeatable in maria-5.3 due to a different query plan.
The following query:
SELECT alias2.f1
FROM t2 AS alias1
LEFT JOIN ( t1 AS alias2 JOIN t1 AS alias3 ON alias3.f2 = alias2.f3 )
ON alias3.f2 = alias2.f2
WHERE alias2.f1 IN ( SELECT f1 FROM t2 AS alias4 ) ;
returns no rows when executed with semijoin=on in maria-5.3-subqueries-mwl90, even though there are rows matching the WHERE clause.
Explain in maria-5.3-subqueries-mwl90:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 Start temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
1 PRIMARY alias2 ALL NULL NULL NULL NULL 3 Using where; End temporary
1 PRIMARY alias3 ALL NULL NULL NULL NULL 3 Using where
explain in maria-5.3:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
1 PRIMARY alias2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
1 PRIMARY alias3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
2 DEPENDENT SUBQUERY alias4 ALL NULL NULL NULL NULL 2 Using where
bzr version-info:
revision-id: <email address hidden>
date: 2011-06-28 18:25:02 +0400
build-date: 2011-06-29 09:45:49 +0300
revno: 3066
branch-nick: maria-5.3-subqueries-mwl90
optimizer_switch in effect:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,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
test case:
SET SESSION optimizer_switch = 'semijoin=on,materialization=off,firstmatch=off,loosescan=off';
CREATE TABLE t2 ( f1 int) ;
INSERT IGNORE INTO t2 VALUES (6),(8);
CREATE TABLE t1 ( f1 int, f2 int, f3 int) ;
INSERT IGNORE INTO t1 VALUES (8,0,0),(7,0,0),(9,0,0);
SELECT alias2.f1
FROM t2 AS alias1
LEFT JOIN ( t1 AS alias2 JOIN t1 AS alias3 ON alias3.f2 = alias2.f3 )
ON alias3.f2 = alias2.f2
WHERE alias2.f1 IN ( SELECT f1 FROM t2 AS alias4 ) ;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with semijoin=on, outer join in maria-5.3-subqueries-mwl90
Neither of examples are repeatable anymore (after MWL#90 has been merged into 5.3 and fixes).
I've added testcases to the testsuite.
Launchpad bug id: 803303
Re: Wrong result with semijoin=on, outer join in maria-5.3-subqueries-mwl90
Another example:
CREATE TABLE t1 ( f10 int) ;
INSERT INTO t1 VALUES (0),(0);
CREATE TABLE t2 ( f10 int, f11 varchar(1)) ;
INSERT INTO t2 VALUES (0,'a'),(0,'b');
CREATE TABLE t3 ( f10 int) ;
INSERT INTO t3 VALUES (0),(0),(0),(0),(0);
CREATE TABLE t4 ( f10 varchar(1), f11 int) ;
INSERT INTO t4 VALUES ('a',0),('b',0);
SELECT * FROM t1
LEFT JOIN ( t2 JOIN t3 ON t3.f10 = t2.f10 ) ON t1.f10 = t2.f10
WHERE t2.f10 IN (
SELECT t4.f11
FROM t4
WHERE t4.f10 != t2.f11
);