[MDEV-10317] EXCTACT(MINUTE_MICROSECOND) truncates data - Jira

XML

Word

Printable

A script demonstrating the problem with EXTRACT:

SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59.999999');

SELECT EXTRACT(MINUTE_MICROSECOND FROM NOW(6));

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM NOW(6));

SELECT * FROM t1;

SHOW CREATE TABLE t1;

SELECT EXTRACT() correctly returns 5959999999

+-----------------------------------------+

| EXTRACT(MINUTE_MICROSECOND FROM NOW(6)) |

+-----------------------------------------+

|                              5959999999 |

+-----------------------------------------+

The value stored in the table is erroneously truncated to 2147483647

+-----------------------------------------+

| EXTRACT(MINUTE_MICROSECOND FROM NOW(6)) |

+-----------------------------------------+

|                              2147483647 |

+-----------------------------------------+

And the created column data type is wrong:

+-------+-----------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                |

+-------+-----------------------------------------------------------------------------------------------------------------------------+

| t1    | CREATE TABLE `t1` (

  `EXTRACT(MINUTE_MICROSECOND FROM NOW(6))` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-----------------------------------------------------------------------------------------------------------------------------+

The expected data type is bigint.

relates to

MCOL-80 EXTRACT() function returned assertion error

MDEV-10309 COALESCE(12345678900) makes a column of a wrong type and truncates the data

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.