ER_ILLEGAL_REFERENCE on 2nd execution of PS with NOT IN, aggregate function, HAVING

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, b INT, KEY(b));

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

PREPARE stmt FROM "

  SELECT SUM( alias1.a ) AS field1, alias2.a AS field2 

  FROM t1 AS alias1 JOIN t1 AS alias2 ON ( alias2.a = alias1.a ) 

  WHERE 1 NOT IN ( SELECT b FROM t1 ) 

  GROUP BY field2 

  HAVING field1 != 1

";

EXECUTE stmt;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

DROP TABLE t1;

MariaDB [test]> EXECUTE stmt;

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

| field1 | field2 |

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

|      3 |      3 |

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

1 row in set (0.01 sec)

MariaDB [test]> EXECUTE stmt;

ERROR 1247 (42S22): Reference 'field1' not supported (reference to group function)