Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
This script:
SELECT CAST(10000000000000000000e0 AS UNSIGNED) AS a, |
CAST(10000000000000000000.0 AS UNSIGNED) AS b, |
CAST(10000000000000000000 AS UNSIGNED) AS c; |
returns a wrong result, without warnings:
+---------------------+----------------------+----------------------+
|
| a | b | c |
|
+---------------------+----------------------+----------------------+
|
| 9223372036854775807 | 10000000000000000000 | 10000000000000000000 |
|
+---------------------+----------------------+----------------------+
|
Notice, the value for a is wrong. It truncated the value to the maximum positive signed value.
The expected result would be to return 10000000000000000000 for all columns.
If I rewrite the script slightly, it still returns the same wrong result for a, but now with a warning:
SELECT CAST(CAST(10000000000000000000 AS DOUBLE) AS UNSIGNED) AS a, |
CAST(CAST(10000000000000000000 AS DECIMAL(30,0)) AS UNSIGNED) AS b, |
CAST(CAST(10000000000000000000 AS UNSIGNED) AS UNSIGNED) AS c; |
SHOW WARNINGS;
|
+--------------------------------------------------------+
|
| cast(cast(10000000000000000000 as double) as unsigned) |
|
+--------------------------------------------------------+
|
| 9223372036854775808 |
|
+--------------------------------------------------------+
|
+-------+------+-------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------+
|
| Note | 1105 | Cast to unsigned converted negative integer to it's positive complement |
|
+-------+------+-------------------------------------------------------------------------+
|
If I rewrite the script using table columns, it also returns the same wrong result, with a warning:
CREATE OR REPLACE TABLE t1 (a DOUBLE, b DECIMAL(30,0), c BIGINT UNSIGNED); |
INSERT INTO t1 VALUES (10000000000000000000,10000000000000000000,10000000000000000000); |
SELECT CAST(a AS UNSIGNED) AS a, |
CAST(b AS UNSIGNED) AS b, |
CAST(c AS UNSIGNED) AS c |
FROM t1; |
SHOW WARNINGS;
|
+---------------------+----------------------+----------------------+
|
| a | b | c |
|
+---------------------+----------------------+----------------------+
|
| 9223372036854775807 | 10000000000000000000 | 10000000000000000000 |
|
+---------------------+----------------------+----------------------+
|
+---------+------+-------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-------------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: '1e19' |
|
+---------+------+-------------------------------------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-