Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10317

EXCTACT(MINUTE_MICROSECOND) truncates data

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: