Pivotal Knowledge Base


OQL on Nested Map Returns Incorrect Results


Pivotal GemFire 8.x and 9.x


When executing an OQL query on which there is a filter on a field that has more than one nested Map<K, V>, the returned ResultSet contains unwanted records that don't match the query criteria. Use the following domain class:

public class CacheValue implements Serializable {
    private static final long serialVersionUID = 5601079927357741350L;
    private String key;
    private final Map<String, Map<String, String>> data;

     * @return the key
    public String getKey() {
      return key;

     * @param key the key to set
    public void setKey(String key) {
      this.key = key;

     * @return the data
    public Map<String, Map<String, String>> getData() {
      return data;

     * @param key
     * @param data
    public CacheValue(String key, Map<String, Map<String, String>> data) {
      this.key = key;
      this.data = data;

And, assuming that the region testRegion has the following entries:

CacheValue [key=empty, data={}]
CacheValue [key=match, data={KEY1={employeeOne=name1}}]
CacheValue [key=nonMatch, data={KEY2={employeeOne=name2}}]

The query SELECT * FROM /testRegion p WHERE p.data['KEY1'][employeeOne'] != null returns all entries instead of just the single one that matches, namely CacheValue [key=match].


A couple of years ago, when GemFire 7.0.2 was the latest release, there was an issue related to OQL consistency. The query results used to contain UNDEFINED objects in the ResultSet when using indexes, but the same query without the usage of indexes didn't include those instances. At that time the OQL engine wasn't working consistently and, so, a decision was made to include the special UNDEFINED object when executing not equality comparisons, both with and without indexes.

This change affects the query from the example because p.data['KEY1'] evaluates as NULL for the non-matching entries. The nested Map.get() operation executed afterwards for the key employeeOne is executed on the result of p.data['KEY1'], which is NULL, and ends up evaluated as UNDEFINED because the engine is trying to extract a field from a NULL value. Later on within the execution, UNDEFINED != NULL is evaluated as true and, thus, the entry is added to the final ResultSet.

Unfortunately, the relevant documentation updates were not made and, thus, we are now a little inconsistent in the User Guide regarding this subject:

The equal and not equal operators have lower precedence than the other comparison operators. They can be used with null. To perform equality or inequality comparisons with UNDEFINED, use the IS_DEFINED and IS_UNDEFINED preset query functions instead of these comparison operators.

The documentation implies that UNDEFINED would not be matched in equality and inequality matches, which is not accurate. There's already an internal JIRA ticket to address the documentation issue: GEM-1922.


The suggested workaround implies adding the IS_DEFINED and AND clauses to the query condition, so they return the correct result.

So, going back to the example above, the query needs to be modified from:

SELECT * FROM /testRegion p WHERE p.data['KEY1'][employeeOne'] != null


SELECT * FROM /testRegion p WHERE IS_DEFINED(p.data['KEY1'][employeeOne']) AND p.data['KEY1'][employeeOne'] != null



Powered by Zendesk