Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.38, 10.0.12
-
None
-
None
Description
Initially reported by stephane@skysql.com on IRC.
CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB; |
|
|
INSERT INTO t1 (a,c) VALUES |
(8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21),
|
(20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28);
|
|
|
SELECT * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c; |
Actual result (all rows except for a=8 shouldn't be there):
+------+------+------+------+
|
| a | b | c | d |
|
+------+------+------+------+
|
| 8 | NULL | 9 | NULL |
|
| 8 | NULL | 10 | NULL |
|
| 13 | NULL | 15 | NULL |
|
| 16 | NULL | 17 | NULL |
|
| 16 | NULL | 18 | NULL |
|
| 16 | NULL | 19 | NULL |
|
| 20 | NULL | 21 | NULL |
|
| 20 | NULL | 22 | NULL |
|
| 20 | NULL | 24 | NULL |
|
| 20 | NULL | 25 | NULL |
|
| 20 | NULL | 26 | NULL |
|
| 20 | NULL | 27 | NULL |
|
| 20 | NULL | 28 | NULL |
|
+------+------+------+------+
|
13 rows in set (0.01 sec)
|
+------+-------------+-------+-------------+---------------+------+---------+-------------+------+----------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------------+---------------+------+---------+-------------+------+----------+----------------------------------------------------+
|
| 1 | SIMPLE | t1 | ref_or_null | idx | idx | 10 | const,const | 3 | 100.00 | Using index condition; Using where; Using filesort |
|
+------+-------------+-------+-------------+---------------+------+---------+-------------+------+----------+----------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
|
MariaDB [test]> show warnings;
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where ((`test`.`t1`.`a` = 8) and ((`test`.`t1`.`b` = 1) or isnull(`test`.`t1`.`b`))) order by `test`.`t1`.`c` |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|