Bug #42791 EQ condition with invalid date gives double warnings

• Submitted: 12 Feb 2009 13:32
• Modified: 12 Feb 2009 16:17
• Reporter: Anders Karlsson
• Status: Verified
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• Version: 5.1.30
• OS: Any
• CPU Architecture: Any

[12 Feb 2009 13:32] Anders Karlsson

Description:
A comparison with an invalid data in a table, where there are rows, will issue 2 warnings. This is only true for eq comparisons, not for greater than or anything else.

How to repeat:
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table tab1(c1 date);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab1 where c1 = '0000-00-00';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '0000-00-00' for column 'c1' at row 1 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from tab1 where c1 < '0000-00-00';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '0000-00-00' for column 'c1' at row 1 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into tab1 values('2009-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tab1 where c1 = '0000-00-00';
Empty set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '0000-00-00' for column 'c1' at row 1 |
| Warning | 1292 | Incorrect date value: '0000-00-00' for column 'c1' at row 1 |
+---------+------+-------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from tab1 where c1 < '0000-00-00';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '0000-00-00' for column 'c1' at row 1 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

[12 Feb 2009 16:17] Sveta Smirnova

Thank you for the report.

Verified as described.

Really sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE'; is needed to repeat.

Test case for test suite:

set sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
create table tab1(c1 date);
select * from tab1 where c1 = '0000-00-00';
select * from tab1 where c1 < '0000-00-00';
insert into tab1 values('2009-01-01');
select * from tab1 where c1 = '0000-00-00';
select * from tab1 where c1 < '0000-00-00';

set sql_mode='NO_ZERO_DATE';
select * from tab1 where c1 = '0000-00-00';

set sql_mode='NO_ZERO_IN_DATE';
select * from tab1 where c1 = '0000-00-00';

drop table tab1;