incorrect index scan with EXISTS and nested field
Description
Components
Affects versions
Fix versions
Labels
Environment
Link to Log File, atop/blg, CBCollectInfo, Core dump
Release Notes Description
Activity
Former user August 3, 2015 at 2:56 PM
verified, test case added
Isha Kandaswamy June 19, 2015 at 7:01 PM
I tested it on the centos build that the user used, and the query works fine.
But I did notice something peculiar which might be related to the issue. If you reinstall couch base onto the centos machine and previously had created the indexes, it retains some indexes in a invalid state. This is being tracked by https://couchbasecloud.atlassian.net/browse/MB-14953
If we run the query at this time we get empty results and an error message. But this is seen with rest calls and cbq.
Im not sure if the user got the error message. If he was using some other sdk and only printing the results array out he wouldn't see the error.
cbq> select * from testusers a where a.type = 'user' and exists (select profileid from testusers b use keys 'AUDIT_'||a.id.profileid) and a.id.profileid = "100009910";
"results": [
]
"errors": [
{
"code": 5000,
"msg": "Index not found - cause: Stale metadata"
}
]
Isha Kandaswamy June 18, 2015 at 10:00 PMEdited
The index scan for one of the users indexes gives incorrect values for high and low range in the spans array.
I used the data the user gave in his description. Tested it on the Beta build (2213).
For what I tested :
"index": "test_profile_profileid",
Spans ==>
"Range": { "High": [ "\"100009910\”” ],
"Inclusion": 3,
"Low": ["\"100009910\""]
The user gets :
"index": "test_profile_profileid”,
Spans ==>
"Range": { "High": [ "100010"],
"Inclusion": 3,
"Low": ["\"100199709\""]
This is the issue.
The query where empty results were seen by the customer :
select * from testusers a where a.type = 'user' and exists (select profileid from testusers b use keys 'AUDIT_'||a.id.profileid) and a.id.profileid = "100009910";
"results": [
{
"a": {
"id": {
"profileid": "100009910"
},
"type": "user"
}
}
]
Explain
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan",
"index": "test_type",
"keyspace": "testusers",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"user\""
],
"Inclusion": 3,
"Low": [
"\"user\""
]
},
"Seek": null
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "test_profile_profileid",
"keyspace": "testusers",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"100009910\""
],
"Inclusion": 3,
"Low": [
"\"100009910\""
]
},
"Seek": null
}
],
"using": "gsi"
}
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "a",
"keyspace": "testusers",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "((((`a`.`type`) = \"user\") and (exists (select (`b`.`profileid`) from testusers as `b` use keys (\"AUDIT_\" || ((`a`.`id`).`profileid`))))) and (((`a`.`id`).`profileid`) = \"100009910\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
]
Executing with using primary index test_pri
select * from testusers a use index (test_pri) where a.type = 'user' and exists (select profileid from testusers b use keys 'AUDIT_'||a.id.profileid) and a.id.profileid = "100009910";
"results": [
{
"a": {
"id": {
"profileid": "100009910"
},
"type": "user"
}
}
]
explain select * from testusers a use index (test_pri) where a.type = 'user' and exists (select profileid from testusers b use keys 'AUDIT_'||a.id.profileid) and a.id.profileid = "100009910";
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "test_pri",
"keyspace": "testusers",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "a",
"keyspace": "testusers",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "((((`a`.`type`) = \"user\") and (exists (select (`b`.`profileid`) from testusers as `b` use keys (\"AUDIT_\" || ((`a`.`id`).`profileid`))))) and (((`a`.`id`).`profileid`) = \"100009910\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
]Details
Assignee
Isha KandaswamyIsha Kandaswamy(Deactivated)Reporter
Gerald SangudiGerald SangudiIs this a Regression?
UnknownTriage
UntriagedPriority
CriticalInstabug
Open Instabug
Details
Details
Assignee
Reporter
Is this a Regression?
Triage
Priority
CriticalInstabug
PagerDuty
PagerDuty Incident
PagerDuty
PagerDuty Incident
PagerDuty
Sentry
Linked Issues
Sentry
Linked Issues
Sentry
Zendesk Support
Linked Tickets
Zendesk Support
Linked Tickets
Zendesk Support
From forums, see https://forums.couchbase.com/t/inconsistent-query-results-in-n1ql/4077
Incorrect index scan is being generated by the combination of AND terms, EXISTS, and nested fields.
Steps to Reproduce :
CREATE PRIMARY INDEX test_pri ON testusers USING GSI;
CREATE INDEX test_type ON testusers(type) USING GSI;
CREATE INDEX test_profile_profileid ON testusers((id.profileid)) WHERE (type = "user") USING GSI;
CREATE INDEX test_index_profileid ON testusers(profileid) WHERE (not (type = "user")) USING GSI;
Works fine :
select * from testusers a where a.type = 'user' and exists (select profileid from testusers b use keys 'AUDIT_'||a.id.profileid);
"results": [
{
"a": {
"id": {
"profileid": "100009910"
},
"type": "user"
}
}
]
Doesn't return Correct results :
select * from testusers a where a.type = 'user' and exists (select profileid from testusers b use keys 'AUDIT_'||a.id.profileid) and a.id.profileid = "100009910";
"results": [
]