User defined variables does not work on where clause

Question

SET @a:=0

The below query works

SELECT *,@a:=@a+1 FROM article WHERE @a<=3

This query returns 3 rows But when the query contains LEFT JOIN it does not work, returns all rows.

SELECT a.*,@a:=@a+1 FROM article a
LEFT JOIN comments c ON c.aid=a.id
WHERE @a<=3

I don't understand why this query returns all rows

possible duplicate of MySQL user-defined variable in WHERE clause — Jordan Running
– Jordan Running, Commented Jul 23, 2015 at 16:46

Uueerdo · Accepted Answer · 2015-07-23 16:57:15Z

1

LIMIT 3 would be a much clearer expression of what (it looks like) you are trying to do. That said, I have been known to borderline abuse session variables, and even I tend to balk at seeing them in WHERE clauses; it is generally inadvisable to cross clauses with them.

....though this could work for you:

SELECT a.*,@a:=@a+1 AS theAValue
FROM article a
LEFT JOIN comments c ON c.aid=a.id
HAVING theAValue <=3
;

HAVING is always processed after all the result fields have been calculated.

answered Jul 23, 2015 at 16:57

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3311313 Over a year ago

i can not use limit cause i use it for pagination for articles

Uueerdo Over a year ago

The LIMIT [offset], [# rows] form of LIMIT is often used for that.

user3311313 Over a year ago

and i can achieve this with other ways i am just curious why this is not working

Uueerdo Over a year ago

My guess would be that in the first case, the conditions in the WHERE were checked after the results were retrieved, and in the second they were evaluated after the JOIN but before the result fields were calculated. One of the problems many people have with session variables is because the order of evaluation in MySQL can be fluid depending on context. (One way to verify would be to initialize @a to 4 and see if you get no rows in the second query.)

Collectives™ on Stack Overflow

User defined variables does not work on where clause

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related