SearchVector lookup is generating redundent Coalesce wrapper.

[T Lee]

In Django 2.2.1, in the SearchVector if we do:

>>> from django.contrib.postgres.search import SearchVector
>>> Entry.objects.annotate(
...     search=SearchVector('body_text', 'summary_text'),
... ).filter(search='Cheese')

The generated SQL currently looks something like

SELECT id /*......*/
FROM "app_entry" WHERE
to_tsvector(COALESCE(COALESCE("app_entry"."body_text", ''), '') || ' '
|| COALESCE(COALESCE("app_entry"."summary_text", ''), '')) @@
(plainto_tsquery('Cheese')) = true

i.e. in the WHERE clause, the fields is wrapped twice with COALESCE, .e.g. COALESCE(COALESCE("app_entry"."body_text", ), )

While it is still possible to create a functional index with this, the generated SQL does not feel optimal at the very least.

It will be great if the generated SQL will keep to one level of COALESCE, .e.g. COALESCE("app_entry"."body_text", ), )

[Mariusz Felisiak]

Thanks for the report. It looks that we resolve expression twice.

[T Lee]

Sorry I didn't know I need to claim the ticket first. Anyhow here is a pull request ​https://github.com/django/django/pull/11380 if it can be used. Thanks.

[Simon Charette]

No worries, feel free to assign the ticket to you. The patch seems good but you'll probably want to add an addition regression test which might require building a queryset and asserting against self.assertNotIn('COALESCE(COALESCE', str(qs.query)). You'll want to add a mention in the 2.2.2 release notes as well.

[T Lee]

Thanks. I have claimed the ticket, and have added both regression test and 2.2.2 release mention in ​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In c38e7a79:

Fixed #30488 -- Removed redundant Coalesce call in SQL generated by SearchVector.

Regression in 405c8363362063542e9e79beac53c8437d389520.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3d4e53bc:

[2.2.x] Fixed #30488 -- Removed redundant Coalesce call in SQL generated by SearchVector.

Regression in 405c8363362063542e9e79beac53c8437d389520.

Backport of c38e7a79f4354ee831f92deb7a658fc0387e3bec from master