Opened 2 months ago

Closed 8 weeks ago

Last modified 8 weeks 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 Changed 2 months ago by Simon Charette

Patch needs improvement: unset

comment:2 Changed 8 weeks ago by felixxm

comment:3 Changed 8 weeks ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

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 Changed 8 weeks ago by Mariusz Felisiak <felisiak.mariusz@…>

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