Inconsistent query results in N1QL

SQL++
1

I have a problem the EXISTS functionality. When combined with 2 other AND statements the query returns no results.

I have 2 documents

PROFILE_100009910

{
“type”: “user”,
“id”: {
“profileid”: “100009910”
}
}

and

{
“type”: “audit”,
“history”: {
“roles”: [
{
“date”: “2014-01-29T20:35:25.000Z”,
“rolen”: “user”
}
]
},
“profileid”: “100009910”
}

I want to check if the audit document exists for the profile doc.

I have 4 indexes on the bucket

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

If I run across all the data in the bucket

select * from testusers a where a.type = ‘user’ and exists (select profileid from testusers b use keys ‘AUDIT_’||a.id.profileid)

I get

“results”: [
{
“a”: {
“id”: {
“profileid”: “100009910”
},
“type”: “user”
}
}
],

As expected as this profile does have a AUIDT document.

But if I add the profileid into the query

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”;

I get

“results”: [
]

If I remove the user test

select * from testusers a where exists (select profileid from testusers b use keys ‘AUDIT_’||a.id.profileid) and a.id.profileid = “100009910”;

It works again

“results”: [
{
“a”: {
“id”: {
“profileid”: “100009910”
},
“type”: “user”
}
}
],

Explain forworking statment

select * from testusers a where exists (select profileid from testusers b use keys ‘AUDIT_’||a.id.profileid) and a.id.profileid = “100009910”;

{
“requestID”: “212b4745-7010-4e16-8489-160a5fcb7d7d”,
“signature”: “json”,
“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”: “((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”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “3.949712ms”,
“executionTime”: “3.682684ms”,
“resultCount”: 1,
“resultSize”: 1631
}
}

Explain for failed statement

explain 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”;
{
“requestID”: “da5a8f92-25e0-41c8-9196-4ba81b4f5e84”,
“signature”: “json”,
“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”: [
“100010”
],
“Inclusion”: 3,
“Low”: [
"“100199709"”
]
},
“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”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “5.620764ms”,
“executionTime”: “5.337366ms”,
“resultCount”: 1,
“resultSize”: 3542
}
}

Looking at the index scan of test_profile_profileid it seems a little odd, the range seems off, so i compared it with another query

explain select * from testusers a where a.type = ‘user’ and a.id.profileid = “100009910”;
{
“requestID”: “1aee05d1-2233-4393-b89c-74f05ca947e2”,
“signature”: “json”,
“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 (((a.id).profileid) = “100009910”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “4.272235ms”,
“executionTime”: “4.010538ms”,
“resultCount”: 1,
“resultSize”: 3436
}
}

Which seems correct.

Is this a bug?

2

Should mention I am using the 4.0 beta, rather than the DP

3

I looked it over and I don’t immediately see any reason for this. It could be a bug or it could be an aspect I’ve not considered. The N1QL category is probably the best place to ask this question to get the right subject matter experts on it. I’ve moved it to this category.

By the way, the forum has markup for formatting long posts like this so it’s possible to see the fixed-width formatting and long posts. If you can use that, it’ll be easier for the rest of the community to read/respond.

4

Hi Ian,

Looks like a bug, which I have filed at https://issues.couchbase.com/browse/MB-15326

We will look into this and fix it. Meanwhile, you can use index hints, i.e. USE INDEX, to prevent the erroneous index scan from being used.

Thanks,
Gerald

5

Thanks for the response, I will give it a go

6

@ianmerrington I don’t see the issue on the Mac OSX beta build. What OS are you running the Couchbase server on ?

7

I am running on Centos 6

8

We tested the query on the centos build with your data and the query works fine.
There is an issue with reinstalling Couchbase over existing indexes. If you reinstall Couchbase and previously had created the indexes, it retains some indexes in a invalid state. This is being tracked on our end. If you run the problematic query at this time you get empty results and an error message. Can you try a fresh install with no pre-existing indexes, and tell us if you still see the issue ?

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"
    }
]