Basic n1ql query returns no results, but USE KEYS does return results

SQL++
1

I have a cb instance (Version: 4.0.0-4051 Community Edition (build-4051)) with 1MM+ documents and 2 primary indexes. However, when I perform a basic query like this, I get no results:

cbq> select * from mybucket limit 10;
{
    "requestID": "15bf360e-ea9b-40d0-9e78-7feec287ab04",
    "signature": {
        "*": "*"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "367.974416ms",
        "executionTime": "367.923748ms",
        "resultCount": 0,
        "resultSize": 0
    }
}

A query with the USE KEYS clause seems to work:

cbq> select * from mybucket USE KEYS ['someKeyHere'];
{
    "requestID": "5e93ea31-f66c-4789-ab0b-bc3db6ba152d",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "doc": "contents"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.515806ms",
        "executionTime": "1.464652ms",
        "resultCount": 1,
        "resultSize": 702
    }
}

I’m quite new to couchbase and n1ql so perhaps I’m missing something obvious here. Thanks for your help!

2

Wow, that’s quite surprising. You should definitely upgrade to 4.5.1, or at least 4.1.1 CE.

For 4.0, what does SELECT COUNT(*) return?

And can you also post your CREATE INDEX definitions.

3

Thanks for the quick reply! We do plan on upgrading but in the meantime I was hoping there was some simple reason these queries don’t work.

count:

cbq> select count(*) from mybucket;
{
    "requestID": "91ae27e9-f1a1-4549-974b-12269f5f4bac",
    "signature": {
        "$1": "number"
    },
    "results": [
        {
            "$1": 1.3434754e+07
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "638.063439ms",
        "executionTime": "638.005469ms",
        "resultCount": 1,
        "resultSize": 43
    }
}

index definitions:

CREATE PRIMARY INDEX primary_1 ON mybucket USING GSI
CREATE PRIMARY INDEX primary_2 ON mybucket USING GSI
4

Running the query using hints doesn’t work as well. I’m curious if the indexes can be rebuilt.

5

Yes. Can you drop both indexes, and then create one index first and run the query.