[MDEV-3689] LP:685952 - Wrong result with "union" in maria-5.3-wl21 Created: 2010-12-06  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: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug685952.xml    

 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



 Comments   
Comment by Igor Babaev [ 2010-12-06 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 685952

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