| Bug #18759 | Incorrect string to numeric conversion | ||
|---|---|---|---|
| Submitted: | 3 Apr 2006 20:27 | Modified: | 20 Jun 2006 16:53 |
| Reporter: | Matthew Lord | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.19-bk | OS: | Linux (linux 2.4.x, 2.6.x, solaris 9) |
| Assigned to: | Jani Tolonen | CPU Architecture: | Any |
[17 Apr 2006 20:27]
Eric Braswell
Also seen in 5.0.18 apple-darwin8.2.0, and 5.0.19 pc-linux-gnu : ... WHERE 'some_string' IN(varchar_col1, int_col1, varchar_col2) int_col1 is not correctly converted to a string for comparison with 'some_string'. By casting int_col1 as a binary string, the query works as expected: ... WHERE 'some_string' IN(string_col1, CAST(int_col1 as BINARY), string_col2)
[20 Jun 2006 16:53]
Jani Tolonen
This is not a bug. Even though the behaviour can vary, the results will be undetermined, if the conversion overflows. What happens in this case is that in case of a string, e.g. numeric value inside strings '1234...' will be converted to real (double) internally, which precision won't be as accurate as bigint (or longlong internally) can be. This is true for 4.1 and 5.0. However, in 5.1 the conversion is done to decimal, which will be more accurate than real (double) values in earlier versions. So in 5.1 and above this should work. In 5.0 and earlier the fix is to use the integer value, not send the value as a string, which can result to loss of data in conversion and thus to undetermined results.
Description: These two statements do not produce the same results in 4.1.18 or later: SELECT a, IF(id IN (21330000000000014), 'yes', '') FROM t1; SELECT a, IF(id IN ('21330000000000014'), 'yes', '') FROM t1; They DID in earlier versions of 4.1 and they still DO return the same results in 5.0.19. I tested this with MyISAM and InnoDB as the table type, both had the incorrect results: SELECT a, IF(id IN ('21330000000000014'), 'yes', '') FROM t1; +------+--------------------------------------------+ | a | IF(id IN ('21330000000000014'), 'yes', '') | +------+--------------------------------------------+ | blah | | | bleh | | | bloo | yes | | bluh | yes | +------+--------------------------------------------+ select id from t1 where a IN("bloo", "bluh"); +-------------------+ | id | +-------------------+ | 21330000000000014 | | 21330000000000015 | +-------------------+ I also tried it with latin1 and utf8 so it doesn't appear to be a charset or collation related issue. How to repeat: USE test; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id), a CHAR(10) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO t1 (id,a) VALUES(21330000000000012,'blah'); INSERT INTO t1 (id,a) VALUES(21330000000000013,'bleh'); INSERT INTO t1 (id,a) VALUES(21330000000000014,'bloo'); INSERT INTO t1 (id,a) VALUES(21330000000000015,'bluh'); -- Strange results on Solaris -- SELECT * FROM t1 WHERE id = 21330000000000014; SELECT * FROM t1 WHERE id = '21330000000000014'; SELECT * FROM t1 WHERE id IN (21330000000000014); SELECT * FROM t1 WHERE id IN ('21330000000000014'); -- Strange results on Solaris & Linux -- SELECT a, IF(id IN (21330000000000014), 'yes', '') FROM t1; SELECT a, IF(id IN ('21330000000000014'), 'yes', '') FROM t1;