Search code examples
mysqlselect

Query returns all rows when matching a value against 0

Never come across this before, just cannot understand the reasoning behind it.

I have a query:

SELECT * FROM (`user`) WHERE `user_email` = 0 AND `user_pass` = 0

This shows all rows, even though I would have read the query as 'Show me all records which have the integer 0 in both user_email and user_pass. Why is that?

Solution

  • You put integer 0, so it converts your varchar fields user_email and user_pass to int. Actually, if non-null string doesn't have leading number on it, it will be converted to 0. For example,

    SELECT 'aaaa' = 0; // returns 1 - true
SELECT '1aaa' =0; // 0 -false
SELECT '1aaa' =1 ;// 1 -true

    Correction. According to docs, they will be compared as float, not as integers.

    In all other cases, the arguments are compared as floating-point (real) numbers