#25517: Concat() database function is not idempotent in SQLite
-------------------------------------+-------------------------------------
Reporter: wsmith323 | Owner: nobody
Type: Bug | Status: new
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):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
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.
I have a fix coded. I will submit a PR shortly.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25517#comment:1>
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.bb16df3851de459d8d89b23b1763e922%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
