I'm doing a leetcode question. 2 similar answers are worked out. But I have no idea why one is wrong and the other is right. The following is the question link. The goal is writing a SQL query to find all numbers that appear at least three times consecutively.
https://leetcode.com/problems/consecutive-numbers/
The table looks like
| Id | Num |
|----|-----|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
Right version:
select distinct Num as ConsecutiveNums
from Logs, (select @prev := -1, @count := 0) as Init
where (@count := case when @prev = (@prev := Num) then @count + 1 else 1 end) >= 3
The output:
| ConsecutiveNums |
|-----------------|
| 1 |
Wrong version:
select distinct Num as ConsecutiveNums
from Logs, (select @prev := -1, @count := 0) as Init
where (case when @prev = (@prev := Num) then @count := @count + 1 else @count := 1 end) >= 3
The output:
| ConsecutiveNums |
|-----------------|
| 1 |
| 2 |
The only difference is @count := is moved into case end.
It seems that else part causes some error, which cannot be explained by my knowledge.
The second version of the code doesn't work for a rather obscure reason. This part:
else @count := 1
... has an expression that has no dynamic component. MySql optimises its execution plan in a way that it does not perform that assignment a second time, but just returns the current value of @count. This is because MySql variables are really not designed to be modified during the execution of a query. When you still decide to use that side-effect, you must be aware of such "optimisation" behaviour.
You can try to force MySql into making the assignment every time. This can be done by including a variable or field reference in the assigned expression. For instance you could use := if(@count, 1, 1) instead of just := 1. The result is the same (always 1), but now it will be re-evaluated and assigned each time it is encountered:
where (case when @prev = (@prev := Num)
then @count := @count + 1
else @count := if(@count, 1, 1)
end) >= 3
You could think of other alternative expressions, like := 1+Num*0, as long as there is a reference to some variable/field, it will solve the issue.
Looking at the first version of the query you provided, you'll see that there the expression assigned to @count already has such dynamic content.
All in all, setting variables in a query is not advised, and future versions of MySql may no longer support it, as is stated in the Reference Manual:
Previous releases of MySQL made it possible to assign a value to a user variable in statements other than
SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.