Duplicated group_by condition

[ziangsong]

So I want to implement this SQL:

selelct id, count(*), max(insert_date) as m_d
from Book
group by id

Here is the Django ORM query:

q = Book.objects.values('id').annotate(c = Count('id'), m_d = Max('insert_date')).order_by()

However, the translated sql is like this:

selelct id, count(*), max(insert_date) as m_d
from Book
group by id, id <-here is another id! It messed up things!

Btw, the id in Book is a foreign key to another table and I am using MySql database.

[Matthias Kestenholz]

Some quick testing shows that the issue even exists when the Count('id') is left away:

In [4]: Page.objects.values('parent').annotate(m_d=Max('title')).order_by()
Out[4]: [{'parent': None, 'm_d': u'Test page 2'}, {'parent': 1, 'm_d': u'Maybe a subpage'}, {'parent': 2, 'm_d': u'Test page 2 wants a subpage too'}]

In [5]: connection.queries
Out[5]: 
[{'sql': u'SELECT "page_page"."parent_id", MAX("page_page"."title") AS "m_d" FROM "page_page" GROUP BY "page_page"."parent_id", "page_page"."parent_id" LIMIT 21',
  'time': '0.001'}]

Inside django.db.models.sql.compiler.SQLCompiler.get_grouping, both group_by and self.query.select contain ('page_page', 'parent_id') which causes the duplicated GROUP BY clause.

[Matthias Kestenholz]

The attached patch fixes this problem (hopefully) by not letting group_by clause duplicates through. I don't think duplicated entries in GROUP BY ever make sense.

Test included.

[ziangsong]

Thank you! I really appreciate it!

[Matthias Kestenholz]

Could you promote this issue to "Ready for checkin" if it fixes the problem for you? Thanks!

[Luke Plant]

In [16180]:

Fixed #15709 - Duplicated group_by condition

Thanks to ziangsong for report, and to mk for the patch