[MDEV-17351] Wrong results for GREATEST,TIMESTAMP,ADDTIME with an out-of-range TIME-alike argument - Jira

XML

Word

Printable

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: Data types, Temporal Types
Labels:
None

Description

Some time expressions can return more hidden fractional digits than the effective data type of this expression supposes to. This happens when an out-of-range non-TIME value gets converted to the maximum possible value of '838:59:59.999999'.

The extra digits are not seen when you just do a SELECT for such expressions, because the extra digits get cut by the protocol:

bool Type_handler::

       Item_send_time(Item *item, Protocol *protocol, st_value *buf) const

  item->get_time(protocol->thd, &buf->value.m_time);

  if (!item->null_value)

    return protocol->store_time(&buf->value.m_time, item->decimals);  -- UNEXPECTED DIGITS GET CUT HERE

  return protocol->store_null();

However, if you pass this expression to certain functions, e.g. MICROSECOND(), or arithmetic operators, or cast to DECIMAL, the presence of extra fractional digits reveals itself.

Looking into this query:

SELECT GREATEST(8395959, TIME'00:00:00');

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

| GREATEST(8395959, TIME'00:00:00') |

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

| 838:59:59                         |

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

one might think that the returned value is '838:59:59'. But in fact it is not. The real returned value is '838:59:59.999999'.

These queries demonstrate confusing effects of hidden extra digits:

SELECT

  CAST(GREATEST(8395959, TIME'00:00:00') AS SIGNED) AS ci,

  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,0)) AS c0,

  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,1)) AS c1,

  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,2)) AS c2,

  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,3)) AS c3,

  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,4)) AS c4,

  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,5)) AS c5,

  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,6)) AS c6,

  CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,7)) AS c7

\G

ci: 8385959

c0: 8385960

c1: 8385960.0

c2: 8385960.00

c3: 8385960.000

c4: 8385960.0000

c5: 8385960.00000

c6: 8385959.999999

c7: 8385959.9999990

Notice:

conversion to INT truncated the extra digits (column ci)
conversion to DECIMAL(30, 0..5) rounded the extra digits (columns c0..c5)
conversion to DECIMAL(30, 6..7) fully revealed the extra digits (columns c6..c7)

The problem is also repeatable with additive expressions:

SELECT

  GREATEST(8395959, TIME'00:00:00') AS ci,

  GREATEST(8395959, TIME'00:00:00')+0 AS c0,

  GREATEST(8395959, TIME'00:00:00')+0.0 AS c1,

  GREATEST(8395959, TIME'00:00:00')+0.00 AS c2,

  GREATEST(8395959, TIME'00:00:00')+0.000 AS c3,

  GREATEST(8395959, TIME'00:00:00')+0.0000 AS c4,

  GREATEST(8395959, TIME'00:00:00')+0.00000 AS c5,

  GREATEST(8395959, TIME'00:00:00')+0.000000 AS c6,

  GREATEST(8395959, TIME'00:00:00')+0.0000000 AS c7

\G

ci: 838:59:59

c0: 8385959

c1: 8385960.0

c2: 8385960.00

c3: 8385960.000

c4: 8385960.0000

c5: 8385960.00000

c6: 8385959.999999

c7: 8385959.9999990

SELECT

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS ci,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0 AS c0,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0 AS c1,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00 AS c2,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000 AS c3,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000 AS c4,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00000 AS c5,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000000 AS c6,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000000 AS c7

\G

ci: 2001-02-04 22:59:59

c0: 20010204225959

c1: 20010204225960.0

c2: 20010204225960.00

c3: 20010204225960.000

c4: 20010204225960.0000

c5: 20010204225960.00000

c6: 20010204225959.999999

c7: 20010204225959.9999990

SELECT

  TIMESTAMP('2001-01-01', 8395959) AS ci,

  TIMESTAMP('2001-01-01', 8395959)+0 AS c0,

  TIMESTAMP('2001-01-01', 8395959)+0.0 AS c1,

  TIMESTAMP('2001-01-01', 8395959)+0.00 AS c2,

  TIMESTAMP('2001-01-01', 8395959)+0.000 AS c3,

  TIMESTAMP('2001-01-01', 8395959)+0.0000 AS c4,

  TIMESTAMP('2001-01-01', 8395959)+0.00000 AS c5,

  TIMESTAMP('2001-01-01', 8395959)+0.000000 AS c6,

  TIMESTAMP('2001-01-01', 8395959)+0.0000000 AS c7

\G

ci: 2001-02-04 22:59:59

c0: 20010204225959

c1: 20010204225960.0

c2: 20010204225960.00

c3: 20010204225960.000

c4: 20010204225960.0000

c5: 20010204225960.00000

c6: 20010204225959.999999

c7: 20010204225959.9999990

The problem is repeatable with MICROSECOND(GREATEST(..)), but starting from 10.3 only

In this script:

SET sql_mode='';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 AS SELECT GREATEST(8395959, TIME'00:00:00');

SHOW CREATE TABLE t1;

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

| Table | Create Table                                                                                                       |

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

| t1    | CREATE TABLE `t1` (

  `GREATEST(8395959, TIME'00:00:00')` time DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

the result of GREATEST() has data type of TIME(0).

However, MICROSECOND() for the same expression returns 999999:

SELECT        GREATEST(8395959, TIME'00:00:00') AS c1,

  MICROSECOND(GREATEST(8395959, TIME'00:00:00')) AS c2;

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

| c1        | c2     |

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

| 838:59:59 | 999999 |

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

Looks wrong.

The problem is repeatable with MICROSECOND(ADDTIME(..)):

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 AS SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)  AS c1;

SHOW CREATE TABLE t1;

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

| Table | Create Table                                                                            |

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

| t1    | CREATE TABLE `t1` (

  `c1` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

SELECT            ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)  AS c1,

      MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)) AS c2;

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

| c1                  | c2     |

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

| 2001-02-04 22:59:59 | 999999 |

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

The problem is repeatable with MICROSECOND(TIMESTAMP(..))

SET sql_mode='';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 AS SELECT TIMESTAMP('2001-01-01 00:00:00', 8395959) AS c1;

SHOW CREATE TABLE t1;

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

| Table | Create Table                                                                            |

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

| t1    | CREATE TABLE `t1` (

  `c1` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

SELECT        TIMESTAMP('2001-01-01 00:00:00', 8395959) AS c1,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959)) AS c2;

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

| c1                  | c2     |

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

| 2001-02-04 22:59:59 | 999999 |

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

Attachments

Issue Links

blocks

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

Closed

relates to

MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument

Open

MDEV-17776 CAST(x AS INTERVAL DAY_SECOND(N))

Closed

MDEV-23388 Assertion `args[0]->decimals == 0' failed in Item_func_round::fix_arg_int

Closed

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-10-02 14:16

Updated:: 2020-08-03 15:47

Resolved:: 2018-10-08 09:39

Git Integration

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