filter SELECTed rows by HAVING by variables value changed in column description

Question

Can anyone explain the differences between following two requests:

SET @foundnow=0;
SELECT id, (@foundnow:=IF(`id`=3,1,0)) as ff
FROM `sometable` 
HAVING @foundnow=0
                                  result is
id  ff
1   0
2   0
3   1

and

SET @foundnow=0;
SELECT id, (@foundnow:=IF(`id`=3,1,0)) as ff
FROM `sometable` 
HAVING ff=0
                                  result is
id  ff
1   0
2   0
4   0
5   0
...

why first gives all rows up to id=3 (including), and second - all rows EXCEPT with id=3?

I guess related to this question is "unexpected" behavior of following request

SET @tot=0;
SELECT @tot:=@tot+1 as `ff`
FROM `anytable`
HAVING (`ff`>10)

which gives rows with ff=12,14,16,18...

Answer 1

@foundnow is NOT a per-row variable. It lives on. Creating a column ff makes it a per-row value. Also, from the documentation:

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is `assigned a value in the select expression list does not work as expected:

Which means that your first example goes like:

• 1: having needs @foundrow, it's 0, so send, set @foundrow to new value (0)
• 2: having needs @foundrow, it's 0, so send, set @foundrow to new value (0)
• 3: having needs @foundrow, it's 0, so send, set @foundrow to new value (1)
• 3: having needs @foundrow, it's 1, do not send
• 4: having needs @foundrow, it's 1, do not send
• 5: having needs @foundrow, it's 1, do not send
• etc...

And your second goes like:

• 1: having needs ff, perform select, set @foundrow, set ff (=no longer @foundrow) = send
• 2: having needs ff, perform select, set @foundrow, set ff (=no longer @foundrow) = send
• 3: having needs ff, perform select, set @foundrow, set ff (=no longer @foundrow) = do not send
• 4: having needs ff, perform select, set @foundrow, set ff (=no longer @foundrow) = send

So, the first example does not evaluate the expression at all if @foundrow!=0, because @foundrow is known and MySQL does not care what's in that select expression. The second one refers to a column in the resultset, and thus has to know that result, and performs the select.

Also, avoid HAVING clauses if you can if you can use WHERE's they are very much preferred.