Why does (a < x < b) produce a result other than ((a < x) and (x < b))?

Question

List all fields of all products whose cost plus 30% is greater than 8 and less than 10.

That was my answer:

SELECT * FROM `produtos` WHERE (8 < (pcusto*1.3) < 10);

This is the feedback:

SELECT * FROM `produtos` WHERE 8 < (pcusto*1.3) AND (pcusto*1.3) < 10;

Although the mathematical reasoning is correct, Phpmyadmin identifies very different results. In the first case it returns more than 1000 records, already in the reply of the template it returns only 60.

What is the primordial difference between each line of code?

Answer 1

It turns out that SQL will run something different than you think with the syntax you used:

WHERE a < x < b

This expression could be written as (a < x) < b producing the same result, but leaving a little more explicit what happens. First the first part of the comparison will be evaluated, a < x, and the result of this expression will be compared to the last value.

If x is greater than a, the comparison a < x will return true, true, which shall be evaluated as whole 1, thus comparing whether 1 < b. In your case, b is worth 10 then 1 < b is true, thus returning all records that have pcusto * 1.3 greater than 8.

If x is less than a, the comparison a < x will return false, false, which will be evaluated as integer 0, thus comparing whether 0 < b. In your case, b is worth 10 then 1 < b is true, thus returning all records that have pcusto * 1.3 less than 8.

I mean, basically your comparison 8 < pcusto*1.3 < 10 will always be true, being returned in the query all records of the table.

When you separate into two independent comparisons, you will logically evaluate the result of both, obtaining the expected result.