Using the "NOT EQUAL" Operators in a "WHERE" Clause Returns the Wrong Query Result Set

Products

• VMware Tanzu Gemfire

Issue/Introduction

Symptoms:

When using multiple "NOT EQUAL" operators in a "WHERE" clause, running against a compact range index, the returned result set may not be correct according to the condition in the "WHERE" Clause.

Example:

Region Definition:
<region name="exampleRegion">
 <region-attributes refid="REPLICATE_PERSISTENT" statistics-enabled="true" 
 disk-store-name="dataStore" disk-synchronous="false">
 <key-constraint>java.lang.String</key-constraint>
 </region-attributes>
 <index name="exampleRegionFieldCIndex" from-clause="/exampleRegion ex" expression="ex.FieldC" />
 </region>
gfsh>query --query="select FieldA,FieldB,FieldC from /exampleRegion where FieldD<>'1' and FieldC<>'04' and FieldC<>'00' and FieldC<>'03'"

Result : true
startCount : 0
endCount : 20
Rows : 533
Query Trace : Query Executed in 18.308296 ms; indexesUsed(1):exampleRegionFieldCIndex(Results: 647)

FieldA | FieldB | FieldC
-------------------------- | ------------------------ | ------------
FieldAexampleValue1 | FieldBexampleValue1 | 03
FieldAexampleValue2 | FieldBexampleValue2 | 05
FieldAexampleValue3 | FieldBexampleValue3 | 02
FieldAexampleValue4 | FieldBexampleValue4 | 01

The result set includes an entry where FieldC is 03 even though FieldC<>'03' was used in the WHERE clause.

Environment

Cause

This is a product issue that is occurring:

1. In the compact range index iterator and,
2. When the Compact Range Index has a lack of PdxString and String comparison logic.

Resolution

This issue has already been fixed in Gemfire8.2.1.2+/Gemfire9.x, but if upgrading is not an option there are two quick workarounds:

Workaround 1
Use IN SET instead of [NOT EQUAL] operator. The modified query for the above query is like:
query --query="select FieldA,FieldB,FieldC from /exampleRegion where FieldD<>'1' and FieldC IN SET('01','02','05')"

Workaround 2
Do not use a compact range index for the field (FieldC). Remove the index (example, RegionFieldCIndex) or change the compact range index to another index type such as a hash index.