Wrong result with view + invalid dates

[Philip Stoev]

If invalid dates are used in a query against a view, the query will return rows that do not match the HAVING predicate.

test case:

DROP TABLE t1;
CREATE TABLE t1 ( col_date_key date) ;
INSERT IGNORE INTO t1 VALUES ('0000-00-00');

CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;

SELECT col_date_key AS field2 FROM t1 HAVING field2 = 'zz' AND field2 <= 'aa' ;

-> no rows returned, impossible where, no warnings

SELECT col_date_key AS field2 FROM v1 HAVING field2 = 'zz' AND field2 <= 'aa' ;

-> 1 row returned, 2 warnings

explain:

MariaDB [test]> explain SELECT col_date_key AS field2 FROM v1 HAVING field2 = 'zz' AND field2 <= 'aa' ;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

bzr version-info

revision-id: <email address hidden>
date: 2011-08-05 22:07:06 +0400
build-date: 2011-08-08 18:27:13 +0300
revno: 3141
branch-nick: maria-5.3

Repeatable in maria-5.3 both before and after WL#106. Not repeatable in maria-5.2, mysql-5.5