| Bug #51705 | Bad implicit CAST in WHERE clause between different types | ||
|---|---|---|---|
| Submitted: | 3 Mar 2010 23:12 | Modified: | 4 Mar 2010 4:22 |
| Reporter: | Ervin Hegedus | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.0.51a-21 (Debian) | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | where clause implicit cast | ||
[4 Mar 2010 4:22]
Valeriy Kravchuk
This is explained in the manual, http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html. In your case the following statement applies: "In all other cases, the arguments are compared as floating-point (real) numbers." If you need something else use explicit CAST(). And yes, all RDBMSes are different, and MySQL differs from PostgreSQL in this case.
Description: Hello, here is the table definition: [code]CREATE TABLE `users` ( `userid` int(10) unsigned NOT NULL auto_increment, `login` varchar(128) NOT NULL, ...[/code] and there are about 3000 records. I type: [code]mysql> SELECT * FROM users WHERE login = 0;[/code] and I get almost all records from table: [code]2982 rows in set (0.02 sec)[/code] If I type: [code]mysql> SELECT login FROM users WHERE login != 0;[/code] I get: [code]+--------------------+ | login | +--------------------+ | 3duser1@gmail.com | | 3duser2@yahoo.com | | 5user21@gmail.com | +--------------------+ [/code] I really don't understan why MySQL compares two different types, but I think if there will be equals, it's a big problem. On same schema in PG I get this answer when I give same query: [code]ERROR: operator does not exist: character varying = integer LINE 1: select * from tuser where login = 0; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.[/code] Could anybody explain this problem? How to repeat: It doesn't matter how I try, this bug comes always in this query.