/*
 * 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.functional;

import static org.apache.geode.cache.Region.SEPARATOR;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

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.IndexInvalidException;
import org.apache.geode.cache.query.IndexNameConflictException;
import org.apache.geode.cache.query.IndexType;
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.Struct;
import org.apache.geode.cache.query.data.Portfolio;
import org.apache.geode.cache.query.data.Position;
import org.apache.geode.cache.query.internal.QueryObserverAdapter;
import org.apache.geode.cache.query.internal.QueryObserverHolder;

public abstract class NonDistinctOrderByTestImplementation {

  @Before
  public void setUp() throws java.lang.Exception {
    CacheUtils.startCache();
  }

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



  public abstract Region createRegion(String regionName, Class valueConstraint);

  public abstract Index createIndex(String indexName, IndexType indexType, String indexedExpression,
      String fromClause) throws IndexInvalidException, IndexNameConflictException,
      IndexExistsException, RegionNotFoundException, UnsupportedOperationException;

  public abstract Index createIndex(String indexName, String indexedExpression, String regionPath)
      throws IndexInvalidException, IndexNameConflictException, IndexExistsException,
      RegionNotFoundException, UnsupportedOperationException;

  public abstract boolean assertIndexUsedOnQueryNode();

  @Test
  public void testOrderByWithIndexResultDefaultProjection() throws Exception {
    String[] queries = {
        // Test case No. IUMR021
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID > 10 order by ID desc ",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID > 10 order by ID asc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID asc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID desc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID desc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID asc",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID != 10 order by ID asc ",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID != 10 order by ID desc ",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID > 10 order by ID desc limit 5",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID > 10 order by ID asc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID asc limit 5 ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID desc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID desc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID asc limit 5",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID != 10 order by ID asc limit 10",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID != 10 order by ID desc limit 10",
        "SELECT   *  FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 0 order by ID desc, pkid desc "};

    Object[][] r = new Object[queries.length][2];
    QueryService qs;
    qs = CacheUtils.getQueryService();
    Position.resetCounter();
    // Create Regions

    Region r1 = createRegion("portfolio1", Portfolio.class);

    for (int i = 1; i < 200; ++i) {
      Portfolio pf = new Portfolio(i);
      pf.shortID = (short) ((short) i / 5);
      r1.put("" + i, pf);
    }

    // Execute Queries without Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        r[i][0] = q.execute();
      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    // Create Indexes

    createIndex("IDIndexPf1", IndexType.FUNCTIONAL, "ID", SEPARATOR + "portfolio1");

    // Execute Queries with Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        QueryObserverImpl observer = new QueryObserverImpl();
        QueryObserverHolder.setInstance(observer);
        r[i][1] = q.execute();
        SelectResults rcw = (SelectResults) r[i][1];
        int indexLimit = queries[i].indexOf("limit");
        int limit = -1;
        boolean limitQuery = indexLimit != -1;
        if (limitQuery) {
          limit = Integer.parseInt(queries[i].substring(indexLimit + 5).trim());
        }
        assertTrue("Result size is " + rcw.size() + " and limit is " + limit,
            !limitQuery || rcw.size() <= limit);

        if (!rcw.getCollectionType().isOrdered()) {
          fail("The collection type=" + rcw.getCollectionType().getSimpleClassName()
              + "  is not ordered");
        }
        if (assertIndexUsedOnQueryNode() && !observer.isIndexesUsed) {
          fail("Index is NOT uesd");
        }

        Iterator itr = observer.indexesUsed.iterator();
        while (itr.hasNext()) {
          if (!(itr.next().toString()).equals("IDIndexPf1")) {
            fail("<IDIndexPf1> was expected but found " + itr.next().toString());
          }
          // assertIndexDetailsEquals("statusIndexPf1",itr.next().toString());
        }

        int indxs = observer.indexesUsed.size();

        System.out.println("**************************************************Indexes Used :::::: "
            + indxs + " Index Name: " + observer.indexName);

      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(r, queries.length, true, queries);
    // Compare each of the query results with queries fired without order by ,
    // but without order by
    ssOrrs.compareExternallySortedQueriesWithOrderBy(queries, r);
  }

  @Test
  public void testOrderByWithIndexResultWithProjection() throws Exception {
    String[] queries = {
        // Test case No. IUMR021
        "SELECT   shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by shortID desc ",
        "SELECT   shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by shortID asc ",
        "SELECT   shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by shortID asc ",
        "SELECT   shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by shortID desc ",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by shortID desc ",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by shortID asc",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by shortID asc ",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by shortID desc ",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by shortID desc limit 5",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by shortID asc limit 5",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by shortID asc limit 5 ",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by shortID desc limit 5",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by shortID desc limit 5",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by shortID asc limit 5",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by shortID asc limit 10",
        "SELECT  shortID, description FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by shortID desc limit 10",

    };
    Object[][] r = new Object[queries.length][2];
    QueryService qs;
    qs = CacheUtils.getQueryService();
    Position.resetCounter();
    // Create Regions

    Region r1 = createRegion("portfolio1", Portfolio.class);

    for (int i = 1; i < 200; ++i) {
      Portfolio pf = new Portfolio(i);
      pf.shortID = (short) ((short) i / 5);
      r1.put("" + i, pf);
    }

    // Execute Queries without Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        r[i][0] = q.execute();
      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    // Create Indexes

    createIndex("IDIndexPf1", IndexType.FUNCTIONAL, "ID", SEPARATOR + "portfolio1");

    // Execute Queries with Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        QueryObserverImpl observer = new QueryObserverImpl();
        QueryObserverHolder.setInstance(observer);
        r[i][1] = q.execute();
        SelectResults rcw = (SelectResults) r[i][1];
        int indexLimit = queries[i].indexOf("limit");
        int limit = -1;
        boolean limitQuery = indexLimit != -1;
        if (limitQuery) {
          limit = Integer.parseInt(queries[i].substring(indexLimit + 5).trim());
        }
        assertTrue(!limitQuery || rcw.size() <= limit);
        // assertIndexDetailsEquals("Set",rcw.getCollectionType().getSimpleClassName());
        if (!rcw.getCollectionType().isOrdered()) {
          fail("The collection type=" + rcw.getCollectionType().getSimpleClassName()
              + "  is not ordered");
        }

        if (assertIndexUsedOnQueryNode() && !observer.isIndexesUsed) {
          fail("Index is NOT uesd");
        }

        Iterator itr = observer.indexesUsed.iterator();
        while (itr.hasNext()) {
          if (!(itr.next().toString()).equals("IDIndexPf1")) {
            fail("<IDIndexPf1> was expected but found " + itr.next().toString());
          }
          // assertIndexDetailsEquals("statusIndexPf1",itr.next().toString());
        }

        int indxs = observer.indexesUsed.size();

        System.out.println("**************************************************Indexes Used :::::: "
            + indxs + " Index Name: " + observer.indexName);

      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(r, queries.length, true, queries);
    ssOrrs.compareExternallySortedQueriesWithOrderBy(queries, r);
  }

  @Test
  public void testMultiColOrderByWithIndexResultDefaultProjection() throws Exception {
    String[] queries = {
        // Test case No. IUMR021
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID > 10 order by ID desc, pkid asc ",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID > 10 order by ID asc, pkid desc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID asc, pkid asc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID desc , pkid desc",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID desc, pkid desc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID asc, pkid asc",
        "SELECT   * FROM " + SEPARATOR + "portfolio1 pf1 where ID != 10 order by ID asc, pkid asc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by ID desc, pkid desc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by ID desc, pkid asc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by ID asc, pkid desc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID asc, pkid asc limit 5 ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID desc, pkid desc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID desc, pkid asc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID asc, pkid desc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by ID asc, pkid asc limit 10",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by ID desc, pkid desc limit 10",

    };
    Object[][] r = new Object[queries.length][2];
    QueryService qs;
    qs = CacheUtils.getQueryService();
    Position.resetCounter();
    // Create Regions
    Region r1 = createRegion("portfolio1", Portfolio.class);

    for (int i = 1; i < 200; ++i) {
      Portfolio pf = new Portfolio(i);
      pf.shortID = (short) ((short) i / 5);
      r1.put("" + i, pf);
    }

    // Execute Queries without Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        r[i][0] = q.execute();
      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    // Create Indexes

    createIndex("IDIndexPf1", IndexType.FUNCTIONAL, "ID", SEPARATOR + "portfolio1");

    // Execute Queries with Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        QueryObserverImpl observer = new QueryObserverImpl();
        QueryObserverHolder.setInstance(observer);
        r[i][1] = q.execute();
        SelectResults rcw = (SelectResults) r[i][1];
        int indexLimit = queries[i].indexOf("limit");
        int limit = -1;
        boolean limitQuery = indexLimit != -1;
        if (limitQuery) {
          limit = Integer.parseInt(queries[i].substring(indexLimit + 5).trim());
        }
        assertTrue(!limitQuery || rcw.size() <= limit);
        assertTrue(rcw.getCollectionType().isOrdered());
        if (assertIndexUsedOnQueryNode() && !observer.isIndexesUsed) {
          fail("Index is NOT uesd");
        }

        Iterator itr = observer.indexesUsed.iterator();
        while (itr.hasNext()) {
          if (!(itr.next().toString()).equals("IDIndexPf1")) {
            fail("<IDIndexPf1> was expected but found " + itr.next().toString());
          }
          // assertIndexDetailsEquals("statusIndexPf1",itr.next().toString());
        }

        int indxs = observer.indexesUsed.size();

        System.out.println("**************************************************Indexes Used :::::: "
            + indxs + " Index Name: " + observer.indexName);

      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(r, queries.length, true, queries);
    ssOrrs.compareExternallySortedQueriesWithOrderBy(queries, r);
  }

  @Test
  public void testMultiColOrderByWithIndexResultWithProjection() throws Exception {
    String[] queries = {
        // Test case No. IUMR021
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by ID desc, pkid desc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by ID asc, pkid asc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID asc, pkid asc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID desc , pkid desc",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID desc, pkid asc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID asc, pkid desc",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by ID asc , pkid desc",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by ID desc, pkid asc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by ID desc, pkid desc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 order by ID asc, pkid asc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID asc, pkid desc limit 5 ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID > 10 and ID < 20 order by ID desc, pkid asc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID desc, pkid desc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID asc, pkid asc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by ID asc , pkid desc limit 10",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where ID != 10 order by ID desc, pkid desc limit 10",

    };
    Object[][] r = new Object[queries.length][2];
    QueryService qs;
    qs = CacheUtils.getQueryService();
    Position.resetCounter();
    // Create Regions

    Region r1 = createRegion("portfolio1", Portfolio.class);

    for (int i = 1; i < 200; ++i) {
      Portfolio pf = new Portfolio(i);
      pf.shortID = (short) ((short) i / 5);
      r1.put("" + i, pf);
    }

    // Execute Queries without Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        r[i][0] = q.execute();
      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    // Create Indexes

    createIndex("IDIndexPf1", IndexType.FUNCTIONAL, "ID", SEPARATOR + "portfolio1");

    // Execute Queries with Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        QueryObserverImpl observer = new QueryObserverImpl();
        QueryObserverHolder.setInstance(observer);
        r[i][1] = q.execute();
        SelectResults rcw = (SelectResults) r[i][1];
        int indexLimit = queries[i].indexOf("limit");
        int limit = -1;
        boolean limitQuery = indexLimit != -1;
        if (limitQuery) {
          limit = Integer.parseInt(queries[i].substring(indexLimit + 5).trim());
        }
        assertTrue(!limitQuery || rcw.size() <= limit);
        assertTrue(rcw.getCollectionType().isOrdered());
        if (assertIndexUsedOnQueryNode() && !observer.isIndexesUsed) {
          fail("Index is NOT uesd");
        }

        Iterator itr = observer.indexesUsed.iterator();
        while (itr.hasNext()) {
          if (!(itr.next().toString()).equals("IDIndexPf1")) {
            fail("<IDIndexPf1> was expected but found " + itr.next().toString());
          }
          // assertIndexDetailsEquals("statusIndexPf1",itr.next().toString());
        }

        int indxs = observer.indexesUsed.size();

        System.out.println("**************************************************Indexes Used :::::: "
            + indxs + " Index Name: " + observer.indexName);

      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(r, queries.length, true, queries);
    ssOrrs.compareExternallySortedQueriesWithOrderBy(queries, r);
  }

  @Test
  public void testMultiColOrderByWithMultiIndexResultDefaultProjection() throws Exception {
    String[] queries = {
        // Test case No. IUMR021
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '12' and ID > 10 order by ID desc, pkid asc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '1' and ID > 10 order by ID asc, pkid desc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '13'and  ID > 10 and ID < 20 order by ID asc, pkid asc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid <'9' and ID > 10 and ID < 20 order by ID desc , pkid desc",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '15' and ID >= 10 and ID <= 20 order by ID desc, pkid desc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '1' and pkid <='9' and ID >= 10 and ID <= 20 order by ID asc, pkid asc",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '0' and ID != 10 order by ID asc, pkid asc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '1' and ID != 10 order by ID desc, pkid desc ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '17' and ID > 10 order by ID desc, pkid asc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '17' and ID > 10 order by ID asc, pkid desc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid < '7' and ID > 10 and ID < 20 order by ID asc, pkid asc limit 5 ",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '18' and ID > 10 and ID < 20 order by ID desc, pkid desc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '0' and ID >= 10 and ID <= 20 order by ID desc, pkid asc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid != '17' and ID >= 10 and ID <= 20 order by ID asc, pkid desc limit 5",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '0' and ID != 10 order by ID asc, pkid asc limit 10",
        "SELECT   * FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '9' and ID != 10 order by ID desc, pkid desc limit 10",

    };
    Object[][] r = new Object[queries.length][2];
    QueryService qs;
    qs = CacheUtils.getQueryService();
    Position.resetCounter();
    // Create Regions

    Region r1 = createRegion("portfolio1", Portfolio.class);

    for (int i = 1; i < 200; ++i) {
      Portfolio pf = new Portfolio(i);
      pf.shortID = (short) ((short) i / 5);
      r1.put("" + i, pf);
    }

    // Execute Queries without Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        r[i][0] = q.execute();
      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    // Create Indexes

    createIndex("IDIndexPf1", IndexType.FUNCTIONAL, "ID", SEPARATOR + "portfolio1");
    createIndex("PKIDIndexPf1", IndexType.FUNCTIONAL, "pkid", SEPARATOR + "portfolio1");
    // Execute Queries with Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        QueryObserverImpl observer = new QueryObserverImpl();
        QueryObserverHolder.setInstance(observer);
        r[i][1] = q.execute();

        SelectResults rcw = (SelectResults) r[i][1];

        int indexLimit = queries[i].indexOf("limit");
        int limit = -1;
        boolean limitQuery = indexLimit != -1;
        if (limitQuery) {
          limit = Integer.parseInt(queries[i].substring(indexLimit + 5).trim());
        }
        assertTrue(!limitQuery || rcw.size() <= limit);
        assertTrue(rcw.getCollectionType().isOrdered());
        if (assertIndexUsedOnQueryNode() && !observer.isIndexesUsed) {
          fail("Index is NOT uesd");
        }

        for (final Object o : observer.indexesUsed) {
          String indexUsed = o.toString();
          if (!(indexUsed).equals("IDIndexPf1")) {
            fail("<IDIndexPf1> was expected but found " + indexUsed);
          }
          // assertIndexDetailsEquals("statusIndexPf1",itr.next().toString());
        }

        int indxs = observer.indexesUsed.size();

        System.out.println("**************************************************Indexes Used :::::: "
            + indxs + " Index Name: " + observer.indexName);

      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(r, queries.length, true, queries);
    ssOrrs.compareExternallySortedQueriesWithOrderBy(queries, r);
  }

  @Test
  public void testMultiColOrderByWithMultiIndexResultProjection() throws Exception {
    String[] queries = {
        // Test case No. IUMR021
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '12' and ID > 10 order by ID desc, pkid asc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '1' and ID > 10 order by ID asc, pkid desc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '13'and  ID > 10 and ID < 20 order by ID asc, pkid asc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid <'9' and ID > 10 and ID < 20 order by ID desc , pkid desc",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '15' and ID >= 10 and ID <= 20 order by ID desc, pkid desc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '1' and pkid <='9' and ID >= 10 and ID <= 20 order by ID asc, pkid asc",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '1' and ID != 10 order by ID asc, pkid asc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '1' and ID != 10 order by ID desc, pkid desc ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '17' and ID > 10 order by ID desc, pkid asc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '17' and ID > 10 order by ID asc, pkid desc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid < '7' and ID > 10 and ID < 20 order by ID asc, pkid asc limit 5 ",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid = '18' and ID > 10 and ID < 20 order by ID desc, pkid desc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '2' and ID >= 10 and ID <= 20 order by ID desc, pkid asc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid != '17' and ID >= 10 and ID <= 20 order by ID asc, pkid desc limit 5",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '0' and ID != 10 order by ID asc, pkid asc limit 10",
        "SELECT   ID, description, createTime, pkid FROM " + SEPARATOR
            + "portfolio1 pf1 where pkid > '9' and ID != 10 order by ID desc, pkid desc limit 10",


    };

    Object[][] r = new Object[queries.length][2];
    QueryService qs;
    qs = CacheUtils.getQueryService();
    Position.resetCounter();
    // Create Regions

    Region r1 = createRegion("portfolio1", Portfolio.class);

    for (int i = 1; i < 200; ++i) {
      Portfolio pf = new Portfolio(i);
      pf.shortID = (short) ((short) i / 5);
      r1.put("" + i, pf);
    }

    // Execute Queries without Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        r[i][0] = q.execute();
      } catch (Exception e) {
        e.printStackTrace();
        fail(q.getQueryString());
      }
    }
    // Create Indexes

    createIndex("IDIndexPf1", IndexType.FUNCTIONAL, "ID", SEPARATOR + "portfolio1");
    createIndex("PKIDIndexPf1", IndexType.FUNCTIONAL, "pkid", SEPARATOR + "portfolio1");
    // Execute Queries with Indexes
    for (int i = 0; i < queries.length; i++) {
      Query q = null;
      try {
        q = CacheUtils.getQueryService().newQuery(queries[i]);
        CacheUtils.getLogger().info("Executing query: " + queries[i]);
        QueryObserverImpl observer = new QueryObserverImpl();
        QueryObserverHolder.setInstance(observer);
        r[i][1] = q.execute();
