Couchbase GSI Index

Couchbase Server
, ,
1

I saw this strange thing with index. I created an index as below

CREATE INDEX `idx_INDEXTEST` ON `INDEXTEST`( 
COLA,
COLB,
ifmissingornull(COLC, "N"),
COLD)
PARTITION BY HASH(COLA,COLB)
WITH {"num_replica":1 }

I ran two queries. Only difference between the two queries is that in one COLC is in lower case. The first query was covered but for 2nd query , I see a fetch. Why is this happening?

select
COLA,COLB
from INDEXTEST
WHERE COLA='Y'
AND COLB='N'
AND ifmissingornull(COLC, "N")='Y'

index1

select
COLA,COLB
from INDEXTEST
WHERE COLA='Y'
AND COLB='N'
AND ifmissingornull(colc, "N")='Y

index2
index2781Ă—71 7.24 KB

’

2

@rajib761 Unlike SQL, N1QL is case-sensitive to field names, because it is based on JSON and JSON field names are case-sensitive:

Your index includes “ifmissingornull(COLC, …)” but not “ifmissingornull(colc, …)”.

2 Likes
3

FYI:

If you need case insensitive search of field, you must make field as quoted identifier and provide an option. Before retrieving field from the document , the document field will convert to lower (NOT value) and retrieve. It can add up in timing every object field must convert to lower on the fly before comparison.

SELECT a.`name`i FROM [{"NamE":"xyz"}] AS a;
{
    "results": [
    {
        "name": "xyz"
    }
    ]
}