Bug #74238 Fractional seconds in DEFAULT and ON UPDATE
Submitted: 6 Oct 2014 17:51 Modified: 7 Oct 2014 12:55
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.6+ OS:Any
Assigned to: CPU Architecture:Any

[6 Oct 2014 17:51] Peter Laursen
Description:
This is documented, but so much an inconvenience that I will characterize it as a bug. 

http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html says

If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition. This is permitted:

CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

How to repeat:
CREATE TABLE `a` (
`rec_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`datetime` DATETIME(4) DEFAULT NULL,
`timestamp` TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rec_id`)
) ENGINE=INNODB;

CREATE TABLE `b` (
`rec_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`datetime` DATETIME(4) DEFAULT NULL,
`timestamp` TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
PRIMARY KEY (`rec_id`)
) ENGINE=INNODB;

CREATE TABLE `c` (
`rec_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`datetime` DATETIME(4) DEFAULT NULL,
`timestamp` TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4),
PRIMARY KEY (`rec_id`)
) ENGINE=INNODB;

Both a, b and c work in MariaDB 10. Only c works in MySQL 5.6 and 5.7

Suggested fix:
Since the column is defined with the datatyoe " `timestamp` TIMESTAMP(4) " there can be no doubt what the precison should be when automatically inserting due to a DEFAULT or ON UPDATE clause.

Handling this is an unnecessary complication for tools that autogenerate SQL (including GUI tools) and all 3 variants should be supported like MariaDB does.
[6 Oct 2014 21:30] Peter Laursen
Same with current-timestamp synonyms - for instance NOW()

-- fails
CREATE TABLE `x` (
`rec_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`datetime` DATETIME(4) DEFAULT NULL,
`timestamp` TIMESTAMP(4) NOT NULL DEFAULT NOW() ON UPDATE NOW(),
PRIMARY KEY (`rec_id`)
) ENGINE=INNODB;

--works
CREATE TABLE `y` (
`rec_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`datetime` DATETIME(4) DEFAULT NULL,
`timestamp` TIMESTAMP(4) NOT NULL DEFAULT NOW(4) ON UPDATE NOW(4),
PRIMARY KEY (`rec_id`)
) ENGINE=INNODB;

If you really want to insist on this behaviour I can provide a long list of documentation pages that need signficant updates! But I hope you don't!
[7 Oct 2014 12:55] MySQL Verification Team
Actually, current behavior is intended and is exactly as it was designed. However, there does not exist SQL standard that specifies anything regarding the precision of the temporal types. Hence, this is NOT a bug, but a fully qualified feature request.

Current design was based on the limitations of .FRM files, but as those will soon be obsolete, then this feature request can be implemented.