MDEV-8336 The meaning of NO_ZERO_DATE is not clear for DATETIME.

In some cases NO_ZERO_DATE did not allow datetime values with zero date part
and non-zero time part (e.g. '0000-00-00 10:20:30.123456').
Allowing values of this kind in all known pieces of the code.

2 files changed, 79 insertions, 0 deletions

diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index d8c18711293..0c62007d2a0 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -893,5 +893,57 @@ DROP TABLE t1;
 # End of MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field
 #
 #
+# MDEV-8336 The meaning of NO_ZERO_DATE is not clear for DATETIME
+#
+SET sql_mode='NO_ZERO_DATE';
+SELECT TIMESTAMP'0000-00-01 10:20:30';
+TIMESTAMP'0000-00-01 10:20:30'
+0000-00-01 10:20:30
+SELECT TIMESTAMP'0000-00-00 10:20:30';
+TIMESTAMP'0000-00-00 10:20:30'
+0000-00-00 10:20:30
+SELECT TIMESTAMP'0000-00-00 00:00:00.000001';
+TIMESTAMP'0000-00-00 00:00:00.000001'
+0000-00-00 00:00:00.000001
+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;
+a	LEAST(a,'2001-01-01 10:20:30')
+0000-00-00 10:20:30	0000-00-00 10:20:30.000000
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00.000001');
+SELECT a, LEAST(a,'2001-01-01 10:20:30') FROM t1;
+a	LEAST(a,'2001-01-01 10:20:30')
+0000-00-00 00:00:00.000001	0000-00-00 00:00:00.000001
+DROP TABLE t1;
+SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s');
+STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s')
+0000-00-00 10:20:30
+SELECT STR_TO_DATE('0000-00-00 00:00:00.000001','%Y-%m-%d %H:%i:%s.%f');
+STR_TO_DATE('0000-00-00 00:00:00.000001','%Y-%m-%d %H:%i:%s.%f')
+0000-00-00 00:00:00.000001
+SET old_mode=zero_date_time_cast;
+SELECT CAST(TIME'10:20:30' AS DATETIME);
+CAST(TIME'10:20:30' AS DATETIME)
+0000-00-00 10:20:30
+SELECT CAST(TIME'00:00:00.000001' AS DATETIME(6));
+CAST(TIME'00:00:00.000001' AS DATETIME(6))
+0000-00-00 00:00:00.000001
+SELECT CAST(CAST('10:20:30' AS TIME) AS DATETIME);
+CAST(CAST('10:20:30' AS TIME) AS DATETIME)
+0000-00-00 10:20:30
+SELECT CAST(CAST('00:00:00.000001' AS TIME(6)) AS DATETIME(6));
+CAST(CAST('00:00:00.000001' AS TIME(6)) AS DATETIME(6))
+0000-00-00 00:00:00.000001
+SELECT CAST(CAST(TIMESTAMP'0000-00-00 10:20:30' AS TIME) AS DATETIME);
+CAST(CAST(TIMESTAMP'0000-00-00 10:20:30' AS TIME) AS DATETIME)
+0000-00-00 10:20:30
+SELECT CAST(CAST(TIMESTAMP'0000-00-00 00:00:00.000001' AS TIME(6)) AS DATETIME(6));
+CAST(CAST(TIMESTAMP'0000-00-00 00:00:00.000001' AS TIME(6)) AS DATETIME(6))
+0000-00-00 00:00:00.000001
+SET old_mode=DEFAULT;
+SET sql_mode=DEFAULT;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index f3e603e9da8..cd311182921 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -623,5 +623,32 @@ let defval='0000-00-00 00:00:00';
 --source include/type_temporal_zero_default.inc
 
 --echo #
+--echo # MDEV-8336 The meaning of NO_ZERO_DATE is not clear for DATETIME
+--echo #
+SET sql_mode='NO_ZERO_DATE';
+SELECT TIMESTAMP'0000-00-01 10:20:30';
+SELECT TIMESTAMP'0000-00-00 10:20:30';
+SELECT TIMESTAMP'0000-00-00 00:00:00.000001';
+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;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00.000001');
+SELECT a, LEAST(a,'2001-01-01 10:20:30') FROM t1;
+DROP TABLE t1;
+SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s');
+SELECT STR_TO_DATE('0000-00-00 00:00:00.000001','%Y-%m-%d %H:%i:%s.%f');
+SET old_mode=zero_date_time_cast;
+SELECT CAST(TIME'10:20:30' AS DATETIME);
+SELECT CAST(TIME'00:00:00.000001' AS DATETIME(6));
+SELECT CAST(CAST('10:20:30' AS TIME) AS DATETIME);
+SELECT CAST(CAST('00:00:00.000001' AS TIME(6)) AS DATETIME(6));
+SELECT CAST(CAST(TIMESTAMP'0000-00-00 10:20:30' AS TIME) AS DATETIME);
+SELECT CAST(CAST(TIMESTAMP'0000-00-00 00:00:00.000001' AS TIME(6)) AS DATETIME(6));
+SET old_mode=DEFAULT;
+SET sql_mode=DEFAULT;
+
+--echo #
 --echo # End of 10.1 tests
 --echo #