Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
It is most likely a regression introduced (or made visible) by the following revision:
revno: 3628
|
revision-id: igor@askmonty.org-20130225031611-jk8lyhhjazov66qc
|
committer: Igor Babaev <igor@askmonty.org>
|
message:
|
Fixed bug mdev-4177
|
I cannot verify it with 100% certainty because the provided test case causes a crash on revisions 3628..3634; but it produces a correct result on maria/5.3 up to revno 3627 and a wrong result starting from revno 3635 and up to (and including) the current 3646.
Also reproducible on current maria/5.5.
Test case:
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,NULL); |
 |
SELECT * FROM t1 |
WHERE ( NULL OR a = 5 ) AND ( b != 1 OR a = 1 ); |
Actual result:
a b
|
5 NULL
|
Expected result - empty set:
a b
|
Inconsistency of the actual result can be confirmed by executing the second part of AND separately:
SELECT * FROM t1 |
WHERE b != 1 OR a = 1; |
a b
|
1 101
|
2 102
|
3 103
|
4 104
|
It is a correct result, and it doesn't include the row with a=5, so the result set for the bigger query cannot include it either (but currently it does).
Reproducible with the default optimizer_switch as well as with all OFF values.
EXPLAIN:
EXPLAIN EXTENDED
|
SELECT * FROM t1
|
WHERE ( NULL OR a = 5 ) AND ( b != 1 OR a = 1 );
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and ((`test`.`t1`.`b` <> 1) or 1))
|