GemFire Complex Query for Map Type Object, with Adding Map Indexes, does not Return Expected Results

Products

• VMware Tanzu Gemfire

Issue/Introduction

Symptoms:

While executing a complex query for a Map type object using Map Indexes that involves AND and OR phrases like the following:

SELECT * FROM /CustomerRegion cr WHERE cr['lid']=5 AND cr['name']='John' AND (cr['job']='doctor' OR cr['job']='professor')

You may get no result set even if you actually have data entries in the target region which meet the given WHERE conditions. 

Environment

Resolution

This issue has been addressed by internal ticket GEM-952 and is fixed starting with the version 8.2.1.2 of GemFire and all subsequent versions. If it's difficult for you to transition to a later version with the given fix, you can potentially use one of the following workarounds until an upgrade to a later version is feasible:

• Remove Map Indexes from related regions. This workaround may introduce query performance issues.
• Rewrite your query using the IN SET phrase instead of the OR phrase as follows:

  SELECT * FROM /CustomerRegion cr WHERE cr['lid']=5 and cr['name']='John' and (cr['job'] IN SET ('doctor','professor'))

  NOTE: If incorporating this workaround using IN SET, you must first upgrade to GemFire 7.0.2.43 or later if currently using an earlier version. This is due to fixes related to IN SET as part of GEM-792 to resolve issues when using IN SET, that were incorporated into the product in 7.0.2.43.