[GEODE-3707] Join queries with two join conditions return duplicate results when executed with indexes

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.3.0
• Component/s: querying
• Assignee: Unassigned
• Reporter: Nabarun Nag
• Created: 26/Sep/17 23:54
• Updated: 27/Sep/17 22:43
• Resolved: 27/Sep/17 22:42

Description

Acceptance: the test below must return the same number of results

Customer.java

public static class Customer implements Serializable {
    public int pkid;
    public int id;
    public int joinId;
    public String name;
    public Map<String, Customer> nested = new HashMap<String, Customer>();

    public Customer(int pkid, int id) {
      this.pkid = pkid;
      this.id = id;
      this.joinId = id;
      this.name = "name" + pkid;
    }

    public Customer(int pkid, int id, int joinId) {
      this.pkid = pkid;
      this.id = id;
      this.joinId = joinId;
      this.name = "name" + pkid;
    }

    public String toString() {
      return "Customer pkid = " + pkid + ", id: " + id + " name:" + name;
    }
  }

Test.java

@Test
public void testJoinTwoRegions(/*String queryString, int resultSize*/) throws Exception {
  Cache cache = getCache();
  Region region1 =
      cache.createRegionFactory().setDataPolicy(DataPolicy.REPLICATE).create("region1");
  Region region2 =
      cache.createRegionFactory().setDataPolicy(DataPolicy.REPLICATE).create("region2");
  for (int i = 1; i < 11; i++) {
    region1.put(i, new EquiJoinIntegrationTest.Customer(i, i, i));
    if(i == 1 || i == 4 || i == 7 || i == 10){
      region2.put(i, new EquiJoinIntegrationTest.Customer(1, 1, 1));
    }else {
      region2.put(i, new EquiJoinIntegrationTest.Customer(i % 5, i, i % 3));
    }


  String queryString = "<trace>select STA.id as STACID, STA.pkid as STAacctNum, STC.id as STCCID, STC.pkid as STCacctNum from /region1 STA, /region2 STC where STA.pkid = 1 AND STA.joinId = STC.joinId or STA.id = STC.id";
  int resultSize = 1;
  QueryService queryService = cache.getQueryService();

  SelectResults results = (SelectResults) queryService.newQuery(queryString).execute();
  int resultSizeWithoutIndex =  results.size();

  
  queryService.createIndex("pkidregion1", "p.pkid", "/region1 p");
  queryService.createIndex("pkidregion2", "p.pkid", "/region2 p");
  queryService.createIndex("indexIDRegion2","p.id", "/region2 p");
  queryService.createIndex("indexIDRegion1","p.id", "/region1 p");
  queryService.createIndex("joinIdregion1", "p.joinId", "/region1 p");
  queryService.createIndex("joinIdregion2", "p.joinId", "/region2 p");
  queryService.createIndex("nameIndex", "p.name", "/region2 p");

  results = (SelectResults) queryService.newQuery(queryString).execute();
  int resultSizeWithIndex = results.size();

  assertEquals(resultSizeWithIndex, resultSizeWithoutIndex);
}