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.

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.

9 People found this is helpful