Bug #36539 data truncation and implicit cast return different values than explicit cast
Description:
An implicit cast of a string containing decimal points returns a different value than an explicit cast of the same string.
Issue occurs in 5.0.38 through 5.0.60, 5.1.23, 6.0.4, but not in 5.0.20a.
How to repeat:
SET SQL_MODE = '';
CREATE TABLE t1 (string varchar(255), implicit int unsigned);
INSERT INTO t1 VALUES ('164.64.194', '164.64.194');
INSERT INTO t1 VALUES ('164.64', '164.64');
SELECT string, implicit, CAST(string AS unsigned) AS explicit FROM t1;
Output:
+------------+----------+----------+
| string | implicit | explicit |
+------------+----------+----------+
| 164.64.194 | 16464 | 164 |
| 164.64 | 165 | 164 |
+------------+----------+----------+
Suggested fix:
This particular issue centered around fetching octets from an IP address. Workarounds include using the INET_ATON function instead, using explicit casting on the value before storing in an integer, or using substring functions to retrieve only a single octet part not including the two decimal points.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: An implicit cast of a string containing decimal points returns a different value than an explicit cast of the same string. Issue occurs in 5.0.38 through 5.0.60, 5.1.23, 6.0.4, but not in 5.0.20a. How to repeat: SET SQL_MODE = ''; CREATE TABLE t1 (string varchar(255), implicit int unsigned); INSERT INTO t1 VALUES ('164.64.194', '164.64.194'); INSERT INTO t1 VALUES ('164.64', '164.64'); SELECT string, implicit, CAST(string AS unsigned) AS explicit FROM t1; Output: +------------+----------+----------+ | string | implicit | explicit | +------------+----------+----------+ | 164.64.194 | 16464 | 164 | | 164.64 | 165 | 164 | +------------+----------+----------+ Suggested fix: This particular issue centered around fetching octets from an IP address. Workarounds include using the INET_ATON function instead, using explicit casting on the value before storing in an integer, or using substring functions to retrieve only a single octet part not including the two decimal points.