#35732: Postgresql Concat using || and Trigram similarity operator precedence
bug
----------------------------------+------------------------------------
Reporter: Gastón Avila | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 5.1
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+------------------------------------
Changes (by Simon Charette):
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
* version: 5.0 => 5.1
Old description:
> 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("...
> }}}
New description:
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("...
}}}
--
Comment:
Regression in 6364b6ee1071381eb3a23ba6b821fc0d6f0fce75.
--
Ticket URL: <https://code.djangoproject.com/ticket/35732#comment:2>
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/01070191c2963666-b8f67e95-ee15-4773-8f51-cd574fcd490a-000000%40eu-central-1.amazonses.com.
