Opened 4 years ago

Last modified 4 years ago

#25517 closed Bug

Concat() database function is not idempotent in SQLite — at Version 2

Reported by: Warren Smith Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Release blocker Keywords: concat coalesce sqlite
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Warren Smith)

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.

Change History (2)

comment:1 Changed 4 years ago by Warren Smith

Description: modified (diff)

comment:2 Changed 4 years ago by Warren Smith

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top