Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.2, 10.3, 10.4
-
None
Description
These scripts:
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE);
|
INSERT INTO t1 VALUES (20101112111417.766);
|
SELECT CAST(a AS DATETIME(3)) AS a FROM t1;
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DATETIME(3), b DOUBLE);
|
INSERT INTO t1 (b) VALUES(20101112111417.766e0);
|
UPDATE t1 SET a=b;
|
SELECT a FROM t1;
|
DROP TABLE IF EXISTS t1,t2;
|
CREATE TABLE t1 (a DATETIME(3));
|
CREATE TABLE t2 (a DOUBLE);
|
INSERT INTO t2 VALUES (20101112111417.766e0);
|
INSERT INTO t1 SELECT * FROM t2;
|
SELECT * FROM t1;
|
return
+-------------------------+
|
| a |
|
+-------------------------+
|
| 2010-11-12 11:14:17.765 |
|
+-------------------------+
|
while
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE);
|
INSERT INTO t1 VALUES (20101112111417.766);
|
ALTER TABLE t1 MODIFY a DATETIME(3);
|
SELECT * FROM t1;
|
returns
+-------------------------+
|
| a |
|
+-------------------------+
|
| 2010-11-12 11:14:17.766 |
|
+-------------------------+
|
The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion, as DECIMAL gives consistent results (see below).
- the last script (with ALTER) uses val_str() of the source and then does string-to-datetime conversion
The underlying code in double-to-datetime conversion effectively uses this expression to calculate fractional digits:
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
|
+--------------------------------------------------+
|
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
|
+--------------------------------------------------+
|
| 0.765625 |
|
+--------------------------------------------------+
|
Notice, it returns 0.765625 instead of just 0.766 which then truncates to 3 digits and gives 0.765
Possible solutions:
1. Use DECIMAL as an intermediate data type in double-to-datetime conversion, as DECIMAL returns consistent results (see below).
2. Fix Item_datetime_typecast::get_date() to round fractional digits instead of truncation
3. Fix the ALTER code to use val_real() instead of val_str() when altering DOUBLE to DATETIME. This will make all cases return .765 at the end, which is probably not so good, as .766 looks better and consistent with what DECIMAL does (see below).