N1QL count query is returning wrong value

SQL++
,
1

Hi,

I am running the below queries on Couchbase query console, and different result for the same where clause -
1.
SELECT COUNT(1) AS itemsNumber FROM bucket AS f
WHERE LOWER(f.sai.fid) = “if_55”
AND ( EVERY x IN f.sai.directT
SATISFIES LOWER(x.tid) != “it_28” END
OR f.sai.directT IS MISSING )

result - 20

SELECT * FROM bucket AS f
WHERE LOWER(f.sai.fid) = “if_55”
AND ( EVERY x IN f.sai.directT
SATISFIES LOWER(x.tid) != “it_28” END
OR f.sai.directT IS MISSING )

result - 5 documnets

SELECT * FROM bucket AS f
WHERE LOWER(f.sai.fid) = “if_55”

result - 20 documnets

in queries 1 and 2, where clause is same but results are different.

I tried other parameters in count but result is same in all case. Please suggest what is missed in query formation.

Thank You.

2

Please check EXPLAIN.

  1. CB version
  2. Post 5 documents with keys (if confidential remove non related fields)
  3. Index definitions used by queries
3

we did Couchbase version upgrade (from 6.0.3 to 6.5.1) this week and I just noticed, same queries are working fine in the older version of Couchbase 6.0.3 but giving unexpected results in the new version 6.5.1

I also compared Explain plan of the below query in both versions.
Both versions is picking the same index but the newer CB version is not considering ‘filter’ in Explain plan.

SELECT COUNT(1) AS itemsNumber FROM bucket AS f
WHERE LOWER(f.sai.fid) = “if_55”
AND ( EVERY x IN f.sai.directT
SATISFIES LOWER(x. tid ) != “it_28” END
OR f.sai.directT IS MISSING )

4

Hi @Naveen_Nisad,

It is bug. Please track via MB-42474, Workaround mentioned at the end