Bug #75991 writ of ALLOW_INVALID_DATES

• Submitted: 21 Feb 2015 18:12
• Modified: 23 Feb 2015 9:52
• 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:12] Programmer Old

Description:
Surely, since "ALLOW_INVALID_DATES" is set in "sql_mode", which affects all running, and is not any type-declaration (see 68756), it is right to expect that it affects all date-work, but no:

How to repeat:
5.6.14-log create temporary table u (e date default '2008/8/8');
Query OK, 0 rows affected (0.31 sec)

5.6.14-log insert into u value (19940931);
Query OK, 1 row affected (0.08 sec)

5.6.14-log select date_format(e, '%Y%b%e') from u;
+--------------------------+
| date_format(e, '%Y%b%e') |
+--------------------------+
| 1994Sep31                |
+--------------------------+
1 row in set (0.00 sec)

5.6.14-log select date_format(nullif(e, default(e)), '%Y%b%e') from u;
+----------------------------------------------+
| date_format(nullif(e, default(e)), '%Y%b%e') |
+----------------------------------------------+
| NULL                                         |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

5.6.14-log show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '19940931' |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
It is very annoying that "ALLOW_INVALID_DATES" s writ runs only to immediate references to a field, not all relevant operations. This is a further example that (see 17998) "sql_mode" is not well done. (And if, as I really suggest, "ALLOW_INVALID_DATES" were part of the declaration, then, too, it would be right for that declaration, along with DATE or DATETIME, to be passed on to all time functions that work the value.)

[23 Feb 2015 9:52] MySQL Verification Team

Hello!

Thank you for the report and test case.

Thanks,
Umesh

[23 Feb 2015 9:59] 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> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                                                             |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

mysql> create temporary table u (e date default '2008/8/8');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into u value (19940931);
Query OK, 1 row affected (0.00 sec)

mysql> select date_format(e, '%Y%b%e') from u;
+--------------------------+
| date_format(e, '%Y%b%e') |
+--------------------------+
| 1994Sep31                |
+--------------------------+
1 row in set (0.01 sec)

mysql> select date_format(nullif(e, default(e)), '%Y%b%e') from u;
+----------------------------------------------+
| date_format(nullif(e, default(e)), '%Y%b%e') |
+----------------------------------------------+
| NULL                                         |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '19940931' |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)

mysql> select nullif(e, default(e)) from u;
+-----------------------+
| nullif(e, default(e)) |
+-----------------------+
| 1994-09-31            |
+-----------------------+
1 row in set (0.00 sec)

mysql>  select date_format('1994-09-31', '%Y%b%e') from u;
+-------------------------------------+
| date_format('1994-09-31', '%Y%b%e') |
+-------------------------------------+
| 1994Sep31                           |
+-------------------------------------+
1 row in set (0.00 sec)