CODEWITHSUNDEEP
mysqlselect
Joe
Joe

Reputation: 21

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?

Upvotes: 0

Views: 439

Answers (5)

a1ex07
a1ex07

Reputation: 37382

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

Upvotes: 1

Dion
Dion

Reputation: 3345

I guess your fields user_email and user_pass are varchar fields. So maybe change 0 to '' (two single quotes)

Upvotes: 1

Pheonix
Pheonix

Reputation: 6052

Since user_email and user_pass are going to be string types, i think this should do :

SELECT * FROM user WHERE user_email = '0' AND user_pass = '0'

Upvotes: 0

rcplusplus
rcplusplus

Reputation: 2877

I don't think the parenthesis are required around user. Also, try putting quotes around both zeroes, that may be causing the problem.

Upvotes: 0

Kai Qing
Kai Qing

Reputation: 18843

I don't think I have ever seen someone declare a table name using parenthesis before. Have you done this:

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

Upvotes: 0

Related Questions