Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.3.12, 5.5.34, 10.0.6
-
None
-
None
Description
I insert an invalid date, e.g. '2004-04-31' using SQL_MODE=ALLOW_INVALID_DATES into a DATE column,
then switch to SQL_MODE=DEFAULT, then cast the value
to DATE and to DATETIME. CAST works differently for the two types.
mysql> select version();
|
+-----------------------------------+
|
| version() |
|
+-----------------------------------+
|
| 5.3.13-MariaDB-valgrind-max-debug |
|
+-----------------------------------+
|
1 row in set (0.00 sec)
|
|
|
mysql> set sql_mode=allow_invalid_dates;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql> drop table if exists t1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql> create table t1 (a date);
|
Query OK, 0 rows affected (0.05 sec)
|
|
|
mysql> insert into t1 values ('2004-04-31');
|
Query OK, 1 row affected (0.00 sec)
|
|
|
mysql> select * from t1;
|
+------------+
|
| a |
|
+------------+
|
| 2004-04-31 |
|
+------------+
|
1 row in set (0.00 sec)
|
|
|
mysql> set sql_mode=default;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql> select * from t1;
|
+------------+
|
| a |
|
+------------+
|
| 2004-04-31 |
|
+------------+
|
1 row in set (0.00 sec)
|
|
|
mysql> select cast(a as date) from t1;
|
+-----------------+
|
| cast(a as date) |
|
+-----------------+
|
| NULL |
|
+-----------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
|
mysql> select cast(a as datetime) from t1;
|
+---------------------+
|
| cast(a as datetime) |
|
+---------------------+
|
| 2004-04-31 00:00:00 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
Notice, CAST(a AS DATE) correctly returns NULL,
while CAST(a AS DATETIME) retuns a datetime value
with an invalid date ignoring SQL_MODE.