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.
The table looks like
| Id | Num | |----|-----| | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 |
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
| ConsecutiveNums | |-----------------| | 1 |
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
| 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.