[Django] #33482: filter on exists-subquery with emoty queryset removes whole WHERE block

[Django]

#33482: filter on exists-subquery with emoty queryset removes whole WHERE block
-------------------------------------+-------------------------------------
Reporter: Tobias | Owner: nobody
Bengfort |
Type: | Status: new
Uncategorized |
Component: Database | Version: 4.0
layer (models, ORM) | Keywords: orm,
Severity: Normal | EmptyResultSet, Exists
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
```
>>> qs = MyModel.objects.filter(~models.Exists(MyModel.objects.none()),
name='test')
>>> qs
<QuerySet []>
>>> print(qs.query)
EmptyResultSet
```

With django-debug-toolbar I can still see the query, but there WHERE block
is missing completely.

This seems to be very similar to #33018.

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

[Django]

#33482: filter on exists-subquery with emoty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, | Triage Stage:
EmptyResultSet, Exists | Unreviewed
Has patch: 0 | Needs documentation: 0

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

Description changed by Tobias Bengfort:

Old description:

> ```
> >>> qs = MyModel.objects.filter(~models.Exists(MyModel.objects.none()),
> name='test')
> >>> qs
> <QuerySet []>
> >>> print(qs.query)
> EmptyResultSet
> ```
>
> With django-debug-toolbar I can still see the query, but there WHERE
> block is missing completely.
>
> This seems to be very similar to #33018.

New description:

>>> qs = MyModel.objects.filter(~models.Exists(MyModel.objects.none()),
name='test')
>>> qs
<QuerySet []>
>>> print(qs.query)
EmptyResultSet

With django-debug-toolbar I can still see the query, but there WHERE block
is missing completely.

This seems to be very similar to #33018.

--

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

[Django]

#33482: filter on exists-subquery with emoty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, | Triage Stage:
EmptyResultSet, Exists | Unreviewed

Has patch: 0 | Needs documentation: 0

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

Description changed by Tobias Bengfort:

Old description:

> >>> qs = MyModel.objects.filter(~models.Exists(MyModel.objects.none()),

> name='test')
> >>> qs
> <QuerySet []>
> >>> print(qs.query)
> EmptyResultSet
>
> With django-debug-toolbar I can still see the query, but there WHERE
> block is missing completely.
>
> This seems to be very similar to #33018.

New description:

{{{

>>> qs = MyModel.objects.filter(~models.Exists(MyModel.objects.none()),
name='test')
>>> qs
<QuerySet []>
>>> print(qs.query)
EmptyResultSet
}}}

With django-debug-toolbar I can still see the query, but there WHERE block
is missing completely.

This seems to be very similar to #33018.

--

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

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, | Triage Stage:
EmptyResultSet, Exists | Unreviewed

Has patch: 0 | Needs documentation: 0

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

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

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------
Reporter: Tobias Bengfort | Owner: Simon
| Charette
Type: Bug | Status: assigned

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

Keywords: orm, | Triage Stage: Accepted
EmptyResultSet, Exists |
Has patch: 0 | Needs documentation: 0

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

Changes (by Simon Charette):

* owner: nobody => Simon Charette
* status: new => assigned
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted

Comment:

I think that this is an issue with `Exists.as_sql` when `self.invert is
True`.

Since `Exists` ''encapsulate'' its negation logic (see `__invert__`) it
should catch `EmptyResultSet` when raised by its `super()` call in
`as_sql` and return an always ''true'' predicate (e.g. `1=1`).

Does the following patch address your issue?

{{{#!diff
diff --git a/django/db/models/expressions.py
b/django/db/models/expressions.py
index 81f8f79c71..7ec5dad57e 100644
--- a/django/db/models/expressions.py
+++ b/django/db/models/expressions.py
@@ -1211,13 +1211,18 @@ def __invert__(self):

def as_sql(self, compiler, connection, template=None,
**extra_context):
query = self.query.exists(using=connection.alias)
- sql, params = super().as_sql(
- compiler,
- connection,
- template=template,
- query=query,
- **extra_context,
- )
+ try:
+ sql, params = super().as_sql(
+ compiler,
+ connection,
+ template=template,
+ query=query,
+ **extra_context,
+ )
+ except EmptyResultSet:
+ if self.negated:
+ return '%s = %s', (1, 1)
+ raise
if self.negated:
sql = 'NOT {}'.format(sql)
return sql, params
diff --git a/tests/expressions/tests.py b/tests/expressions/tests.py
index 5cf9dd1ea5..5d902c86e8 100644
--- a/tests/expressions/tests.py
+++ b/tests/expressions/tests.py
@@ -1905,6 +1905,13 @@ def test_optimizations(self):
)
self.assertNotIn('ORDER BY', captured_sql)

+ def test_negated_empty_exists(self):
+ manager = Manager.objects.create()
+ qs = Manager.objects.filter(
+ ~Exists(Manager.objects.none()), pk=manager.pk
+ )
+ self.assertQuerysetEqual(qs,
Manager.objects.filter(pk=manager.pk))
+

class FieldTransformTests(TestCase):
}}}

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

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, | Triage Stage: Accepted
EmptyResultSet, Exists |

