Hi Team,
I am not sure whats wrong but I am getting inconsistent results from my N1QL query.
I am certain that there would some way to fix this. This is causing failure in my test automation in the CI/CD pipeline. Is this how it behaves normally or the query is crazy? Whats making the result inconsistent?
I am using 4.5 version on Windows 10 Home.
Please provide sample document, index definitions and EXPLAIN output of the query.
Are you doing any UPDATES/DELETES/INSERTS in parallel
Also try with 4.5.1 or 4.6.0
Just to be sure, can you execute this from cbq shell and see if you get the same inconsistency?
Here is the explain:
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "silver-spoon",
"keyspace": "silver-spoon",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Fetch",
"keyspace": "silver-spoon",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((`silver-spoon`.`subscriptionFeature`) is not null)"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`silver-spoon`.`subscriptionFeature`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "select subscriptionFeature from `silver-spoon` where subscriptionFeature is not null"
}
]
Here is the whole of data:
[
{
"silver-spoon": 12
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 19, 2017 1:25:51 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string9",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 28, 2017 1:52:01 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "stringt",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 28, 2017 2:07:51 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "stringm",
"featureDescription": "string",
"featureName": "string",
"id": "16"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 28, 2017 2:23:43 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "DELETE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 19, 2017 3:36:53 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 19, 2017 4:48:24 PM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 19, 2017 6:16:50 PM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string3",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 28, 2017 1:00:27 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string5",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 28, 2017 1:24:17 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string7",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 28, 2017 1:45:42 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": {
"history": {
"action": "CREATE",
"data": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string8",
"featureDescription": "string",
"featureName": "string"
}
},
"roles": [
"DEVELOPER"
],
"time": "Mar 28, 2017 1:47:57 AM",
"type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
"user": "Abhideep"
}
}
},
{
"silver-spoon": 16
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string3",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string5",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string7",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string8",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string9",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "stringt",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "stringm",
"featureDescription": "string",
"featureName": "string",
"id": "16"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"subscriptionFeature": {
"featureCategory": "string",
"featureCode": "string2",
"featureDescription": "string",
"featureName": "string"
}
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-6"
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-9"
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-10"
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-11"
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-12"
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-13"
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-14"
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-16"
}
},
{
"silver-spoon": {
"cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-15"
}
}
]
This inconsistency is similar for all type of select queries. I could not find the way to list the indexes. No, I am not doing any other operations in parallel. I didnt try with any other version. I cant keep changing versions during development phase due to these kind of problems. Couchbase should behave consistent when there is only one node. Is it unfair expectation?
@keshav_m I first realized this when I saw this due to my Java code. So I tried on web console. Both are behaving same.
What is couch base version. Could you please try this in cbq shell.
SELECT `subscriptionFeature` FROM `silver-spoon`
WHERE `subscriptionFeature` IS NOT MISSING
AND `subscriptionFeature` IS NOT NULL;
Also provide all the indexes.
select * from system:indexes where keyspace_id = "silver-spoon";
Here are the indexes:
[
{
“indexes”: {
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “b52864b01f1922ce”,
“index_key”: [],
“is_primary”: true,
“keyspace_id”: “silver-spoon”,
“name”: “silver-spoon”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}
},
{
“indexes”: {
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “1b7f5657604860e6”,
“index_key”: [],
“is_primary”: true,
“keyspace_id”: “silver-spoon”,
“name”: “#primary”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}
}
]
I executed the query you sent. Similar behavior, exactly i.e. inconsistent.
Here is the cbq execution (similar result):
cbq> select subscriptionFeature from silver-spoon where subscriptionFeature is not null;
{
“requestID”: “a5b2e75a-ee37-432e-a6d5-2d1d72c9a251”,
“signature”: {
“subscriptionFeature”: “json”
},
“results”: [
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “string3”,
“featureDescription”: “string”,
“featureName”: “string”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “string5”,
“featureDescription”: “string”,
“featureName”: “string”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “string7”,
“featureDescription”: “string”,
“featureName”: “string”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “string8”,
“featureDescription”: “string”,
“featureName”: “string”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “string9”,
“featureDescription”: “string”,
“featureName”: “string”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “stringt”,
“featureDescription”: “string”,
“featureName”: “string”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “stringm”,
“featureDescription”: “string”,
“featureName”: “string”,
“id”: “16”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “string”,
“featureDescription”: “string”,
“featureName”: “string”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “string”,
“featureDescription”: “string”,
“featureName”: “string”
}
},
{
“subscriptionFeature”: {
“featureCategory”: “string”,
“featureCode”: “string2”,
“featureDescription”: “string”,
“featureName”: “string”
}
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “20.0533ms”,
“executionTime”: “19.051ms”,
“resultCount”: 10,
“resultSize”: 2396
}
}
cbq> select subscriptionFeature from silver-spoon where subscriptionFeature is not null;
{
“requestID”: “7fd627d4-07ad-47c9-83e6-a13a57af2f10”,
“signature”: {
“subscriptionFeature”: “json”
},
“results”: [
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “12.0316ms”,
“executionTime”: “12.0316ms”,
“resultCount”: 0,
“resultSize”: 0
}
}
cbq>
The plan looks fine. Could you please drop both the indexes and create primary index and try it.
Hey @vsr1
Deleting the indexes and then creating one, worked. Current index is like this:
[
{
“indexes”: {
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “ce3284ede7d90997”,
“index_key”: ,
“is_primary”: true,
“keyspace_id”: “silver-spoon”,
“name”: “silver-spoon”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}
}
]
Now I am getting consistent output. Thanks for this suggestion.
But the question is still open - why did it happen ?
@abhideepchakravarty, It would be great if you can send us the debug logs so we can diagnose the problem. You can switch the Index service to debug mode via UI->Settings. Create your original setup of both the primary indexes and when you hit the problem, do a Log Collection via UI and share the logs with us.
@vsr1 Any solution to this inconsistency problem? We are also experiencing same for count query.
SELECT count(omd.orderKey) AS assignedCnt FROM temp AS omd
WHERE meta(omd).id LIKE “myKey:%” AND
( omd.type = “order” ) AND
( ( ANY shipNode IN omd.nodes SATISFIES shipNode.name IN
[“AnnNodeCanada”, “AnnNodeHolidays”, “AnnNodeIndia”, “AnnNodeIndiaPick”, “AnnNodeUS”] END )
AND ( omd.fulfillmentStatus = “ASSIGNED” ) )
The above query returns inconsistent results which is affecting our prod. Please help
Regards
Jothi
Post as separate post with EXPLAIN output and index definitions.