| Bug #40067 | Query on decimal column returns no value | ||
|---|---|---|---|
| Submitted: | 15 Oct 2008 20:02 | Modified: | 16 Oct 2008 6:33 |
| Reporter: | Jens Schanz | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.0.51a | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Oct 2008 21:33]
MySQL Verification Team
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead. Thank you for your interest in MySQL. Please see: http://bugs.mysql.com/bug.php?id=23260
[16 Oct 2008 5:47]
Jens Schanz
Bug reopened. Have a look at http://bugs.mysql.com/bug.php?id=23260 Bug should be fixed in 5.0 and 5.1 tree since 5.0.32 and 5.1.15. I could reproduce that behaviour in 5.0.45 and 5.0.51a. I've done further tests with that. Very dangerous are updates on that column. Not only the specified record will be updated or delete, also all will be touched.
[16 Oct 2008 6:33]
Sveta Smirnova
Thank you for the feedback. Bug #23260 haven't been fixed properly yet. You can see this is still "Verified" and not "Closed". Reason of comparison problem is same for 2 bugs: treating '99999999999999999' in where clause as string. For your case there is workaround - explicit casting: mysql> SELECT * FROM temp WHERE id = cast('99999999999999999' as decimal(19,0)); +-------------------+ | id | +-------------------+ | 99999999999999999 | +-------------------+ 1 row in set (0.00 sec)
Description: Look at "How to repeat". I think this explains it better ... How to repeat: 1. Create the following table CREATE TABLE `temp` ( `id` decimal(19,0) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2. Insert the value "99999999999999999" in column "id" INSERT INTO temp VALUES ('99999999999999999'); 3. Now check the table with a simple query SELECT * FROM temp; +-------------------+ | id | +-------------------+ | 99999999999999999 | +-------------------+ 4. Now query exactly that record with a where clause mysql> SELECT * FROM temp WHERE id = '99999999999999999'; Empty set (0.00 sec) Huh? Strange ... 5. Do a query with like and placeholder SELECT * FROM temp WHERE id like '99999999999999999%'; +-------------------+ | id | +-------------------+ | 99999999999999999 | +-------------------+ 1 row in set (0.00 sec) Works as expected ... 6. If you insert a value with 16 digits instead of 17. It works as expected, too. INSERT INTO temp VALUES ('9999999999999999'); 7. Now do a query on that number ... SELECT * FROM temp WHERE id = '9999999999999999'; +------------------+ | id | +------------------+ | 9999999999999999 | +------------------+ Voila ...