|
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' |
|
+---------+------+-------------------------------------------+
|
|