[MDEV-9344] Conversion from DOUBLE to DATETIME is inconsistent - Jira

Alexander Barkov created issue - 2015-12-30 10:38

Alexander Barkov made changes - 2015-12-30 10:38

Field	Original Value	New Value
Link		This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Alexander Barkov added a comment - 2015-12-30 10:51

Note, If I change the target data type from DATETIME(3) to DECIMAL(20,3):

SELECT CAST(20101112111417.766e0 AS DECIMAL(20,3));

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DOUBLE);

INSERT INTO t1 VALUES (20101112111417.766);

SELECT CAST(a AS DECIMAL(20,3)) FROM t1;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DOUBLE);

INSERT INTO t1 VALUES (20101112111417.766);

ALTER TABLE t1 MODIFY a DECIMAL(20,3);

SELECT * FROM t1;

then all three scripts consistently return the same result:

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

| a                  |

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

| 20101112111417.766 |

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

Alexander Barkov added a comment - 2015-12-30 10:51 Note, If I change the target data type from DATETIME(3) to DECIMAL(20,3): SELECT CAST(20101112111417.766e0 AS DECIMAL(20,3)); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DOUBLE); INSERT INTO t1 VALUES (20101112111417.766); SELECT CAST(a AS DECIMAL(20,3)) FROM t1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DOUBLE); INSERT INTO t1 VALUES (20101112111417.766); ALTER TABLE t1 MODIFY a DECIMAL(20,3); SELECT * FROM t1; then all three scripts consistently return the same result: +--------------------+ | a | +--------------------+ | 20101112111417.766 | +--------------------+

Alexander Barkov made changes - 2015-12-30 10:58

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then convert the double result to DATETIME
- the third script uses val_str() of the source and then convert the string result to DATETIME

Alexander Barkov made changes - 2015-12-30 11:01

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then convert the double result to DATETIME
- the third script uses val_str() of the source and then convert the string result to DATETIME

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then convert the double result to DATETIME
- the third script uses val_str() of the source and then convert the string result to DATETIME

The underlying code in double-to-datetime conversion effectively uses this expression to calculate fractional digits:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncated to 3 digits and gives 0.765

Alexander Barkov made changes - 2015-12-30 11:02

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then convert the double result to DATETIME
- the third script uses val_str() of the source and then convert the string result to DATETIME

The underlying code in double-to-datetime conversion effectively uses this expression to calculate fractional digits:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncated to 3 digits and gives 0.765

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then performce double-to-datetime conversion
- the third script uses val_str() of the source and then convert the string result to DATETIME

The underlying code in double-to-datetime conversion effectively uses this expression to calculate fractional digits:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncated to 3 digits and gives 0.765

Alexander Barkov made changes - 2015-12-30 11:03

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then performce double-to-datetime conversion
- the third script uses val_str() of the source and then convert the string result to DATETIME

The underlying code in double-to-datetime conversion effectively uses this expression to calculate fractional digits:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncated to 3 digits and gives 0.765

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then perform double-to-datetime conversion
- the third script uses val_str() of the source and then convert the string result to DATETIME

The underlying code in double-to-datetime conversion effectively uses this expression to calculate fractional digits:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncated to 3 digits and gives 0.765

Alexander Barkov made changes - 2015-12-30 11:03

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then perform double-to-datetime conversion
- the third script uses val_str() of the source and then convert the string result to DATETIME

The underlying code in double-to-datetime conversion effectively uses this expression to calculate fractional digits:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncated to 3 digits and gives 0.765

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then perform double-to-datetime conversion
- the third script 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncated to 3 digits and gives 0.765

Alexander Barkov made changes - 2015-12-30 11:03

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then perform double-to-datetime conversion
- the third script 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncated to 3 digits and gives 0.765

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then perform double-to-datetime conversion
- the third script 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncates to 3 digits and gives 0.765

Alexander Barkov made changes - 2015-12-30 11:10

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then perform double-to-datetime conversion
- the third script 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
Notice, it returns 0.765625 instead of just 0.766 which then truncates to 3 digits and gives 0.765

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then perform double-to-datetime conversion
- the third script 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

Alexander Barkov made changes - 2015-12-30 11:12

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- the first and the second script use val_real() of the source and then perform double-to-datetime conversion
- the third script 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

Alexander Barkov made changes - 2015-12-30 11:13

Description

{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
and
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

Alexander Barkov made changes - 2015-12-30 11:14

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3));
{code}
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

Alexander Barkov made changes - 2015-12-30 11:14

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (20101112111417.766);
SELECT CAST(a AS DATETIME(3)) FROM t1;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

Alexander Barkov made changes - 2015-12-30 11:14

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| CAST(a AS DATETIME(3)) |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

Alexander Barkov made changes - 2015-12-30 11:16

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

Alexander Barkov made changes - 2015-12-30 11:17

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

The difference happens because:
- all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion
- 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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

Alexander Barkov made changes - 2015-12-30 11:19

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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.

Alexander Barkov made changes - 2015-12-30 11:20

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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.

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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).

Alexander Barkov made changes - 2015-12-30 11:20

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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
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).

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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).

Alexander Barkov made changes - 2015-12-30 11:21

Description

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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).

These scripts:
{code}
SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;
{code}
{code}
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;
{code}
{code}
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;
{code}
{code}
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;
{code}
return
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.765 |
+-------------------------+
{noformat}

while
{code}
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;
{code}
returns
{noformat}
+-------------------------+
| a |
+-------------------------+
| 2010-11-12 11:14:17.766 |
+-------------------------+
{noformat}

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:
{code}
MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
+--------------------------------------------------+
| 20101112111417.766e0-FLOOR(20101112111417.766e0) |
+--------------------------------------------------+
| 0.765625 |
+--------------------------------------------------+
{code}
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).

Alexander Barkov made changes - 2016-01-11 06:09

Component/s

Temporal Types [ 11000 ]

Alexander Barkov made changes - 2018-06-15 02:52

Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Alexander Barkov made changes - 2018-06-15 02:52

Fix Version/s		10.4 [ 22408 ]
Fix Version/s	10.2 [ 14601 ]

Alexander Barkov made changes - 2019-11-15 13:03

Component/s

Data types [ 13906 ]

Julien Fritsch made changes - 2019-11-18 11:41

Epic Link

MDEV-21071 [ 80504 ]

Julien Fritsch made changes - 2019-11-18 11:51

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Sergei Golubchik made changes - 2021-12-06 21:32

Workflow

MariaDB v3 [ 73344 ]

MariaDB v4 [ 139925 ]

Alexander Barkov made changes - 2024-11-20 06:02

Link

This issue relates to MDEV-9336 [ MDEV-9336 ]

Alexander Barkov made changes - 2024-11-20 06:05

Link

This issue relates to MDEV-9343 [ MDEV-9343 ]

MariaDB Server

Conversion from DOUBLE to DATETIME is inconsistent

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration