[MDEV-8919] Wrong result for CAST(9999999999999999999.0) Created: 2015-10-08  Updated: 2019-05-17  Resolved: 2019-05-17

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.0, 10.1, 10.2
Fix Version/s: 10.4.5

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-19502 TIME_ROUND_FRACTIONAL is not respecte... Open
Sprint: 10.1.15, 10.2.2-1

 Description    

SELECT CAST(9999999999999999999e0 AS UNSIGNED) AS c1, CAST(9999999999999999999.0 AS UNSIGNED) AS c2;

returns

+---------------------+---------------------+
 
| c1                  | c2                  |
 
+---------------------+---------------------+
 
| 9223372036854775807 | 9999999999999999999 |
 
+---------------------+---------------------+

The c1 value looks wrong.

Note, implicit cast on INSERT works fine:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a BIGINT UNSIGNED);
 
INSERT INTO t1 VALUES (9999999999999999999e0);
 
SELECT * FROM t1


+----------------------+
 
| a                    |
 
+----------------------+
 
| 10000000000000000000 |
 
+----------------------+



 Comments   
Comment by Alexander Barkov [ 2016-06-23 ]

Negative numbers also do not convert well:

SELECT CAST(-1e0 AS UNSIGNED),CAST(-1e308 AS UNSIGNED);


+------------------------+--------------------------+
 
| CAST(-1e0 AS UNSIGNED) | CAST(-1e308 AS UNSIGNED) |
 
+------------------------+--------------------------+
 
|   18446744073709551615 |      9223372036854775808 |
 
+------------------------+--------------------------+

The expected result is to return 0 in both cases, similar to what happens in case of DECIMAL:

SELECT CAST(-1.0 AS UNSIGNED);


+------------------------+
 
| CAST(-1.0 AS UNSIGNED) |
 
+------------------------+
 
|                      0 |
 
+------------------------+

Note, implicit cast on INSERT works as expected:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a BIGINT UNSIGNED);
 
INSERT INTO t1 VALUES (-1e0);
 
SELECT * FROM t1;


+------+
 
| a    |
 
+------+
 
|    0 |
 
+------+

Comment by Alexander Barkov [ 2016-06-23 ]

Negative TIME values also do not convert well:

SELECT CAST(TIME'-00:00:01.123' AS UNSIGNED);


+---------------------------------------+
 
| CAST(TIME'-00:00:01.123' AS UNSIGNED) |
 
+---------------------------------------+
 
|                  18446744073709551615 |
 
+---------------------------------------+

The expected result should be 0, similar to what happens in case of CAST(-1.123 AS UNSIGNED).

Note, implicit cast on INSERT works as expected:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a BIGINT UNSIGNED);
 
INSERT INTO t1 VALUES (TIME'-00:00:01.123');
 
SELECT * FROM t1;


+------+
 
| a    |
 
+------+
 
|    0 |
 
+------+

Comment by Alexander Barkov [ 2016-06-27 ]

This script:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DOUBLE UNSIGNED);
 
INSERT INTO t1 VALUES (1.9e19);
 
SELECT CAST(a AS SIGNED), CAST(MIN(a) AS SIGNED) FROM t1;

also returns a wrong result:

+---------------------+------------------------+
 
| CAST(a AS SIGNED)   | CAST(MIN(a) AS SIGNED) |
 
+---------------------+------------------------+
 
| 9223372036854775807 |   -9223372036854775808 |
 
+---------------------+------------------------+

The expected result is to return 9223372036854775807 in both columns.

Comment by Alexander Barkov [ 2016-06-27 ]

This script:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DECIMAL(30,1) UNSIGNED);
 
INSERT INTO t1 VALUES (1e19);
 
SELECT a, CAST(a AS SIGNED) FROM t1;

returns

+------------------------+----------------------+
 
| a                      | CAST(a AS SIGNED)    |
 
+------------------------+----------------------+
 
| 10000000000000000000.0 | -8446744073709551616 |
 
+------------------------+----------------------+
 
without warnings.

The expected result for CAST is 9223372036854775807, with a warning.

Comment by Alexander Barkov [ 2019-05-17 ]

The old patch is outdated. As this is a minor issue, let's fix in 10.4 using Type_handler infrastructrure.

Generated at Thu Feb 08 07:30:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.