MySQL HAVING Causes Extra Increment of Session Variable

Question

I'm writing a select statement to get the top 3 most recent items for a list of users. An example can be seen in this fiddle: http://sqlfiddle.com/#!9/cb25a4/12

The rows are getting ordered correctly using sessions variable which are set to 0 in a dummy FROM statement so as to not mess up future session requests. However, when I want to limit rows in the outer SELECT by row_num < 4, the increments I have in the SELECT clause are hit twice... or something.

Is the HAVING clause causing these variables to increments twice then? That's what it looks like. If so, so there any way I can get around this? Thanks.

Answer 1

Found a way to do this, though it feels like a hack and I hope somebody is able to find a different/more elegant way. The sessions variables are incremented once in WHERE and HAVING clauses, so I just added a conditional to see whether or not the column has been incremented yet. Now it's only getting incremented in the HAVING clause. Example of my hacky fix: http://sqlfiddle.com/#!9/cb25a4/35

Answer 2

Rearrange the subqueries; the nesting you have seems to be the problem:

SELECT *
 FROM 
 (
 SELECT main.*,
 @row_num := IF(@dummy1 = main.user_id,
 @row_num := @row_num + 1, 1) AS row_num,
 @dummy1 := main.user_id AS dummy1
 FROM listens AS main
 JOIN 
 (
 SELECT @row_num := 0, @dummy1 := 0
 ) AS init
 ORDER BY user_id, track_id 
 ) AS x
 WHERE row_num < 4
 ORDER BY user_id, created_at DESC 

• AlexOlsen: Looks great, but I believe that by putting the WHERE row_num < 4 and ORDER BY created_at in the outer query, we're not guaranteeing chronological ordering of rows in the subquery. Therefore we might be filtering out rows in the outer WHERE that chronologically should be included. Thoughts?
• Rick James: Your concern is probably correct. Still, I think I have pointed you in the right direction.

Answer 3

It seems to be a bug present only if HAVING clause is used.

If you add an arbitrary GROUP BY clause however, the problem disappears.

 SELECT main.*,
 @row_num := IF(@dummy1 = main.user_id, @row_num := @row_num + 1, 1) AS row_num,
 @dummy1 := main.user_id AS dummy1
 FROM
 (SELECT * FROM listens ORDER BY user_id, track_id) AS main, 
 (SELECT @row_num := 0, @dummy1 := 0) AS dummy
 -- added GROUP BY
 GROUP BY main.user_id, main.track_id, main.created_at
 HAVING row_num < 10 
 ORDER BY user_id, created_at DESC