Has patch: 1 | Needs documentation: 0

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

Changes (by Simon Charette):

* has_patch: 0 => 1

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

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin
Has patch: 1 | Needs documentation: 0

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

Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

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

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette

Type: Bug | Status: closed

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

Severity: Normal | Resolution: fixed

Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin

Has patch: 1 | Needs documentation: 0

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

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

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

Comment:

In [changeset:"b7d1da5a62fe4141beff2bfea565f7ef0038c94c" b7d1da5a]:
{{{
#!CommitTicketReference repository=""
revision="b7d1da5a62fe4141beff2bfea565f7ef0038c94c"
Fixed #33482 -- Fixed QuerySet filtering againts negated Exists() with
empty queryset.

Thanks Tobias Bengfort for the report.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33482#comment:7>

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin

Has patch: 1 | Needs documentation: 0

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

Comment (by GitHub <noreply@…>):

In [changeset:"6f185a53a2a7acad0ad8383267942d554527688a" 6f185a5]:
{{{
#!CommitTicketReference repository=""
revision="6f185a53a2a7acad0ad8383267942d554527688a"
Refs #33482 -- Fixed QuerySet selecting and filtering againts negated
Exists() with empty queryset.

Regression in b7d1da5a62fe4141beff2bfea565f7ef0038c94c.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33482#comment:8>

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette

Type: Bug | Status: new

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

Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin

Has patch: 1 | Needs documentation: 0

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

Changes (by Tobias Bengfort):

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

Comment:

I still get this error in 4.2 if I include the empty exists expression in
the filter:

{{{
def test_filter_by_empty_exists(self):
manager = Manager.objects.create()
qs = Manager.objects.annotate(
exists=Exists(Manager.objects.none())
).filter(pk=manager.pk, exists=False)
self.assertSequenceEqual(qs, [manager])
self.assertIs(qs.get().exists, False)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33482#comment:9>

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin

Has patch: 1 | Needs documentation: 0

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

Comment (by Simon Charette):

The fact the problem manifests itself when the queryset is not negated was
missed during the initial patch.

Tobias, can you confirm [https://github.com/django/django/pull/17336 the
following PR] addresses the issue.

It seemed worth continuing the discussion here instead of creating a
separate ticket.

--
Ticket URL: <https://code.djangoproject.com/ticket/33482#comment:10>

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin

Has patch: 1 | Needs documentation: 0

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

Comment (by Tobias Bengfort):

Thanks for the quick response! It looks good. It fixed the bug in my
application.

--
Ticket URL: <https://code.djangoproject.com/ticket/33482#comment:11>

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin

Has patch: 1 | Needs documentation: 0

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

Comment (by GitHub <noreply@…>):

In [changeset:"ea596a52d9f905596cc5335930c8f2ac4511204c" ea596a52]:
{{{
#!CommitTicketReference repository=""
revision="ea596a52d9f905596cc5335930c8f2ac4511204c"
Refs #33482 -- Fixed QuerySet selecting and filtering againts Exists()
with empty queryset.

Thanks Tobias Bengfort for the report.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33482#comment:12>

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette

Type: Bug | Status: closed

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

Severity: Normal | Resolution: fixed

Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin

Has patch: 1 | Needs documentation: 0

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

Changes (by Mariusz Felisiak):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/33482#comment:13>

[Django]

#33482: filter on exists-subquery with empty queryset removes whole WHERE block
-------------------------------------+-------------------------------------

Reporter: Tobias Bengfort | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: orm, | Triage Stage: Ready for
EmptyResultSet, Exists | checkin

Has patch: 1 | Needs documentation: 0

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

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"458bc9e768ede649e8e540823dd4ec0c6383744b" 458bc9e]:
{{{
#!CommitTicketReference repository=""
revision="458bc9e768ede649e8e540823dd4ec0c6383744b"
[5.0.x] Refs #33482 -- Fixed QuerySet selecting and filtering againts
Exists() with empty queryset.

Thanks Tobias Bengfort for the report.

Backport of ea596a52d9f905596cc5335930c8f2ac4511204c from main
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33482#comment:14>