Opened 7 weeks ago

Last modified 13 days ago

#29291 new Bug

Conditional expressions and ~Q queries

Reported by: Bo Marchman Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Bo Marchman)

I've run into an issue with conditional expressions using negated Q objects.

I would expect qs1 and qs2 to be equivalent in the following code:

class Application(models.Model):
    pass

class Score(models.Model):
    application = models.ForeignKey(Application, on_delete=models.PROTECT)
    reviewed = models.BooleanField()

a1 = Application.objects.create()
Score.objects.create(reviewed=False, application=a1)
Score.objects.create(reviewed=True, application=a1)

a2 = Application.objects.create()

qs1 = Application.objects.annotate(
    needs_review=Case(
        When(~Q(score__reviewed=True), then=V(True)),
        default=V(False),
        output_field=BooleanField()
    )
).filter(needs_review=True)

qs2 = Application.objects.filter(
    ~Q(score__reviewed=True)
)

print(qs1) # <QuerySet [<Application: Application object (1)>, <Application: Application object (2)>]>
print(qs2) # <QuerySet [<Application: Application object (2)>]>

assert set(qs1) == set(qs2) 

The identical ~Q expression is behaving differently in the context of Case/When and filter. Am I completely missing something and this is expected behavior?

This is using Django 2.0.4.

Change History (5)

comment:1 Changed 7 weeks ago by Bo Marchman

Description: modified (diff)

comment:2 Changed 7 weeks ago by Simon Charette

Could you possible provide the SQL queries generated by these querysets and which database you a querying against? You can retrieve them by doing str(queryset.query).

comment:3 Changed 7 weeks ago by Bo Marchman

This is on Postgres 9.6.8.

For qs1:

SELECT "test_app_application"."id",
       CASE
           WHEN NOT ("test_app_score"."reviewed" = TRUE
                     AND "test_app_score"."reviewed" IS NOT NULL) THEN TRUE
           ELSE FALSE
       END AS "needs_review"
FROM "test_app_application"
LEFT OUTER JOIN "test_app_score" ON ("test_app_application"."id" = "test_app_score"."application_id")
WHERE CASE
          WHEN NOT ("test_app_score"."reviewed" = TRUE
                    AND "test_app_score"."reviewed" IS NOT NULL) THEN TRUE
          ELSE FALSE
      END = TRUE

And qs2:

SELECT "test_app_application"."id"
FROM "test_app_application"
WHERE NOT ("test_app_application"."id" IN
             (SELECT U1."application_id"
              FROM "test_app_score" U1
              WHERE U1."reviewed" = TRUE))

comment:4 Changed 7 weeks ago by Simon Charette

Triage Stage: UnreviewedAccepted
Version: 2.0master

I feel like qs2 is correct and that q1 should be using a subquery as well to correctly deal with SQL's boolean logic with regards to NULL.

SELECT "test_app_application"."id",
    CASE
          WHEN NOT ("test_app_application"."id" IN
             (SELECT U1."application_id"
              FROM "test_app_score" U1
              WHERE U1."reviewed" = TRUE)) THEN TRUE
          ELSE FALSE
      END as "needs_review"
FROM "test_app_application"
WHERE CASE
          WHEN NOT ("test_app_application"."id" IN
             (SELECT U2."application_id"
              FROM "test_app_score" U2
              WHERE U2."reviewed" = TRUE)) THEN TRUE
          ELSE FALSE
      END = TRUE

I'm not sure how to convert it into a subquery pushdown though.

comment:5 Changed 13 days ago by Robert Sparks

I think this is just a consequence of the way annotate works on a reverse ForeignKey rather than being a bug in the conditional expression.

In [53]: Application.objects.annotate(
    ...:     needs_review=Case(
    ...:         When(~Q(score__reviewed=True), then=V(True)),
    ...:         default=V(False),
    ...:         output_field=BooleanField()
    ...:     )
    ...: )
Out[53]: <QuerySet [<Application: Application object (1)>, <Application: Application object (1)>, <Application: Application object (2)>]>

In [54]: Application.objects.annotate(
    ...:     needs_review=Case(
    ...:         When(~Q(score__reviewed=True), then=V(True)),
    ...:         default=V(False),
    ...:         output_field=BooleanField()
    ...:     )
    ...: ).values_list('needs_review',flat=True)
Out[54]: <QuerySet [True, False, True]>

Or more simply:

In [55]: Application.objects.annotate(score_has_been_reviewed=F('score__reviewed
    ...: '))
Out[55]: <QuerySet [<Application: Application object (1)>, <Application: Application object (1)>, <Application: Application object (2)>]>

In [56]: Application.objects.annotate(score_has_been_reviewed=F('score__reviewed
    ...: ')).values_list('score_has_been_reviewed',flat=True)
Out[56]: <QuerySet [False, True, None]>
Note: See TracTickets for help on using tickets.
Back to Top