#35732 closed Bug (fixed)
Postgresql Concat using || and Trigram similarity operator precedence bug
Reported by: | Gastón Avila | Owned by: | Gastón Avila |
---|---|---|---|
Component: | contrib.postgres | Version: | 5.1 |
Severity: | Release blocker | Keywords: | |
Cc: | Gastón Avila | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
A change from 5.0.8 to 5.1 raised a test failure in one of our queries that combines Concat with TrigramSimilarity. @emicuencac tracked this down to a recently deployed simplification on how Concat is rendered to sql here #34955 which leads to the pipe operator not being wrapped in parenthesis which were implicit when using CONCAT(...).
Here is a more explicit example
Model.objects .annotate( concat_result=Concat(F("field"), V("tew")), similarity=TrigramSimilarity("concat_result", search_term), ) .filter(concat_result__trigram_similar=search_term) .values("field"), [{"field": "Matthew"}],
which works well with django 5.0.8 but fails in 5.1. It fails because the mentioned change renders CONCAT using the ||
operator without wrapping parenthesis and ends up sending something like this to the DB
which would render this before the change
WHERE CONCAT('something', 'other_word') % 'search_term'
but now renders
WHERE 'something' || 'other_word' % 'search_term'
which breaks the query because the similarity operator is evaluated first.
The error that looks like this
def execute( self, query: Query, params: Params | None = None, *, prepare: bool | None = None, binary: bool | None = None, ) -> Self: """ Execute a query or command to the database. """ try: with self._conn.lock: self._conn.wait( self._execute_gen(query, params, prepare=prepare, binary=binary) ) except e._NO_TRACEBACK as ex: > raise ex.with_traceback(None) E django.db.utils.ProgrammingError: argument of WHERE must be type boolean, not type text E LINE 1: ...e" FROM "suggest_vins_makemodelsearchentry" WHERE COALESCE("...
Change History (8)
comment:2 by , 5 weeks ago
Description: | modified (diff) |
---|---|
Severity: | Normal → Release blocker |
Triage Stage: | Unreviewed → Accepted |
Version: | 5.0 → 5.1 |
Regression in 6364b6ee1071381eb3a23ba6b821fc0d6f0fce75.
comment:3 by , 5 weeks ago
Needs documentation: | set |
---|
comment:4 by , 5 weeks ago
Needs documentation: | unset |
---|
comment:5 by , 4 weeks ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:6 by , 4 weeks ago
Triage Stage: | Accepted → Ready for checkin |
---|
Hola Gastón, thank you for your report!
Could you please provide specific details of the
Model
definition that you are using, so we can triage this ticket accordingly.EDIT: I see now, after taking a quick look at your PR, that you used the
Model
as defined in the test suite, thanks.