Description:
When inserting extreme TIME values into a TIME column, and later converting those values to JSON,
the absolute values appear to become smaller.
Test case is clipped from MTR test json.json_functions_innodb
mysql> CREATE TABLE times (t TIME(6));
Query OK, 0 rows affected (0.64 sec)
mysql> INSERT INTO times VALUES
-> ('-838:59:59'),
-> ('838:59:59'),
-> ('-00:00:00.000001'),
-> ('-00:00:00'),
-> ('24:00:00'),
-> ('-12:00:00'),
-> ('-24:00:00');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM times ORDER BY CAST(t AS JSON);
+-------------------+
| t |
+-------------------+
| -838:59:59.000000 |
| -24:00:00.000000 |
| -12:00:00.000000 |
| -00:00:00.000001 |
| 00:00:00.000000 |
| 24:00:00.000000 |
| 838:59:59.000000 |
+-------------------+
7 rows in set (0.00 sec)
mysql> CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, j JSON);
Query OK, 0 rows affected (0.62 sec)
mysql> INSERT INTO t(j) SELECT CAST(t AS JSON) FROM times ORDER BY t;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t;
+----+---------------------+
| id | j |
+----+---------------------+
| 1 | "-630:59:59.000000" |
| 2 | "-24:00:00.000000" |
| 3 | "-12:00:00.000000" |
| 4 | "-00:00:00.000001" |
| 5 | "00:00:00.000000" |
| 6 | "24:00:00.000000" |
| 7 | "630:59:59.000000" |
+----+---------------------+
7 rows in set (0.00 sec)
mysql> DROP TABLE t, times;
Query OK, 0 rows affected (0.04 sec)
How to repeat:
CREATE TABLE times (t TIME(6));
INSERT INTO times VALUES
('-838:59:59'),
('838:59:59'),
('-00:00:00.000001'),
('-00:00:00'),
('24:00:00'),
('-12:00:00'),
('-24:00:00');
SELECT * FROM times ORDER BY CAST(t AS JSON);
CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, j JSON);
INSERT INTO t(j) SELECT CAST(t AS JSON) FROM times ORDER BY t;
SELECT * FROM t;
DROP TABLE t, times;
Suggested fix:
Make correct values.
Description: When inserting extreme TIME values into a TIME column, and later converting those values to JSON, the absolute values appear to become smaller. Test case is clipped from MTR test json.json_functions_innodb mysql> CREATE TABLE times (t TIME(6)); Query OK, 0 rows affected (0.64 sec) mysql> INSERT INTO times VALUES -> ('-838:59:59'), -> ('838:59:59'), -> ('-00:00:00.000001'), -> ('-00:00:00'), -> ('24:00:00'), -> ('-12:00:00'), -> ('-24:00:00'); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM times ORDER BY CAST(t AS JSON); +-------------------+ | t | +-------------------+ | -838:59:59.000000 | | -24:00:00.000000 | | -12:00:00.000000 | | -00:00:00.000001 | | 00:00:00.000000 | | 24:00:00.000000 | | 838:59:59.000000 | +-------------------+ 7 rows in set (0.00 sec) mysql> CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, j JSON); Query OK, 0 rows affected (0.62 sec) mysql> INSERT INTO t(j) SELECT CAST(t AS JSON) FROM times ORDER BY t; Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t; +----+---------------------+ | id | j | +----+---------------------+ | 1 | "-630:59:59.000000" | | 2 | "-24:00:00.000000" | | 3 | "-12:00:00.000000" | | 4 | "-00:00:00.000001" | | 5 | "00:00:00.000000" | | 6 | "24:00:00.000000" | | 7 | "630:59:59.000000" | +----+---------------------+ 7 rows in set (0.00 sec) mysql> DROP TABLE t, times; Query OK, 0 rows affected (0.04 sec) How to repeat: CREATE TABLE times (t TIME(6)); INSERT INTO times VALUES ('-838:59:59'), ('838:59:59'), ('-00:00:00.000001'), ('-00:00:00'), ('24:00:00'), ('-12:00:00'), ('-24:00:00'); SELECT * FROM times ORDER BY CAST(t AS JSON); CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, j JSON); INSERT INTO t(j) SELECT CAST(t AS JSON) FROM times ORDER BY t; SELECT * FROM t; DROP TABLE t, times; Suggested fix: Make correct values.