Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've spent the most part of yesterday tracking down a strange bug in our PHP/MySQL application and the culprit seems to be a poor query. We have a table like

+--------------------+----------------------+------+-----+---------+-------+
| Field              | Type                 | Null | Key | Default | Extra |
+--------------------+----------------------+------+-----+---------+-------+
| foo_id             | smallint(5) unsigned | NO   | PRI | NULL    |       | 
| bar_id             | smallint(5) unsigned | NO   | PRI | NULL    |       | 
| foo_external_id    | varchar(20)          | NO   |     | NULL    |       | 
| ...                | ...                  | ...  | ... | ....    | ...   | 
+--------------------+----------------------+------+-----+---------+-------+

Running this query

select foo_id, foo_external_id
from   foo_bar
where  foo_external_id = 14
and    bar_id = 5

returns

+-----------+----------------------+
| foo_id    | foo_external_id      |
+-----------+----------------------+
|      4058 | 14df729146edd353000f | 
|      9150 | 14                   | 
+-----------+----------------------+

instead of just

+-----------+----------------------+
| foo_id    | foo_external_id      |
+-----------+----------------------+
|      9150 | 14                   | 
+-----------+----------------------+

Of course the problem is that we are missing quotes around that 14 literal up there, so my question is are WHERE conditions supposed to do type-checks or is this another of the MySQL gotchas we all love?

share|improve this question
3  
I think it's one of those annoying MySQL gotchas. (My "favorite" is the behaviour of WHERE 1) –  a_horse_with_no_name Jan 17 '12 at 9:58
    
Using statements with ? arguments would solve this problem for you along with making you less susceptible to sql injection. –  Aaron Brown Feb 18 '12 at 2:02
add comment

1 Answer 1

According to the SQL Standard, numeric values may only be compared with numeric values e.g. an INTEGER value can be compared with a NUMERIC value by coercing both to NUMERIC values.

It sounds like MySQL is not compliant with Standards in this regard. To be fair, most SQL product exhibit similar implicit type coercions and it is encumbent on users to avoid them (in contrast to a strongly type 3GL whose users might expect a compile error!)

share|improve this answer
    
The statement fails with an error message in Oracle and PostgreSQL. –  a_horse_with_no_name Jan 17 '12 at 11:09
    
@a_horse_with_no_name: do you mean the OP's? i.e. evaluating '14df729146edd353000f' = 14? You can add SQL Server to your list. I suspect the mySQL behaviour of evaluating the above to true is based on Microsoft VBA 's VAL() function. –  onedaywhen Jan 17 '12 at 13:51
    
FYI, SQL Server would bomb on this because of strict rules around data type precedence. I've found MySQL to be quite random about the conversion processes –  gbn Jan 17 '12 at 14:02
    
@gbn: I tried it with SQL Server and a comparison varchar_column = 1 worked. –  a_horse_with_no_name Jan 17 '12 at 15:38
1  
@gbn: that is at least not expected by me. Oracle and PostgreSQL will not allow where varchar_column = 1 –  a_horse_with_no_name Jan 17 '12 at 22:29
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.