Type safety in WHERE clauses

Question

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?

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!)