SQLite Forum

bug report : adding constant to GROUP BY leads to different output
> "silly" cases may be more likely to find bugs because humans don't usually write like this

Precisely.  And silly bugs should still be fixed, but they have a lower
priority because they do not impact real-world applications.

My point is that even though your original test case is "silly" (in the sense
that it would never come up in a real-world application) the same malfunction
can be provoked using a different test case that *might* appear in a real-world
application.  This increases the severity and priority of the bug.

I hasten to point out, though, that even though this is a bug that *might*
appear in a real-world application, it has apparently been in the code for
many years and nobody has ever run across it, despite SQLite being used in
literally millions of applications.  So, even though it needs to be fixed,
it is not an emergency.  Even though it would be reasonable for an application
to contain SQL queries like this, apparently no application does so.

Status:  I have a patch that fixes your original problem.  But I am continuing
to investigate the logic of the optimization that attempts to avoid unnecessary
sorts (which is the source of this problem) to see if there are yet other
subtle design errors.  I'll say more about this, because the time I spend
writing replies on the Forum is time that is *not* spent fixing and improving
the actual code.  Over and out.