maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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