Index FTS does not indexes some documents (version 7.0 beta)

Full Text Search
1

Hello friends of CouchBase, I have problems with the indexed document in the database. The FTS Indices, do not indexed some documents.

image
image1352×941 68.9 KB

image
image

Query

SELECT SEARCH_META().id
FROM Import AS import USE INDEX(index_table USING FTS)
WHERE import.type = 'bill'
    AND SEARCH(import, {"indexName": "index_table", "size": 20, "score": "none", "from": 180, "query": {"conjuncts": [{"inclusive_start": TRUE, "inclusive_end": TRUE, "field": "actual_arrival_date", "start": "2020-01-01", "end": "2020-01-30"}, {"field": "containers.loads.description_text", "match_phrase": "coffee"}]}, "sort": ["actual_arrival_date"]})

Response

image
image380×733 34.4 KB

19 Documents

It should be returned

Query

SELECT COUNT(1)
FROM Import AS import USE INDEX(index_table USING FTS)
WHERE import.type = 'bill'
    AND SEARCH(import, {"indexName": "index_table", "size": 20, "score": "none", "from": 180, "query": {"conjuncts": [{"inclusive_start": TRUE, "inclusive_end": TRUE, "field": "actual_arrival_date", "start": "2020-01-01", "end": "2020-01-30"}, {"field": "containers.loads.description_text", "match_phrase": "coffee"}]}, "sort": ["actual_arrival_date"]})

Response

image

Sometimes does not return 3,4, … Documents

SO Ubuntu 20
Couchbase 7.0 Enterprise beta

2

@nelsonxx1 ,

I don’t think this is a problem with FTS index and that you can confirm by directly hitting the fts search endpoints over a curl command. Please let us know if there is a difference in the results with direct FTS curls.

Also, feel free to create support tickets with more details on the N1QL explain command outputs also along with all the above details.

N1QL features like SEARCH and FLEX Index has it’s own operational nitty gritties to be aware of while using it.
So, please familiarise with the relevant documentation on them.

Cheers!

3

@sreeks

Query

SELECT SEARCH_META().id
FROM Import AS import USE INDEX(index_table USING FTS)
WHERE import.type = 'bill'
    AND SEARCH(import, {"indexName": "index_table", "size": 20, "score": "none", "from": 180, "query": {"conjuncts": [{"inclusive_start": TRUE, "inclusive_end": TRUE, "field": "actual_arrival_date", "start": "2020-01-01", "end": "2020-01-30"}, {"field": "containers.loads.description_text", "match_phrase": "coffee"}]}, "sort": ["actual_arrival_date"]})

Explain

plan.#operator	plan.~children	text
"Sequence"	[{"#operator":"IndexFtsSearch","as":"import","index":"index_table","index_id":"66d28f58757a98f5","keyspace":"Import","namespace":"default","search_info":{"field":"\"\"","outname":"out","query":"{\"from\": 180, \"indexName\": \"index_table\", \"query\": {\"conjuncts\": [{\"end\": \"2020-01-30\", \"field\": \"actual_arrival_date\", \"inclusive_end\": true, \"inclusive_start\": true, \"start\": \"2020-01-01\"}, {\"field\": \"containers.loads.description_text\", \"match_phrase\": \"coffee\"}]}, \"score\": \"none\", \"size\": 20, \"sort\": [\"actual_arrival_date\"]}"},"using":"fts"},{"#operator":"Fetch","as":"import","keyspace":"Import","namespace":"default"},{"#operator":"Parallel","~child":{"#operator":"Sequence","~children":[{"#operator":"Filter","condition":"(((`import`.`type`) = \"bill\") and search(`import`, {\"from\": 180, \"indexName\": \"index_table\", \"query\": {\"conjuncts\": [{\"end\": \"2020-01-30\", \"field\": \"actual_arrival_date\", \"inclusive_end\": true, \"inclusive_start\": true, \"start\": \"2020-01-01\"}, {\"field\": \"containers.loads.description_text\", \"match_phrase\": \"coffee\"}]}, \"score\": \"none\", \"size\": 20, \"sort\": [\"actual_arrival_date\"]}))"},{"#operator":"InitialProject","result_terms":[{"expr":"(search_meta((`import`.`out`)).`id`)"}]}]}}]	"SELECT SEARCH_META().id\nFROM Import AS import USE INDEX(index_table USING FTS)\nWHERE import.type = 'bill'\n    AND SEARCH(import, {\"indexName\": \"index_table\", \"size\": 20, \"score\": \"none\", \"from\": 180, \"query\": {\"conjuncts\": [{\"inclusive_start\": TRUE, \"inclusive_end\": TRUE, \"field\": \"actual_arrival_date\", \"start\": \"2020-01-01\", \"end\": \"2020-01-30\"}, {\"field\": \"containers.loads.description_text\", \"match_phrase\": \"coffee\"}]}, \"sort\": [\"actual_arrival_date\"]})"

image
image1295×393 37 KB

Query

