3

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...

Ntaxa
  • 31
  • 2

1 Answers1

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.

Wrikken
  • 69,272
  • 8
  • 97
  • 136
  • thanks, in my case i CAN'T avoid HAVING. i need to break row fetching at specific condition (here this condition is simplified to `id`=3). So, i need FIRST query, but found unexpected behavior. – Ntaxa Dec 11 '12 at 19:38
  • 1
    ... well, this is how it works, the manual does warn you: _"As a general rule, you should never assign a value to a user variable and read the value within the same statement."_ ... and more alarmingly for you: _"The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server"_ If you could elaborate on the actual conditions / query that you are using that `HAVING` on maybe we can figure something out. – Wrikken Dec 11 '12 at 19:41
  • Thanks. So, using same variable in condition (HAVING or WHERE) and assignment new value AT ANY place (we can change in HAVING closure) is bad idea? So there is no proper methods to "break" rows fetching on some condition? – Ntaxa Dec 11 '12 at 20:04
  • Pretty much yeah. Usualy though, there _is_ a way to rewrite it to (often even variable-less) `WHERE`... But without your actual query & data-structure, hard to tell. Your example on the end is just `LIMIT 10, some_very_large_number`, I assume your actual query is more complicated.. – Wrikken Dec 11 '12 at 20:12
  • i need to select rows with biggest `id` limited by SUM of `size` field `SET @enough=0; SELECT id, (@enough:=@enough+size) as ff FROM `ometable HAVING @enough<1000 ORDER BY id DESC` – Ntaxa Dec 11 '12 at 20:16
  • `SELECT a.id, SUM(b.size) FROM ometable a JOIN ometable b ON b.id >= a.id ORDER BY a.id DESC HAVING SUM(b.size) < 1000` OTOH.... – Wrikken Dec 11 '12 at 20:34
  • i think solution with JOIN is much slower. I thought exist some method to stop fetching rows. Something like `limit` only based on custom condition not on rows count – Ntaxa Dec 11 '12 at 20:44
  • BTW: it might just be faster to discard the rest of the data in your application rather then in MySQL .... – Wrikken Dec 11 '12 at 20:44
  • OK, this is query where all variables related things are concentrated only in WHERE condition. It mix setting and checking variable value, but in one place, not in column description and HAVING `SELECT id FROM sometable WHERE @foundnow=0 AND IF(id=5,@foundnow:=1,1) ORDER BY id DESC` – Ntaxa Dec 11 '12 at 21:25