Opened 5 years ago

Closed 5 years ago

#29932 closed Bug (fixed)

QuerySet.difference() after intersection() returns incorrect results on SQLite and Oracle

Reported by: michaeldel Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 2.1
Severity: Normal Keywords: queryset sqlite difference intersection
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by michaeldel)

Considering a simple model

from django.db import models

class Foo(models.Model):
    pass

Here is the minimal way to encounter this issue

Foo.objects.create(pk=1)
Foo.objects.create(pk=2)

a = Foo.objects.all()
b = Foo.objects.intersection(Foo.objects.filter(pk=1))

assert a.count() == 2
assert b.count() == 1

diff = a.difference(b)

assert diff.exists()  # fails with SQLite!

This operation however works as expected on PostgreSQL.

Change History (6)

comment:1 by michaeldel, 5 years ago

Description: modified (diff)

comment:2 by michaeldel, 5 years ago

Description: modified (diff)

comment:3 by Tim Graham, 5 years ago

Summary: Queryset `difference()` after `intersection()` returns wrong queryset on SQLite → QuerySet.difference() after intersection() returns incorrect results on SQLite
Triage Stage: Unreviewed → Accepted

Reproduced at 413583e2e27af89e3ffaa4f8e34ee018fb77173c.

comment:4 by ChillarAnand, 5 years ago

Owner: changed from nobody to ChillarAnand
Status: new → assigned

comment:5 by Mariusz Felisiak, 5 years ago

Has patch: set
Owner: changed from ChillarAnand to Mariusz Felisiak
Summary: QuerySet.difference() after intersection() returns incorrect results on SQLite → QuerySet.difference() after intersection() returns incorrect results on SQLite and Oracle

Wrapping compound queries in a parentheses solves this issue on Oracle. It is more complicated on SQLite because compound queries do not support parentheses on SQLite and I think that the only solution is to wrap them in a subquery.

​PR

@ChillarAnand I hope you don't mind that I assigned this ticket to myself.

comment:6 by Tim Graham <timograham@…>, 5 years ago

Resolution: → fixed
Status: assigned → closed

In f9a33e3c:

Fixed #29932 -- Fixed combining compound queries with sub-compound queries on SQLite and Oracle.

Note: See TracTickets for help on using tickets.
Back to Top