|
This SQL script:
PREPARE stmt FROM 'SELECT CAST(? AS SIGNED),CAST(1e20 AS SIGNED)';
|
SET @a=1e20;
|
EXECUTE stmt USING @a;
|
returns different results for the same number when used
as a prepared statement parameter vs passing it directly:
+----------------------+----------------------+
|
| CAST(? AS SIGNED) | CAST(1e20 AS SIGNED) |
|
+----------------------+----------------------+
|
| -9223372036854775808 | 9223372036854775807 |
|
+----------------------+----------------------+
|
1 row in set (0.00 sec)
|
The value for the parameter is wrong.
It should be fixed so both CASTs return 9223372036854775807.
When casting the same double number to UNSIGNED:
PREPARE stmt FROM 'SELECT CAST(? AS UNSIGNED),CAST(1e20 AS UNSIGNED)';
|
SET @a=1e20;
|
EXECUTE stmt USING @a;
|
it also returns different values:
+---------------------+------------------------+
|
| CAST(? AS UNSIGNED) | CAST(1e20 AS UNSIGNED) |
|
+---------------------+------------------------+
|
| 9223372036854775808 | 9223372036854775807 |
|
+---------------------+------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
and the warning is wrong:
mysql> SHOW WARNINGS;
|
+-------+------+-------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------+
|
| Note | 1105 | Cast to unsigned converted negative integer to it's positive complement |
|
+-------+------+-------------------------------------------------------------------------+
|
because there are actually no any negative numbers involved.
It should be fixed to return the same value for both CASTs.
The warning should be either removed, or changed to something more
applicable to this case.
Btw, the value 9223372036854775807, returned by CAST(1e20 AS UNSIGNED),
looks wrong. It's the maximum possible signed number.
It should return the maximum possible unsigned number instead.
|