[MDEV-4103] Wrong result (missing row) with index_merge, multiple indexes, AND and OR conditions - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Won't Fix
Affects Version/s: 5.2.13, 5.1.66
Fix Version/s: 5.2.15, 5.1.73
Component/s: None
Labels:
- upstream

Description

The problem is also reproducible on MySQL 5.1-5.6 and filed as http://bugs.mysql.com/bug.php?id=68194.
On MariaDB, reproducible on 5.1 and 5.2, but not on 5.3 and higher, where index_merge is not used for the query even if it's on in the optimizer_switch, and the result is correct.

set optimizer_switch='index_merge=on';
SELECT  * FROM t1 WHERE 
pk IN ( 255, 2, 193, 255, 106 )  
OR ( 
( f5 IN ( 'why' , 'uv' ) OR NOT ( f4 = 'mm' ) ) 
AND 
( ( pk = 1 ) OR f1 NOT BETWEEN 8 AND 3 + 133 ) 
) 
AND ( f4 LIKE 'Wyoming' OR f5 LIKE 'Oregon' ) 
;
pk	f1	f2	f3	f4	f5
set optimizer_switch='index_merge=off';
SELECT  * FROM t1 WHERE 
pk IN ( 255, 2, 193, 255, 106 )  
OR ( 
( f5 IN ( 'why' , 'uv' ) OR NOT ( f4 = 'mm' ) ) 
AND 
( ( pk = 1 ) OR f1 NOT BETWEEN 8 AND 3 + 133 ) 
) 
AND ( f4 LIKE 'Wyoming' OR f5 LIKE 'Oregon' ) 
;
pk	f1	f2	f3	f4	f5
2	345	123	h	M	w

EXPLAIN with index_merge=on:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	PRIMARY,f1,f4,f5	NULL	NULL	NULL	29	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3`,`test`.`t1`.`f4` AS `f4`,`test`.`t1`.`f5` AS `f5` from `test`.`t1` where ((`test`.`t1`.`pk` in (255,2,193,255,106)) or (((`test`.`t1`.`f5` in ('why','uv')) or (`test`.`t1`.`f4` <> 'mm')) and ((`test`.`t1`.`pk` = 1) or (`test`.`t1`.`f1` not between 8 and (3 + 133))) and ((`test`.`t1`.`f4` like 'Wyoming') or (`test`.`t1`.`f5` like 'Oregon'))))

optimizer_switch (default):

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=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,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 (
  pk INT PRIMARY KEY,
  f1 INT,
  f2 INT,
  f3 VARCHAR(10),
  f4 VARCHAR(10),
  f5 VARCHAR(64),
  KEY (f1),
  key (f4),
  key (f5)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES  
 (2, 345, 123, 'h', 'M', 'w') , 
 (3, 46,   61235, 'N', 'w', 'r') , 
 (4, 69,   0, 'why', 'Washington', 'itis') ,  
 (5, 7325, 0,  'z', 'n', 'm') ,
 (6, 297, 234, 'r', 'r', 'then') , 
 (7, 5352, 0, 'California', 'zp', 'pfkxceksatefqsdksjijcszxwbjj') , 
 (8, 102,  54729, 'a', 'r', 'f') ,  
 (9, 6623,  27839, 't', 'want', 'xceksatefqsdksjijcs') , 
 (10, 5189,  239, 'e', 'Illinois', 'say') , 
 (11, 16638, NULL, 's', 'Iowa', 'Alabama') , 
 (12, 343, 234, 'now', 'Oklahoma', 'now') , 
 (13, 3,   37398, 'tefqsdksji', 'Louisiana', 'Arkansas') ,  
 (14, 2620, 182, 'ef', 'f', 'Minnesota') ,  
 (15, 7778, 0, 'qs', 'mm', 'now') ,  
 (16, 454, 153, 'sdk', 'a', 'dksjijcszxwbjjvvk') ,  
 (17, 0,  353, 's', 'j', 'n') , 
 (18, 16406,  24, 'MA', 'i', 'r') , 
 (19, 60642,  75, 'l', 'California', 'ok') ,  
 (20, 6,   52133, 'm', 'New Jer', 'e') ,  
 (21, 8025, 3, 'zxwbjjvvk', 'did', 'h') , 
 (22, 575,  5, 'South Caro', 'w', 'bj') ,
 (23, 3,   37398, 'tefqsdksji', 'Louisiana', 'Arkansas') ,  
 (24, 2620, 182, 'ef', 'f', 'Minnesota') ,  
 (25, 7778, 0, 'qs', 'mm', 'now') ,  
 (26, 454, 153, 'sdk', 'a', 'dksjijcszxwbjjvvk') ,  
 (27, 0,  353, 's', 'j', 'n') , 
 (28, 16406,  24, 'MA', 'i', 'r') , 
 (29, 60642,  75, 'l', 'California', 'ok') ,  
 (30, 6,   52133, 'm', 'New Jer', 'e') 
;

set optimizer_switch='index_merge=on';

SELECT  * FROM t1 WHERE 
  pk IN ( 255, 2, 193, 255, 106 )  
  OR ( 
      ( f5 IN ( 'why' , 'uv' ) OR NOT ( f4 = 'mm' ) ) 
        AND 
      ( ( pk = 1 ) OR f1 NOT BETWEEN 8 AND 3 + 133 ) 
    ) 
    AND ( f4 LIKE 'Wyoming' OR f5 LIKE 'Oregon' ) 
;

Gliffy Diagrams

Attachments

Issue Links

links to

Bug #68194

Activity

Hide

Permalink

Elena Stepanova added a comment - 2013-01-31 19:48

Comment from the corresponding MySQL bug report:

"
Noted in 5.6.11 changelog.

A bug in range optimization sometimes led to incorrect condition
calculation for index merge union. This could lead to missing rows.
"

It means that it was only fixed in 5.6, so MariaDB 5.1 / 5.2 won't get the bugfix.

Show

Elena Stepanova added a comment - 2013-01-31 19:48 Comment from the corresponding MySQL bug report: " Noted in 5.6.11 changelog. A bug in range optimization sometimes led to incorrect condition calculation for index merge union. This could lead to missing rows. " It means that it was only fixed in 5.6, so MariaDB 5.1 / 5.2 won't get the bugfix.

Wrong result (missing row) with index_merge, multiple indexes, AND and OR conditions

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile