Wrong result (missing rows) on 2nd execution of PS with ANY ( SQ )

revno: 3843

revision-id: psergey@askmonty.org-20130731132452-4qb11t56i2jw16aa

parent: psergey@askmonty.org-20130731093701-10tmxhe668f3u1lx

committer: Sergey Petrunya <psergey@askmonty.org>

branch nick: 5.5

timestamp: Wed 2013-07-31 17:24:52 +0400

message:

  MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL

  - Modify the way Item_cond::fix_fields() and Item_cond::eval_not_null_tables() 

    calculate bitmap for Item_cond_or::not_null_tables():

    if they see a "... OR inexpensive_const_false_item OR ..." then the item can

    be ignored.

  - Updated test results. There can be more warnings produced since parts of WHERE 

    are evaluated more times.

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (b INT) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1),(2);

CREATE TABLE t3 (c INT) ENGINE=MyISAM;

INSERT INTO t3 VALUES (2),(5);

prepare stmt from "SELECT * FROM t1 INNER JOIN t2 ON (b = a) WHERE 3 > ANY ( SELECT c FROM t3 )";

execute stmt;

execute stmt;

drop table t1, t2, t3;

MariaDB [test]> execute stmt;

+------+------+

| a    | b    |

+------+------+

|    1 |    1 |

|    2 |    2 |

+------+------+

2 rows in set (0.05 sec)

MariaDB [test]> execute stmt;

Empty set (0.00 sec)