MySQL Bugs: #68670: CREATE TABLE ... AS SELECT not propagating default when CURRENT

Bug #68670	CREATE TABLE ... AS SELECT not propagating default when CURRENT_TIMESTAMP
Submitted:	14 Mar 2013 9:50	Modified:	18 Mar 2013 13:16
Reporter:	Paul Keenan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.69	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using the CREATE TABLE ... AS SELECT syntax, default values are normally propagated into the new table definition.  This isn't happening when the field has a default value of CURRENT_TIMESTAMP; it gets the zero date '0000-00-00 00:00:00' instead.

I've seen the same behaviour on Mac OSX with MySQL Community Server 5.5.28 and Windows 7 with MySQL Community Server 5.6.10.

How to repeat:
mysql> create table t1 (f1 timestamp not null default current_timestamp, f2 timestamp not null default '2013-01-01 00:00:00');
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type      | Null | Key | Default             | Extra |                                                                           +-------+-----------+------+-----+---------------------+-------+
| f1    | timestamp | NO   |     | CURRENT_TIMESTAMP   |       |
| f2    | timestamp | NO   |     | 2013-01-01 00:00:00 |       |
+-------+-----------+------+-----+---------------------+-------+
2 rows in set (0.01 sec)

mysql> create table t2 as select * from t1;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type      | Null | Key | Default             | Extra |
+-------+-----------+------+-----+---------------------+-------+
| f1    | timestamp | NO   |     | 0000-00-00 00:00:00 |       |
| f2    | timestamp | NO   |     | 2013-01-01 00:00:00 |       |
+-------+-----------+------+-----+---------------------+-------+
2 rows in set (0.03 sec)

Suggested fix:
Field t2.f1 should be created with the default value CURRENT_TIMESTAMP.

Hello Paul,

Thank you for the report.
Verified as described

Workaround:
=============

mysql> create table t1 (f1 timestamp not null default current_timestamp, f2 timestamp not null default '2013-01-01 00:00:00');
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> 
mysql> desc t1;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type      | Null | Key | Default             | Extra |
+-------+-----------+------+-----+---------------------+-------+
| f1    | timestamp | NO   |     | CURRENT_TIMESTAMP   |       |
| f2    | timestamp | NO   |     | 2013-01-01 00:00:00 |       |
+-------+-----------+------+-----+---------------------+-------+
2 rows in set (0.01 sec)

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.02 sec)

mysql> desc t2;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type      | Null | Key | Default             | Extra |
+-------+-----------+------+-----+---------------------+-------+
| f1    | timestamp | NO   |     | CURRENT_TIMESTAMP   |       |
| f2    | timestamp | NO   |     | 2013-01-01 00:00:00 |       |
+-------+-----------+------+-----+---------------------+-------+
2 rows in set (0.01 sec)

mysql> 

later if you need data:

insert into t2 select * from t1;

Regards,
Umesh

Duplicate of Oracle-internal bug#16163936. Exists since at least 5.1. Already fixed in 5.6.11.