geode-core/src/integrationTest/java/org/apache/geode/cache/query/internal/index/IndexHintJUnitTest.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more contributor license
 * agreements. See the NOTICE file distributed with this work for additional information regarding
 * copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance with the License. You may obtain a
 * copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License
 * is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
 * or implied. See the License for the specific language governing permissions and limitations under
 * the License.
 */
package org.apache.geode.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 javax.transaction.TransactionManager;

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

import org.apache.geode.cache.Region;
import org.apache.geode.cache.query.CacheUtils;
import org.apache.geode.cache.query.Index;
import org.apache.geode.cache.query.IndexExistsException;
import org.apache.geode.cache.query.IndexNameConflictException;
import org.apache.geode.cache.query.Query;
import org.apache.geode.cache.query.QueryService;
import org.apache.geode.cache.query.RegionNotFoundException;
import org.apache.geode.cache.query.SelectResults;
import org.apache.geode.cache.query.data.Portfolio;
import org.apache.geode.cache.query.functional.StructSetOrResultsSet;
import org.apache.geode.cache.query.internal.DefaultQuery;
import org.apache.geode.cache.query.internal.QueryExecutionContext;
import org.apache.geode.cache.query.internal.QueryObserverAdapter;
import org.apache.geode.cache.query.internal.QueryObserverHolder;
import org.apache.geode.distributed.internal.DistributionConfig;
import org.apache.geode.internal.cache.InternalCache;
import org.apache.geode.internal.cache.TXManagerImpl;
import org.apache.geode.test.junit.categories.OQLIndexTest;

@Category({OQLIndexTest.class})
public class IndexHintJUnitTest {
  private Region region;

  @Before
  public void setUp() throws Exception {
    System.setProperty(DistributionConfig.GEMFIRE_PREFIX + "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();
    verifyQueryIndexHint();
  }

  @Test
  public void testSingleIndexHintWithTx() throws Exception {
    createRegion();
    populateData(10);
    TXManagerImpl txManager = CacheUtils.getCache().getTxManager();
    try {
      txManager.begin();
      verifyQueryIndexHint();
    } finally {
      txManager.commit();
    }
  }


  @Test
  public void testSingleIndexHintWithJTANotStarted() throws Exception {
    createRegion();
    populateData(10);
    InternalCache cache = CacheUtils.getCache();
    TransactionManager jtaManager =
        (TransactionManager) cache.getJNDIContext().lookup("java:/TransactionManager");
    try {
      jtaManager.begin();
      verifyQueryIndexHint();
    } finally {
      jtaManager.commit();
    }
  }

  @Test
  public void testSingleIndexHintWithJTAStarted() throws Exception {
    createRegion();
    populateData(1);
    InternalCache cache = CacheUtils.getCache();
    TransactionManager jtaManager =
        (TransactionManager) cache.getJNDIContext().lookup("java:/TransactionManager");
    try {
      jtaManager.begin();
      region.get("1");
      verifyQueryIndexHint();
    } finally {
      jtaManager.commit();
    }
  }

  private void verifyQueryIndexHint() throws Exception {
    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();

    @Override
    public void beforeIndexLookup(Index index, int oper, Object key) {
      indexesUsed.add(index.getName());
    }

    @Override
    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);
  }
}