| Bug #42219 | Querying on certain DECIMAL values, including integers, fails when quoted | ||
|---|---|---|---|
| Submitted: | 20 Jan 2009 14:51 | Modified: | 20 Jan 2009 16:37 |
| Reporter: | Nigel Kingswood | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.1.30, 5.1.29 | OS: | Linux (64bit Linux version 2.6.23.17-88.fc7) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | decimal, INTEGER, quote, string | ||
[20 Jan 2009 16:21]
Valeriy Kravchuk
Sorry, but this is not a bug. String is compared with integer as real numbers, and this is documented in http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html. Hence the result you have. Also there is a workaround that may apply to Oracle side as well: mysql> select * from tmp.test1 where intx='1' and inty='2' and dec1=cast('3092' -> as decimal); +------+------+--------------------------+ | intx | inty | dec1 | +------+------+--------------------------+ | 1 | 2 | 3092.0000000000000000000 | +------+------+--------------------------+ 1 row in set (0.00 sec) As far as I remember, Oracle supports both CAST and DECIMAL (as a synonym for NUMBER).
[20 Jan 2009 16:37]
Nigel Kingswood
This problem did not occur in 5.0 and according to the page link you sent, should only happen when there are more than 53 bits of precision being used.
SELECT '3092' = CAST('3092' AS DECIMAL);
returns 1. If this conversion returns 1, why does the query not work?
I cannot alter the Oracle queries. If I could, this would not be an issue.
Description: Using a SELECT with quoted decimals fails for certain values, even integers How to repeat: create table tmp.test1 ( intx int, inty int, dec1 decimal(38,19), PRIMARY KEY (intx, inty) ) engine innodb; insert into tmp.test1 values (1,2,3092),(1,3,-3092),(1,4,3092.1),(1,5,123); With Quote ========== select * from tmp.test1 where intx='1' and inty='2' and dec1= '3092'; Empty set (0.00 sec) Without Quote ============= select * from tmp.test1 where intx='1' and inty='2' and dec1= 3092; +------+------+--------------------------+ | intx | inty | dec1 | +------+------+--------------------------+ | 1 | 2 | 3092.0000000000000000000 | +------+------+--------------------------+ 1 row in set (0.00 sec) With Quote - non integer ======================== select * from tmp.test1 where intx='1' and inty='4' and dec1= '3092.1'; +------+------+--------------------------+ | intx | inty | dec1 | +------+------+--------------------------+ | 1 | 4 | 3092.1000000000000000000 | +------+------+--------------------------+ 1 row in set (0.00 sec) With Quote - integer ======================== select * from tmp.test1 where intx='1' and inty='5' and dec1= '123'; +------+------+-------------------------+ | intx | inty | dec1 | +------+------+-------------------------+ | 1 | 5 | 123.0000000000000000000 | +------+------+-------------------------+ 1 row in set (0.00 sec) Suggested fix: The workaround of not quoting the decimal section is not an option here, as these queries come directly from Oracle, which correctly interprets the quoted decimals, and we have to have the same, sensible, behavior.