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: | |
| 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: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.
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' ) ;