N1QL query is giving different result while running from Java-sdk and couchbase UI

SQL++
, ,
1

Hi,
I tried to run the below query from Java-sdk and couchbase UI, UI is giving the expected result but sdk is giving empty -

Java sdk parameterized query -
query -
SELECT ff_S.S.bKey AS bKey FROM ff AS ff_S
WHERE LOWER(ff_S.S.custSId)
LIKE $f1_ff_S_S_custSId AND LOWER(ff_S.S.custID) = $f2_ff_S_S_custID

Parameter
“$f1_ff_S_S_custSId”:"%44%","$f2_ff_S_S_custID":“100000001”

Result - null

Query from UI -
SELECT ff_S.S.bKey AS bKey FROM ff AS ff_S
WHERE LOWER(ff_S.S.custSId) LIKE “%44%” AND LOWER(ff_S.S.custID) = “100000001”

Result - expected items in response

Used Index -
CREATE INDEX idx_ff_1 ON ff(lower((S.custID)),(S.id),lower((S.SName)),lower((S.custSId)),(S.bKey))

sdk-version - 2.7.6 and 3.0.9 (tried on both, same behavior)

2

Hi @Naveen_Nisad . That’s odd - do you get the same issue if you don’t do a parameterized query, e.g. hardcode the parameters? (Of course that is not a good practice, but just to try something out.)

3

Try with 6.6.2 , Try adhoc query from SDK and see if hitting MB-44354

4

@graham.pople - I didn’t try without parameters as this is not even possible in my case.

@vsr1 we are using Adhoc false for caching Query plan, now enabling it solved my issue.

But now caching of query plan won’t be applicable and I will need to understand how much query performance will be impacted.

Thanks for value suggestions