Hi, Peter! On Oct 06, Peter Laursen wrote:
Refer: http://bugs.mysql.com/bug.php?id=74238
here I prefer the 'relaxed parsing' in Maria DB.
But look at this:
CREATE TABLE `d` ( `rec_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `datetime` DATETIME(4) DEFAULT NULL, `timestamp` TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(5) ON UPDATE CURRENT_TIMESTAMP(4), PRIMARY KEY (`rec_id`) ) ENGINE=INNODB; -- it actually creates the table
SHOW CREATE TABLE d; /* returns
CREATE TABLE `d` ( `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 DEFAULT CHARSET=latin1 */
I don't think DEFAULT CURRENT_TIMESTAMP(5) should be accepted for the datatype DATETIME(4).
What say?
I checked the code, and it happens to be intentional. If the default value is CURRENT_TIMESTAMP(5), it's the same as if you explicitly insert CURRENT_TIMESTAMP(5) into the TIMESTAMP(4) column. And you can do that - explicitly insert, I mean - that's not a bug, the value will be truncated, as expected. So, the result is exactly the same as inserting CURRENT_TIMESTAMP(4). That's why specifying a default of CURRENT_TIMESTAMP(5) is allowed - it produces the same result as a default of CURRENT_TIMESTAMP(4). On the other hand, if you insert a CURRENT_TIMESTAMP(3) value into TIMESTAMP(4) column - the result will be different, the last digit in the column will be always 0. For default values it's not supported (frm file does not currently remember the width for the default value). That's why you cannot specify a default of CURRENT_TIMESTAMP(3) for a TIMESTAMP(4) column. Regards, Sergei