[MDEV-17477] Wrong result for TIME('-2001-01-01 10:20:30') and numerous other str-to-time conversion problems Created: 2018-10-16  Updated: 2018-11-16  Resolved: 2018-10-20

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4.0

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-16991 Rounding vs truncation for TIME, DATE... Closed

 Description    

SELECT TIME('-2001-01-01 10:20:30');


+------------------------------+
 
| TIME('-2001-01-01 10:20:30') |
 
+------------------------------+
 
| 10:20:30                     |
 
+------------------------------+

Looks wrong. The expected result is NULL.

 Comments   
Comment by Alexander Barkov [ 2018-10-20 ]

Other related problems:

Trailing spaces change the result of TIME():

SELECT TIME('2001-01-01') AS c1, TIME('2001-01-01  ') AS c2;


+----------+----------+
 
| c1       | c2       |
 
+----------+----------+
 
| 00:20:01 | 00:00:00 |
 
+----------+----------+


SELECT TIME('0001:01:01 '), TIME('0001:01:01  ');


+---------------------+----------------------+
 
| TIME('0001:01:01 ') | TIME('0001:01:01  ') |
 
+---------------------+----------------------+
 
| 01:01:01            | 00:00:00             |
 
+---------------------+----------------------+


SELECT TIME('1 2'), TIME('1 2 ');


+-------------+--------------+
 
| TIME('1 2') | TIME('1 2 ') |
 
+-------------+--------------+
 
| 00:00:01    | 26:00:00     |
 
+-------------+--------------+

Trailing spaces following DATE + 'T' (date/time separator) change the meaning of TIME():

SELECT TIME('2001-01-01T'), TIME('2001-01-01T ');


+---------------------+----------------------+
 
| TIME('2001-01-01T') | TIME('2001-01-01T ') |
 
+---------------------+----------------------+
 
| 00:20:01            | 00:00:00             |
 
+---------------------+----------------------+


SELECT TIME('00901-01-01'), TIME('00901-01-01T');


+---------------------+----------------------+
 
| TIME('00901-01-01') | TIME('00901-01-01T') |
 
+---------------------+----------------------+
 
| 00:09:01            | 00:00:00             |
 
+---------------------+----------------------+

Different result for 'T' with trailing digits in TIME()

SELECT TIME('901-01-01T1'), TIME('901-01-01T10');


+---------------------+----------------------+
 
| TIME('901-01-01T1') | TIME('901-01-01T10') |
 
+---------------------+----------------------+
 
| 00:09:01            | 10:00:00             |
 
+---------------------+----------------------+


SELECT TIME('091-01-01T1'), TIME('091-01-01T10');


+---------------------+----------------------+
 
| TIME('091-01-01T1') | TIME('091-01-01T10') |
 
+---------------------+----------------------+
 
| NULL                | 10:00:00             |
 
+---------------------+----------------------+

Trailing garbage change the result of TIME()

SELECT TIME('0001:01:01x'), TIME('0001:01:01xx');


+---------------------+----------------------+
 
| TIME('0001:01:01x') | TIME('0001:01:01xx') |
 
+---------------------+----------------------+
 
| 01:01:01            | 00:00:00             |
 
+---------------------+----------------------+

but not if the garbage is punctuation:

SELECT TIME('0001:01:01.'), TIME('0001:01:01..');


+---------------------+----------------------+
 
| TIME('0001:01:01.') | TIME('0001:01:01..') |
 
+---------------------+----------------------+
 
| 01:01:01            | 01:01:01             |
 
+---------------------+----------------------+


SELECT TIME('0001:01:01-'), TIME('0001:01:01--');


+---------------------+----------------------+
 
| TIME('0001:01:01-') | TIME('0001:01:01--') |
 
+---------------------+----------------------+
 
| 01:01:01            | 01:01:01             |
 
+---------------------+----------------------+


SELECT TIME('0001:01:01-'), TIME('0001:01:01--');


+---------------------+----------------------+
 
| TIME('0001:01:01-') | TIME('0001:01:01--') |
 
+---------------------+----------------------+
 
| 01:01:01            | 01:01:01             |
 
+---------------------+----------------------+

TIME() produces negative zero for the minus followed by garbage

SELECT TIME('-xxx'), TIME('-xxxxxxxxxxxxxxxxxxxx');


+--------------+-------------------------------+
 
| TIME('-xxx') | TIME('-xxxxxxxxxxxxxxxxxxxx') |
 
+--------------+-------------------------------+
 
| -00:00:00    | -00:00:00                     |
 
+--------------+-------------------------------+


SELECT TIME('-      '), TIME('-                         ');


+-----------------+------------------------------------+
 
| TIME('-      ') | TIME('-                         ') |
 
+-----------------+------------------------------------+
 
| -00:00:00       | -00:00:00                          |
 
+-----------------+------------------------------------+

But TIME() with a sign only produces NULL

SELECT TIME('-'), TIME('-');


+-----------+-----------+
 
| TIME('-') | TIME('-') |
 
+-----------+-----------+
 
| NULL      | NULL      |
 
+-----------+-----------+

This is different comparing to sign followed by garbage and time followed by spaces (see above).

Examples when zero fractional digits change the meaning completely

SELECT TIME('1-1-1 1:1:1'), TIME('1-1-1 1:1:1.0');


+---------------------+-----------------------+
 
| TIME('1-1-1 1:1:1') | TIME('1-1-1 1:1:1.0') |
 
+---------------------+-----------------------+
 
| 00:00:01            | 01:01:01.0            |
 
+---------------------+-----------------------+

Comment by Alexander Barkov [ 2018-10-20 ]

Examples when trailing garbage behaviour depends on the data type

SELECT CAST('20050326 garbage' as date), CAST('50326 garbage' as time);
 
SHOW WARNINGS;


+----------------------------------+-------------------------------+
 
| CAST('20050326 garbage' as date) | CAST('50326 garbage' as time) |
 
+----------------------------------+-------------------------------+
 
| NULL                             | 05:03:26                      |
 
+----------------------------------+-------------------------------+


+---------+------+-------------------------------------------------+
 
| Level   | Code | Message                                         |
 
+---------+------+-------------------------------------------------+
 
| Warning | 1292 | Incorrect datetime value: '20050326 garbage'    |
 
| Warning | 1292 | Truncated incorrect time value: '50326 garbage' |
 
+---------+------+-------------------------------------------------+

The first column should return '2005-03-26' with a warning.

Comment by Alexander Barkov [ 2018-10-20 ]

'sign space hh:mm:ss' works fine, 'sign space DD hh:mm:ss' does not work well:

SELECT TIME('- 01:00:00'), TIME('- 1 01:00:00');


+--------------------+----------------------+
 
| TIME('- 01:00:00') | TIME('- 1 01:00:00') |
 
+--------------------+----------------------+
 
| -01:00:00          | -01:00:00            |
 
+--------------------+----------------------+

The expected result for the second column is '-25:00:00'.

Generated at Thu Feb 08 08:36:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.