I have the following query on the server:
SELECT META().id as docid, * FROM <bucket_name> WHERE type=’<doc_type>’ and <property_1> >= 558141711 and <propery_1> <= 558249951 order by <property_1> desc
There is a doc with the same type as <doc_type> that has <property_1> = “558148002.064351” and is not included in the query results. But the document appears in the same query on couchbase mobile 1.4.1 (iOS). The document exists on the server because it gets replicated.
Any suggestions?
Query has predicate property_1 >= 558141711 AND property_1 <= 558249951
Any values outside this range will not qualify.
If the document has property_1 = "558148002.064351"
This will not qualify because it is string. In N1QL number and string are different and number is smaller than string (check N1QL data type Collation)
If you want to treat them same use TO_NUMBER(property_1). If not able convert the function returns NULL and which automatically eliminate by predicate.
Thanks for the quick answer, property_1 is actually a double, it was between “” because I copied from a log. Anyways, I tried using TO_NUMBER to cast the values but i had no success. The document has also a string property called “identifier” which is a unique identifier as string, and i tried to find the it using that property, but still no success. I tried multiple queries with different parameters, like document id, or property_1 equals that value; but it can not find the document on the server.
So I logged all documents using:
SELECT * FROM <bucket_name> WHERE type=<doc_type>
in order to find the missing document using Cmd+F in the results area and i was searching for documents that contain “18-09092018” and I found the follwing docs:
18-09092018-046-00003
18-09092018-496-00004
18-09092018-013-00005
18-09092018-036-00006
18-09092018-991-00011
18-09092018-996-00012
18-09092018-342-00013
but the mobile query has the following result:
18-09092018-046-00001
18-09092018-496-00002
18-09092018-013-00003
18-09092018-036-00004
18-09092018-996-00005
18-09092018-342-00006
18-09092018-496-00007
18-09092018-013-00008
18-09092018-036-00009
18-09092018-991-00010
18-09092018-996-00011
18-09092018-342-00012
18-09092018-342-00013
So it seems that a lot of documents are missing from the server query
Find that META().id document you think it is missing in result, Check if the document is present on server through UI , If present and rerun the query with USE KEYS [“document key”] by avoiding index and see if your query gets right result.
Check out what index query using and see if any documents skipped in indexer.log
Try with primary index and if that works drop secondary index it used and re-create it.