Partitioned Region Query Restrictions

This topic lists partitioned region query restrictions in VMware Tanzu GemFire.

Query Restrictions in Partitioned Regions

Partitioned region queries function the same as non-partitioned region queries, except for the restrictions listed in this section. Partitioned region queries that do not follow these guidelines generate an UnsupportedOperationException.

Join queries between partitioned region and between partitioned regions and replicated regions are supported through the function service only. Join queries partitioned regions are not supported through the client server API.
You can run join queries on partitioned regions and on partitioned regions and replicated regions only if they are co-located. Equi-join queries are supported only on partitioned regions that are co-located and where the co-located columns are indicated in the WHERE clause of the query. In the case of multi-column partitioning, there should also be an AND clause in the WHERE specification. See Colocate Data from Different Partitioned Regions for more information about partitioned region co-location.
Equi-join queries are allowed between partitioned regions and between partitioned regions and local replicated regions as long as the local replicated region also exists on all partitioned region nodes. To perform a join query on a partitioned region and another region (partitioned or not), you need to use the query.execute method and supply it with a function execution context. See Performing an Equi-Join Query on Partitioned Regions for an example.
The query must be just a SELECT expression (as opposed to arbitrary OQL expressions), preceded by zero or more IMPORT statements. For example, this query is not allowed because it is not just a SELECT expression:
```
// NOT VALID for partitioned regions
(SELECT DISTINCT *FROM /prRgn WHERE attribute > 10).size
```
This query is allowed:
```
// VALID for partitioned regions
SELECT DISTINCT *FROM /prRgn WHERE attribute > 10
```
The SELECT expression itself can be arbitrarily complex, including nested SELECT expressions, as long as only one partitioned region is referenced.
The partitioned region reference can only be in the first FROM clause iterator. Additional FROM clause iterators are allowed if they do not reference any regions (such as drilling down into the values in the partitioned region).
The first FROM clause iterator must contain only one reference to the partitioned region (the reference can be a parameter, such as $1).
The first FROM clause iterator cannot contain a subquery, but subqueries are allowed in additional FROM clause iterators.
You can use ORDER BY on partitioned region queries, but the fields that are specified in the ORDER BY clause must be part of the projection list.
If a partition region (or a bucket) being queried has been destroyed, the query is reattempted on the new primary for the destroyed bucket (if it exists). After certain number of attempts, a QueryException is thrown if all buckets (calculated at the startup of the query) cannot be queried.