Incorrect SQL boolean expression for multiple aggregate filters

[Beuc]

See the test app attached (with test case).

The query:

        inactive_books_qs = Book.objects \
            .annotate(last_review_date=Max('review__date')) \
            .annotate(last_publication_date=Max('publication__date')) \
            .filter(last_publication_date__lt=tree_months_ago) \
            .filter(Q(last_review_date__lt=tree_months_ago)|Q(last_review_date__isnull=True)) \
            .only('id')

The resulting SQL:

SELECT [...] HAVING (MAX("aggregatebug_publication"."date") < 2010-01-21  AND MAX("aggregatebug_review"."date") < 2010-01-21  AND MAX("aggregatebug_review"."date") IS NULL)

The expected SQL:

SELECT [...] HAVING MAX("aggregatebug_publication"."date") < 2010-01-21  AND (MAX("aggregatebug_review"."date") < 2010-01-21  OR MAX("aggregatebug_review"."date") IS NULL)

Issues:

• parenthesis not closed after MAX("aggregatebug_publication"."date") < 2010-01-21
• AND instead of OR!!! MAX("aggregatebug_review"."date") < 2010-01-21 AND MAX("aggregatebug_review"."date") IS NULL
• no parenthesis around the test above

There may be multiple bugs. When trying to fix things around:

Index: models/sql/query.py
===================================================================
--- models/sql/query.py	(révision 13022)
+++ models/sql/query.py	(copie de travail)
@@ -1013,7 +1013,7 @@
                 entry.add((aggregate, lookup_type, value), AND)
                 if negate:
                     entry.negate()
-                self.having.add(entry, AND)
+                self.having.add(entry, connector)
                 return
 
         opts = self.get_meta()

(this is by no way a suggested patch)

well in that case I got s/AND/OR/ correctly, but still the wrong parenthesis:

HAVING ((MAX("aggregatebug_publication"."date") < 2010-01-21  AND MAX("aggregatebug_review"."date") < 2010-01-21 ) OR MAX("aggregatebug_review"."date") IS NULL)

(should be around OR, not around AND)

[Beuc]

testcase for aggregation bug

[Alex Gaynor]

This is a dupe of #11293.

[Beuc]

Damn I missed the "ORM aggregation" component (looked in "Database layer") :(

[Jacob]

Milestone 1.2 deleted