#35732: Postgresql Concat using || and Trigram similarity operator precedence
bug
--------------------------+--------------------------------------------
Reporter: avilaton | Type: Bug
Status: new | Component: contrib.postgres
Version: 5.0 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------+--------------------------------------------
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 #17471 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("...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35732>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070191c2488ec6-b842b58c-75c1-4441-a5f0-2d2870239b3c-000000%40eu-central-1.amazonses.com.
