Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
-
None
-
None
Description
I don't think there is anything to fix in MariaDB, as it's other implementation that seems to be wrong, but since there was interest in differences in behavior, here it goes.
Test case:
--source include/have_innodb.inc
|
CREATE TABLE t1 (i INT) ENGINE=InnoDB; |
INSERT INTO t1 VALUES (1); |
ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP; |
SELECT * FROM t1; |
on maria/10.0-mdev452 and maria/10.0-base revno 3447, it returns
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (1);
|
ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
|
SELECT * FROM t1;
|
i ts
|
1 2012-12-15 06:22:48
|
on mysql-5.6.9 and previous versions of MySQL and MariaDB, it returns
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (1);
|
ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
|
SELECT * FROM t1;
|
i ts
|
1 0000-00-00 00:00:00
|
Same is true for a NULL-able TIMESTAMP column and for DATETIME column with auto-init.
The difference only happens with InnoDB; with MyISAM, both MariaDB and MySQL insert the auto-initialized value.
I can file a bug for MySQL, although it will probably get very low priority, since that's how it's been in previous versions, too.
Attachments
Issue Links
Activity
Verified the bug on various versions of MySQL/MariaDB, found the related MySQL patch,
determined this is a MySQL-only bug.
I'll re-open it temporarily so that I don't forget to report it to MySQL as suggested
Apparently this is a bug in MySQL 5.6. More specifically it is an incomplete fix for MySQL's
Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT CURRENT_TIMESTAMP INSERTS ZERO
They fixed only for MyISAM, and never even tested for InnoDB. Since I implementated the feature from scratch,
we don't have this bug. We have this test case in 10.0, and it works correctly with both MyISAM and InnoDB.
I suggest that you submit a bug against MySQL, it is a good point that our reimplementation is of better
quality.
This is the test case I derived from your test above:
CREATE TABLE t1i (i INT) ENGINE=InnoDB;
INSERT INTO t1i VALUES (1);
ALTER TABLE t1i ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
SELECT * FROM t1i;
CREATE TABLE t1m (i INT) ENGINE=MyISAM;
INSERT INTO t1m VALUES (1);
ALTER TABLE t1m ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
SELECT * FROM t1m;
In addition, please add that their own test case for MySQL Bug#11745578 works incorrectly
with InnoDB.