Pivotal GemFire all versions prior to 18.104.22.168 that support Map Indexes
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.
This issue has been addressed by internal ticket GEM-952 and is fixed starting with the version 22.214.171.124 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 126.96.36.199 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 188.8.131.52.