Details
Description
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":
,
"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": [
]