Issue with CASE WHEN THEN ELSE END and = TRUE

SQL++
1

The N1QL below is generated from LinkToCouchBase through OData. Returns zero records if run as is. If I remove the “= TRUE” it works correctly. If I show the WHERE statement as a value they show correctly as TRUE/FALSE values. This appears to be a bug in the Query Engine itself.

SELECT
Extent1.docId as docId,
Extent1.description as description
FROM app as Extent1
WHERE (Extent1.type = ‘chargeDscrp’)
AND (Extent1.docId IS NOT MISSING AND Extent1.docId IS NOT MISSING)
AND (((Extent1.tenantName = ‘legacy’)
AND CASE WHEN ((CASE WHEN (Extent1.description IS NULL) THEN NULL ELSE LOWER(Extent1.description) END IS NULL) OR FALSE) THEN NULL ELSE (LOWER(Extent1.description) LIKE ‘%hc room%’) END) = TRUE)
ORDER BY Extent1.docId
ASC LIMIT 10000

2

The following seems working fine.

INSERT INTO default VALUES ("k01",{"type":"chargeDscrp", "tenantName" : "legacy", "docId":"k01","description":"This hc room is good"});
SELECT a.docId AS docId, a.description AS description
FROM default AS a
WHERE a.type = "chargeDscrp"
AND  (a.docId IS NOT MISSING AND a.docId IS NOT MISSING)
AND (((a.tenantName = "legacy")
AND CASE WHEN ((CASE WHEN (a.description IS NULL) THEN NULL ELSE LOWER(a.description) END IS NULL) OR FALSE) THEN NULL ELSE (LOWER(a.description) LIKE "%hc room%") END) = TRUE);

Could you please post the following:

  • Couchbase version
  • Sample document that not working.
  • EXPLAIN plan when it is not working.
  • Index definition used in the explain
3

I was testing this on CB 5.1.2 version. I switched it to 6.0.1 and it works. So please disregard. Apparently it has been fix already.