[MDEV-17477] Wrong result for TIME('-2001-01-01 10:20:30') and numerous other str-to-time conversion problems - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4.0
Component/s: Temporal Types
Labels:
None

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.

Attachments

Issue Links

blocks

MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2018-10-20 03:40 - edited

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            |

+---------------------+-----------------------+

Alexander Barkov added a comment - 2018-10-20 03:40 - edited 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 | +---------------------+-----------------------+

Alexander Barkov added a comment - 2018-10-20 13:33 - edited

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.

Alexander Barkov added a comment - 2018-10-20 13:33 - edited 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.

Alexander Barkov added a comment - 2018-10-20 13:42 - edited

'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'.

Alexander Barkov added a comment - 2018-10-20 13:42 - edited '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'.

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-10-16 19:53

Updated:: 2018-11-16 06:14

Resolved:: 2018-10-20 18:32

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server