[Django] #29932: Queryset `difference()` after `intersection()` returns wrong queryset on SQLite

Django

unread,

Nov 8, 2018, 2:23:45 PM11/8/18

to django-...@googlegroups.com

#29932: Queryset `difference()` after `intersection()` returns wrong queryset on
SQLite
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
michaeldel |
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) | Keywords: queryset sqlite
Severity: Normal | difference intersection
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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() == 1 # fails with SQLite!
}}}
This operation however works as expected on PostgreSQL.

--
Ticket URL: <https://code.djangoproject.com/ticket/29932>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Nov 8, 2018, 2:24:40 PM11/8/18

to django-...@googlegroups.com

#29932: Queryset `difference()` after `intersection()` returns wrong queryset on
SQLite
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by michaeldel:

Old description:

> 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() == 1 # fails with SQLite!
> }}}
> This operation however works as expected on PostgreSQL.

New description:

Considering a simple model
{{{
#!python
from django.db import models

class Foo(models.Model):
pass
}}}

Here is the minimal way to encounter this issue
{{{

#!python
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() == 1 # fails with SQLite!
}}}
This operation however works as expected on PostgreSQL.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/29932#comment:1>

Django

unread,

Nov 8, 2018, 2:24:57 PM11/8/18

to django-...@googlegroups.com

#29932: Queryset `difference()` after `intersection()` returns wrong queryset on
SQLite
-------------------------------------+-------------------------------------

Reporter: michaeldel | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by michaeldel:

Old description:

> Considering a simple model
> {{{
> #!python

> from django.db import models
>
> class Foo(models.Model):
> pass
> }}}
>
> Here is the minimal way to encounter this issue
> {{{

> #!python

> 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() == 1 # fails with SQLite!
> }}}
> This operation however works as expected on PostgreSQL.

New description:

Considering a simple model
{{{
#!python

from django.db import models

class Foo(models.Model):
pass
}}}

Here is the minimal way to encounter this issue
{{{

#!python
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.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/29932#comment:2>

Django

unread,

Nov 8, 2018, 7:14:56 PM11/8/18

to django-...@googlegroups.com

#29932: QuerySet.difference() after intersection() returns incorrect results on

SQLite
-------------------------------------+-------------------------------------
Reporter: michaeldel | Owner: nobody

Type: Bug | Status: new

Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: queryset sqlite | Triage Stage: Accepted
difference intersection |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* stage: Unreviewed => Accepted

Comment:

Reproduced at 413583e2e27af89e3ffaa4f8e34ee018fb77173c.

--
Ticket URL: <https://code.djangoproject.com/ticket/29932#comment:3>

Django

unread,

Nov 9, 2018, 5:27:01 AM11/9/18

to django-...@googlegroups.com

#29932: QuerySet.difference() after intersection() returns incorrect results on
SQLite
-------------------------------------+-------------------------------------
Reporter: michaeldel | Owner:

| ChillarAnand
Type: Bug | Status: assigned

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by ChillarAnand):

* status: new => assigned
* owner: nobody => ChillarAnand

--
Ticket URL: <https://code.djangoproject.com/ticket/29932#comment:4>

Django

unread,

Dec 2, 2018, 5:24:55 PM12/2/18

to django-...@googlegroups.com

#29932: QuerySet.difference() after intersection() returns incorrect results on

SQLite and Oracle
-------------------------------------+-------------------------------------
Reporter: michaeldel | Owner: felixxm

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* owner: ChillarAnand => felixxm
* has_patch: 0 => 1

Comment:

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.

[https://github.com/django/django/pull/10715 PR]

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

--
Ticket URL: <https://code.djangoproject.com/ticket/29932#comment:5>

Django

unread,

Dec 6, 2018, 3:51:05 PM12/6/18

to django-...@googlegroups.com

#29932: QuerySet.difference() after intersection() returns incorrect results on
SQLite and Oracle
-------------------------------------+-------------------------------------
Reporter: michaeldel | Owner: felixxm

Type: Bug | Status: closed

Component: Database layer | Version: 2.1
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: queryset sqlite | Triage Stage: Accepted
difference intersection |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham <timograham@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"f9a33e3c3f4cfbff60522cd54455e78c96a9d2a4" f9a33e3c]:
{{{
#!CommitTicketReference repository=""
revision="f9a33e3c3f4cfbff60522cd54455e78c96a9d2a4"
Fixed #29932 -- Fixed combining compound queries with sub-compound queries
on SQLite and Oracle.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29932#comment:6>

Reply all

Reply to author

Forward