Bug #84162 No warning message shows up when invalid datetime such '2016-4-31' is written.

• Submitted: 12 Dec 2016 12:14
• Reporter: Ben qu
• Status: Open
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 5.6.24
• OS: Linux (2.6.32-220.23.2.ali878.el6.x86_64)
• CPU Architecture: Any

[12 Dec 2016 12:14] Ben qu

Description:
According to the user manual(http://dev.mysql.com/doc/refman/5.7/en/datetime.html), a warning message shows up when '2016-4-31'  is passed:

'The server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. '

While I did not see the warning. Below is the details:
======================================
mysql> select * from datetime_warning_test where opTime <'2016-04-31';
+---------------------+
| opTime              |
+---------------------+
| 2016-04-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from datetime_warning_test where opTime <'2016-04-32';
+---------------------+
| opTime              |
+---------------------+
| 2016-04-01 00:00:00 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show create table datetime_warning_test;
+-----------------------+----------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                   |
+-----------------------+----------------------------------------------------------------------------------------------------------------+
| datetime_warning_test | CREATE TABLE `datetime_warning_test` (
  `opTime` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------------------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

================================

This is out of my expectation, I check it on MySQL 5.6.24 and MySQL 5.1.24. Neither shows the warning. Please let me know if it is a bug.

How to repeat:
1.   Create a table:

mysql> create table datetime_warning_test (`opTime` datetime DEFAULT NULL);

2.   Query with input like '2016-04-31'

mysql> select * from datetime_warning_test where opTime <'2016-04-31';
+---------------------+
| opTime              |
+---------------------+
| 2016-04-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

No Warning shows up.

Suggested fix:
It seems the flags break the day validation checks.  I am a mysql newbie and not sure what '!(flags & TIME_INVALID_DATES)' checks for.  So either remove the flags check or do not make the flag contain TIME_INVALID_DATES.

$diff -u  mysql-5.6.24/sql-common/my_time.c mysql-5.6.24/sql-common/my_time.c1
--- mysql-5.6.24/sql-common/my_time.c   2016-11-02 15:06:38.970974557 +0800
+++ mysql-5.6.24/sql-common/my_time.c1  2016-12-12 20:09:24.846973419 +0800
@@ -128,8 +128,7 @@
       *was_cut= MYSQL_TIME_WARN_ZERO_IN_DATE;
       return TRUE;
     }
-    else if ((!(flags & TIME_INVALID_DATES) &&
-              ltime->month && ltime->day > days_in_month[ltime->month-1] &&
+    else if ((ltime->month && ltime->day > days_in_month[ltime->month-1] &&
               (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 ||
                ltime->day != 29)))
     {
 139   }
 140   else if (flags & TIME_NO_ZERO_DATE)
 141   {
 142     *was_cut= MYSQL_TIME_WARN_ZERO_DATE;
 143     return TRUE;
 144   }
 145   return FALSE;
 146 }