CASE clause in WHERE

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.

Answer

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.