Been a while since I reported on MySQL's stupidity, but I came across a fine new example yesterday.
Create a table with a varchar column.
create table foo (foo varchar(10));
Insert a data value which is two numbers separated by a pipe character (don't ask, just accept that this was the data format I found in my table).
insert into foo values ('111|1');
Now let's try to select some data.
mysql> select * from foo where foo = '111|1'; +-------+ | foo | +-------+ | 111|1 | +-------+ 1 row in set (0.00 sec)
Ok. That makes sense. That's expected behaviour.
mysql> select * from foo where foo = '111'; Empty set (0.00 sec)
That also makes sense, of course. The string isn't '111', so it doesn't match.
mysql> select * from foo where foo = 111; +-------+ | foo | +-------+ | 111|1 | +-------+ 1 row in set (0.00 sec)
Huh! I mean "What!?!".
There are at least two fundamentally stupid things going on here.
Firstly, MySQL is allowing me to match a string column against a number. When a user tries to match a value of one type against a column of another type, the only sensible action is to throw an error. The user is trying to do something completely wrong. Tell them that. Don't try and work something out.
Secondly, if you insist on trying to convert datatypes in order to force a match, then convert the user's data into the database column's datatype, not the other way round. The database column is a string. Convert the number to a string and try to match that string against the database (that would have returned no data). Instead MySQL is trying to convert the database value into a number to match the user's input. It looks like it's using something like Perl's string to number conversion so the string "111|1" is converted to the number 111 and therefore matches the user's input.
So you can actually get MySQL to match data which doesn't match at all. I wasted two hours on this yesterday.
I found this yesterday on a MySQL 4.x server. I've just tried it on a 5.0.67 server and the same bug is still there.
Oh, and setting the SQL mode to "traditional" doesn't seem to fix it either.
For instance...
perl -wle "print '111|1' == 111"
javascript:alert(parseInt("111|1"))
Granted, the perl example throws a warning, but it does evaluate that statement as true.
You may consider it a bug; others might consider it a feature. And if you were to change how "111|1" gets cast to a number, or return an error for comparing a varchar to a number, who knows what that would break/fix.
Incidentally, SQLite and PostgreSQL don't have this problem/feature.
Re:MySQL is just trying to cater to us scripting f
davorg on 2009-02-25T22:39:33
perl -wle "print '111|1' == 111"
Yes, and I mentioned that it was using a similar algorithm to Perl. But, of course, Perl users can choose which conversion is used by using either "eq" or "==". MySQL users don't appear to have a choice.
And if you were to change how "111|1" gets cast to a number, or return an error for comparing a varchar to a number, who knows what that would break/fix.
Of course. That's always a danger when you release buggy code - people can come to rely on the bugs. Maybe they should fix the bug, but include a "SUPPORTS_BUGGY_OLD_CODE" mode to the fixed release
:-) Re:MySQL is just trying to cater to us scripting f
autarch on 2009-02-25T23:05:09
The difference is that, in theory, MySQL is a database. Databases should be strict and cranky, because they're the authority for your important data.
I expect my scripting language to act cute and helpful, and my database to kick me in the ass whenever I get sloppy.
Since Mysql is "helpful" and converts every column value from string to a number, there's no chance in hell it'll use indexes to do the lookup.
The way you fix it in the query is by using quotes around the literal value.
But Mysql should do the proper thing and that is, IMnsHO, to convert the user data to the column type, and then do the lookup.