A few months ago I got a strange email from one of my clients that contained two very simple looking select queries. The only difference between the two queries is that one included the distinct keyword and the other didn’t. The strange part is that the query that used distinct returned zero rows. I spent a few days narrowing down the clients data into a small test case then created a generic test case from that. I also traced the problem to the code that decides which index to use for a group by loose index scan which can be used to resolve queries using distinct.
The example can be found in the bug and in this sql file. My patch was a step in the right direction but not complete enough to solve all the issues. Since this isn’t a crashing but I was tempted to make this blog post into one of those sql quiz questions but decided to be nice instead. Feel free to use the sql file to fool your friends though.