Bug #75992 no warning when, under "ALLOW_INVALID_DATES", an invalid date is discarded

• Submitted: 21 Feb 2015 18:29
• Modified: 23 Feb 2015 9:43
• Reporter: Programmer Old
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.14-log, 5.6.24
• OS: Windows (Vista)
• CPU Architecture: Any

[21 Feb 2015 18:29] Programmer Old

Description:
Sometimes invalid dates from the date-construct are, with no warning, discarded.

How to repeat:
5.6.14-log select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                                                        |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_DATE,ALLOW_INVALID_DATES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.6.14-log select date '2014/2/31';
+------------------+
| date '2014/2/31' |
+------------------+
| 2014-02-31       |
+------------------+
1 row in set (0.00 sec)

5.6.14-log select date '2014/1/31';
+------------------+
| date '2014/1/31' |
+------------------+
| 2014-01-31       |
+------------------+
1 row in set (0.00 sec)

5.6.14-log select date '2014/2/31' + 1;
+----------------------+
| date '2014/2/31' + 1 |
+----------------------+
|                    1 | ** no warning that the date has been made 0 instead of 20140231
+----------------------+
1 row in set (0.00 sec)

5.6.14-log select date '2014/1/31' + 1;
+----------------------+
| date '2014/1/31' + 1 |
+----------------------+
|             20140132 |
+----------------------+
1 row in set (0.00 sec)

5.6.14-log select date(date '2014/2/31');
+------------------------+
| date(date '2014/2/31') |
+------------------------+
| NULL                   | ** no warning that the invalid date has been made NULL
+------------------------+
1 row in set (0.00 sec)

5.6.14-log select date_format(date '2014/2/31', '%Y%b%e');
+-----------------------------------------+
| date_format(date '2014/2/31', '%Y%b%e') |
+-----------------------------------------+
| NULL                                    | ** no warning that the invalid date has been made NULL
+-----------------------------------------+
1 row in set (0.00 sec)

5.6.14-log select date_format(date '2014/1/31', '%Y%b%e');
+-----------------------------------------+
| date_format(date '2014/1/31', '%Y%b%e') |
+-----------------------------------------+
| 2014Jan31                               |
+-----------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Some invalid dates, when rejected, are turned into NULL with a warning--but not those in the foregoing examples. In those examples there is actually no need to reject them, but if rejected, make them NULL, not 0, and with a warning,

[23 Feb 2015 9:43] MySQL Verification Team

Hello!

Thank you for the report and test case.

Thanks,
Umesh

[23 Feb 2015 9:45] MySQL Verification Team

//

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.24                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.24-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql>  set session sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_DATE,ALLOW_INVALID_DATES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1681 | 'NO_ZERO_DATE' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date '2014/2/31';
+------------------+
| date '2014/2/31' |
+------------------+
| 2014-02-31       |
+------------------+
1 row in set (0.01 sec)

mysql> select date '2014/2/31';
+------------------+
| date '2014/2/31' |
+------------------+
| 2014-02-31       |
+------------------+
1 row in set (0.00 sec)

mysql> select date '2014/1/31';
+------------------+
| date '2014/1/31' |
+------------------+
| 2014-01-31       |
+------------------+
1 row in set (0.00 sec)

mysql>  select date '2014/2/31' + 1;
+----------------------+
| date '2014/2/31' + 1 |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> select date '2014/1/31' + 1;
+----------------------+
| date '2014/1/31' + 1 |
+----------------------+
|             20140132 |
+----------------------+
1 row in set (0.00 sec)

mysql> select date(date '2014/2/31');
+------------------------+
| date(date '2014/2/31') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)