Pivotal Knowledge Base


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


Pivotal GemFire 7.x-


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.


Region Definition:
<region name="exampleRegion">
<region-attributes refid="REPLICATE_PERSISTENT" statistics-enabled="true"
disk-store-name="dataStore" disk-synchronous="false">
<index name="exampleRegionFieldCIndex" from-clause="/exampleRegion ex" expression="ex.FieldC" />
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.


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.


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.



Powered by Zendesk