Bug with select 1?

SQL++
1

With the following query, it does not return any result.
select 1 from zs a
JOIN zs b ON KEY b.parent FOR a
where a.tag.token = ‘c7468adf-ac8a-4551-a4fa-3b136f9d76ca’ and a.tag.expiryepoch > 1 and a.metadata.published > 1 and b.abc = 1

However if you were to change the query to the following, you actually get 1 resultset.
select * from zs a
JOIN zs b ON KEY b.parent FOR a
where a.tag.token = ‘c7468adf-ac8a-4551-a4fa-3b136f9d76ca’ and a.tag.expiryepoch > 1 and a.metadata.published > 1 and b.abc = 1

CREATE INDEX zs1 ON zs(parent,abc)

Document A
{
“parent”: “1496116023888_5a14fc5c-e29a-4fd8-90c4-e052748ff01c”,
“deleted”: false
}

Document B
{
“parent”: “1496116023888_5a14fc5c-e29a-4fd8-90c4-e052748ff01c”,
“deleted”: true,
“abc”: 1
}

Document 1496116023888_5a14fc5c-e29a-4fd8-90c4-e052748ff01c
{
“metadata”: {
“published”: 1496116023888,
},
“tag”: {
“expiryepoch”: 1496202423888,
“token”: “c7468adf-ac8a-4551-a4fa-3b136f9d76ca”
}
}

2

which version of Couchbase Server do you use?

3

Could you post EXPLAIN of non working query. You might be hitting https://issues.couchbase.com/browse/MB-20781. Can you try with 4.6.0.

4

We are using 4.6.0
here is the explain

{
“requestID”: “1f2aedf2-2664-44cb-86e8-c0938a466742”,
“clientContextID”: “b9dc3575-b9f6-41a2-9eeb-96f7f97fc96d”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“covers”: [
“cover (((a.tag).token))”,
“cover (((a.tag).expiryepoch))”,
“cover (((a.metadata).published))”,
“cover (((a.metadata).deleted))”,
“cover ((a.outline))”,
“cover ((meta(a).id))”
],
“index”: “zstoken”,
“index_id”: “a30e132f3c8a7317”,
“keyspace”: “zs”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“successor(“c7468adf-ac8a-4551-a4fa-3b136f9d76ca”)”
],
“Inclusion”: 0,
“Low”: [
"“c7468adf-ac8a-4551-a4fa-3b136f9d76ca”",
“1”,
“1”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexJoin”,
“as”: “b”,
“for”: “a”,
“keyspace”: “zs”,
“namespace”: “default”,
“on_key”: “cover ((b.parent))”,
“scan”: {
“covers”: [
“cover ((b.parent))”,
“cover ((b.abc))”,
“cover ((meta(b).id))”
],
“index”: “zstest1”,
“index_id”: “6f0334ed06b560b2”,
“using”: “gsi”
}
},
{
"#operator": “Filter”,
“condition”: “((((cover (((a.tag).token)) = “c7468adf-ac8a-4551-a4fa-3b136f9d76ca”) and (1 < cover (((a.tag).expiryepoch)))) and (1 < cover (((a.metadata).published)))) and (cover ((b.abc)) = 1))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “1”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “select 1 from zs a \r\nJOIN zs b ON KEY b.parent FOR a\r\nwhere a.tag.token = ‘c7468adf-ac8a-4551-a4fa-3b136f9d76ca’ and a.tag.expiryepoch > 1 and a.metadata.published > 1 and\r\nb.abc = 1”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “11.513946ms”,
“executionTime”: “11.46943ms”,
“resultCount”: 1,
“resultSize”: 4004
}
}

5

5.0.0-2564 Enterprise Edition (build-2564) works fine.

6

Hi @wai.kwang.mak,

Opened https://issues.couchbase.com/browse/MB-24594, We will fix in next release. Work around will be remove abc from zs1 index