Chained comparisons in MySQL get unexpected result

Question

I had an issue with a MySQL statement that was returning null all the time, and although I was able to figure it out, the cause left me a bit puzzled.

This is a simplified version of the problematic query:

SELECT id FROM users WHERE id = id = 2;

The error happened because id = is repeated, removing one of the id = solved the problem (as there is a user with the id 2). But what has me a bit confused is how it "fails silently", returning 0 rows, instead of giving an error.

I tested a similar query on SQL Server, and got the error message Incorrect syntax near '=' (something similar to what I would have expected from MySQL).

Originally, I thought that MySQL was making a comparison between the first two fields and the last one was the result of the comparison (with 0 for false, and 1 for true). Something like this:

SELECT id FROM users WHERE (id = id) = 2;

But then I ran some queries that (kind of) contradicted that. Let me elaborate a bit more.

Imagine this table (called "users"):

id  | username
----|----------------
0   | anonymous
1   | root
2   | john
3   | doe

If I do SELECT * FROM users WHERE id = id = 1, I get all 4 users. And with SELECT * FROM users WHERE id = id = 0, I don't get any. That seems to confirm the comparison theory. But, things get confusing if I do something like this:

SELECT * FROM users WHERE id = username = 1;
SELECT * FROM users WHERE id = username = 0;

None of the records have the same id as username (they are not even the same type: int(11) and varchar(25) respectively), but with the first one, I get one result back: "anonymous". And with the second one, I get all the users but "anonymous". Why does that happen? I see that it has something to do with the id being 0, because if I replace "anonymous'" id from 0 to 4, then I don't get it anymore with the first query (and it shows up in the second).

I guess it has to do with MySQL turning strings/varchars into 0 when comparing with numbers. But why does it permit chained comparisons in the same clause? What order does it follows when comparing them?

Things get funny when queries like this are valid, and actually return (unexpected?) values. For example:

SELECT * FROM users WHERE id = id = id = username = username = id = username = 1;

returns the records with id 2 and 3, but no the ones with id 0 and 1. Why is that?

---

tl;dr version: Why do queries with chained comparison operation work? What is the order followed in the comparisons? Is this a bug or the expected behavior?

• A SQLFiddle with the database and the last query: http://sqlfiddle.com/#!9/4f2ab/4

Answer 1

Prerequisites to understand how chained comparisons in MySQL work

Understanding SELECT-WHERE query

This is pretty obvious, but I'm making this answer for every programmer, even if you don't really know how SQL works. A SELECT-WHERE query, basically:

1. Loops every row affected by the query and
2. evaluates the where_condition with the values of that specific row.
3. If the where_condition results in TRUE, it will be listed.

A pseudo-code could be:

for every row:
    current values = row values # for example: username = 'anonymous'
    if (where_condition = TRUE)
        selected_rows.append(this row)

Almost every string equals 0 (or FALSE)

Unless the string is a number ('1', '423', '-42') or the string starts with a number, every other string equals 0 (or FALSE). The "numeric strings" equals its equivalent number and the "starting numeric string" equals its initial number. Providing some examples: mysql> SELECT 'a' = 0;

+---------+
| 'a' = 0 |
+---------+
|       1 |
+---------+
1 row in set, 1 warning (0.00 sec)

.

mysql> SELECT 'john' = 0;
+------------+
| 'john' = 0 |
+------------+
|          1 |
+------------+
1 row in set, 1 warning (0.00 sec)

.

mysql> SELECT '123' = 123;
+-------------+
| '123' = 123 |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

.

mysql> SELECT '12a5' = 12;
+-------------+
| '12a5' = 12 |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

WHERE_condition is resolved like Mathematical Operations

Chained comparisons are resolved one by one, with the only preference of parenthesis first, starting from the left until a TRUE or FALSE is what remains.

So, for example 1 = 1 = 0 = 0 will be traced as follows:

1 = 1 = 0 = 0
 [1]  = 0 = 0
     [0]  = 0
         [1]
