MySQL Query - the Clause `where (varchar)` returns items starting with a number only. Why?

Question

I have an interesting result and don't understand why.

SELECT * 
  FROM items im
 WHERE ItemNumber

ItemNumber is a varchar(50).

The results of this query is returning all items that the ItemNumber starts with a number. If the ItemNumber begins with a letter, it is excluded.

Does anyone have an explanation why the query interacts in this way?

Answer 1

This is MySQL's strategy to be clever and assuming that it knows what you mean (as opposed to do what you write).

Any expression that evaluates to non-zero is considered true. Those items that start with a letter cannot (implicitely) be converted to a number thus it's considered zero and therfor "false". I would think that items that have the (character) value '0' are also excluded.

The following statement for example will happily delete all rows in your table:

DELETE FROM foobar
WHERE 42;

You cannot turn this behaviour off. Not even in ANSI mode will MySQL throw a syntax expression.