From 6a81399e5a2c62837b5ca0c2a99ab221c7f6aa86 Mon Sep 17 00:00:00 2001 From: yuxianjiang Date: Thu, 26 Nov 2020 21:03:34 -0500 Subject: [PATCH] [bugfix] create table having invalid default value with strict sql_mode problem When sql_mode is strict, SQL 'create table tsm1 as select CURRENT_TIMESTAMP as updatetime from t1;' will create a table field updatetime with default value '0000-00-00 00:00:00'. In the same time, SQL 'create table tsm1 (updatetime datetime NOT NULL DEFAULT '0000-00-00 00:00:00')' will lead to error. solution During create table from select item list, check sql_mode for temporal type and make it nullable. --- sql/sql_insert.cc | 3 +++ 1 file changed, 3 insertions(+) diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 15d80fd05..98973c84d 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2641,6 +2641,9 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info, if (item->maybe_null) cr_field->flags &= ~NOT_NULL_FLAG; + if ((cr_field->flags & NOT_NULL_FLAG) && item->is_temporal() + && (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))) + cr_field->flags &= ~NOT_NULL_FLAG; alter_info->create_list.push_back(cr_field); } -- 2.22.0