title: Chained comparisons in MySQL get unexpected result
body: <p>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.</p>
  
  <p>This is a simplified version of the problematic query:</p>
  
  <pre><code>SELECT id FROM users WHERE id = id = 2;
  </code></pre>
  
  <p>The error happened because <code>id =</code> is repeated, removing one of the <code>id =</code> 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.</p>
  
  <p>I tested a similar query on SQL Server, and got the error message <code>Incorrect syntax near '='</code> (something similar to what I would have expected from MySQL).</p>
  
  <p>Originally, I thought that MySQL was making a <strong>comparison between the first two fields</strong> and the last one was the result of the comparison (with 0 for false, and 1 for true). Something like this:</p>
  
  <pre><code>SELECT id FROM users WHERE (id = id) = 2;
  </code></pre>
  
  <p>But then I ran some queries that (kind of) contradicted that. Let me elaborate a bit more. </p>
  
  <p>Imagine this table (called "users"):</p>
  
  <pre><code>id  | username
  ----|----------------
  0   | anonymous
  1   | root
  2   | john
  3   | doe
  </code></pre>
  
  <p>If I do <code>SELECT * FROM users WHERE id = id = 1</code>, I get all 4 users. And with <code>SELECT * FROM users WHERE id = id = 0</code>, I don't get any. That seems to confirm the comparison theory. But, things get confusing if I do something like this:</p>
  
  <pre><code>SELECT * FROM users WHERE id = username = 1;
  SELECT * FROM users WHERE id = username = 0;
  </code></pre>
  
  <p>None of the records have the same id as username (they are not even the same type: <code>int(11)</code> and <code>varchar(25)</code> 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 <code>id</code> 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).</p>
  
  <p>I guess it has to do with <strong>MySQL turning strings/varchars into 0 when comparing with numbers</strong>. But why does it permit chained comparisons in the same clause? What order does it follows when comparing them?</p>
  
  <p>Things get <em>funny</em> when queries like this are valid, and actually return (<em>unexpected?</em>) values. For example:</p>
  
  <pre><code>SELECT * FROM users WHERE id = id = id = username = username = id = username = 1;
  </code></pre>
  
  <p>returns the records with id 2 and 3, but no the ones with id 0 and 1. Why is that?</p>
  
  <hr>
  
  <p>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?</p>
  
  <ul>
  <li>A SQLFiddle with the database and the last query: <a href="http://sqlfiddle.com/#!9/4f2ab/4" rel="noreferrer">http://sqlfiddle.com/#!9/4f2ab/4</a></li>
  </ul>