Nicholas Flees Nicholas Flees - 1 month ago 15
SQL Question

MySQL Chained Inequality

MySQL doesn't complain when "chained inequality" syntax is used in a query's

WHERE
clause, but produces a result that is different from a query of a similar spirit, written with proper syntax. So what query is it actually running and what constraints are being applied? And why doesn't it produce an error?

A simplistic example: Suppose I have a table in a MySQL database a table called User with an attribute called
score
.

I can write a query of the following form with chained inequality syntax:

SELECT *
FROM User
WHERE 10 < score < 15;


and a query of the following form with proper syntax:

SELECT *
FROM User
WHERE score > 10 AND score < 15;


with different results.

To repeat the questions:


  1. What query is actually being run with the chained inequality? (Specifically, what are the constraints applied?)

  2. Why doesn't MySQL produce an error?


Answer

According to the documentation:

For operators that occur at the same precedence level within an expression, evaluation proceeds left to right, with the exception that assignments evaluate right to left.

MySQL will evaluate this as:

WHERE (10 < score) < 15

Because MySQL treats boolean values as numbers in a numeric context, the parenthesized part is turned into 0 or 1 (or NULL if score is NULL) for the subsequent comparison. So, the result is always true.

I believe this explains both why MySQL doesn't produce an error and why you are getting different results from what you expect.

You have various options using AND, but this would be typical:

WHERE score > 10 AND score < 15
Comments