#31304: PostgreSQL full-text search employs coalesce function for non-null
single-
column searches with SearchVector
-------------------------------------+-------------------------------------
Reporter: Paul Boddie | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: PostgreSQL text | Triage Stage: Accepted
search FTS coalesce SearchVector |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Paul, I did more investigation and it looks like we can't also drop the
`Coalesce` even in cases where there's a single expression and no
concatenation needs to take place. The reason for that is that
`to_tsvector('')` and `to_tsvector(null)` are not equivalent as the latter
will result in SQL `Unknown` when used with the `@@` operator. That's
problematic when negating expressions as both `to_tsvector(null) @@
to_tsquery('test')` and `NOT to_tsvector(null) @@ to_tsquery('test')`
result in the same value effectively breaking `~Q` and `exclude`.
Regarding the `.null` handling I also did a bit of investigation and
here's a case where checking for `output_field.null` would be a false
negative
{{{#!python
class Manager(models.Model):
name = models.TextField()
class Store(models.Model):
manager = models.ForeignKey(Manager)
second_manager = models.ForeignKey(Manager, null=True)
Store.objects.annotate(
search=SearchVector('manager__name', 'second_manager__name')
).filter(search='Victor')
}}}
In this particular case the `Col` that `'second_manager__name'` resolves
to would have `Manager.name` as `output_field` but it's not `null=True`
even it's ''nullable'' if `store.second_manager is None`. In order to
properly determine whether or not the expression should be coalesced we
need to annotate `Col` with a flag that takes the whole relationship
nullability in account.
--
Ticket URL: <https://code.djangoproject.com/ticket/31304#comment:6>
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/066.1621954436ac2b6f123ffd4cacdf0253%40djangoproject.com.
