gemfire-core/src/test/java/com/gemstone/gemfire/cache/query/internal/index/IndexHintJUnitTest.java

package com.gemstone.gemfire.cache.query.internal.index;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

import java.util.ArrayList;
import java.util.Set;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.experimental.categories.Category;

import com.gemstone.gemfire.cache.Region;
import com.gemstone.gemfire.cache.query.CacheUtils;
import com.gemstone.gemfire.cache.query.Index;
import com.gemstone.gemfire.cache.query.IndexExistsException;
import com.gemstone.gemfire.cache.query.IndexNameConflictException;
import com.gemstone.gemfire.cache.query.Query;
import com.gemstone.gemfire.cache.query.QueryService;
import com.gemstone.gemfire.cache.query.RegionNotFoundException;
import com.gemstone.gemfire.cache.query.SelectResults;
import com.gemstone.gemfire.cache.query.data.Portfolio;
import com.gemstone.gemfire.cache.query.functional.StructSetOrResultsSet;
import com.gemstone.gemfire.cache.query.internal.DefaultQuery;
import com.gemstone.gemfire.cache.query.internal.QueryExecutionContext;
import com.gemstone.gemfire.cache.query.internal.QueryObserverAdapter;
import com.gemstone.gemfire.cache.query.internal.QueryObserverHolder;
import com.gemstone.gemfire.test.junit.categories.IntegrationTest;

@Category(IntegrationTest.class)
public class IndexHintJUnitTest {
  private Region region;
  
  @Before
  public void setUp() throws Exception {
    System.setProperty("gemfire.Query.VERBOSE", "true");
    CacheUtils.startCache();
  }

  @After
  public void tearDown() throws Exception {
    CacheUtils.closeCache();
  }

  //tests the grammar for a hint with a single index name
  @Test
  public void testSingleIndexHint() throws Exception {
    createRegion();
    QueryService qs = CacheUtils.getQueryService();
    DefaultQuery query = (DefaultQuery) qs
        .newQuery("<hint 'FirstIndex'> select * from /Portfolios p where p.ID > 10");
    QueryExecutionContext qec = new QueryExecutionContext(new Object[1],
        CacheUtils.getCache(), query);
    query.executeUsingContext(qec);

    assertTrue(qec.isHinted("FirstIndex"));
    assertEquals(-1, qec.getHintSize("FirstIndex"));
  }
  
  //Tests the grammar for a hint with two index names
  @Test
  public void testTwoIndexHint() throws Exception {
    createRegion();
    QueryService qs = CacheUtils.getQueryService();
    DefaultQuery query = (DefaultQuery) qs
        .newQuery("<hint 'FirstIndex', 'SecondIndex'> select * from /Portfolios p where p.ID > 10");
    QueryExecutionContext qec = new QueryExecutionContext(new Object[1],
        CacheUtils.getCache(), query);
    query.executeUsingContext(qec);

    assertTrue(qec.isHinted("FirstIndex"));
    assertTrue(qec.isHinted("SecondIndex"));
  }
  
  //Tests that index hints are ordered correctly
  @Test
  public void testIndexHintOrdering() throws Exception {
    createRegion();
    QueryService qs = CacheUtils.getQueryService();
    DefaultQuery query = (DefaultQuery)qs.newQuery("<hint 'FirstIndex','SecondIndex','ThirdIndex','FourthIndex'>select * from /Portfolios p where p.ID > 10");
    QueryExecutionContext qec = new QueryExecutionContext(new Object[1], CacheUtils.getCache(), query);
    query.executeUsingContext(qec);
    
    assertTrue(qec.isHinted("FirstIndex"));
    assertTrue(qec.isHinted("SecondIndex"));
    assertTrue(qec.isHinted("ThirdIndex"));
    assertTrue(qec.isHinted("FourthIndex"));
    
    assertEquals(-4, qec.getHintSize("FirstIndex"));
    assertEquals(-3, qec.getHintSize("SecondIndex"));
    assertEquals(-2, qec.getHintSize("ThirdIndex"));
    assertEquals(-1, qec.getHintSize("FourthIndex"));
  }
  
