Inconsistent count() results

SQL++
1

Couchbase 4.5.1:

If I run the following query on travel-sample I expect to get total = 1 but it actually returns total = 187 (all the documents where type = ‘airline’);

SELECT count(*) as total FROM travel-sample where type=‘airline’ AND meta().id = ‘airline_10’

If I delete the secondary indexes that include the airline type:
DROP INDEX travel-sample.def_type
DROP INDEX travel-sample.def_icao

The query works as expected and I get total = 1. Why does the existence of the secondary indexes affect this count?

1 Like
2

Can you post the EXPLAIN with the indexes?

3

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexCountScan”,
“covers”: [
“cover ((travel-sample.type))”,
“cover ((meta(travel-sample).id))”
],
“index”: “def_type”,
“index_id”: “4ea21d5d7502e3cc”,
“keyspace”: “travel-sample”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“airline”"
],
“Inclusion”: 3,
“Low”: [
"“airline”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexCountProject”,
“result_terms”: [
{
“as”: “total”,
“expr”: “count()"
}
]
}
]
},
“text”: "SELECT count() as total FROM travel-sample where type=‘airline’ AND meta().id = ‘airline_10’”
}
]

4

You found a issue. Will fix in next release. https://issues.couchbase.com/browse/MB-21958

Workaround :
SELECT count(meta().id) as total FROM travel-sample where type=‘airline’ AND meta().id = ‘airline_10’

1 Like