SELECT COUNT(1)
FROM Import AS import USE INDEX(index_table USING FTS)
WHERE import.type = 'bill'
    AND SEARCH(import, {"indexName": "index_table", "size": 20, "score": "none", "from": 180, "query": {"conjuncts": [{"inclusive_start": TRUE, "inclusive_end": TRUE, "field": "actual_arrival_date", "start": "2020-01-01", "end": "2020-01-30"}, {"field": "containers.loads.description_text", "match_phrase": "coffee"}]}, "sort": ["actual_arrival_date"]})

Explain

plan.#operator	plan.~children	text
"Sequence"	[{"#operator":"IndexFtsSearch","as":"import","covers":["cover (search(`import`, {\"from\": 180, \"indexName\": \"index_table\", \"query\": {\"conjuncts\": [{\"end\": \"2020-01-30\", \"field\": \"actual_arrival_date\", \"inclusive_end\": true, \"inclusive_start\": true, \"start\": \"2020-01-01\"}, {\"field\": \"containers.loads.description_text\", \"match_phrase\": \"coffee\"}]}, \"score\": \"none\", \"size\": 20, \"sort\": [\"actual_arrival_date\"]}))","cover ((meta(`import`).`id`))","cover (search_score((`import`.`out`)))","cover (search_meta((`import`.`out`)))"],"filter_covers":{"cover ((`import`.`type`))":"bill"},"index":"index_table","index_id":"66d28f58757a98f5","keyspace":"Import","namespace":"default","search_info":{"field":"\"\"","outname":"out","query":"{\"from\": 180, \"indexName\": \"index_table\", \"query\": {\"conjuncts\": [{\"end\": \"2020-01-30\", \"field\": \"actual_arrival_date\", \"inclusive_end\": true, \"inclusive_start\": true, \"start\": \"2020-01-01\"}, {\"field\": \"containers.loads.description_text\", \"match_phrase\": \"coffee\"}]}, \"score\": \"none\", \"size\": 20, \"sort\": [\"actual_arrival_date\"]}"},"using":"fts"},{"#operator":"Parallel","~child":{"#operator":"Sequence","~children":[{"#operator":"Filter","condition":"((cover ((`import`.`type`)) = \"bill\") and cover (search(`import`, {\"from\": 180, \"indexName\": \"index_table\", \"query\": {\"conjuncts\": [{\"end\": \"2020-01-30\", \"field\": \"actual_arrival_date\", \"inclusive_end\": true, \"inclusive_start\": true, \"start\": \"2020-01-01\"}, {\"field\": \"containers.loads.description_text\", \"match_phrase\": \"coffee\"}]}, \"score\": \"none\", \"size\": 20, \"sort\": [\"actual_arrival_date\"]})))"},{"#operator":"InitialGroup","aggregates":["count(1)"],"group_keys":[]}]}},{"#operator":"IntermediateGroup","aggregates":["count(1)"],"group_keys":[]},{"#operator":"FinalGroup","aggregates":["count(1)"],"group_keys":[]},{"#operator":"Parallel","~child":{"#operator":"Sequence","~children":[{"#operator":"InitialProject","result_terms":[{"expr":"count(1)"}]}]}}]	"SELECT COUNT(1)\nFROM Import AS import USE INDEX(index_table USING FTS)\nWHERE import.type = 'bill'\n    AND SEARCH(import, {\"indexName\": \"index_table\", \"size\": 20, \"score\": \"none\", \"from\": 180, \"query\": {\"conjuncts\": [{\"inclusive_start\": TRUE, \"inclusive_end\": TRUE, \"field\": \"actual_arrival_date\", \"start\": \"2020-01-01\", \"end\": \"2020-01-30\"}, {\"field\": \"containers.loads.description_text\", \"match_phrase\": \"coffee\"}]}, \"sort\": [\"actual_arrival_date\"]})"

image
image1313×201 24.8 KB

4

@sreeks

I have these fields filtering on the table. 8 columns, of which 7 are text fields. Also add an extra field for filtering that is not displayed on table (HARMONIZED_NUMBER), which is also a text field

image
image1854×237 42.8 KB

The index that you show you is the one that use to make the filtering in the text fields.

The best response times I got them using Index FTS. When using the Commuinity 6.1 version I did not have problems, everything worked well.

5

@nelsonxx1,

What I understood here is that - the same queries are working on 6.1 and broken in 7.0 Beta.
Tagging @abhinav as he might know of any recent fixes on that front.

Until then, my recommendation would always be to use FTS directly than over N1QL if there are no specific N1QL specific requirements.

6

yes @sreeks.

Sorry, I’m wrong, the version I used before was Community 6.6.1. Everything worked fine in that version

7

@nelsonxx1,

As 6.6.1 is working, You should wait official release 7.0 (7.0 beta2 is 5 months old) and try it.
Also narrow down the repro and provide the sample documents.
1) Try remove size, from, sort in SEARCH() and see if gives correct/same results both queries.
2) Then narrow down your date range and try until you no longer repro.
3) collect all the documents that you can repo and strip relevant info if needed change data of fields share those documents or Send as private message.