Details
-
Bug
-
Resolution: Fixed
-
Critical
-
6.5.1, 6.6.0, Cheshire-Cat
-
Untriaged
-
1
-
Unknown
Description
The fields for the following queries need to be set up with "keyword" analyzer within the index for the query to be sargable for the index:
- TermQuery
- PhraseQuery
- MultiPhraseQuery
- FuzzyQuery
- PrefixQuery
- RegexpQuery
- WildcardQuery
This change is necessary to assure consistent results during a covering and a non-covering query scenario when no index options are specified within the SEARCH(..) function.
This will also change the behavior for the above mentioned queries, but in a good away - that the user will always see the same result with and without validation.
Note: this issue in validation (with a non-covered query) could still happen over certain data with must_not (NEG) over Match and MatchPhrase queries when the user has an index with a field using non-standard analyzer and does NOT specify the index name within options or the analyzer within the query.
See: https://forums.couchbase.com/t/n1ql-search-query-result-varies-depending-on-selected-fields/
Here's an example:
Index: "field": "dept", "analyzer": "keyword" Data: {"dept": "Pre-sales"} |
Query: SELECT * from keyspace WHERE SEARCH(keyspace, "-dept:Sales"); |
- Search phase would return the document as the match query (which uses the keyword analyzer) wouldn't match Sales with Pre-sales.
- However, during the validation phase - since there's no index context, or analyzer explicitly specified for the match query - the default analyzer is used which causes the document to not be returned for the query.
The workarounds here are these queries ..
SELECT * from keyspace WHERE SEARCH(keyspace, "-dept:Sales", {"index":"index_name"}); |
SELECT *
|
FROM keyspace
|
WHERE SEARCH(keyspace, {"must_not": {"disjuncts":[{"field": "dept", "match": "Sales", "analyzer":"keyword"}]}}); |
Attachments
Issue Links
- causes
-
MB-39633 FlexIndex:n1fty: not sargable with default index
- Closed
- relates to
-
MB-60258 N1QL queries with Search Functions starts failing randomly in between continuous query workload
- Closed
-
MB-57657 [Trinity Reverify] N1QL+FTS SEARCH queries to be allowed over indexes with non-overlapping analyzer definitions
- Closed
-
MB-39887 FTS: N1FTY: for non-covering queries n1fty expects query should contain index_name or analyzer for NEG cases of match and match_phrase queries
- Open
-
MB-39686 FTS:N1FTY: Prefix queries not sargable for search func in the queries with default index
- Closed
-
MB-41536 [N1QL+FTS] During verification set DefaultAnalyzer correctly in case "field" is not specified in FTS query
- Closed