|
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 |
|
+---------------------+--------+
|
|