Wrong result (extra row) with AND and OR conditions

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))