Strange SELECT Behaviour in a simple query.

[berridgeab]

Bonjour!

 

I have come across some strange behaviour in MySQL when performing a simple select.

 

InnoDB Table table1 has one column called Qty. Qty is an datatype INT unsigned column. Therefore the only acceptable values for this column are numeric values and NULL.

 

The query I am performing is

 

SELECT * FROM table1 WHERE Qty = "2Apples"

 

So when I perform the query, I would naturally expect MySQL to return 0 results as the column cannot possibly contain any data that matches "2Apples" due to the alphanumeric searchstring.

 

But it does return results, specifically any value matching "2". It is like MySQL is automatically rewriting the my query to match the following

 

SELECT * FROM table1 WHERE Qty = "2"

 

Does MySQL automatically omit non-numeric characters on a searchstring targeting a column with an INT type? Is it performing some kind of casting on the searchstring due to the column datatype being INT?

 

I notice if I change the query slightly so that the text begins the searchstring, this behaviour disappears and I get 0 results, as intended.

 

SELECT * FROM table1 WHERE Qty = "Apples2"

 

Its not really a problem, just a peculiarity and wanted someone to explain to me why it does this. I have scoured the MySQL documentation but could not find anything related to this.

[PFMaBiSmAd]

Yes, it is doing what it is defined to do - http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html

 

See the last rule at that link (in all other cases, the arguments are compared as floating-point (real) numbers.) "2Apples", when parsed as a floating point number results in the value 2 (the first non-numerical character serves as a stop character.)

[berridgeab]

Thanks for your fast reply