#25517: Concat() database function is not idempotent in SQLite
-------------------------------------+-------------------------------------
Reporter: wsmith323 | Owner: wsmith323
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: concat coalesce | Triage Stage:
sqlite | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by wsmith323):
* owner: nobody => wsmith323
* status: new => assigned
Old description:
> When running on sqlite, with a `Concat()` expression used in the query,
> the `ConcatPair.coalesce()` method is called every time the SQL is
> generated. This is normal, expected, and AFAIK, correct behavior.
>
> The problem is that `ConcatPair.coalesce()` is not idempotent. So,
> **//every//** time the SQL is generated, each expression within the
> {{{ConcatPair}}} instance is wrapped with an additional `COALESCE()` SQL
> function. If generated enough times, the SQL can reach a point where it
> will crash the sqlite query parser.
>
> This problem may not manifest itself in a typical request/response
> context, as the SQL with the additional `COALESCE()` calls will work
> identically to the original and the SQL may not be re-generated a
> sufficient number of times to crash the parser.
>
> However, in a long-running process (where this bug was found), it can be
> easily triggered. For example, say I have a "base" queryset with a
> `Concat()` within an `.annotate()`. I never actually evaluate this
> queryset, but I use it to construct other querysets which I do evaluate.
> Because all of these querysets share the same instance of
> `Query._annotations`, evaluating ANY of these querysets will add an
> additional level of `COALESCE()` to the SQL generated by the others.
>
> I have a fix coded. I will submit a PR shortly.
New description:
When running on sqlite, with a `Concat()` expression used in the query,
the `ConcatPair.coalesce()` method is called every time the SQL is
generated. This is normal, expected, and AFAIK, correct behavior.
The problem is that `ConcatPair.coalesce()` is not idempotent. So,
**//every//** time the SQL is generated, each expression within the
{{{ConcatPair}}} instance is wrapped with an additional `COALESCE()` SQL
function. If generated enough times, the SQL can reach a point where it
will crash the sqlite query parser.
This problem may not manifest itself in a typical request/response
context, as the SQL with the additional `COALESCE()` calls will work
identically to the original and the SQL may not be re-generated a
sufficient number of times to crash the parser.
However, in a long-running process (where this bug was found), it can be
easily triggered. For example, say I have a "base" queryset with a
`Concat()` within an `.annotate()`. I never actually evaluate this
queryset, but I use it to construct other querysets which I do evaluate.
Because all of these querysets share the same instance of
`Query._annotations`, evaluating ANY of these querysets will add an
additional level of `COALESCE()` to the SQL generated by the others.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25517#comment:3>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.30b0e6af437b915ae297fece685e2e70%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
