Bug #36539 data truncation and implicit cast return different values than explicit cast
Submitted: 6 May 2008 15:44 Modified: 6 May 2008 17:19
Reporter: Scott Noyes Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.38+, 5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[6 May 2008 15:44] Scott Noyes
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.