[
https://issues.apache.org/jira/browse/GEODE-6806?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dick Cavender closed GEODE-6806.
--------------------------------
> 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 Ramos
> Assignee: Juan Ramos
> Priority: Major
> Labels: GeodeCommons
> Fix For: 1.10.0
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> 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
(v8.3.4#803005)
