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

Wrong results for GREATEST,TIMESTAMP,ADDTIME with an out-of-range TIME-alike argument

    Details

      Description

      Some time expressions can return more hidden fractional digits than the effective data type of this expression supposes to. This happens when an out-of-range non-TIME value gets converted to the maximum possible value of '838:59:59.999999'.

      The extra digits are not seen when you just do a SELECT for such expressions, because the extra digits get cut by the protocol:

      bool Type_handler::
             Item_send_time(Item *item, Protocol *protocol, st_value *buf) const
      {
        item->get_time(protocol->thd, &buf->value.m_time);
        if (!item->null_value)
          return protocol->store_time(&buf->value.m_time, item->decimals);  -- UNEXPECTED DIGITS GET CUT HERE
        return protocol->store_null();
      }
      

      However, if you pass this expression to certain functions, e.g. MICROSECOND(), or arithmetic operators, or cast to DECIMAL, the presence of extra fractional digits reveals itself.

      Looking into this query:

      SELECT GREATEST(8395959, TIME'00:00:00');
      

      +-----------------------------------+
      | GREATEST(8395959, TIME'00:00:00') |
      +-----------------------------------+
      | 838:59:59                         |
      +-----------------------------------+
      

      one might think that the returned value is '838:59:59'. But in fact it is not. The real returned value is '838:59:59.999999'.

      These queries demonstrate confusing effects of hidden extra digits:

      SELECT
        CAST(GREATEST(8395959, TIME'00:00:00') AS SIGNED) AS ci,
        CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,0)) AS c0,
        CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,1)) AS c1,
        CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,2)) AS c2,
        CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,3)) AS c3,
        CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,4)) AS c4,
        CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,5)) AS c5,
        CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,6)) AS c6,
        CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,7)) AS c7
      \G
      

      ci: 8385959
      c0: 8385960
      c1: 8385960.0
      c2: 8385960.00
      c3: 8385960.000
      c4: 8385960.0000
      c5: 8385960.00000
      c6: 8385959.999999
      c7: 8385959.9999990
      

      Notice:

      • conversion to INT truncated the extra digits (column ci)
      • conversion to DECIMAL(30, 0..5) rounded the extra digits (columns c0..c5)
      • conversion to DECIMAL(30, 6..7) fully revealed the extra digits (columns c6..c7)

      The problem is also repeatable with additive expressions:

      SELECT
        GREATEST(8395959, TIME'00:00:00') AS ci,
        GREATEST(8395959, TIME'00:00:00')+0 AS c0,
        GREATEST(8395959, TIME'00:00:00')+0.0 AS c1,
        GREATEST(8395959, TIME'00:00:00')+0.00 AS c2,
        GREATEST(8395959, TIME'00:00:00')+0.000 AS c3,
        GREATEST(8395959, TIME'00:00:00')+0.0000 AS c4,
        GREATEST(8395959, TIME'00:00:00')+0.00000 AS c5,
        GREATEST(8395959, TIME'00:00:00')+0.000000 AS c6,
        GREATEST(8395959, TIME'00:00:00')+0.0000000 AS c7
      \G
      

      ci: 838:59:59
      c0: 8385959
      c1: 8385960.0
      c2: 8385960.00
      c3: 8385960.000
      c4: 8385960.0000
      c5: 8385960.00000
      c6: 8385959.999999
      c7: 8385959.9999990
      

      SELECT
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS ci,
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0 AS c0,
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0 AS c1,
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00 AS c2,
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000 AS c3,
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000 AS c4,
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00000 AS c5,
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000000 AS c6,
        ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000000 AS c7
      \G
      

      ci: 2001-02-04 22:59:59
      c0: 20010204225959
      c1: 20010204225960.0
      c2: 20010204225960.00
      c3: 20010204225960.000
      c4: 20010204225960.0000
      c5: 20010204225960.00000
      c6: 20010204225959.999999
      c7: 20010204225959.9999990
      

      SELECT
        TIMESTAMP('2001-01-01', 8395959) AS ci,
        TIMESTAMP('2001-01-01', 8395959)+0 AS c0,
        TIMESTAMP('2001-01-01', 8395959)+0.0 AS c1,
        TIMESTAMP('2001-01-01', 8395959)+0.00 AS c2,
        TIMESTAMP('2001-01-01', 8395959)+0.000 AS c3,
        TIMESTAMP('2001-01-01', 8395959)+0.0000 AS c4,
        TIMESTAMP('2001-01-01', 8395959)+0.00000 AS c5,
        TIMESTAMP('2001-01-01', 8395959)+0.000000 AS c6,
        TIMESTAMP('2001-01-01', 8395959)+0.0000000 AS c7
      \G
      

      ci: 2001-02-04 22:59:59
      c0: 20010204225959
      c1: 20010204225960.0
      c2: 20010204225960.00
      c3: 20010204225960.000
      c4: 20010204225960.0000
      c5: 20010204225960.00000
      c6: 20010204225959.999999
      c7: 20010204225959.9999990
      

      The problem is repeatable with MICROSECOND(GREATEST(..)), but starting from 10.3 only

      In this script:

      SET sql_mode='';
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT GREATEST(8395959, TIME'00:00:00');
      SHOW CREATE TABLE t1;
      

      +-------+--------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                       |
      +-------+--------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `GREATEST(8395959, TIME'00:00:00')` time DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------------------------------+
      

      the result of GREATEST() has data type of TIME(0).

      However, MICROSECOND() for the same expression returns 999999:

      SELECT        GREATEST(8395959, TIME'00:00:00') AS c1,
        MICROSECOND(GREATEST(8395959, TIME'00:00:00')) AS c2;
      

      +-----------+--------+
      | c1        | c2     |
      +-----------+--------+
      | 838:59:59 | 999999 |
      +-----------+--------+
      

      Looks wrong.

      The problem is repeatable with MICROSECOND(ADDTIME(..)):

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)  AS c1;
      SHOW CREATE TABLE t1;
      

      +-------+-----------------------------------------------------------------------------------------+
      | Table | Create Table                                                                            |
      +-------+-----------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `c1` datetime DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-----------------------------------------------------------------------------------------+
      

      SELECT            ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)  AS c1,
            MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)) AS c2;
      

      +---------------------+--------+
      | c1                  | c2     |
      +---------------------+--------+
      | 2001-02-04 22:59:59 | 999999 |
      +---------------------+--------+
      

      The problem is repeatable with MICROSECOND(TIMESTAMP(..))

      SET sql_mode='';
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT TIMESTAMP('2001-01-01 00:00:00', 8395959) AS c1;
      SHOW CREATE TABLE t1;
      

      +-------+-----------------------------------------------------------------------------------------+
      | Table | Create Table                                                                            |
      +-------+-----------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `c1` datetime DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-----------------------------------------------------------------------------------------+
      

      SELECT        TIMESTAMP('2001-01-01 00:00:00', 8395959) AS c1,
        MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959)) AS c2;
      

      +---------------------+--------+
      | c1                  | c2     |
      +---------------------+--------+
      | 2001-02-04 22:59:59 | 999999 |
      +---------------------+--------+
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: