MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error

4 files changed, 44 insertions, 1 deletions

diff --git a/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc b/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc
index 4cf3914e60a..1fea4ca5bb9 100644
--- a/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc
+++ b/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc
@@ -97,3 +97,16 @@ CREATE TABLE t1 (a INT);
 ALTER TABLE t1 ADD b TIMESTAMP;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error
+--echo #
+
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
+SET sql_mode='ANSI,NO_ZERO_DATE';
+CREATE TABLE t1 (a TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result
index cdf612e6db8..61a4eb8a934 100644
--- a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result
+++ b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result
@@ -173,3 +173,16 @@ t1	CREATE TABLE `t1` (
   `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
+#
+# MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error
+#
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
+SET sql_mode='ANSI,NO_ZERO_DATE';
+CREATE TABLE t1 (a TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+a
+2001-01-01 10:20:30
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+SET timestamp=DEFAULT;
diff --git a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result
index 1c42da57bfc..fb820dc167d 100644
--- a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result
+++ b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result
@@ -178,3 +178,18 @@ t1	CREATE TABLE `t1` (
   `b` timestamp NULL DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
+#
+# MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error
+#
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
+SET sql_mode='ANSI,NO_ZERO_DATE';
+CREATE TABLE t1 (a TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES ();
+Warnings:
+Warning	1364	Field 'a' doesn't have a default value
+SELECT * FROM t1;
+a
+0000-00-00 00:00:00
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+SET timestamp=DEFAULT;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 2751c79a0a4..fa5eb2a6796 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4161,7 +4161,8 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
     if (!sql_field->def &&
         !sql_field->has_default_function() &&
         (sql_field->flags & NOT_NULL_FLAG) &&
-        !is_timestamp_type(sql_field->sql_type))
+        (!is_timestamp_type(sql_field->sql_type) ||
+         opt_explicit_defaults_for_timestamp))
     {
       sql_field->flags|= NO_DEFAULT_VALUE_FLAG;
       sql_field->pack_flag|= FIELDFLAG_NO_DEFAULT;
@@ -4170,6 +4171,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
     if (thd->variables.sql_mode & MODE_NO_ZERO_DATE &&
         !sql_field->def && !sql_field->vcol_info &&
         is_timestamp_type(sql_field->sql_type) &&
+        !opt_explicit_defaults_for_timestamp &&
         (sql_field->flags & NOT_NULL_FLAG) &&
         (type == Field::NONE || type == Field::TIMESTAMP_UN_FIELD))
     {