Pivotal Knowledge Base

Follow

Using the NOT EQUAL operators in a WHERE clause returns the wrong query result set

Environment

Product Version
Pivotal GemFire 7.x - 8.2.1.1

Symptom

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.


For 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.

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(exampleRegionFieldCIndex) or change the compact range index to another index type such as a hash index.

 

Comments

Powered by Zendesk