[MB-64717] Fix predicate on array field where arr != []

Product: Couchbase Server
Components: Index-Service, Query-Service
Issue Link: MB-64717
Affects Version: 7.6.x
Fix Version: 8.0.0

Summary

A COUNT query that includes a WHERE clause to filter non-empty arrays (e.g., arr!=[]) returns an incorrect result. The query incorrectly includes documents with empty arrays in its final count.

Consider a dataset with two documents:

• One document with an empty array: {{ {"id":1, "arr":[]}

  }}

• One document with a non-empty array: {{ {"id":2, "arr":}

  }}

Problematic query:

SELECT count(id) from Bug.`_default`.`_default`
WHERE id > 0 and arr!=[]

This will return a count of 2 - AN INCORRECT VALUE.

Symptoms 

A COUNT query returns an inaccurate result when using a WHERE clause to filter out documents containing empty arrays (e.g., WHERE arr != []). The returned count is higher than expected because it incorrectly includes documents with empty arrays.

Triggers

This issue is triggered when a COUNT query is executed with a WHERE clause condition that attempts to exclude documents containing empty arrays (e.g., arr != []).

Verification

Examine the Dataset: First, inspect your data to confirm that some documents contain a field that is an empty array (e.g., "my_array": []).

Review the Query: Second, check the COUNT query that is returning an incorrect value. Confirm that its WHERE clause uses a condition equivalent to my_array != [] to filter on that specific field.

Workarounds

As a workaround, update the query's WHERE clause to use the ARRAY_LENGTH() function. Instead of arr != [], use the condition ARRAY_LENGTH(arr) > 0 to accurately count only the documents with non-empty arrays.

Problematic query:

SELECT count(id) from Bug.`_default`.`_default`
WHERE id > 0 and arr!=[]

Query with the workaround:

SELECT count(id) from default._default._default
WHERE id > 0 and ARRAY_LENGTH(arr)!=0