MariaDB Development
  1. MariaDB Development
  2. MDEV-4858

Wrong results for a huge unsigned value inserted into a TIME column

    Details

    • Type: Bug Bug
    • Status: In Progress
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0.3, 5.5.32, 5.3.12
    • Fix Version/s: 10.0.11, 5.5.38, 5.3.13
    • Labels:
    • Global Rank:
      3423

      Description

      SET sql_mode=traditional;
      DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME(6));
      INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); 
      SHOW WARNINGS;
      
      ERROR 1292 (22007): Incorrect time value: '-4294967296' for column 'a' at row 1
      

      The expected error text is:

      ERROR 1292 (22007): Incorrect time value: '18446744069414584320' for column 'a' at row 1
      

      MySQL-5.6 is not affected.

      When running in a non-traditional mode, the same huge unsigned value
      produces a negative value in the column:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME(6));
      INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
      SHOW WARNINGS;
      SELECT  * FROM t1;
      

      returns

      +-------------------+
      | a                 |
      +-------------------+
      | -838:59:59.999999 |
      +-------------------+
      

      The expected result is the maximum positive number.

      Comparision of TIME to a huge unsigned number also returns a wrong result:

      mysql> SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);
      +------------------------------------------------------+
      | TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED) |
      +------------------------------------------------------+
      |                                                    1 |
      +------------------------------------------------------+
      

      The expected result is 0.

      A similar problem is observed with dynamic columns:

      mysql> SELECT column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1, column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2; SHOW WARNINGS;
      +------------+------+
      | t1         | t2   |
      +------------+------+
      | -838:59:59 | NULL |
      +------------+------+
      1 row in set, 1 warning (0.00 sec)
      
      +---------+------+--------------------------------------------------+
      | Level   | Code | Message                                          |
      +---------+------+--------------------------------------------------+
      | Warning | 1292 | Incorrect datetime value: '-9223372036854775808' |
      +---------+------+--------------------------------------------------+
      1 row in set (0.00 sec)
      

      The result for t2 should be '-838:59:59', and a warning for both t1 and t2 should be displayed.

        Issue Links

          Activity

          There are no comments yet on this issue.

            People

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

              Dates

              • Created:
                Updated: