-
Type: New Feature
-
Status: Resolved
-
Priority: Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 5.5
-
Component/s: Core VCS, Query & PageProvider
-
Environment:PostgreSQL
-
Impact type:Content model Change
-
Upgrade notes:
For PostgreSQL, where this feature is not supported natively, we will match the phrase "foo bar" using:
to_tsquery('foo & bar') @@ to_tsvector(fulltext) AND fulltext LIKE '% foo bar %'
This means that the fulltext column will have to be in clear text, pre-canonicalized (lowercase, no diacritics), and with an initial and trailing space.
Note that a more complex match like
abc "foo bar" -"gee man"
will have to be turned into something like:
to_tsquery('abc & foo & bar') @@ to_tsvector(fulltext) AND (fulltext LIKE '% foo bar %' AND NOT fulltext LIKE '% gee man %')
i.e., the initial fulltext match has to match a superset of the complete query, so it cannot easily contain excluded phrases.
Algorithm:
- pre-filter using fulltext on a query that is a superset of the original query (remove negative phrases (which are at AND level), and split positive phrases into ANDed words),
- AND with a LIKE-based search for all terms, except those that are already exactly matched by the first part, i.e., toplevel ANDed non-phrases.