Bug #68194 Wrong result (missing row) with index_merge

Submitted: 27 Jan 2013 22:50 Modified: 31 Jan 2013 16:03
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1, 5.5, 5.6.9 OS:Any
Assigned to: CPU Architecture:Any

[27 Jan 2013 22:50] Elena Stepanova
Description:
With the structure and data provided in 'How to repeat' section, the following query returns an empty result set with index_merge=on (default) and 1 row with index_merge=off. The latter is the correct result, since the row satisfies the first condition on pk, and the big OR condition should be irrelevant.

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:

select_type	SIMPLE
table	t1
type	index_merge
possible_keys	PRIMARY,f1,f4,f5
key	f4,f5,PRIMARY
key_len	13,67,4
ref	NULL
rows	5
filtered	100.00
Extra	Using sort_union(f4,f5,PRIMARY); Using where
Warnings:
Level	Note
Code	1003
Message	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 <cache>((3 + 133)))) and ((`test`.`t1`.`f4` like 'Wyoming') or (`test`.`t1`.`f5` like 'Oregon'))))

How to repeat:
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' ) 
;

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' ) 
;
[27 Jan 2013 22:54] Elena Stepanova
Sorry, the last fragment in the test is not needed, it's just a duplicate remained from an intermediate test case (starting from the second 'SET optimizer_switch').
[28 Jan 2013 12:02] Simon Martin
I think the bug on the reported test case is when index_merge is off.

The final "AND ( f4 LIKE 'Wyoming' OR f5 LIKE 'Oregon' )" is outside the OR so should be satisfied along with the PK condition, but in the returned result with  index_merge=off it is not.

However if you move the bracket to put the last AND inside the OR the bug seems to move, as now index_merge=on still returns no result (which it should I think). So the bug as stated by Elena is there too?

On 5.5:

mysql> set query_cache_type = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.00 sec)

mysql> 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' ) 
    -> ;
Empty set (0.00 sec)

mysql> set optimizer_switch='index_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> 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    |
+----+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> set optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT sql_no_cache  * 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' ) 
    ->     )
    -> ;
Empty set (0.00 sec)

mysql> 
mysql> set optimizer_switch='index_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT sql_no_cache * 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    |
+----+------+------+------+------+------+
1 row in set (0.00 sec)
[28 Jan 2013 13:07] MySQL Verification Team
Thank you for the bug report.

mysql 5.5 > SELECT sql_no_cache * 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    |
+----+------+------+------+------+------+
1 row in set (0.00 sec)

mysql 5.5 >show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.31              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.31              |
| version_comment         | Source distribution |
| version_compile_machine | x86                 |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)
[28 Jan 2013 13:36] Elena Stepanova
@Godofredo Miguel Solorzano

Hi,

I don't quite understand the verification comment -- do you consider the *non*-empty result set to be a bug? Not sure how you got it though, is it with index_merge ON or OFF? Or do you mean that it's been already fixed in 5.5.31 (why 'verified', then?)

@Simon Martin

Hi,

Given the order of precedence for OR and AND, your change does not make a difference. In an essence, we initially have

A || ( ( B || C ) && ( D || E ) ) && ( F || G ) 

so, A is self-sufficient. 

They all are equivalents

A || ( ( B || C ) && ( D || E ) ) && ( F || G ) 
A || ( B || C ) && ( D || E ) && ( F || G )
A || ( ( B || C ) && ( D || E ) && ( F || G ) )

The last of which is yours.
[28 Jan 2013 14:24] Simon Martin
@Elena Thanks, you're right of course. I missed that.
[28 Jan 2013 14:51] Jørgen Løland
@Elena: The bug is confirmed as described. Thank you for the bug report.
[28 Jan 2013 15:58] MySQL Verification Team
@Elena the bug was verified, sorry I pasted partial results. Thanks.
[31 Jan 2013 16:03] Paul DuBois
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.