id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
33655	Unnecessary column in a GROUP BY clause with QuerySet.exists()	Marc Perrin	Marc Perrin	"I've got a question about the interaction between exists() and group by.

For example something like: 

{{{
Manager.values('field').annotate(cnt=Count('id')).filter(cnt__gt=1).exists()
}}}

The corresp. query with PostgreSQL looks like this:

{{{
SELECT (1) AS ""a""
FROM ""app_model""
GROUP BY ""app_model"".""field"", (1)
HAVING COUNT(""app_model"".""id"") > 1
LIMIT 1
}}}

exists() (see [https://github.com/django/django/blob/470708f50d8c13a50770893b8d7181f5218bf3ac/django/db/models/sql/query.py#L563]) clears the SELECT clause and replaces it by (if I understand correctly) a hardcoded value 1 (as ""a""), along with a limit of 1, which makes sense to me.

But get_group_by() (see [https://github.com/django/django/blob/6b53114dd862ec97c282fdfdc83579cbd6d1560d/django/db/models/sql/compiler.py#L79]) pushes this hardcoded value to the GROUP BY clause and we end up with the query above.

Now, on PostgreSQL, that works, but to me it sounds like it works by luck/lucky robustness... and certainly the same query without the , (1) in the GROUP BY clause yields the same result.
The problem is that outside of PostgreSQL that GROUP BY clause can be downright invalid...
Note that if I alter exists() to use {'a': 2} instead of {'a': 1}, it does not work anymore (on PostgreSQL), because (2) in the SELECT clause means the hardcoded number 2 while (2) in the GROUP BY clause presumably (??) refers to the 2nd expr of the SELECT clause, but we only got one... 

My feeling is that get_group_by() should avoid adding extra/raw sql (or at least extra/raw pure values, if we can detect that...) to the group by expressions?
 
NB: the existing/old ticket that seems most related to my question would probably be: [https://code.djangoproject.com/ticket/24835] "	Cleanup/optimization	closed	Database layer (models, ORM)	4.0	Normal	fixed	exists group by	Simon Charette	Ready for checkin	1	0	0	0	0	0