Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
Notice a similar bug MDEV-17351 (which is about hybrid functions).
MICROSECOND(arg) can return 999999 in some contexts where TIME(arg) returns a result without fractional digits.
Internally, the argument gets converted to the maximum possible TIME value of '838:59:59.999999'.
Confusing examples when a number gets converted to '838:59:59.999999'
SELECT
|
TIME(8395959) AS c1, |
MICROSECOND(8395959) AS c2; |
+-----------+--------+
|
| c1 | c2 |
|
+-----------+--------+
|
| 838:59:59 | 999999 |
|
+-----------+--------+
|
SELECT
|
TIME(8395959.0) AS c1, |
MICROSECOND(8395959.0) AS c2; |
+-------------+--------+
|
| c1 | c2 |
|
+-------------+--------+
|
| 838:59:59.9 | 999999 |
|
+-------------+--------+
|
SELECT
|
TIME(CAST(8395959 AS SIGNED)) AS c1, |
MICROSECOND(CAST(8395959 AS SIGNED)) AS c2; |
+-----------+--------+
|
| c1 | c2 |
|
+-----------+--------+
|
| 838:59:59 | 999999 |
|
+-----------+--------+
|
SELECT
|
TIME(CAST(8395959 AS DECIMAL(30,1))) AS c1, |
MICROSECOND(CAST(8395959 AS DECIMAL(10,1))) AS c2; |
+-------------+--------+
|
| c1 | c2 |
|
+-------------+--------+
|
| 838:59:59.9 | 999999 |
|
+-------------+--------+
|
SELECT
|
TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c1, |
MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c2; |
+-----------+--------+
|
| c1 | c2 |
|
+-----------+--------+
|
| 838:59:59 | 999999 |
|
+-----------+--------+
|
More confusing examples when a string gets converted to '838:59:59.999999'
Intuitively, an explicit conversion from string to time using the TIME() function, and an implicit conversion of a function argument (from a non-TIME actual parameter to TIME formal parameter) should return the same result. But they do not, because of different ways of precision detection for string arguments (mentioned in the previous paragraph):
SELECT
|
TIME('839:59:59') AS c0, |
MICROSECOND(TIME('839:59:59')) AS explicit, |
MICROSECOND('839:59:59') AS implicit; |
+-----------+----------+----------+
|
| c0 | explicit | implicit |
|
+-----------+----------+----------+
|
| 838:59:59 | 0 | 999999 |
|
+-----------+----------+----------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP
-
- Closed
-