[#MDEV-4858] Wrong results for a huge unsigned value inserted into a TIME column

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.

[MDEV-4858] Wrong results for a huge unsigned value inserted into a TIME column Created: 2013-08-08 Updated: 2014-06-04 Resolved: 2014-06-04
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.0.3, 5.5.32, 5.3.12
Fix Version/s:	5.5.38, 10.0.12, 5.3.13

[MDEV-4858] Wrong results for a huge unsigned value inserted into a TIME column Created: 2013-08-08 Updated: 2014-06-04 Resolved: 2014-06-04