MySQL Bugs: #116790: Retrieving TIME type from MYSQL JSON yields incorrect result

Bug #116790	Retrieving TIME type from MYSQL JSON yields incorrect result
Submitted:	27 Nov 2:24	Modified:	2 Dec 16:52
Reporter:	Albert Lesniewski	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	json, server, time

Description:
Hi, I notice a bug with regards of retrieving time values stored in JSON type in MySQL, this bug was found on MySQL Server version 8.0.22, but from studying the code it seems that the bug still exists in MySQL Server github trunk (61a3a1d). The problem occurs as the TIME values retrieved in JSON form do not match the one inserted from TIME type. 

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 CAST(t AS JSON) FROM times;

--------------------------------------------

+--------------------+
| CAST(t AS JSON)    |
+--------------------+
| "-06:59:59.000000" |
| "06:59:59.000000"  |
| "-00:00:00.000001" |
| "00:00:00.000000"  |
| "24:00:00.000000"  |
| "-12:00:00.000000" |
| "-24:00:00.000000" |
+--------------------+

As we can see, the resulting time values in JSON type are different from the ones we inserted.

Suggested fix:
We identified the problem to be in `sql_scalar_to_json`, in the switch-case for the temporal types all temporal types use the same function to parse the temporal values `TIME_from_longlong_datetime_packed`, but in case of TIME type we should use `TIME_from_longlong_time_packed`.

Hi Mr. Lesniewski,

Thank you for your bug report.

However, this is not a bug.

You can read our Reference Manual on https://dev.mysql.com and see the acceptable range for that data type.

When the values are within the range, you get expected results:

ysql> CREATE TABLE times (t TIME(6));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO times VALUES ('8:59:59'), ('3:59:59'), ('00:00:00.000001'), ('00:00:00'), ('23:59:59'), ('12:00:00'), ('23:59:59');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> show warnings;
Empty set (0.00 sec)

mysql> select CAST(t AS JSON) FROM times;
+-------------------+
| CAST(t AS JSON)   |
+-------------------+
| "08:59:59.000000" |
| "03:59:59.000000" |
| "00:00:00.000001" |
| "00:00:00.000000" |
| "23:59:59.000000" |
| "12:00:00.000000" |
| "23:59:59.000000" |
+-------------------+

Not a bug.

Hello! The values used by Albert are actually in the range of allowed values for the TIME data type, according to https://dev.mysql.com/doc/refman/8.0/en/time.html :
"TIME values may range from '-838:59:59' to '838:59:59'."

So, doesn't it look like a bug?

Salut Guilhem,

Actually, no .......

The range that you mentioned is correct, but the values out of the range from 00:00:00 to 23:59:59 can be used ONLY for the elapsed time.

So, when you cast TIME as JSON, that is definitely NOT the elapsed time.

We shall consider to update that page for the TIME data type to better explain this.

Not a bug.

I talked with Guilhem, and we remain unconvinced by the explanation. I read the manual (and JSON spec), and there is no mention of this behavior as being intended. But what I would want to bring to your attention is that the full -838:+838 is (seemingly) correctly stored in JSON (both binary and DOM format), I know this because by applying my suggested fix we do get correct results for the whole TIME range, so the data is there, stored correctly in JSON, it is a matter of using the correct function to retrieve that data (TIME_from_longlong_time_packed, not TIME_from_longlong_datetime_packed). With the suggested fix, the reported reproducible example returns expected result, I can also do:

CREATE TABLE t(a JSON); INSERT INTO t VALUES (cast(cast('-838:00:00' as time) as json)); SELECT * from t;

Result of those queries is correct as expected, which suggests (again) that the full range is correctly stored in the JSON column. The result is correct since in this case the code calls `Json_datetime::from_packed`, which properly identifies the field type as TIME and calls `TIME_from_longlong_time_packed`, as is expected. Thus my suggestion stands that `sql_scalar_to_json` should also identify the TIME field type and call the correct function in that case.

Given the above, I have trouble understanding how can the reported behavior be intended (this is how I interpret the 'not a bug' conclusion), when based on my findings everything points to that the full range of TIME is stored in JSON, thus should be possible to retrieve it?

Hi,

We shall discuss with Development teams that are in charge of these data types, whether this could be considered as a feature request.

Hi Mr. Lesniewski, Guilhtm,

We had an interesting discussion on the issue of this bug report.

It was agreed that this is not a bug.

However, we shall need to plan for two different sets of actions.

First of all, our Reference Manual will have to clarify the differences between TIME of the day and TIME interval, as well as problems that can arise from the conversions thereof.

Second, we will plan to add a new data type, TIME_INTERVAL, so that it can take the current range of the TIME, while TIME will be restricted to the range from 00:00:00 to 23:59:59. This will be done either in some 9.*.* version or 10.*.* version.

We shall inform you when the documentation is corrected.

Changing to Verified/Docs, assigning to myself.

This bug report will be updated when the documentation issue is fixed.