Hi Sergei, Please review a patch for MDEV-7778.
As agreed on IRC, this is a merge from the latest MySQL, which has already fixed the problem.
Greetings.
diff --git a/mysql-test/r/function_defaults.result b/mysql-test/r/function_defaults.result index 50183a1..b8f42b8 100644 --- a/mysql-test/r/function_defaults.result +++ b/mysql-test/r/function_defaults.result @@ -660,7 +660,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -669,7 +669,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -678,7 +678,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -705,7 +705,7 @@ CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; Table Create Table t7 CREATE TABLE `t7` ( - `f` datetime DEFAULT NULL + `f` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t7; f @@ -714,7 +714,7 @@ CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; Table Create Table t8 CREATE TABLE `t8` ( - `g` datetime DEFAULT NULL + `g` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t8; g @@ -723,7 +723,7 @@ CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; Table Create Table t9 CREATE TABLE `t9` ( - `h` datetime DEFAULT NULL + `h` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t9; h @@ -774,14 +774,14 @@ t12 CREATE TABLE `t12` ( `s` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `t` datetime DEFAULT NULL, `u` datetime DEFAULT '1986-09-27 03:00:00', - `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, `d` timestamp NOT NULL DEFAULT '1986-09-27 03:00:00', `e` timestamp NULL DEFAULT NULL, - `f` datetime DEFAULT NULL, - `g` datetime DEFAULT NULL, - `h` datetime DEFAULT NULL, + `f` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `g` datetime DEFAULT CURRENT_TIMESTAMP, + `h` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `i` datetime DEFAULT NULL, `j` datetime DEFAULT '1986-09-27 03:00:00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -802,7 +802,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` datetime DEFAULT NULL + `a` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -811,7 +811,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` datetime DEFAULT NULL + `b` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -820,7 +820,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` datetime DEFAULT NULL + `c` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -2216,7 +2216,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -2225,7 +2225,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -2234,7 +2234,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -2261,7 +2261,7 @@ CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; Table Create Table t7 CREATE TABLE `t7` ( - `f` datetime(6) DEFAULT NULL + `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t7; f @@ -2270,7 +2270,7 @@ CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; Table Create Table t8 CREATE TABLE `t8` ( - `g` datetime(6) DEFAULT NULL + `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t8; g @@ -2279,7 +2279,7 @@ CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; Table Create Table t9 CREATE TABLE `t9` ( - `h` datetime(6) DEFAULT NULL + `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t9; h @@ -2330,14 +2330,14 @@ t12 CREATE TABLE `t12` ( `s` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), `t` datetime(6) DEFAULT NULL, `u` datetime(6) DEFAULT '1986-09-27 03:00:00.098765', - `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', - `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', - `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), + `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6), `d` timestamp(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765', `e` timestamp(6) NULL DEFAULT NULL, - `f` datetime(6) DEFAULT NULL, - `g` datetime(6) DEFAULT NULL, - `h` datetime(6) DEFAULT NULL, + `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6), + `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), `i` datetime(6) DEFAULT NULL, `j` datetime(6) DEFAULT '1986-09-27 03:00:00.098765' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -2358,7 +2358,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` datetime(6) DEFAULT NULL + `a` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -2367,7 +2367,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` datetime(6) DEFAULT NULL + `b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -2376,7 +2376,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` datetime(6) DEFAULT NULL + `c` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t4; c diff --git a/mysql-test/r/function_defaults_innodb.result b/mysql-test/r/function_defaults_innodb.result index 302d4c8..2525d4e 100644 --- a/mysql-test/r/function_defaults_innodb.result +++ b/mysql-test/r/function_defaults_innodb.result @@ -661,7 +661,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -670,7 +670,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -679,7 +679,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -706,7 +706,7 @@ CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; Table Create Table t7 CREATE TABLE `t7` ( - `f` datetime DEFAULT NULL + `f` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t7; f @@ -715,7 +715,7 @@ CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; Table Create Table t8 CREATE TABLE `t8` ( - `g` datetime DEFAULT NULL + `g` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t8; g @@ -724,7 +724,7 @@ CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; Table Create Table t9 CREATE TABLE `t9` ( - `h` datetime DEFAULT NULL + `h` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t9; h @@ -775,14 +775,14 @@ t12 CREATE TABLE `t12` ( `s` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `t` datetime DEFAULT NULL, `u` datetime DEFAULT '1986-09-27 03:00:00', - `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, `d` timestamp NOT NULL DEFAULT '1986-09-27 03:00:00', `e` timestamp NULL DEFAULT NULL, - `f` datetime DEFAULT NULL, - `g` datetime DEFAULT NULL, - `h` datetime DEFAULT NULL, + `f` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `g` datetime DEFAULT CURRENT_TIMESTAMP, + `h` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `i` datetime DEFAULT NULL, `j` datetime DEFAULT '1986-09-27 03:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -803,7 +803,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` datetime DEFAULT NULL + `a` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -812,7 +812,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` datetime DEFAULT NULL + `b` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -821,7 +821,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` datetime DEFAULT NULL + `c` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -2217,7 +2217,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -2226,7 +2226,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -2235,7 +2235,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -2262,7 +2262,7 @@ CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; Table Create Table t7 CREATE TABLE `t7` ( - `f` datetime(6) DEFAULT NULL + `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t7; f @@ -2271,7 +2271,7 @@ CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; Table Create Table t8 CREATE TABLE `t8` ( - `g` datetime(6) DEFAULT NULL + `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t8; g @@ -2280,7 +2280,7 @@ CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; Table Create Table t9 CREATE TABLE `t9` ( - `h` datetime(6) DEFAULT NULL + `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t9; h @@ -2331,14 +2331,14 @@ t12 CREATE TABLE `t12` ( `s` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), `t` datetime(6) DEFAULT NULL, `u` datetime(6) DEFAULT '1986-09-27 03:00:00.098765', - `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', - `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', - `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), + `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6), `d` timestamp(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765', `e` timestamp(6) NULL DEFAULT NULL, - `f` datetime(6) DEFAULT NULL, - `g` datetime(6) DEFAULT NULL, - `h` datetime(6) DEFAULT NULL, + `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6), + `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), `i` datetime(6) DEFAULT NULL, `j` datetime(6) DEFAULT '1986-09-27 03:00:00.098765' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -2359,7 +2359,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` datetime(6) DEFAULT NULL + `a` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -2368,7 +2368,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` datetime(6) DEFAULT NULL + `b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -2377,7 +2377,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` datetime(6) DEFAULT NULL + `c` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t4; c diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 5992b13..53fc310 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -255,7 +255,7 @@ ushort smallint(5) unsigned zerofill NULL NO 00000 # umedium mediumint(8) unsigned NULL NO 0 # ulong int(11) unsigned NULL NO 0 # ulonglong bigint(13) unsigned NULL NO 0 # -time_stamp timestamp NULL NO 0000-00-00 00:00:00 # +time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP # date_field char(10) latin1_swedish_ci YES NULL # time_field time NULL YES NULL # date_time datetime NULL YES NULL # diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 38838e7..1ec2dfc 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -690,4 +690,45 @@ drop table t1; drop function f1; set timestamp=0; SET time_zone=DEFAULT; +# +# MDEV-7778 impossible create copy of table, if table contain default value for timestamp field +# +SET sql_mode="NO_ZERO_DATE"; +CREATE TABLE t1 ( +ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); +CREATE TABLE t2 AS SELECT * from t1 LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; +SET sql_mode=DEFAULT; +# +# MDEV-8082 ON UPDATE is not preserved by CREATE TABLE .. SELECT +# +CREATE TABLE t1 ( +vc VARCHAR(10) NOT NULL DEFAULT 'test', +ts timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP +); +CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `vc` varchar(10) NOT NULL DEFAULT 'test', + `ts` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `vc` varchar(10) NOT NULL DEFAULT 'test', + `ts` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; End of 10.0 tests diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result index dc1cbb3..6ebf83a 100644 --- a/mysql-test/r/type_timestamp_hires.result +++ b/mysql-test/r/type_timestamp_hires.result @@ -109,7 +109,7 @@ create table t3 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp(4) NOT NULL DEFAULT '0000-00-00 00:00:00.0000' + `a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t3; Table Create Table @@ -130,7 +130,7 @@ create table t3 select max(a), min(a), sum(a), avg(a) from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp(4) NOT NULL DEFAULT '0000-00-00 00:00:00.0000', + `a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4), `a+0` decimal(25,4) NOT NULL DEFAULT '0.0000', `a-1` decimal(25,4) NOT NULL DEFAULT '0.0000', `a*1` decimal(25,4) NOT NULL DEFAULT '0.0000', diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index 77ce8c5..195252a 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -492,4 +492,31 @@ drop function f1; set timestamp=0; SET time_zone=DEFAULT; +--echo # +--echo # MDEV-7778 impossible create copy of table, if table contain default value for timestamp field +--echo # + +SET sql_mode="NO_ZERO_DATE"; +CREATE TABLE t1 ( + ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); +CREATE TABLE t2 AS SELECT * from t1 LIMIT 0; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; +SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-8082 ON UPDATE is not preserved by CREATE TABLE .. SELECT +--echo # +CREATE TABLE t1 ( + vc VARCHAR(10) NOT NULL DEFAULT 'test', + ts timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP +); +CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 0; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; + + --echo End of 10.0 tests diff --git a/sql/field.cc b/sql/field.cc index e7e046a..31d8b46 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -9923,35 +9923,52 @@ Create_field::Create_field(Field *old_field,Field *orig_field) char_length= length; /* - Copy the default value from the column object orig_field, if: - 1) The column has a constant default value. - 2) The column type is not a BLOB type. - 3) The original column (old_field) was properly initialized with a record - buffer pointer. - 4) The original column doesn't have a default function to auto-initialize - the column on INSERT + Copy the default (constant/function) from the column object orig_field, if + supplied. We do this if all these conditions are met: + + - The column allows a default. + + - The column type is not a BLOB type. + + - The original column (old_field) was properly initialized with a record + buffer pointer. */ - if (!(flags & (NO_DEFAULT_VALUE_FLAG | BLOB_FLAG)) && // 1) 2) - old_field->ptr && orig_field && // 3) - !old_field->has_insert_default_function()) // 4) - { - char buff[MAX_FIELD_WIDTH]; - String tmp(buff,sizeof(buff), charset); - my_ptrdiff_t diff; - - /* Get the value from default_values */ - diff= (my_ptrdiff_t) (orig_field->table->s->default_values- - orig_field->table->record[0]); - orig_field->move_field_offset(diff); // Points now at default_values - if (!orig_field->is_real_null()) + if (!(flags & (NO_DEFAULT_VALUE_FLAG | BLOB_FLAG)) && + old_field->ptr != NULL && + orig_field != NULL) + { + bool default_now= false; + if (real_type_with_now_as_default(sql_type)) { - char buff[MAX_FIELD_WIDTH], *pos; - String tmp(buff, sizeof(buff), charset), *res; - res= orig_field->val_str(&tmp); - pos= (char*) sql_strmake(res->ptr(), res->length()); - def= new Item_string(pos, res->length(), charset); + // The SQL type of the new field allows a function default: + default_now= orig_field->has_insert_default_function(); + bool update_now= orig_field->has_update_default_function(); + + if (default_now && update_now) + unireg_check= Field::TIMESTAMP_DNUN_FIELD; + else if (default_now) + unireg_check= Field::TIMESTAMP_DN_FIELD; + else if (update_now) + unireg_check= Field::TIMESTAMP_UN_FIELD; + } + if (!default_now) // Give a constant default + { + char buff[MAX_FIELD_WIDTH]; + String tmp(buff,sizeof(buff), charset); + + /* Get the value from default_values */ + my_ptrdiff_t diff= orig_field->table->default_values_offset(); + orig_field->move_field_offset(diff); // Points now at default_values + if (!orig_field->is_real_null()) + { + char buff[MAX_FIELD_WIDTH], *pos; + String tmp(buff, sizeof(buff), charset), *res; + res= orig_field->val_str(&tmp); + pos= (char*) sql_strmake(res->ptr(), res->length()); + def= new Item_string(pos, res->length(), charset); + } + orig_field->move_field_offset(-diff); // Back to record[0] } - orig_field->move_field_offset(-diff); // Back to record[0] } } diff --git a/sql/field.h b/sql/field.h index 6f4bcc6..8c426b0 100644 --- a/sql/field.h +++ b/sql/field.h @@ -119,6 +119,21 @@ inline bool is_temporal_type_with_date(enum_field_types type) /** + Tests if field real type can have "DEFAULT CURRENT_TIMESTAMP", + i.e. represents TIMESTAMP types in SQL. + + @param type Field type, as returned by field->real_type(). + @retval true If field real type can have "DEFAULT CURRENT_TIMESTAMP". + @retval false If field real type can not have "DEFAULT CURRENT_TIMESTAMP". +*/ +inline bool real_type_with_now_as_default(enum_field_types type) +{ + return type == MYSQL_TYPE_TIMESTAMP || type == MYSQL_TYPE_TIMESTAMP2 || + type == MYSQL_TYPE_DATETIME || type == MYSQL_TYPE_DATETIME2; +} + + +/** Recognizer for concrete data type (called real_type for some reason), returning true if it is one of the TIMESTAMP types. */ diff --git a/sql/table.h b/sql/table.h index c63f648..df7fd85 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1374,6 +1374,10 @@ struct TABLE } bool update_const_key_parts(COND *conds); + + my_ptrdiff_t default_values_offset() const + { return (my_ptrdiff_t) (s->default_values - record[0]); } + uint actual_n_key_parts(KEY *keyinfo); ulong actual_key_flags(KEY *keyinfo); int update_default_fields();
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp