Postgresql Concat using || and Trigram similarity operator precedence bug

[Gastón Avila]

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("...

[Natalia Bidart]

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 that you used the Model as defined in the test suite, thanks.

[Simon Charette]

Regression in 6364b6ee1071381eb3a23ba6b821fc0d6f0fce75.

[Sarah Boyce <42296566+sarahboyce@…>]

In c3ca607:

Fixed #35732 -- Wrapped ConcatPair expression in parentheses to ensure operator precedence.

When ConcatPair was updated to use

this lost the implicit wrapping from CONCAT(...).

This broke the WHERE clauses when used in combination with PostgreSQL trigram similarity.

Regression in 6364b6ee1071381eb3a23ba6b821fc0d6f0fce75.

Co-authored-by: Emiliano Cuenca <106986074+emicuencac@…>

[Sarah Boyce <42296566+sarahboyce@…>]

In 590f5e0:

[5.1.x] Fixed #35732 -- Wrapped ConcatPair expression in parentheses to ensure operator precedence.

When ConcatPair was updated to use

this lost the implicit wrapping from CONCAT(...).

This broke the WHERE clauses when used in combination with PostgreSQL trigram similarity.

Regression in 6364b6ee1071381eb3a23ba6b821fc0d6f0fce75.

Backport of c3ca6075cc0ad425bcf905fe14062f38eb9fbcbf from main.

Co-authored-by: Emiliano Cuenca <106986074+emicuencac@…>