  //Tests that even though we would have chosen the index anyways, that the hint still
  //allows us to chose the same index
  @Test
  public void testIndexUsageWithIndexHint() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("<hint 'IDIndex'>select * from /Portfolios p where p.ID > 10");
    query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
  }
  
  //Tests scenario where we provide a hint that is unuseable for the query
  //The other index should be used instead of the hint because the hint itself
  //would not have helped
  @Test
  public void testIndexUsageWithUnusableIndexHint() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.secId", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("<hint 'SecIndex'>select * from /Portfolios p where p.ID > 10");
    query.execute();
    //verify index usage
    assertFalse(observer.wasIndexUsed("SecIndex")); 
    assertTrue(observer.wasIndexUsed("IDIndex")); 
  }
  

  //given a choice between two indexes, we hint on each one in different queries and verify
  //that both indexes are used
  @Test
  public void testMultiIndexWithSingleIndexHint() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.status", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    SelectResults[][] results = new SelectResults[1][2];
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("<hint 'IDIndex'>select * from /Portfolios p where p.ID > 10 and p.status = 'inactive'");
    results[0][0] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
    observer.reset();
    
    query = qs.newQuery("<hint 'SecIndex'>select * from /Portfolios p where p.ID > 10 and p.status = 'inactive'");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("SecIndex")); 

    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    assertEquals(495, results[0][1].size());
    //Not really with and without index but we can use this method to verify they are the same results
    //regardless of which index used
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
  }
  
  //Using junction, we will hint and make sure single index hints are functioning
  @Test
  public void testMultiIndexWithSingleIndexHintWithRangeJunction() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.status", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    SelectResults[][] results = new SelectResults[1][2];
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("<hint 'IDIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive'");
    results[0][0] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
    observer.reset();

    query = qs.newQuery("<hint 'SecIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive'");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("SecIndex")); 
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    
    assertEquals(95, results[0][1].size());
    //Not really with and without index but we can use this method to verify they are the same results
    //regardless of which index used
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
  }
  
  //Using junction, we will hint and make sure multi index hints are functioning
  @Test
  public void testMultiIndexWithMultiIndexHint() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.status", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    SelectResults[][] results = new SelectResults[1][2];
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("<hint 'IDIndex', 'SecIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive'");
    results[0][0] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
    assertTrue(observer.wasIndexUsed("SecIndex")); 
    observer.reset();
    
    query = qs.newQuery("<hint 'IDIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive'");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    
    assertEquals(95, results[0][1].size());
    //Not really with and without index but we can use this method to verify they are the same results
    //regardless of which index used
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
  }
  
  @Test
  public void testIndexWithCompiledInSet() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.status", "/Portfolios p");
    createIndex("DescriptionIndex", "p.description", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    SelectResults[][] results = new SelectResults[1][2];
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("<hint 'IDIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN SET ('XXXX', 'XXXY')");
    results[0][0] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
    observer.reset();

    query = qs.newQuery("<hint 'SecIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN SET ('XXXX', 'XXXY')");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("SecIndex")); 
    observer.reset();

    //Compare results with the first two index queries
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();    
    assertEquals(95, results[0][1].size());
    //Not really with and without index but we can use this method to verify they are the same results
    //regardless of which index used
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
    
    query = qs.newQuery("<hint 'DescriptionIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN SET ('XXXX', 'XXXY')");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("DescriptionIndex")); 
    
    //Compare results with the final index result
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
  }
  
  @Test
  public void testHintSingleIndexWithCompiledIn() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.status", "/Portfolios p");
    createIndex("DescriptionIndex", "p.description", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    SelectResults[][] results = new SelectResults[1][2];
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("<hint 'IDIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN (select p.description from /Portfolios p where p.ID > 10)");
    results[0][0] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
    observer.reset();
    
    query = qs.newQuery("<hint 'SecIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN (select p.description from /Portfolios p where p.ID > 10)");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("SecIndex")); 
    observer.reset();
    
    //Compare results with the first two index queries
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    assertEquals(95, results[0][1].size());
    //Not really with and without index but we can use this method to verify they are the same results
    //regardless of which index used
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
    
    query = qs.newQuery("<hint 'DescriptionIndex'>select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN (select p.description from /Portfolios p where p.ID > 10) ");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("DescriptionIndex")); 
    
    //Compare results with the final index result
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
  }
  
  @Test
  public void testHintMultiIndexWithCompiledIn() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.status", "/Portfolios p");
    createIndex("DescriptionIndex", "p.description", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    SelectResults[][] results = new SelectResults[1][2];
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN (<hint 'IDIndex', 'SecIndex', 'DescriptionIndex'>select p.description from /Portfolios p where p.ID > 10)");
    results[0][0] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
    assertTrue(observer.wasIndexUsed("SecIndex")); 
    observer.reset();
    
    query = qs.newQuery("select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN (select p.description from /Portfolios p where p.ID > 10)");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("SecIndex")); 
    assertFalse(observer.wasIndexUsed("DescriptionIndex"));
    //We end up using IDIndex for this case.
    observer.reset();
    
    //Compare results with the first two index queries
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    assertEquals(95, results[0][1].size());
    //Not really with and without index but we can use this method to verify they are the same results
    //regardless of which index used
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
    
    query = qs.newQuery("select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN (<hint 'DescriptionIndex'>select p.description from /Portfolios p where p.ID > 10)");
    results[0][1] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("DescriptionIndex")); 
    assertFalse(observer.wasIndexUsed("SecIndex"));
    //We end up using IDIndex for this case also

    //Compare results with the final index result
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
  }
  
  //Hints inside of a nested query will trigger index usage for that query only
  //Unusable hints should behave the same for nested queries in that they are not used if unapplicable to the query
  @Test
  public void testHintNestedCompiledIn() throws Exception {
    createRegion();
    populateData(1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.status", "/Portfolios p");
    createIndex("DescriptionIndex", "p.description", "/Portfolios p");
    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    SelectResults[][] results = new SelectResults[1][2];
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN (<hint 'IDIndex', 'SecIndex', 'DescriptionIndex'>select p.description from /Portfolios p where p.ID > 10)");
    results[0][0] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("IDIndex")); 
    assertTrue(observer.wasIndexUsed("SecIndex")); 
    //Because it was a hint for the inner query, it was an unuseable hint for that query
    assertFalse(observer.wasIndexUsed("DescriptionIndex"));
    observer.reset();
    
    //query again with no hints for a "bare" comparison
    query = qs.newQuery("select * from /Portfolios p where p.ID > 10 and p.ID < 200 and p.status = 'inactive' and p.description IN (select p.description from /Portfolios p where p.ID > 10)");
    results[0][1] = (SelectResults) query.execute();
    observer.reset();
    
    //Compare results with the first two index queries
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    assertEquals(95, results[0][1].size());
    //Not really with and without index but we can use this method to verify they are the same results
    //regardless of which index used
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
  }
  
  @Test
  public void testJoinHint() throws Exception{
    createRegion();
    Region portfolios2 = createRegion("Portfolios_2");
    populateData(1000);
    populateData(portfolios2, 1000);
    //create index
    createIndex("IDIndex", "p.ID", "/Portfolios p");
    createIndex("SecIndex", "p.status", "/Portfolios p");
    createIndex("DescriptionIndex", "p.description", "/Portfolios p");
    
    createIndex("IDIndexOnPortfolios2", "p.ID", "/Portfolios_2 p");

    //set observer
    QueryObserverImpl observer = new QueryObserverImpl();
    QueryObserverHolder.setInstance(observer);
    //execute query
    SelectResults[][] results = new SelectResults[1][2];
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery("<hint 'SecIndex'>select * from /Portfolios p, /Portfolios_2 p2 where p.ID = p2.ID and p.status = 'inactive'");
    results[0][0] = (SelectResults) query.execute();
    //verify index usage
    assertTrue(observer.wasIndexUsed("SecIndex")); 
    observer.reset();
    
    //query again with no hints for a "bare" comparison
    query = qs.newQuery("select * from /Portfolios p, /Portfolios_2 p2 where p.ID = p2.ID and p.status = 'inactive'");
    results[0][1] = (SelectResults) query.execute();
    observer.reset();
    
    //Compare results with the first two index queries
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    assertEquals(500, results[0][1].size());
    //Not really with and without index but we can use this method to verify they are the same results
    //regardless of which index used
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(results, 1, new String[]{"<query with hints>"});
 
  }
  
  class QueryObserverImpl extends QueryObserverAdapter
  {
    ArrayList indexesUsed = new ArrayList();

    public void beforeIndexLookup(Index index, int oper, Object key) {
      indexesUsed.add(index.getName());
    }
    
    public void beforeIndexLookup(Index index, int lowerBoundOperator,
        Object lowerBoundKey, int upperBoundOperator, Object upperBoundKey,
        Set NotEqualKeys) {
      indexesUsed.add(index.getName());
    }
    
    public boolean wasIndexUsed(String indexName) {
      return indexesUsed.contains(indexName);
    }
    
    public void reset() {
      indexesUsed.clear();
    }
  }
  
  private Region createRegion() {
    region = createRegion("Portfolios");
    return region;
  }
  
  private Region createRegion(String regionName) {
    return CacheUtils.createRegion(regionName, Portfolio.class);
  }
  
  private void populateData(int numObjects) {
    populateData(region, numObjects);
  }
  
  private void populateData(Region region, int numObjects) {
    for (int i = 0; i < numObjects; i++) {
      region.put("" + i, new Portfolio(i));
    }
  }
  
  private void createIndex(String indexName, String indexedExpression, String regionPath) throws RegionNotFoundException, IndexExistsException, IndexNameConflictException{
    CacheUtils.getQueryService().createIndex(indexName, indexedExpression, regionPath);
  }
}