Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
10.1.6-2
Description
The documentation days:
The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.
But it does not say if DATETIME values with zero YYYYMMDD and non-zero hhmmss.uuuuuu parts (e.g. '0000-00 10:20:30') is permitted.
Different parts of the code tread it differently for DATETIME.
Example1: '0000-00-00 10:20:30' is not permitted
SET sql_mode='NO_ZERO_DATE';
|
SELECT TIMESTAMP'0000-00-00 10:20:30', TIMESTAMP'0000-00-01 10:20:30';
|
returns
+--------------------------------+--------------------------------+
|
| TIMESTAMP'0000-00-00 10:20:30' | TIMESTAMP'0000-00-01 10:20:30' |
|
+--------------------------------+--------------------------------+
|
| NULL | 0000-00-01 10:20:30 |
|
+--------------------------------+--------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
That is only the YYYYMMDD part is used to decide if the value is "ZERO DATE".
Example2: '0000-00-00 10:20:30' behaves differently in the same script:
SET sql_mode='NO_ZERO_DATE';
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DATETIME);
|
INSERT INTO t1 VALUES ('0000-00-00 10:20:30');
|
SELECT a, LEAST(a,'2001-01-01 10:20:30') FROM t1;
|
returns:
+---------------------+--------------------------------+
|
| a | LEAST(a,'2001-01-01 10:20:30') |
|
+---------------------+--------------------------------+
|
| 0000-00-00 10:20:30 | NULL |
|
+---------------------+--------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
That is INSERT and "SELECT a FROM t1" treated '0000-00-00 10:20:30' as a permitted value, while LEAST() treated it as a non-permitted value.
It should be fixed to treat in the same way in all context.
Example3:
SET sql_mode='NO_ZERO_DATE';
|
SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s');
|
Example4:
SET sql_mode='NO_ZERO_DATE';
|
SELECT TIMESTAMP'0000-00-00 10:20:30';
|
Exapmple 5:
SET old_mode=zero_date_time_cast;
|
SET SQL_MODE='NO_ZERO_DATE';
|
SELECT CAST(TIME'10:20:30' AS DATETIME);
|
Example 6:
SET old_mode=zero_date_time_cast;
|
SET SQL_MODE='NO_ZERO_DATE';
|
SELECT CAST((CAST('10:20:30' AS TIME)) AS DATETIME);
|
Example 7:
SET old_mode=zero_date_time_cast;
|
SET SQL_MODE='NO_ZERO_DATE';
|
SELECT CAST((CAST(TIMESTAMP'0000-00-00 10:20:30' AS TIME)) AS DATETIME);
|