Juan José Ramos Cassella created GEODE-6806:
-----------------------------------------------
Summary: Wrong Results in multi OQL joins + Indexes + LIMIT clause
Key: GEODE-6806
URL: https://issues.apache.org/jira/browse/GEODE-6806
Project: Geode
Issue Type: Bug
Components: querying
Reporter: Juan José Ramos Cassella
When joining multiple regions with indexes defined on them, the results of the
query might be wrong if the {{LIMIT}} keyword is used in the {{OQL}} and the
intermediate result size of the query is greater than the actual {{LIMIT}} used.
Internally we get the _partial_ results from the {{indexes}} and _cut down_
these intermediate results during the actual execution. With the goal of
speeding things up, we use the {{LIMIT}} value to stop the iteration sooner but
we do not know if the first X results (governed by the {{LIMIT}} value) might
or might not fulfil the rest of the operands.
The {{LIMIT}} clause is _disabled_ at the {{index}} level but still enabled in
this intermediate step, which is causing the final result to not contain
certain rows that should be included.
The following example reproduces the issue (doesn't matter whether {{PDX}} is
used or not):
{code:java}
private void populateTripleJointRegions(int expectedMatches, int
extraEntitiesPerRegion, Cache cache, Region<String, Object> orderRegion,
Region<String, Object> validationIssueRegion, Region<String, Object>
validationIssueXRefRegion) {
for (int i = 0; i < expectedMatches; i++) {
PdxInstance orderPdx = cache
.createPdxInstanceFactory("org.apache.geode.test.Order")
.writeString("orderId", "orderId_" + i)
.writeInt("version", i)
.create();
PdxInstance validationIssuePdx = cache
.createPdxInstanceFactory("org.apache.geode.test.ValidationIssue")
.writeString("issueId", "issueId_" + i)
.writeDate("createdTime", Calendar.getInstance().getTime())
.create();
PdxInstance validationIssueXRefPdx = cache
.createPdxInstanceFactory("org.apache.geode.test.OrderValidationIssueXRef")
.writeString("validationIssueXRefID", "validationIssueXRefID_" + i)
.writeString("referenceIssueId", "issueId_" + i)
.writeString("referenceOrderId", "orderId_" + i)
.writeInt("referenceOrderVersion", i)
.create();
orderRegion.put("orderId_" + i, orderPdx);
validationIssueRegion.put("issueId_" + i , validationIssuePdx);
validationIssueXRefRegion.put("validationIssueXRefID_" + i,
validationIssueXRefPdx);
}
for (int i = 0; i < extraEntitiesPerRegion; i++) {
PdxInstance orderPdx = cache
.createPdxInstanceFactory("org.apache.geode.test.Order")
.writeString("orderId", "orderId#" + i)
.writeInt("version", i)
.create();
PdxInstance validationIssuePdx = cache
.createPdxInstanceFactory("org.apache.geode.test.ValidationIssue")
.writeString("referenceIssueId", "referenceIssueId#" + i)
.writeDate("createdTime", Calendar.getInstance().getTime())
.create();
PdxInstance validationIssueXRefPdx = cache
.createPdxInstanceFactory("org.apache.geode.test.OrderValidationIssueXRef")
.writeString("validationIssueXRefID", "validationIssueXRefID#" + i)
.writeString("referenceIssueId", "validationIssueID2#" + i)
.writeString("referenceOrderId", "orderId#2" + i)
.writeInt("referenceOrderVersion", i)
.create();
orderRegion.put("orderId#" + i, orderPdx);
validationIssueRegion.put("issueId#" + i , validationIssuePdx );
validationIssueXRefRegion.put("validationIssueXRefID#" + i,
validationIssueXRefPdx);
}
}
@Test
public void
joiningThreeRegionsWhenIntermediateResultSizeIsHigherThanLimitClauseShouldNotTrimResults()
throws Exception {
int matches = 10;
int extraEntitiesPerRegion = 25;
Cache cache = CacheUtils.getCache();
QueryService queryService = cache.getQueryService();
String queryString = "SELECT issue.issueId, issue.createdTime, o.orderId,
o.version "
+ "FROM /ValidationIssue issue, /OrderValidationIssueXRef xRef, /Order
o "
+ "WHERE "
+ "issue.issueId = xRef.referenceIssueId "
+ "AND "
+ "xRef.referenceOrderId = o.orderId "
+ "AND "
+ "xRef.referenceOrderVersion = o.version ";
try {
Region<String, Object> orderRegion = cache.<String,
Object>createRegionFactory()
.setDataPolicy(DataPolicy.REPLICATE).create("Order");
Region<String, Object> validationIssueRegion = cache.<String,
Object>createRegionFactory()
.setDataPolicy(DataPolicy.REPLICATE).create("ValidationIssue");
Region<String, Object> validationIssueXRefRegion = cache.<String,
Object>createRegionFactory()
.setDataPolicy(DataPolicy.REPLICATE).create("OrderValidationIssueXRef");
populateTripleJointRegions(matches, extraEntitiesPerRegion,
orderRegion, validationIssueRegion, validationIssueXRefRegion);
SelectResults baseResults = (SelectResults)
queryService.newQuery(queryString).execute();
SelectResults resultsWithLimitOne = (SelectResults)
queryService.newQuery(queryString + "LIMIT 2").execute();
SelectResults resultsWithLimitTwo = (SelectResults)
queryService.newQuery(queryString + "LIMIT 5").execute();
assertThat(baseResults.size()).isEqualTo(matches);
assertThat(resultsWithLimitOne.size()).isEqualTo(2);
assertThat(resultsWithLimitTwo.size()).isEqualTo(5);
// Add Indexes and test again
cache.getQueryService().createIndex("order_orderID", "orderId", "/Order",
null);
cache.getQueryService().createIndex("validationIssue_issueID", "issueId",
"/ValidationIssue", null);
cache.getQueryService().createIndex("orderValidationIssueXRef_referenceOrderId",
"referenceOrderId", "/OrderValidationIssueXRef", null);
cache.getQueryService().createIndex("orderValidationIssueXRef_referenceIssueId",
"referenceIssueId", "/OrderValidationIssueXRef", null);
SelectResults baseResultsWithIndexes = (SelectResults)
queryService.newQuery(queryString).execute();
SelectResults resultsWithLimitOneWithIndexes = (SelectResults)
queryService.newQuery(queryString + "LIMIT 2").execute();
SelectResults resultsWithLimitTwoWithIndexes = (SelectResults)
queryService.newQuery(queryString + "LIMIT 5").execute();
assertThat(baseResultsWithIndexes.size()).isEqualTo(matches);
// FAIL: the actual size will be 0 in both cases.
assertThat(resultsWithLimitOneWithIndexes.size()).isEqualTo(2);
assertThat(resultsWithLimitTwoWithIndexes.size()).isEqualTo(5);
} finally {
cache.getRegion("Order").destroyRegion();
cache.getRegion("ValidationIssue").destroyRegion();
cache.getRegion("OrderValidationIssueXRef").destroyRegion();
}
}
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