Final result: 1 -> TRUE

How it works

I will trace the last query, which I consider the most complicated and yet, the most beautiful to explain:

SELECT * FROM users WHERE id = id = id = username = username = id = username = 1;

First of all, I will show every where_condition with every row variables:

id = id = id = username = username = id = username = 1

0 = 0 = 0 = 'anonymous' = 'anonymous' = 0 = 'anonymous' = 1
1 = 1 = 1 = 'root'      = 'root'      = 1 = 'root'      = 1
2 = 2 = 2 = 'john'      = 'john'      = 2 = 'john'      = 1
3 = 3 = 3 = 'doe'       = 'doe'       = 3 = 'doe'       = 1

And now I will trace every row:

0 = 0 = 0 = 'anonymous' = 'anonymous' = 0 = 'anonymous' = 1
 [1]  = 0 = 'anonymous' = 'anonymous' = 0 = 'anonymous' = 1
     [0]  = 'anonymous' = 'anonymous' = 0 = 'anonymous' = 1
         [1]            = 'anonymous' = 0 = 'anonymous' = 1
                       [0]            = 0 = 'anonymous' = 1
                                     [1]  = 'anonymous' = 1
                                         [0]            = 1
                                                       [0] -> no match


1 = 1 = 1 = 'root'      = 'root'      = 1 = 'root'      = 1
 [1]  = 1 = 'root'      = 'root'      = 1 = 'root'      = 1
     [1]  = 'root'      = 'root'      = 1 = 'root'      = 1
         [0]            = 'root'      = 1 = 'root'      = 1
                       [1]            = 1 = 'root'      = 1
                                     [1]  = 'root'      = 1
                                         [0]            = 1
                                                       [0] -> no match


2 = 2 = 2 = 'john'      = 'john'      = 2 = 'john'      = 1
 [1]  = 2 = 'john'      = 'john'      = 2 = 'john'      = 1
     [0]  = 'john'      = 'john'      = 2 = 'john'      = 1
         [1]            = 'john'      = 2 = 'john'      = 1
                       [0]            = 2 = 'john'      = 1
                                     [0]  = 'john'      = 1
                                         [1]            = 1
                                                       [1] -> match


3 = 3 = 3 = 'doe'       = 'doe'       = 3 = 'doe'       = 1
 [1]  = 3 = 'doe'       = 'doe'       = 3 = 'doe'       = 1
     [0]  = 'doe'       = 'doe'       = 3 = 'doe'       = 1
         [1]            = 'doe'       = 3 = 'doe'       = 1
                       [0]            = 3 = 'doe'       = 1
                                     [0]  = 'doe'       = 1
                                         [1]            = 1
                                                       [1] -> match

The query shows the rows with id = 2 and id = 3 since they match the where_condition.

Answer 2

I do not know MYSQL at all but suspect that you are encountering a side effect of the fact that each predicate on a where clause must evaluate to true. E.g.

Select *
from table 
where 1 = 1

is in effect saying

Select *
from table 
where is_true(1 = 1)

I suggest you try other values than 1 and 0 and see what result you get.

Obviously (id=id) is true so in the use of 0 and 1 for false/true this is equal to 1

After some more experimenting Alvaro Montoro added to this that (int == int) = true; but for int > 1: (int == true) = false, and (int == false) = false.

Answer 3

This query:

SELECT *
FROM users
WHERE id = username = 1;

Would return true for "anonymous", 0 if it were parsed as:

SELECT *
FROM users
WHERE id = (username = 1);

id is 0, so it is saying "false = "false".

Note that these are not chained comparisons of any sort. MySQL is turning the comparisons into integers (0 and 1) and then applying =. So, 1 = 1 = 1 is true, but 0 = 0 = 0 is false. So, this is a weird combination of binary logic and equality comparisons.

My advice is simply not to use more than one comparison in an expression. Forget the fact that MySQL supports this -- it is not part of the standard and most databases do not behave this way.