Failing to match functional index

SQL++
1

Hi,

I’ve ecountered a case where N1QL is refusing to match my functional index, and I wonder how to get around it.

I’m building the following index:

create index `Test` on ComplexSearchDatabase(
    first seg.DepartureDateTime for seg in Response.Segments when seg.Id =
        first s.SegmentId for s in Response.Seats when s.Id = 
            first r.SeatIds[0] for r in Response.Routes when r.Id = Response.Journeys[0].RouteIds[0] end end end)

I would then expect this query to make use of it:

select
    first seg.DepartureDateTime for seg in Response.Segments when seg.Id =
        first s.SegmentId for s in Response.Seats when s.Id = 
            first r.SeatIds[0] for r in Response.Routes when r.Id = Response.Journeys[0].RouteIds[0] end end end
from ComplexSearchDatabase use index(`Test`)

But it doesn’t, even with the hint, but instead fail over to using the #primary index.

I have a couple of other collection operator based indexes which are working as expected, so I fail to see what’s failing here. Perhaps someone could give me some guidance?

Couchbase 6.0.0 Community Edition

2

Index selection is based on predicate not based on projection. Even if you give hint it must qualify based on predicate other wise it will result in wrong results so it will not be chosen

If leading index key value is MISSING, indexer will be not index the that document. If there is no predicate then we need to return all the documents. So the index will not qualify. Only option is primary index OR add WHERE (leading index key expr) IS NOT MISSING. In your case the index key is so complex and 3 nested level it may work or may not work.