[Django] #27412: Coalesce function does not work with subqueries

Django

unread,

Nov 1, 2016, 5:32:47 AM11/1/16

to django-...@googlegroups.com

{{{
class Foo(models.Model):
val = models.IntegerField()
}}}

with data 1, 2, 3, 4, 5. In SQL I can do (roughly)

{{{
SELECT val FROM foo WHERE COALESCE((SELECT val FROM foo WHERE val=6 LIMIT
1 OFFSET 0), 3);
}}}

to get 3, 4, and 5. Translated into Django ORM query language:

{{{
Foo.objects.filter(val__gte=Coalesce(Foo.objects.filter(val=6).values_list('val',
flat=True)[:1], 3))
}}}

I get a ProgrammingError because Django does not handle subqueries, and
passes the QuerySet object directly to the SQL backend.

The reason I don’t want to use first() instead is that I am doing some
query-joining in a loop, and repeated first() calls result in lots of
queries, dragging the database down.

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

Django

unread,

Nov 1, 2016, 4:49:47 PM11/1/16

to django-...@googlegroups.com

#27412: Coalesce function does not work with subqueries
-------------------------------------+-------------------------------------

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

Changes (by Tim Graham):

* stage: Unreviewed => Accepted

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

Django

unread,

Nov 2, 2016, 1:24:20 AM11/2/16

to django-...@googlegroups.com

#27412: Coalesce function should work with subqueries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Description changed by Tzu-ping Chung:

Old description:

> Say I have a model
>
> {{{
> class Foo(models.Model):
> val = models.IntegerField()
> }}}
>

> with data 1, 2, 3, 4, 5. In SQL I can do (roughly)
>
> {{{
> SELECT val FROM foo WHERE COALESCE((SELECT val FROM foo WHERE val=6 LIMIT
> 1 OFFSET 0), 3);
> }}}
>
> to get 3, 4, and 5. Translated into Django ORM query language:
>
> {{{
> Foo.objects.filter(val__gte=Coalesce(Foo.objects.filter(val=6).values_list('val',
> flat=True)[:1], 3))
> }}}
>
> I get a ProgrammingError because Django does not handle subqueries, and
> passes the QuerySet object directly to the SQL backend.
>
> The reason I don’t want to use first() instead is that I am doing some
> query-joining in a loop, and repeated first() calls result in lots of
> queries, dragging the database down.

New description:

Say I have a model

{{{
class Foo(models.Model):
val = models.IntegerField()
}}}

with data 1, 2, 3, 4, 5. In SQL I can do (roughly)

{{{
SELECT val FROM foo WHERE val >= COALESCE((SELECT val FROM foo WHERE val=6

LIMIT 1 OFFSET 0), 3);
}}}

to get 3, 4, and 5. Translated into Django ORM query language:

{{{
Foo.objects.filter(val__gte=Coalesce(Foo.objects.filter(val=6).values_list('val',
flat=True)[:1], 3))
}}}

I get a ProgrammingError because Django does not handle subqueries, and
passes the QuerySet object directly to the SQL backend.

The reason I don’t want to use first() instead is that I am doing some
query-joining in a loop, and repeated first() calls result in lots of
queries, dragging the database down.

--

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

Django

unread,

Jan 4, 2019, 3:44:30 AM1/4/19

to django-...@googlegroups.com

#27412: Coalesce function should work with subqueries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by onlined):

Thanks to `Subquery`, I think this ticket can be closed.

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

Django

unread,

Aug 23, 2022, 11:33:12 PM8/23/22

to django-...@googlegroups.com

#27412: Coalesce function should work with subqueries
-------------------------------------+-------------------------------------

Reporter: Tzu-ping Chung | Owner: nobody

Type: New feature | Status: closed

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

Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by Simon Charette):

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

Comment:

Confirmed support even without the `Subquery` wrapping in
[https://github.com/django/django/pull/15989 this PR].

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

Django

unread,

Aug 24, 2022, 6:31:03 AM8/24/22

to django-...@googlegroups.com

#27412: Coalesce function should work with subqueries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Carlton Gibson <carlton@…>):

In [changeset:"b30c0081d4d8a31ab7dc7f72a4c7099af606ef29" b30c0081]:
{{{
#!CommitTicketReference repository=""
revision="b30c0081d4d8a31ab7dc7f72a4c7099af606ef29"
Refs #27412 -- Confirmed support for executing Coalesce(subquery).

This has been supported for subqueries wrapped in Subquery since the
expression
was introduced and for Queryset directly since Subquery resolves to
sql.Query.

Piggy-backed on the existing tests covering Coalesce handling of
EmptyResultSet
as it seemed like a proper location to combine testing.
}}}

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

Reply all

Reply to author

Forward