Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#30385 closed Bug (fixed)

SearchVector's usage of CONCAT makes it impossible to index against.

Reported by: Simon Charette Owned by: Simon Charette
Component: contrib.postgres Version: 2.2
Severity: Release blocker Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

SearchVector was changed in Django 2.2 to allow querying non-text fields (#29582) by using CONCAT which automatically cast such fields to text and deals with NULL value appropriately.

This had the unintended side effect of making the generated SQL loose it's IMMUTABLE status as CONCAT can generate locale/TZ dependant output which makes the generated SQL expression impossible to index against.

Change History (4)

comment:1 by Simon Charette, 5 years ago

Patch needs improvement: unset

comment:2 by Mariusz Felisiak, 5 years ago

​PR

comment:3 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

Resolution: → fixed
Status: assigned → closed

In 405c836:

Fixed #30385 -- Restored SearchVector(config) immutability.

Regression in 1a28dc3887e8d66d5e3ff08cf7fb0a6212b873e5.

The usage of CONCAT to allow SearchVector to deal with non-text fields
made the generated expression non-IMMUTABLE which prevents a functional
index to be created for it.

Using a combination of COALESCE and ::text makes sure the expression
preserves its immutability.

Refs #29582. Thanks Andrew Brown for the report, Nick Pope for the
review.

comment:4 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

In 88bf635c:

[2.2.x] Fixed #30385 -- Restored SearchVector(config) immutability.

Regression in 1a28dc3887e8d66d5e3ff08cf7fb0a6212b873e5.

The usage of CONCAT to allow SearchVector to deal with non-text fields
made the generated expression non-IMMUTABLE which prevents a functional
index to be created for it.

Using a combination of COALESCE and ::text makes sure the expression
preserves its immutability.

Refs #29582. Thanks Andrew Brown for the report, Nick Pope for the
review.

Backport of 405c8363362063542e9e79beac53c8437d389520 from master

Note: See TracTickets for help on using tickets.
Back to Top