From: Martin Hansson
Date: April 13 2012 8:57am
Subject: bzr push into mysql-trunk branch (martin.hansson:3874 to 3875) Bug#11745578
List-Archive: http://lists.mysql.com/commits/143480
X-Bug: 11745578
Message-Id: <201204130857.q3D8vAlH019496@acsmt356.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 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).