#34254: Exists annotations can return non-boolean results (i.e. None) if used
with
an empty QuerySet.
-------------------------------------+-------------------------------------
Reporter: Keryn | Owner: nobody
Knight |
Type: Bug | Status: new
Component: Database | Version: dev
layer (models, ORM) | Keywords: Exists
Severity: Normal | EmptyQuerySet sqlite postgres
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I suspect this is following on from, but potentially separate to #33018 --
because that ticket starts out using `Django 3.2` to observe that an empty
queryset (`EmptyQuerySet` or whatever, via `none()`) can short circuit
evaluation to be `0 as ...` in the SQL query, which is exactly the same
problem I observed.
However, as far as I can tell, the result of an `Exists(queryset.none())`
can still return values outside of `True/False`, namely, **None**.
Using Django `main` as of `4593bc5da115f2e808a803a4ec24104b6c7a6152` (from
`Wed Jan 11 ... 2023`), here's the behaviour on both postgres and sqlite.
In both scenarios I'm using `3.10.2` with `psycopg2==2.9.3` and
`sqlite3.sqlite_version` is `3.37.0` and `sqlite3.version` is `2.6.0`.
IPython outputs **8** and **11** are the problems.
{{{
class A(models.Model):
pass
class B(models.Model):
pass
}}}
{{{
In [1]: from app.models import A, B
In [2]: A.objects.using("pg").create()
Out[2]: <A: A object (1)>
In [3]: B.objects.using("pg").create()
Out[3]: <B: B object (1)>
In [4]: A.objects.using("sqlite").create()
Out[4]: <A: A object (1)>
In [4]: B.objects.using("sqlite").create()
Out[4]: <B: B object (1)>
In [5]: from django.db.models import Exists
In [6]:
A.objects.using("sqlite").annotate(should_be_bool=Exists(B.objects.all())).first().should_be_bool
Out[6]: True
In [7]:
A.objects.using("sqlite").annotate(should_be_bool=Exists(B.objects.filter(pk=99999999))).first().should_be_bool
Out[7]: False
In [8]:
A.objects.using("sqlite").annotate(should_be_bool=Exists(B.objects.none())).first().should_be_bool
# This is the problem, it returned neither True nor False
In [9]:
A.objects.using("pg").annotate(should_be_bool=Exists(B.objects.all())).first().should_be_bool
Out[9]: True
In [10]:
A.objects.using("pg").annotate(should_be_bool=Exists(B.objects.filter(pk=99999999))).first().should_be_bool
Out[10]: False
In [11]:
A.objects.using("pg").annotate(should_be_bool=Exists(B.objects.none())).first().should_be_bool
# This is the problem, it returned neither True nor False
}}}
And the queries, which are the same for postgres & sqlite:
{{{
# ...
{'sql': 'SELECT "app_a"."id", EXISTS(SELECT 1 AS "a" FROM "app_b" LIMIT 1)
AS "should_be_bool" FROM "app_a" ORDER BY "app_a"."id" ASC LIMIT 1',
'time': '0.001'},
{'sql': 'SELECT "app_a"."id", EXISTS(SELECT 1 AS "a" FROM "app_b" U0
WHERE U0."id" = 99999999 LIMIT 1) AS "should_be_bool" FROM "app_a" ORDER
BY "app_a"."id" ASC LIMIT 1',
'time': '0.001'},
{'sql': 'SELECT "app_a"."id", NULL AS "should_be_bool" FROM "app_a" ORDER
BY "app_a"."id" ASC LIMIT 1',
'time': '0.001'}
}}}
Given `Exists` has an `output_field` of `BooleanField` and that
definition doesn't have `null=True` as an argument, it seems incongruent
from both an expectations ("exists sounds boolean") and implementation
("it doesn't say it could be null") standpoint.
------------
Whilst the problem exists in `main`, it has also changed behaviour
(presumably or potentially unexpectedly) since `3.2`, where `postgres` and
`sqlite` actually do different things, hence we tested both above. So
`main` is now ''consistent'', but I'd personally argue it's consistently
''wrong'' (for a given value thereof, no judgement made!)
In `3.2.16`, under sqlite, using `annotate(x=Exists(y.none()))` returns
`False` but on `main` it now returns `None` (see above) -- the `3.2`
behaviour is correct for ''my expectations''
{{{
In [4]:
A.objects.using("sqlite").annotate(should_be_bool=Exists(B.objects.none())).first().should_be_bool
Out[4]: False
In [5]: connections['sqlite'].queries
Out[5]:
{'sql': 'SELECT "app_a"."id", 0 AS "should_be_bool" FROM "app_a" ORDER BY
"app_a"."id" ASC LIMIT 1',
'time': '0.000'}
}}}
In `3.2.16` with postgres we get neither `None` nor `False` but the
integer `0` instead:
{{{
In [4]:
A.objects.using("pg").annotate(should_be_bool=Exists(B.objects.none())).first().should_be_bool
Out[4]: 0
In [5]: connections['pg'].queries
Out[5]:
{'sql': 'SELECT "app_a"."id", 0 AS "should_be_bool" FROM "app_a" ORDER BY
"app_a"."id" ASC LIMIT 1',
'time': '0.001'}
}}}
-------------
So we can observe that under `3.2` using the `0 AS ...` behaviour
- sqlite appears to behave ''correctly'' (returning `False`)
- postgres appears to behave incorrectly (failing to cast a raw integer to
a boolean)
And under `main` using the `NULL AS ...` behaviour
- sqlite no longer behaves the same, returning `None` where I'd expect
`False` (or even `True` given the way SQL EXISTS works...)
- postgres behaves differently, `0` is now `None` but I'd still expect
`True` or `False` as the output.
--
Ticket URL: <https://code.djangoproject.com/ticket/34254>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070185a5ef7b8a-9871bea2-9b92-4dc8-b29a-5bf9e1fbf215-000000%40eu-central-1.amazonses.com.
