Parameterized query (java sdk) with where Index is not working, but same working from Couchbase UI

Java SDK
, ,
1

Hi,

We are building below Parameterized query -
*{“scan_wait”:“10s”,"$entitytype":“PriceDTA”,"$fwa":1,“statement”:"SELECT max(taV) as maxResult FROM pod WHERE *
( ( ( taiid = $taiid AND fwid = $fwaid ) AND fwaV <= $fwa )
AND entityType = $entitytype )","$fwaid":“Ifa_51”,"$taiid":“ITaiDTO_24”,“timeout”:“15s”,“scan_consistency”:“statement_plus”}

Query fired on UI -
*SELECT max(taV) as maxResult FROM pod WHERE *
( ( ( taiid = “ITaiDTO_24” AND fwid = “Ifa_51”) AND fwaV <= 1)
AND entityType = “PriceDTA”)

Index -
CREATE INDEX idx_pod_1 ON pod(taiid,taV) WHERE (entityType = “PriceDTA”)
CREATE INDEX idx_pod_2 ON pod(taiid,fwid,fwaV) WHERE (entityType = “PriceDTA”)

In the above case parameterized query is failing with no index available.

2

SDKs uses prepared statements. Index where clause part of query can’t be parameterized unless adhoc=true set.

The reason is prepare statements means generate plan once and execute repeatedly.
plan generation picks the index. index where part of query is not static index selection can’t be made.
idx_pod_2 has entries only entityType = “PriceDTA”,if you pass “$entitytype”:“Price” during execution, index doesn’t have entries. due to that plan generation picks the index that can service all the possible values.

3

tried adhoc to true,

N1qlParams.build()
.consistency(ScanConsistency.valueOf(provider.getProviderProperties().getScanConsistency().toString()))
.scanWait(provider.getProviderProperties().getScanWait(), TimeUnit.SECONDS)
.serverSideTimeout(provider.getProviderProperties().getServerSideTimeout(), TimeUnit.SECONDS)
.adhoc(true);

log -
{serverSideTimeout=‘15s’, consistency=STATEMENT_PLUS, scanWait=‘10s’, clientContextId=‘null’, maxParallelism=null, scanCap=null, pipelineCap=null, pipelineBatch=null, adhoc=true, readonly=false, pretty=true, disableMetrics=false, rawParams=null}

Still having same issue.

4

If you are creating the index immediately before executing the query, be aware that it can take several seconds for the index to be created. In our unit tests we poll up to 60 seconds on the first usage of a newly created index