2011-08-09 11:43:05 |
Philip Stoev |
description |
Repeatable in maria-5.3 both before and after WL#106. Not repeatable in maria-5.2, mysql-5.5
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: psergey@askmonty.org-20110805180706-aa76hjdmnfx51kko
date: 2011-08-05 22:07:06 +0400
build-date: 2011-08-08 18:27:13 +0300
revno: 3141
branch-nick: maria-5.3 |
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: psergey@askmonty.org-20110805180706-aa76hjdmnfx51kko
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 |
|