Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL)
-
None
Description
CREATE OR REPLACE TABLE t1 (a TIME(6)); |
INSERT INTO t1 VALUES ('-24:10:10.10'); |
SELECT
|
EXTRACT(MINUTE FROM a), |
EXTRACT(SECOND FROM a), |
EXTRACT(MICROSECOND FROM a), |
EXTRACT(DAY FROM a), |
EXTRACT(DAY_HOUR FROM a), |
EXTRACT(DAY_MINUTE FROM a), |
EXTRACT(DAY_SECOND FROM a), |
EXTRACT(DAY_MICROSECOND FROM a) |
FROM t1 |
\G
|
*************************** 1. row ***************************
|
EXTRACT(MINUTE FROM a): -10
|
EXTRACT(SECOND FROM a): -10
|
EXTRACT(MICROSECOND FROM a): -100000
|
EXTRACT(DAY FROM a): 1
|
EXTRACT(DAY_HOUR FROM a): -100
|
EXTRACT(DAY_MINUTE FROM a): -10010
|
EXTRACT(DAY_SECOND FROM a): -1001010
|
EXTRACT(DAY_MICROSECOND FROM a): -1001010100000
|
The result for EXTRACT(DAY..) looks wrong. It should be -1.
The SQL standard says:
If <extract field> is a <primary datetime field>, then the result is the value of the datetime field identified by that <primary datetime field> and has the same sign as the <extract source>. NOTE 186 — If the value of the identified <primary datetime field> is zero or if <extract source> is not an <interval value expression>, then the sign is irrelevant.
The negative time value '-24:10:10.10' plays role of INTERVAL DAY TO SECOND in this example. The extract source is an <interval value expression>. So the sign is relevant and should be derived from the value.
Attachments
Issue Links
- blocks
-
MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP
-
- Closed
-