Pivotal Knowledge Base

Follow

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

Environment 

 Product  Version
 Pivotal  GemFire  All versions prior to 8.2.1.2 which support Map  Indexes

Symptom

Suppose that you execute a complex query for a Map type object using Map Indexes, which 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. 

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.

 

Comments

Powered by Zendesk