June_Stephanie June_Stephanie - 4 years ago 90
MySQL Question

Why MYSQL returns different result for a+2=b+1=c vs a+1 = b and b+1=c?

I write a SQL query to find all numbers that appear at least three times consecutively:

| Id | Num|

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 1 |

| 6 | 2 |

| 7 | 2 |

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

My original query returns 1 and 2:

SELECT l1.Num
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id + 2 = l2.Id + 1 = l3.Id
AND l1.Num = l2.Num = l3.Num;


+------------------------------------------------------------------------

Then I change my query to this (basically I change the a+2 = b+1 = c to a + 1 = b and b+1 = c), it returns the correct answer:

SELECT l1.Num
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id + 1 = l2.Id AND l2.Id + 1 = l3.Id
AND l1.Num = l2.Num AND l1.Num= l3.Num;


+------------------------------------------------------------------------

It drives me crazy, I cannot figure our why. Could anyone kindly explain this to me? Thanks in advance!!!!

Answer Source

Well, let's have a look at this query: WHERE l1.Id + 2 = l2.Id + 1 = l3.Id In MySQL as in most programming languages, the = is just a binary operator, which means it takes a left and a right side and returns true if they are the same and false if otherwise.

In your query, you're first comparing l1.Id + 1 with l2.Id resulting in a truth value. And then, you compare the truth value with l3.Id. (The truth value gets automatically casted to 0/1). This leads to a different result.

tl;dr: = in MySQL is not like = often used in mathematical notation.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download