3875 Martin Hansson 2012-04-13
Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP
DEFAULT CURRENT_TIMESTAMP INSERTS ZERO
It is a standard SQL feature that every value in a column
added with ALTER TABLE ADD COLUMN should be the value from
the default clase. This was not implemented and is added by
this patch.
modified:
mysql-test/include/function_defaults.inc
mysql-test/r/function_defaults.result
sql/sql_table.cc
3874 Annamalai Gurusami 2012-04-13 [merge]
Null merge from mysql-5.5 to mysql-trunk.
=== modified file 'mysql-test/include/function_defaults.inc'
--- a/mysql-test/include/function_defaults.inc 2012-01-31 15:16:16 +0000
+++ b/mysql-test/include/function_defaults.inc 2012-04-13 08:56:09 +0000
@@ -1107,3 +1107,60 @@ WHERE t1.a = 1 AND t1.ts >= '2000-09-28
SELECT b FROM t1;
DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT
+--echo # CURRENT_TIMESTAMP INSERTS ZERO
+--echo #
+SET timestamp = 1000;
+
+CREATE TABLE t1 ( b INT );
+INSERT INTO t1 VALUES (1);
+
+eval ALTER TABLE t1 ADD COLUMN a6 $datetime DEFAULT $now ON UPDATE $now FIRST;
+eval ALTER TABLE t1 ADD COLUMN a5 $datetime DEFAULT $now FIRST;
+eval ALTER TABLE t1 ADD COLUMN a4 $datetime ON UPDATE $now FIRST;
+
+eval ALTER TABLE t1 ADD COLUMN a3 $timestamp DEFAULT $now ON UPDATE $now FIRST;
+eval ALTER TABLE t1 ADD COLUMN a2 $timestamp DEFAULT $now FIRST;
+eval ALTER TABLE t1 ADD COLUMN a1 $timestamp ON UPDATE $now FIRST;
+
+eval ALTER TABLE t1 ADD COLUMN c1 $timestamp ON UPDATE $now AFTER b;
+eval ALTER TABLE t1 ADD COLUMN c2 $timestamp DEFAULT $now AFTER c1;
+eval ALTER TABLE t1 ADD COLUMN c3 $timestamp DEFAULT $now ON UPDATE $now AFTER c2;
+
+eval ALTER TABLE t1 ADD COLUMN c4 $datetime ON UPDATE $now AFTER c3;
+eval ALTER TABLE t1 ADD COLUMN c5 $datetime DEFAULT $now AFTER c4;
+eval ALTER TABLE t1 ADD COLUMN c6 $datetime DEFAULT $now ON UPDATE $now AFTER c5;
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+
+eval CREATE TABLE t1 ( a $timestamp DEFAULT $now, b $datetime DEFAULT $now );
+INSERT INTO t1 VALUES ();
+
+SET timestamp = 1000000000;
+
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3);
+ALTER TABLE t1 MODIFY COLUMN b DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3);
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+
+eval CREATE TABLE t1 (
+ a $timestamp DEFAULT '1999-12-01 11:22:33',
+ b $datetime DEFAULT '1999-12-01 11:22:33'
+);
+INSERT INTO t1 VALUES ();
+
+eval ALTER TABLE t1 MODIFY COLUMN a $timestamp DEFAULT $now;
+eval ALTER TABLE t1 MODIFY COLUMN b $datetime DEFAULT $now;
+INSERT INTO t1 VALUES ();
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
=== modified file 'mysql-test/r/function_defaults.result'
--- a/mysql-test/r/function_defaults.result 2012-01-31 15:16:16 +0000
+++ b/mysql-test/r/function_defaults.result 2012-04-13 08:56:09 +0000
@@ -1488,6 +1488,51 @@ b
1
DROP TABLE t1, t2;
#
+# Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT
+# CURRENT_TIMESTAMP INSERTS ZERO
+#
+SET timestamp = 1000;
+CREATE TABLE t1 ( b INT );
+INSERT INTO t1 VALUES (1);
+ALTER TABLE t1 ADD COLUMN a6 DATETIME DEFAULT NOW() ON UPDATE NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a5 DATETIME DEFAULT NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a4 DATETIME ON UPDATE NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a3 TIMESTAMP DEFAULT NOW() ON UPDATE NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a2 TIMESTAMP DEFAULT NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN a1 TIMESTAMP ON UPDATE NOW() FIRST;
+ALTER TABLE t1 ADD COLUMN c1 TIMESTAMP ON UPDATE NOW() AFTER b;
+ALTER TABLE t1 ADD COLUMN c2 TIMESTAMP DEFAULT NOW() AFTER c1;
+ALTER TABLE t1 ADD COLUMN c3 TIMESTAMP DEFAULT NOW() ON UPDATE NOW() AFTER c2;
+ALTER TABLE t1 ADD COLUMN c4 DATETIME ON UPDATE NOW() AFTER c3;
+ALTER TABLE t1 ADD COLUMN c5 DATETIME DEFAULT NOW() AFTER c4;
+ALTER TABLE t1 ADD COLUMN c6 DATETIME DEFAULT NOW() ON UPDATE NOW() AFTER c5;
+SELECT * FROM t1;
+a1 a2 a3 a4 a5 a6 b c1 c2 c3 c4 c5 c6
+0000-00-00 00:00:00 1970-01-01 03:16:40 1970-01-01 03:16:40 NULL 1970-01-01 03:16:40 1970-01-01 03:16:40 1 0000-00-00 00:00:00 1970-01-01 03:16:40 1970-01-01 03:16:40 NULL 1970-01-01 03:16:40 1970-01-01 03:16:40
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP DEFAULT NOW(), b DATETIME DEFAULT NOW() );
+INSERT INTO t1 VALUES ();
+SET timestamp = 1000000000;
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3);
+ALTER TABLE t1 MODIFY COLUMN b DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3);
+SELECT * FROM t1;
+a b
+1970-01-01 03:16:40.000 1970-01-01 03:16:40.000
+DROP TABLE t1;
+CREATE TABLE t1 (
+a TIMESTAMP DEFAULT '1999-12-01 11:22:33',
+b DATETIME DEFAULT '1999-12-01 11:22:33'
+);
+INSERT INTO t1 VALUES ();
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP DEFAULT NOW();
+ALTER TABLE t1 MODIFY COLUMN b DATETIME DEFAULT NOW();
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+a b
+1999-12-01 11:22:33 1999-12-01 11:22:33
+2001-09-09 04:46:40 2001-09-09 04:46:40
+DROP TABLE t1;
+#
# Function defaults run 2. Six digits scale on seconds precision.
#
SET TIME_ZONE = "+00:00";
@@ -2973,3 +3018,48 @@ SELECT b FROM t1;
b
1
DROP TABLE t1, t2;
+#
+# Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT
+# CURRENT_TIMESTAMP INSERTS ZERO
+#
+SET timestamp = 1000;
+CREATE TABLE t1 ( b INT );
+INSERT INTO t1 VALUES (1);
+ALTER TABLE t1 ADD COLUMN a6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a5 DATETIME(6) DEFAULT NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a4 DATETIME(6) ON UPDATE NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a3 TIMESTAMP(6) DEFAULT NOW(6) ON UPDATE NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a2 TIMESTAMP(6) DEFAULT NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN a1 TIMESTAMP(6) ON UPDATE NOW(6) FIRST;
+ALTER TABLE t1 ADD COLUMN c1 TIMESTAMP(6) ON UPDATE NOW(6) AFTER b;
+ALTER TABLE t1 ADD COLUMN c2 TIMESTAMP(6) DEFAULT NOW(6) AFTER c1;
+ALTER TABLE t1 ADD COLUMN c3 TIMESTAMP(6) DEFAULT NOW(6) ON UPDATE NOW(6) AFTER c2;
+ALTER TABLE t1 ADD COLUMN c4 DATETIME(6) ON UPDATE NOW(6) AFTER c3;
+ALTER TABLE t1 ADD COLUMN c5 DATETIME(6) DEFAULT NOW(6) AFTER c4;
+ALTER TABLE t1 ADD COLUMN c6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) AFTER c5;
+SELECT * FROM t1;
+a1 a2 a3 a4 a5 a6 b c1 c2 c3 c4 c5 c6
+0000-00-00 00:00:00.000000 1970-01-01 03:16:40.000000 1970-01-01 03:16:40.000000 NULL 1970-01-01 03:16:40.000000 1970-01-01 03:16:40.000000 1 0000-00-00 00:00:00.000000 1970-01-01 03:16:40.000000 1970-01-01 03:16:40.000000 NULL 1970-01-01 03:16:40.000000 1970-01-01 03:16:40.000000
+DROP TABLE t1;
+CREATE TABLE t1 ( a TIMESTAMP(6) DEFAULT NOW(6), b DATETIME(6) DEFAULT NOW(6) );
+INSERT INTO t1 VALUES ();
+SET timestamp = 1000000000;
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3);
+ALTER TABLE t1 MODIFY COLUMN b DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3);
+SELECT * FROM t1;
+a b
+1970-01-01 03:16:40.000 1970-01-01 03:16:40.000
+DROP TABLE t1;
+CREATE TABLE t1 (
+a TIMESTAMP(6) DEFAULT '1999-12-01 11:22:33',
+b DATETIME(6) DEFAULT '1999-12-01 11:22:33'
+);
+INSERT INTO t1 VALUES ();
+ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP(6) DEFAULT NOW(6);
+ALTER TABLE t1 MODIFY COLUMN b DATETIME(6) DEFAULT NOW(6);
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+a b
+1999-12-01 11:22:33.000000 1999-12-01 11:22:33.000000
+2001-09-09 04:46:40.000000 2001-09-09 04:46:40.000000
+DROP TABLE t1;
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2012-04-13 08:10:22 +0000
+++ b/sql/sql_table.cc 2012-04-13 08:56:09 +0000
@@ -7825,6 +7825,20 @@ copy_data_between_tables(TABLE *from,TAB
copy_ptr->do_copy(copy_ptr);
}
prev_insert_id= to->file->next_insert_id;
+
+ /* Set the function defaults. */
+ List_iterator<Create_field> iter(create);
+ for (uint i= 0; i < to->s->fields; ++i)
+ {
+ const Create_field *definition= iter++;
+ if (definition->field == NULL) // this column didn't exist in old table.
+ {
+ Field *column= to->field[i];
+ if (column->has_insert_default_function())
+ column->evaluate_insert_default_function();
+ }
+ }
+
error=to->file->ha_write_row(to->record[0]);
to->auto_increment_field_not_null= FALSE;
if (error)
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (martin.hansson:3874 to 3875) Bug#11745578 | Martin Hansson | 13 Apr |
