LATEST VERSION: 8.2.7 - CHANGELOG
Pivotal GemFire® v8.2

Performing an Equi-Join Query on Partitioned Regions

Performing an Equi-Join Query on Partitioned Regions

In order to perform equi-join operations on partitioned regions or partitioned regions and replicated regions, you need to use the query.execute method and supply it with a function execution context. You need to use GemFire's FunctionService executor because join operations are not yet directly supported for partitioned regions without providing a function execution context.

See Partitioned Region Query Restrictions for more information on partitioned region query limitations.

For example, let's say your equi-join query is the following:
SELECT DISTINCT * FROM /QueryRegion1 r1,
/QueryRegion2 r2 WHERE r1.ID = r2.ID
In this example QueryRegion2 is colocated with QueryRegion1, and both regions have same type of data objects.

On the server side:
 Function prQueryFunction1 = new QueryFunction();
 FunctionService.registerFunction(prQueryFunction1);

 public class QueryFunction extends FunctionAdapter {
    @Override
    public void execute(FunctionContext context) {
      Cache cache = CacheFactory.getAnyInstance();
      QueryService queryService = cache.getQueryService();
      ArrayList allQueryResults = new ArrayList();
      ArrayList arguments = (ArrayList)(context.getArguments());
      String qstr = (String)arguments.get(0);
      try {
           Query query = queryService.newQuery(qstr);
           SelectResults result = (SelectResults)query
             .execute((RegionFunctionContext)context);
           ArrayList arrayResult = (ArrayList)result.asList();
           context.getResultSender().sendResult((ArrayList)result.asList());
           context.getResultSender().lastResult(null);
	          } catch (Exception e) {
	           // handle exception
             }
       }
} 
	 
On the server side, Query.execute() operates on the local data of the partitioned region.

On the client side:
 
Function function = new QueryFunction();
String queryString = "SELECT DISTINCT * FROM /QueryRegion1 r1,
		/QueryRegion2 r2 WHERE r1.ID = r2.ID";
ArrayList argList = new ArrayList();
argList.add(queryString);
Object result = FunctionService.onRegion(CacheFactory.getAnyInstance()
     .getRegion("QueryRegion1" ))
     .withArgs(argList).execute(function).getResult();
ArrayList resultList = (ArrayList)result;
resultList.trimToSize();
List queryResults = null;
if (resultList.size() != 0) {
   queryResults = new ArrayList();
   for (Object obj : resultList) {
      if (obj != null ) {
      queryResults.addAll((ArrayList)obj);
	     }
   }
}

On the client side, note that you can specify a bucket filter while invoking FunctionService.onRegion(). In this case, the query engine relies on FunctionService to direct the query to specific nodes.

Additional Notes on Using the Query.execute and RegionFunctionContext APIs

You can also pass multiple parameters (besides the query itself) to the query function by specifying the parameters in the client-side code (FunctionService.onRegion(..).withArgs()). Then you can handle the parameters inside the function on the server side using context.getArguments. Note that it does not matter which order you specify the parameters as long as you match the parameter handling order on the server with the order specified in the client.