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

[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.

[Tim Graham]

Reproduced at 413583e2e27af89e3ffaa4f8e34ee018fb77173c.

[Mariusz Felisiak]

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.

[Tim Graham <timograham@…>]

In f9a33e3c:

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