Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query:
SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
WHERE f2
AND ( f3 IS NULL OR f1 = 7 )
AND f1 NOT BETWEEN 255 AND 0 OR f3 BETWEEN 2 AND 2 AND
( f3 IS NULL OR f4 != 1 ) ;
returns no rows when executed with "union" in maria-5.1-wl21 and 1 row when executed with other optimizations and other trees. In 5.1 , the "union" optimization is not used.
test case:
CREATE TABLE t1 (f1 int,f2 int,f3 int,f4 int,
PRIMARY KEY (f1),KEY (f3),KEY (f4)) ;
INSERT IGNORE INTO t1 VALUES ('9','0','2','6'),('9930','0','0',NULL);
SET SESSION optimizer_switch='index_merge_intersection=off,index_merge_sort_intersection=off';
SET SESSION optimizer_switch='index_merge_union=on,index_merge_sort_union=off';
SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
WHERE f2 AND
( f3 IS NULL OR f1 = 7 )
AND f1 NOT BETWEEN 255 AND 0 OR f3 BETWEEN 2 AND 2 AND
( f3 IS NULL OR f4 != 1 ) ;
explain:
id: 1
select_type: SIMPLE
table: t1
type: index_merge
possible_keys: PRIMARY,f3,f4
key: f3,PRIMARY
key_len: 5,4
ref: NULL
rows: 2
Extra: Using union(f3,PRIMARY); Using where
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 685952
Re: Wrong result with "union" in maria-5.3-wl21
This bug is reproducible on maria-5.1-wl24 and has to be fixed there. After this the fix will be merged into maria-5.1-wl21.