Mysql 5.5 strange behavior with group by with limit based of INDEX vs INDEX FOR GROUP BY

Update

This bug has been fixed in 5.5.27, 5.6.7 and 5.7.0

I feel like I've found a bug in mysql, but haven't been able to track it down. Any idea what's going on here?

This was working just find in mysql 5.1, but then it never used the INDEX FOR GROUP BY.

```
mysql> select count(DISTINCT(IF(user_id = 0, ip, user_id))) from tm_tmp group by artist_id limit 11;
+-----------------------------------------------+
| count(DISTINCT(IF(user_id = 0, ip, user_id))) |
+-----------------------------------------------+
| 1 |
| 2 |
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 3 |
| 2 |
| 1 |
| 1 |
+-----------------------------------------------+
11 rows in set (0.00 sec)

mysql> select count(DISTINCT(IF(user_id = 0, ip, user_id))) from tm_tmp group by artist_id limit 12;
+-----------------------------------------------+
| count(DISTINCT(IF(user_id = 0, ip, user_id))) |
+-----------------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |

I would definitely submit this as a bug. There might even be two bugs here.

First, there was a change in MySQL 5.5 that allowed COUNT(DISTINCT) to use a loose index scan (indicated by the using index for group by in the explain). Documented change is at the bottom of this doc page. It looks like the structure of your query should not be eligible for a loose index:

Loose index scan is not applicable for the following queries:

SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;

Your use-case of wrapping the DISTINCT around an IF seems to be edge-case they could have missed.

Second, the output on the query that uses a loose index scan seems unexpected as well. I could see it being different because the loose index scan isn't retrieving all rows. But I would still expect it to be something other than 0 for all rows.

One thing I love about MySQL is when they open up their WorkLogs to the public. Here is the WorkLog on extending loose index scans to other aggregate functions (such as COUNT(DISTINCT) in your case).

StackExchange Database Administrators Q#13817, answer score: 4