CODEWITHSUNDEEP
mysqlsql
stack
stack

Reputation: 10228

Why query returns empty result set without LIMIT clause?

I have this mysql function:

BEGIN 
  DECLARE top_tags VARCHAR(100) charset utf8; 
  SELECT   substring_index(group_concat(x.NAME order BY x.tag_score DESC separator ','), ',', 5)
  INTO top_tags 
  FROM (SELECT t.NAME, Sum(r.score) AS tag_score 
        FROM reputations r 
          JOIN qanda_tags qt ON qt.qanda_id = r.question_id 
          JOIN tags t ON t.id = qt.tag_id 
        WHERE r.owner_id = 1 
          AND r.date_time > CASE 'all' 
            WHEN 'WEEK' THEN unix_timestamp(date_sub(Now(), interval 1 week))
            WHEN 'MONTH' THEN unix_timestamp(date_sub(now(), interval 1 month))
            WHEN 'YEAR' THEN unix_timestamp(date_sub(now(), interval 1 year))
          ELSE 1 
          END 
        group BY t.NAME ) x; 
  return top_tags; 
end

It returns an empty result set:

enter image description here

And when I add limit 60 clause right after group BY t.NAME, it returns the expected result:

enter image description here

Why really?

Note: limit 61 or more causes no result either. limit 60 or less has the result.

Upvotes: 0

Views: 171

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I'm not sure, but would it be more efficient to do the filtering before the group_concat()?

  SELECT group_concat(x.NAME order BY x.tag_score DESC separator ',')
  INTO top_tags 
  FROM (SELECT t.NAME, Sum(r.score) AS tag_score 
        FROM reputations r JOIN
             qanda_tags qt
             ON qt.qanda_id = r.question_id JOIN
             tags t
             ON t.id = qt.tag_id 
        WHERE r.owner_id = 1  AND
              r.date_time > (CASE 'all' 
                               WHEN 'WEEK' THEN unix_timestamp(date_sub(Now(), interval 1 week))
                               WHEN 'MONTH' THEN unix_timestamp(date_sub(now(), interval 1 month))
                               WHEN 'YEAR' THEN unix_timestamp(date_sub(now(), interval 1 year))
                               ELSE 1 
                            END)
        group BY t.NAME
        order by tag_score desc
        limit 5
       ) x;

Upvotes: 1

Related Questions