Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
CAST("string-with-a-huge-number" AS INT) erroneously performs a binary complement:
SELECT CAST("18446744073709552001" AS INT) AS c1; |
SHOW WARNINGS;
|
+----+
|
| c1 |
|
+----+
|
| -1 |
|
+----+
|
1 row in set, 1 warning (0.000 sec)
|
+---------+------+-----------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: '18446744073709552001' |
|
+---------+------+-----------------------------------------------------------+
|
The same problem is repeatable with CAST in dynamic columns:
SELECT COLUMN_GET(COLUMN_CREATE(1, "18446744073709552001" AS CHAR), 1 AS INT) AS c1; |
SHOW WARNINGS;
|
+---------+------+------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------------------------------------------+
|
| Warning | 1918 | Encountered illegal value '18446744073709552001' when converting to INT |
|
| Note | 1105 | Cast to signed converted positive out-of-range integer to it's negative complement |
|
+---------+------+------------------------------------------------------------------------------------+
|
Applying binary complement to string->int conversion is wrong. Binary complement should only be applied when converting between integer types of different signness.
Note, implicit conversion works fine though:
CREATE OR REPLACE TABLE t1 (a BIGINT); |
INSERT IGNORE INTO t1 VALUES ("18446744073709552001"); |
SELECT * FROM t1; |
+---------------------+
|
| a |
|
+---------------------+
|
| 9223372036854775807 |
|
+---------------------+
|
Notice, unlike explicit CAST, implicit conversion does not apply binary complement. Instead, it correctly cuts the number to the maximum possible signed 64-bit value, which is 9223372036854775807.
Explicit CAST should be fixed accordingly.