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 

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