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

import static org.apache.phoenix.query.KeyRange.EVERYTHING_RANGE;
import static org.apache.phoenix.query.KeyRange.getKeyRange;
import static org.apache.phoenix.query.QueryConstants.MILLIS_IN_DAY;
import static org.apache.phoenix.util.TestUtil.BINARY_NAME;
import static org.apache.phoenix.util.TestUtil.BTABLE_NAME;
import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
import static org.apache.phoenix.util.TestUtil.assertDegenerate;
import static org.apache.phoenix.util.TestUtil.assertEmptyScanKey;
import static org.apache.phoenix.util.TestUtil.like;
import static org.apache.phoenix.util.TestUtil.not;
import static org.apache.phoenix.util.TestUtil.rowKeyFilter;
import static org.apache.phoenix.util.TestUtil.substr;
import static org.apache.phoenix.util.TestUtil.substr2;
import static org.junit.Assert.assertArrayEquals;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
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.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import com.google.common.collect.Lists;
import org.apache.hadoop.hbase.HConstants;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
import org.apache.hadoop.hbase.filter.Filter;
import org.apache.hadoop.hbase.filter.FilterList;
import org.apache.hadoop.hbase.filter.FilterList.Operator;
import org.apache.hadoop.hbase.util.Bytes;
import org.apache.phoenix.compile.WhereOptimizer.KeyExpressionVisitor.KeySlots;
import org.apache.phoenix.compile.WhereOptimizer.KeyExpressionVisitor.SingleKeySlot;
import org.apache.phoenix.compile.WhereOptimizer.KeyExpressionVisitor.SlotsIterator;
import org.apache.phoenix.compile.WhereOptimizer.KeyExpressionVisitor.TrailingRangeIterator;
import org.apache.phoenix.expression.Expression;
import org.apache.phoenix.expression.function.SubstrFunction;
import org.apache.phoenix.filter.BooleanExpressionFilter;
import org.apache.phoenix.filter.RowKeyComparisonFilter;
import org.apache.phoenix.filter.SingleCQKeyValueComparisonFilter;
import org.apache.phoenix.filter.SingleKeyValueComparisonFilter;
import org.apache.phoenix.filter.SkipScanFilter;
import org.apache.phoenix.jdbc.PhoenixConnection;
import org.apache.phoenix.jdbc.PhoenixPreparedStatement;
import org.apache.phoenix.query.BaseConnectionlessQueryTest;
import org.apache.phoenix.query.KeyRange;
import org.apache.phoenix.query.QueryConstants;
import org.apache.phoenix.schema.ColumnNotFoundException;
import org.apache.phoenix.schema.ColumnRef;
import org.apache.phoenix.schema.SortOrder;
import org.apache.phoenix.schema.types.PChar;
import org.apache.phoenix.schema.types.PDate;
import org.apache.phoenix.schema.types.PDecimal;
import org.apache.phoenix.schema.types.PDouble;
import org.apache.phoenix.schema.types.PInteger;
import org.apache.phoenix.schema.types.PLong;
import org.apache.phoenix.schema.types.PUnsignedLong;
import org.apache.phoenix.schema.types.PVarchar;
import org.apache.phoenix.util.ByteUtil;
import org.apache.phoenix.util.DateUtil;
import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.ScanUtil;
import org.apache.phoenix.util.SchemaUtil;
import org.apache.phoenix.util.StringUtil;
import org.apache.phoenix.util.TestUtil;
import org.junit.Test;

public class WhereOptimizerTest extends BaseConnectionlessQueryTest {
    
    private static StatementContext compileStatement(String query) throws SQLException {
        return compileStatement(query, Collections.emptyList(), null);
    }

    private static StatementContext compileStatement(String query, Integer limit) throws SQLException {
        return compileStatement(query, Collections.emptyList(), limit);
    }

    private static StatementContext compileStatement(String query, List<Object> binds) throws SQLException {
        return compileStatement(query, binds, null);
    }

    private static StatementContext compileStatement(String query, List<Object> binds, Integer limit) throws SQLException {
        PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
        PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
        assertRoundtrip(query);
        TestUtil.bindParams(pstmt, binds);
        QueryPlan plan = pstmt.compileQuery();
        assertEquals(limit, plan.getLimit());
        return plan.getContext();
    }
  
    @Test
    public void testTrailingRangesIterator() throws Exception {
        KeyRange[] all = new KeyRange[] {EVERYTHING_RANGE,EVERYTHING_RANGE,EVERYTHING_RANGE,EVERYTHING_RANGE,EVERYTHING_RANGE, EVERYTHING_RANGE};
        List<KeyRange[]> singleAll = Collections.singletonList(all);
        KeyRange[] r1 = new KeyRange[] {
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                getKeyRange(Bytes.toBytes("A")),
                EVERYTHING_RANGE, EVERYTHING_RANGE};
        KeyRange[] r2 = new KeyRange[] {
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                getKeyRange(Bytes.toBytes("B")),
                EVERYTHING_RANGE, EVERYTHING_RANGE};
        KeyRange[] r3 = new KeyRange[] {
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                getKeyRange(Bytes.toBytes("C")),
                EVERYTHING_RANGE, EVERYTHING_RANGE};
        KeyRange[] r4 = new KeyRange[] {
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                getKeyRange(Bytes.toBytes("D")),
                EVERYTHING_RANGE, EVERYTHING_RANGE};
        KeyRange[] r5 = new KeyRange[] {
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                EVERYTHING_RANGE,
                getKeyRange(Bytes.toBytes("A"),true,Bytes.toBytes("D"),true),
                EVERYTHING_RANGE, EVERYTHING_RANGE};
        int initPkPos = 1;
        int pkPos = 3;
        List<List<List<KeyRange[]>>> slotsTrailingRangesList = Lists.<List<List<KeyRange[]>>>newArrayList(
                Lists.<List<KeyRange[]>>newArrayList(Lists.<KeyRange[]>newArrayList(r5)),
                Lists.<List<KeyRange[]>>newArrayList(
                        Lists.<KeyRange[]>newArrayList(r1, r2),
                        Lists.<KeyRange[]>newArrayList(r3, r4)
                        ),
                Lists.<List<KeyRange[]>>newArrayList(),
                Lists.<List<KeyRange[]>>newArrayList(singleAll)
                );
        List<KeyRange> results = Lists.<KeyRange>newArrayList();
        List<KeyRange> expectedResults = Lists.newArrayList(getKeyRange(Bytes.toBytes("A")),getKeyRange(Bytes.toBytes("B")),getKeyRange(Bytes.toBytes("C")),getKeyRange(Bytes.toBytes("D")));
        TrailingRangeIterator iterator = new TrailingRangeIterator(initPkPos, pkPos, slotsTrailingRangesList);
        while (iterator.hasNext()) {
            do {
                do {
                    KeyRange range = iterator.getRange();
                    results.add(range);
                } while (iterator.nextTrailingRange());
            } while (iterator.nextRange());
        }
        assertEquals(expectedResults, results);
    }
    
    @Test
    public void testSlotsIterator() throws Exception {
        List<KeySlots> keySlotsList = Lists.newArrayList();
        keySlotsList.add(new SingleKeySlot(null, 0, 
                Lists.<KeyRange>newArrayList(
                        KeyRange.getKeyRange(Bytes.toBytes("A")),
                        KeyRange.getKeyRange(Bytes.toBytes("B"))
                                )));
        keySlotsList.add(new SingleKeySlot(null, 1, 
                Lists.<KeyRange>newArrayList(
                        KeyRange.getKeyRange(Bytes.toBytes("C"))
                                )));
        keySlotsList.add(new SingleKeySlot(null, 0, 
                Lists.<KeyRange>newArrayList(
                        KeyRange.getKeyRange(Bytes.toBytes("D")),
                        KeyRange.getKeyRange(Bytes.toBytes("E"))
                                )));
        keySlotsList.add(new SingleKeySlot(null, 1, 
                Lists.<KeyRange>newArrayList()));
        SlotsIterator iterator = new SlotsIterator(keySlotsList, 0);
        String[][] expectedResults = {
                {"A",null,"D",null},
                {"B",null, "D", null},
                {"A",null,"E",null},
                {"B",null,"E",null},
                };
        int j = 0;
        while (iterator.next()) {
            int i;
            for (i = 0; i < keySlotsList.size(); i++) {
                KeyRange range = iterator.getRange(i);
                String result = range == null ? null : Bytes.toString(range.getLowerRange());
                String expectedResult = expectedResults[j][i];
                assertEquals(expectedResult,result);
            }
            assertEquals(i,expectedResults[j].length);
            j++;
        }
        assertEquals(j, expectedResults.length);
    }
    
    @Test
    public void testMathFunc() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES));
        conn.createStatement().execute("create table test (id integer primary key)");
        Scan scan = compileStatement("select ID, exp(ID) from test where exp(ID) < 10").getScan();

        assertNotNull(scan.getFilter());
        assertTrue(scan.getStartRow().length == 0);
        assertTrue(scan.getStopRow().length == 0);
    }
    
    @Test
    public void testSingleKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where organization_id='" + tenantId + "'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        assertArrayEquals(PVarchar.INSTANCE.toBytes(tenantId), scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }

    @Test
    public void testGetByteBitExpression() throws SQLException {
        ensureTableCreated(getUrl(), TestUtil.BINARY_NAME, TestUtil.BINARY_NAME);
        int result = 1;
        String query = "select * from " + BINARY_NAME + " where GET_BYTE(a_binary, 0)=" + result;
        Scan scan = compileStatement(query).getScan();

        byte[] tmpBytes, tmpBytes2, tmpBytes3;
        tmpBytes = PInteger.INSTANCE.toBytes(result);
        tmpBytes2 = new byte[16];
        System.arraycopy(tmpBytes, 0, tmpBytes2, 0, tmpBytes.length);
        tmpBytes = ByteUtil.nextKey(tmpBytes);
        tmpBytes3 = new byte[16];
        System.arraycopy(tmpBytes, 0, tmpBytes3, 0, tmpBytes.length);
        assertArrayEquals(tmpBytes2, scan.getStartRow());
        assertArrayEquals(tmpBytes3, scan.getStopRow());

        query = "select * from " + BINARY_NAME + " where GET_BIT(a_binary, 0)=" + result;
        scan = compileStatement(query).getScan();

        tmpBytes = PInteger.INSTANCE.toBytes(result);
        tmpBytes2 = new byte[16];
        System.arraycopy(tmpBytes, 0, tmpBytes2, 0, tmpBytes.length);
        tmpBytes = ByteUtil.nextKey(tmpBytes);
        tmpBytes3 = new byte[16];
        System.arraycopy(tmpBytes, 0, tmpBytes3, 0, tmpBytes.length);
        assertArrayEquals(tmpBytes2, scan.getStartRow());
        assertArrayEquals(tmpBytes3, scan.getStopRow());
    }

    @Test
    public void testDescDecimalRange() throws SQLException {
        String ddl = "create table t (k1 bigint not null, k2 decimal, constraint pk primary key (k1,k2 desc))";
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES));
        conn.createStatement().execute(ddl);
        String query = "select * from t where k1 in (1,2) and k2>1.0";
        Scan scan = compileStatement(query).getScan();

        byte[] startRow = ByteUtil.concat(PLong.INSTANCE.toBytes(1), ByteUtil.nextKey(QueryConstants.SEPARATOR_BYTE_ARRAY), QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
        byte[] upperValue = PDecimal.INSTANCE.toBytes(BigDecimal.valueOf(1.0));
        byte[] stopRow = ByteUtil.concat(PLong.INSTANCE.toBytes(2), SortOrder.invert(upperValue,0,upperValue.length), QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
        assertTrue(scan.getFilter() instanceof SkipScanFilter);
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testSingleCharPaddedKeyExpression() throws SQLException {
        String tenantId = "1";
        String query = "select * from atable where organization_id='" + tenantId + "'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        byte[] key = StringUtil.padChar(PChar.INSTANCE.toBytes(tenantId), 15);
        assertArrayEquals(key, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(key), scan.getStopRow());
    }

    @Test
    public void testSingleBinaryPaddedKeyExpression() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES));
        conn.createStatement().execute("create table bintable (k BINARY(15) PRIMARY KEY)");
        String tenantId = "1";
        String query = "select * from bintable where k='" + tenantId + "'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        byte[] key = ByteUtil.fillKey(PVarchar.INSTANCE.toBytes(tenantId), 15);
        assertArrayEquals(key, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(key), scan.getStopRow());
    }

    @Test
    public void testReverseSingleKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where '" + tenantId + "' = organization_id";
        Scan scan = compileStatement(query).getScan();
        assertNull(scan.getFilter());

        assertArrayEquals(PVarchar.INSTANCE.toBytes(tenantId), scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }

    @Test
    public void testStartKeyStopKey() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute("CREATE TABLE start_stop_test (pk char(2) not null primary key)");
        conn.close();

        String query = "select * from start_stop_test where pk >= 'EA' and pk < 'EZ'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        assertArrayEquals(PVarchar.INSTANCE.toBytes("EA"), scan.getStartRow());
        assertArrayEquals(PVarchar.INSTANCE.toBytes("EZ"), scan.getStopRow());
    }

    @Test
    public void testConcatSingleKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where organization_id || 'foo' ='" + tenantId + "'||'foo'";
        Scan scan = compileStatement(query).getScan();

        // The || operator cannot currently be used to form the start/stop key
        assertNotNull(scan.getFilter());
        assertEquals(0, scan.getStartRow().length);
        assertEquals(0, scan.getStopRow().length);
    }

    @Test
    public void testLiteralConcatExpression() throws SQLException {
        String query = "select * from atable where null||'foo'||'bar' = 'foobar'";
        Scan scan = new Scan();
        List<Object> binds = Collections.emptyList();
        compileStatement(query, binds);

        assertNull(scan.getFilter());
        assertEquals(0, scan.getStartRow().length);
        assertEquals(0, scan.getStopRow().length);
    }

    @Test
    public void testSingleKeyNotExpression() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where not organization_id='" + tenantId + "'";
        Scan scan = compileStatement(query).getScan();

        assertNotNull(scan.getFilter());
        assertEquals(0, scan.getStartRow().length);
        assertEquals(0, scan.getStopRow().length);
    }

    @Test
    public void testMultiKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3)='" + keyPrefix + "'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)), 15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testMultiKeyBindExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String query = "select * from atable where organization_id=? and substr(entity_id,1,3)=?";
        List<Object> binds = Arrays.<Object>asList(tenantId,keyPrefix);
        Scan scan = compileStatement(query, binds).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testEqualRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 00:00:00");
        Date endDate = DateUtil.parseDate("2012-01-02 00:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')=?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(startDate));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(endDate));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testDegenerateRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 01:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')=?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();
        assertDegenerate(scan);
    }

    @Test
    public void testBoundaryGreaterThanRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 00:00:00");
        Date endDate = DateUtil.parseDate("2012-01-02 00:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')>?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(endDate));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.nextKey(ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testBoundaryGreaterThanOrEqualRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 00:00:00");
        Date endDate = DateUtil.parseDate("2012-01-01 00:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')>=?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(endDate));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.nextKey(ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testGreaterThanRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 01:00:00");
        Date endDate = DateUtil.parseDate("2012-01-02 00:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')>?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(endDate));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.nextKey(ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLessThanRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 01:00:00");
        Date endDate = DateUtil.parseDate("2012-01-02 00:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')<?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host)/*,QueryConstants.SEPARATOR_BYTE_ARRAY*/);
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(endDate));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testBoundaryLessThanRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 00:00:00");
        Date endDate = DateUtil.parseDate("2012-01-01 00:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')<?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host)/*,QueryConstants.SEPARATOR_BYTE_ARRAY*/);
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(endDate));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLessThanOrEqualRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 01:00:00");
        Date endDate = DateUtil.parseDate("2012-01-02 00:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')<=?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host)/*,QueryConstants.SEPARATOR_BYTE_ARRAY*/);
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(endDate));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testBoundaryLessThanOrEqualRound() throws Exception {
        String inst = "a";
        String host = "b";
        Date startDate = DateUtil.parseDate("2012-01-01 00:00:00");
        Date endDate = DateUtil.parseDate("2012-01-02 00:00:00");
        String query = "select * from ptsdb where inst=? and host=? and round(date,'DAY')<=?";
        List<Object> binds = Arrays.<Object>asList(inst,host,startDate);
        Scan scan = compileStatement(query, binds).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host)/*,QueryConstants.SEPARATOR_BYTE_ARRAY*/);
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY,
                PDate.INSTANCE.toBytes(endDate));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testOverlappingKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String entityId = "002333333333333";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3)='" + keyPrefix + "' and entity_id='" + entityId + "'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId));
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(startRow), scan.getStopRow());
    }

    @Test
    public void testSubstrExpressionWithoutLengthVariable() {
        assertEquals("SUBSTR(ENTITY_ID, 1)",((SubstrFunction)substr2(ENTITY_ID,1)).toString());
    }

    @Test
    public void testSubstrExpressionWithLengthVariable() {
        assertEquals("SUBSTR(ENTITY_ID, 1, 10)",((SubstrFunction)substr(ENTITY_ID,1, 10)).toString());
    }

    @Test
    public void testTrailingSubstrExpression() throws SQLException {
        String tenantId = "0xD000000000001";
        String entityId = "002333333333333";
        String query = "select * from atable where substr(organization_id,1,3)='" + tenantId.substring(0, 3) + "' and entity_id='" + entityId + "'";
        Scan scan = compileStatement(query).getScan();
        assertNotNull(scan.getFilter());

        byte[] startRow = ByteUtil.concat(StringUtil.padChar(PVarchar.INSTANCE.toBytes(tenantId.substring(0,3)),15),
            PVarchar.INSTANCE.toBytes(entityId));
        assertArrayEquals(startRow, scan.getStartRow());
        // Even though the first slot is a non inclusive range, we need to do a next key
        // on the second slot because of the algorithm we use to seek to and terminate the
        // loop during skip scan. We could end up having a first slot just under the upper
        // limit of slot one and a value equal to the value in slot two and we need this to
        // be less than the upper range that would get formed.
        byte[] stopRow = ByteUtil.concat(StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId.substring(0,3))),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testBasicRangeExpression() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where organization_id <= '" + tenantId + "'";
        Scan scan = compileStatement(query).getScan();
        assertNull(scan.getFilter());

        assertTrue(scan.getStartRow().length == 0);
        byte[] stopRow = ByteUtil.concat(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testKeyRangeExpression1() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "002";
        String keyPrefix2= "004";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) >= '" + keyPrefix1 + "' and substr(entity_id,1,3) < '" + keyPrefix2 + "'";
        Scan scan = compileStatement(query).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PChar.INSTANCE.toBytes(keyPrefix1),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PChar.INSTANCE.toBytes(keyPrefix2),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testKeyRangeExpression2() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "002";
        String keyPrefix2= "004";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) >= '" + keyPrefix1 + "' and substr(entity_id,1,3) <= '" + keyPrefix2 + "'";
        Scan scan = compileStatement(query).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PChar.INSTANCE.toBytes(keyPrefix1),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PChar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PChar.INSTANCE.toBytes(keyPrefix2)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testKeyRangeExpression3() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "002";
        String keyPrefix2= "004";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) > '" + keyPrefix1 + "' and substr(entity_id,1,3) <= '" + keyPrefix2 + "'";
        Scan scan = compileStatement(query).getScan();
        
        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix1)),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix2)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testKeyRangeExpression4() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "002";
        String entityId= "002000000000002";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) > '" + keyPrefix1 + "' and substr(entity_id,1,3) = '" + entityId + "'";
        Scan scan = compileStatement(query).getScan();
        assertDegenerate(scan);
    }

    @Test
    public void testKeyRangeExpression5() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "002";
        String entityId= "002000000000002";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) <= '" + keyPrefix1 + "' and entity_id = '" + entityId + "'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId));
        assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
    }

    @Test
    public void testKeyRangeExpression6() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "002";
        String entityId= "002000000000002";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) < '" + keyPrefix1 + "' and entity_id = '" + entityId + "'";
        Scan scan = compileStatement(query).getScan();
        assertDegenerate(scan);
    }

    @Test
    public void testKeyRangeExpression7() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "002";
        String entityId= "002000000000002";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) < '" + keyPrefix1 + "' and entity_id < '" + entityId + "'";
        Scan scan = compileStatement(query).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = PChar.INSTANCE.toBytes(tenantId);
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PChar.INSTANCE.toBytes(keyPrefix1),entityId.length()));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testKeyRangeExpression8() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "001";
        String entityId= "002000000000002";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) > '" + keyPrefix1 + "' and entity_id = '" + entityId + "'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId));
        assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
    }

    @Test
    public void testKeyRangeExpression9() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix1 = "002";
        String keyPrefix2 = "0033";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3) >= '" + keyPrefix1 + "' and substr(entity_id,1,4) <= '" + keyPrefix2 + "'";
        Scan scan = compileStatement(query).getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PChar.INSTANCE.toBytes(keyPrefix1),15)); // extra byte is due to implicit internal padding
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PChar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PChar.INSTANCE.toBytes(keyPrefix2)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    /**
     * This is testing the degenerate case where nothing will match because the overlapping keys (keyPrefix and entityId) don't match.
     * @throws SQLException
     */
    @Test
    public void testUnequalOverlappingKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String entityId = "001333333333333";
        String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,1,3)='" + keyPrefix + "' and entity_id='" + entityId + "'";
        Scan scan = compileStatement(query).getScan();
        assertDegenerate(scan);
    }

    @Test
    public void testTopLevelOrKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where organization_id='" + tenantId + "' or a_integer=2";
        Scan scan = compileStatement(query).getScan();

        assertNotNull(scan.getFilter());
        assertEquals(0, scan.getStartRow().length);
        assertEquals(0, scan.getStopRow().length);
    }

    @Test
    public void testSiblingOrKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where organization_id='" + tenantId + "' and (a_integer = 2 or a_integer = 3)";
        Scan scan = compileStatement(query).getScan();

        assertNotNull(scan.getFilter());
        assertArrayEquals(PVarchar.INSTANCE.toBytes(tenantId), scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }

    @Test
    public void testColumnNotFound() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where bar='" + tenantId + "'";
        try {
            compileStatement(query);
            fail();
        } catch (ColumnNotFoundException e) {
            // expected
        }
    }

    @Test
    public void testNotContiguousPkColumn() throws SQLException {
        String keyPrefix = "002";
        String query = "select * from atable where substr(entity_id,1,3)='" + keyPrefix + "'";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertNotNull(scan.getFilter());
        assertEquals(0, scan.getStartRow().length);
        assertEquals(0, scan.getStopRow().length);
    }

    @Test
    public void testMultipleNonEqualitiesPkColumn() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String query = "select * from atable where organization_id >= '" + tenantId + "' AND substr(entity_id,1,3) > '" + keyPrefix + "'";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertNotNull(scan.getFilter());
//        assertArrayEquals(PVarchar.INSTANCE.toBytes(tenantId), scan.getStartRow());
        assertArrayEquals(
                ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId),
                                PChar.INSTANCE.toBytes(
                                        PChar.INSTANCE.pad(
                                                PChar.INSTANCE.toObject(ByteUtil.nextKey(PChar.INSTANCE.toBytes(keyPrefix))),
                                                15))),
                                                        scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }

    @Test
    public void testRHSLiteral() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where organization_id='" + tenantId + "' and 0 >= a_integer limit 1000";
        StatementContext context = compileStatement(query, 1000);
        Scan scan = context.getScan();

        assertNotNull(scan.getFilter());
        assertArrayEquals(PVarchar.INSTANCE.toBytes(tenantId), scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }


    @Test
    public void testKeyTypeMismatch() {
        String query = "select * from atable where organization_id=5";
        try {
            compileStatement(query);
            fail();
        } catch (SQLException e) {
            assertTrue(e.getMessage().contains("Type mismatch"));
        }
    }

    @Test
    public void testLikeExtractAllKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String query = "select * from atable where organization_id = ? and entity_id  LIKE '" + keyPrefix + "%'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLikeExtractAllKeyExpression2() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "中文";
        String query = "select * from atable where organization_id = ? and entity_id  LIKE '" + keyPrefix + "%'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLikeExtractAllAsEqKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String query = "select * from atable where organization_id LIKE ? and entity_id  LIKE '" + keyPrefix + "%'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        assertNull(scan.getFilter());
        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLikeNoWildcardExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String query = "select * from atable where organization_id LIKE ? and entity_id  LIKE '" + keyPrefix + "'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        byte[] startRow = ByteUtil.concat(
                PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.nextKey(startRow);
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLikeExtractKeyExpression2() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String likeArg = keyPrefix + "_";
        String query = "select * from atable where organization_id = ? and entity_id  LIKE '" + likeArg + "'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertEquals(
                rowKeyFilter(like(
                    ENTITY_ID,
                    likeArg,
                    context)),
                filter);

        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLikeOptKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String likeArg = keyPrefix + "%003%";
        String query = "select * from atable where organization_id = ? and entity_id  LIKE '" + likeArg + "'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertEquals(
                rowKeyFilter(like(
                    ENTITY_ID,
                    likeArg,
                    context)),
                filter);

        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLikeOptKeyExpression2() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String likeArg = keyPrefix + "%003%";
        String query = "select * from atable where organization_id = ? and substr(entity_id,1,10)  LIKE '" + likeArg + "'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertEquals(
                rowKeyFilter(like(
                    substr(ENTITY_ID,1,10),
                    likeArg,
                    context)),
                filter);

        byte[] startRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15));
        byte[] stopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId),StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15));
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    @Test
    public void testLikeNoOptKeyExpression3() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String likeArg = keyPrefix + "%003%";
        String query = "select * from atable where organization_id = ? and substr(entity_id,4,10)  LIKE '" + likeArg + "'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertEquals(
                rowKeyFilter(like(
                    substr(ENTITY_ID,4,10),
                    likeArg,
                    context)),
                filter);

        byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId);
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(startRow), scan.getStopRow());
    }

    @Test
    public void testLikeNoOptKeyExpression() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String likeArg = "%001%" + keyPrefix + "%";
        String query = "select * from atable where organization_id = ? and entity_id  LIKE '" + likeArg + "'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertEquals(
                rowKeyFilter(like(
                    ENTITY_ID,
                    likeArg,
                    context)),
                filter);

        byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId);
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(startRow), scan.getStopRow());
    }

    @Test
    public void testLikeNoOptKeyExpression2() throws SQLException {
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String likeArg = keyPrefix + "%";
        String query = "select * from atable where organization_id = ? and entity_id  NOT LIKE '" + likeArg + "'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertEquals(
                rowKeyFilter(not(like(
                    ENTITY_ID,
                    likeArg,
                    context))),
                filter);

        byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId);
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(startRow), scan.getStopRow());
    }

    @Test
    public void testLikeDegenerate() throws SQLException {
        String tenantId = "000000000000001";
        String query = "select * from atable where organization_id = ? and entity_id  LIKE '0000000000000012%003%'";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    @Test
    public void testDegenerateDivision1() throws SQLException {
        String query = "select * from atable where a_integer = 3 / null";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    @Test
    public void testDegenerateDivision2() throws SQLException {
        String query = "select * from atable where a_integer / null = 3";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    @Test
    public void testDegenerateMult1() throws SQLException {
        String query = "select * from atable where a_integer = 3 * null";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    @Test
    public void testDegenerateMult2() throws SQLException {
        String query = "select * from atable where a_integer * null = 3";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    @Test
    public void testDegenerateAdd1() throws SQLException {
        String query = "select * from atable where a_integer = 3 + null";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    @Test
    public void testDegenerateAdd2() throws SQLException {
        String query = "select * from atable where a_integer + null = 3";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    @Test
    public void testDegenerateSub1() throws SQLException {
        String query = "select * from atable where a_integer = 3 - null";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    @Test
    public void testDegenerateSub2() throws SQLException {
        String query = "select * from atable where a_integer - null = 3";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();

        assertDegenerate(scan);
    }

    /*
     * The following 5 tests are testing the comparison in where clauses under the case when the rhs
     * cannot be coerced into the lhs. We need to confirm the decision make by expression compilation
     * returns correct decisions.
     */
    @Test
    public void testValueComparisonInt() throws SQLException {
        ensureTableCreated(getUrl(),"PKIntValueTest", "PKIntValueTest");
        String query;
        // int <-> long
        // Case 1: int = long, comparison always false, key is degenerated.
        query = "SELECT * FROM PKintValueTest where pk = " + Long.MAX_VALUE;
        assertQueryConditionAlwaysFalse(query);
        // Case 2: int != long, comparison always true, no key set since we need to do a full
        // scan all the time.
        query = "SELECT * FROM PKintValueTest where pk != " + Long.MAX_VALUE;
        assertQueryConditionAlwaysTrue(query);
        // Case 3: int > positive long, comparison always false;
        query = "SELECT * FROM PKintValueTest where pk >= " + Long.MAX_VALUE;
        assertQueryConditionAlwaysFalse(query);
        // Case 4: int <= Integer.MAX_VALUE < positive long, always true;
        query = "SELECT * FROM PKintValueTest where pk <= " + Long.MAX_VALUE;
        assertQueryConditionAlwaysTrue(query);
        // Case 5: int >= Integer.MIN_VALUE > negative long, always true;
        query = "SELECT * FROM PKintValueTest where pk >= " + (Long.MIN_VALUE + 1);
        assertQueryConditionAlwaysTrue(query);
        // Case 6: int < negative long, comparison always false;
        query = "SELECT * FROM PKintValueTest where pk <= " + (Long.MIN_VALUE + 1);
        assertQueryConditionAlwaysFalse(query);
    }

    @Test
    public void testValueComparisonUnsignedInt() throws SQLException {
        ensureTableCreated(getUrl(), "PKUnsignedIntValueTest", "PKUnsignedIntValueTest");
        String query;
        // unsigned_int <-> negative int/long
        // Case 1: unsigned_int = negative int, always false;
        query = "SELECT * FROM PKUnsignedIntValueTest where pk = -1";
        assertQueryConditionAlwaysFalse(query);
        // Case 2: unsigned_int != negative int, always true;
        query = "SELECT * FROM PKUnsignedIntValueTest where pk != -1";
        assertQueryConditionAlwaysTrue(query);
        // Case 3: unsigned_int > negative int, always true;
        query = "SELECT * FROM PKUnsignedIntValueTest where pk > " + (Long.MIN_VALUE + 1);
        assertQueryConditionAlwaysTrue(query);
        // Case 4: unsigned_int < negative int, always false;
        query = "SELECT * FROM PKUnsignedIntValueTest where pk < " + + (Long.MIN_VALUE + 1);
        assertQueryConditionAlwaysFalse(query);
        // unsigned_int <-> big positive long
        // Case 1: unsigned_int = big positive long, always false;
        query = "SELECT * FROM PKUnsignedIntValueTest where pk = " + Long.MAX_VALUE;
        assertQueryConditionAlwaysFalse(query);
        // Case 2: unsigned_int != big positive long, always true;
        query = "SELECT * FROM PKUnsignedIntValueTest where pk != " + Long.MAX_VALUE;
        assertQueryConditionAlwaysTrue(query);
        // Case 3: unsigned_int > big positive long, always false;
        query = "SELECT * FROM PKUnsignedIntValueTest where pk >= " + Long.MAX_VALUE;
        assertQueryConditionAlwaysFalse(query);
        // Case 4: unsigned_int < big positive long, always true;
        query = "SELECT * FROM PKUnsignedIntValueTest where pk <= " + Long.MAX_VALUE;
        assertQueryConditionAlwaysTrue(query);
    }

    @Test
    public void testValueComparisonUnsignedLong() throws SQLException {
        ensureTableCreated(getUrl(), "PKUnsignedLongValueTest", "PKUnsignedLongValueTest");
        String query;
        // unsigned_long <-> positive int/long
        // Case 1: unsigned_long = negative int/long, always false;
        query = "SELECT * FROM PKUnsignedLongValueTest where pk = -1";
        assertQueryConditionAlwaysFalse(query);
        // Case 2: unsigned_long = negative int/long, always true;
        query = "SELECT * FROM PKUnsignedLongValueTest where pk != " + (Long.MIN_VALUE + 1);
        assertQueryConditionAlwaysTrue(query);
        // Case 3: unsigned_long > negative int/long, always true;
        query = "SELECT * FROM PKUnsignedLongValueTest where pk > -1";
        assertQueryConditionAlwaysTrue(query);
        // Case 4: unsigned_long < negative int/long, always false;
        query = "SELECT * FROM PKUnsignedLongValueTest where pk < " + (Long.MIN_VALUE + 1);
        assertQueryConditionAlwaysFalse(query);
    }

    private void assertQueryConditionAlwaysTrue(String query) throws SQLException {
        Scan scan = compileStatement(query).getScan();
        assertEmptyScanKey(scan);
    }

    private void assertQueryConditionAlwaysFalse(String query) throws SQLException {
        Scan scan = compileStatement(query).getScan();
        assertDegenerate(scan);
    }
    
    @Test
    public void testOrSameColExpression() throws SQLException {
        String tenantId1 = "000000000000001";
        String tenantId2 = "000000000000003";
        String query = "select * from atable where organization_id = ? or organization_id  = ?";
        List<Object> binds = Arrays.<Object>asList(tenantId1,tenantId2);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        
        assertNotNull(filter);
        assertTrue(filter instanceof SkipScanFilter);
        ScanRanges scanRanges = context.getScanRanges();
        assertNotNull(scanRanges);
        List<List<KeyRange>> ranges = scanRanges.getRanges();
        assertEquals(1,ranges.size());
        List<List<KeyRange>> expectedRanges = Collections.singletonList(Arrays.asList(
                PChar.INSTANCE.getKeyRange(PChar.INSTANCE.toBytes(tenantId1), true, PChar.INSTANCE.toBytes(tenantId1), true),
                PChar.INSTANCE.getKeyRange(PChar.INSTANCE.toBytes(tenantId2), true, PChar.INSTANCE.toBytes(tenantId2), true)));
        assertEquals(expectedRanges, ranges);
        byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId1);
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId2)), scan.getStopRow());
    }
    
    @Test
    public void testAndOrExpression() throws SQLException {
        String tenantId1 = "000000000000001";
        String tenantId2 = "000000000000003";
        String entityId1 = "002333333333331";
        String entityId2 = "002333333333333";
        String query = "select * from atable where (organization_id = ? and entity_id  = ?) or (organization_id = ? and entity_id  = ?)";
        List<Object> binds = Arrays.<Object>asList(tenantId1,entityId1,tenantId2,entityId2);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();

        assertNotNull(filter);
        assertTrue(filter instanceof RowKeyComparisonFilter);
        
        ScanRanges scanRanges = context.getScanRanges();
        assertEquals(ScanRanges.EVERYTHING,scanRanges);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testOrDiffColExpression() throws SQLException {
        String tenantId1 = "000000000000001";
        String entityId1 = "002333333333331";
        String query = "select * from atable where organization_id = ? or entity_id  = ?";
        List<Object> binds = Arrays.<Object>asList(tenantId1,entityId1);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();

        assertNotNull(filter);
        assertTrue(filter instanceof RowKeyComparisonFilter);
        ScanRanges scanRanges = context.getScanRanges();
        assertEquals(ScanRanges.EVERYTHING,scanRanges);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testOrSameColRangeExpression() throws SQLException {
        String query = "select * from atable where substr(organization_id,1,3) = ? or organization_id LIKE 'foo%'";
        List<Object> binds = Arrays.<Object>asList("00D");
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();

        assertNotNull(filter);
        assertTrue(filter instanceof SkipScanFilter);
        ScanRanges scanRanges = context.getScanRanges();
        assertNotNull(scanRanges);
        List<List<KeyRange>> ranges = scanRanges.getRanges();
        assertEquals(1,ranges.size());
        List<List<KeyRange>> expectedRanges = Collections.singletonList(Arrays.asList(
                PChar.INSTANCE.getKeyRange(
                        StringUtil.padChar(PChar.INSTANCE.toBytes("00D"),15), true,
                        StringUtil.padChar(ByteUtil.nextKey(PChar.INSTANCE.toBytes("00D")),15), false),
                PChar.INSTANCE.getKeyRange(
                        StringUtil.padChar(PChar.INSTANCE.toBytes("foo"),15), true,
                        StringUtil.padChar(ByteUtil.nextKey(PChar.INSTANCE.toBytes("foo")),15), false)));
        assertEquals(expectedRanges, ranges);
    }

    @Test
    public void testOrPKRanges() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        ensureTableCreated(getUrl(), TestUtil.BTABLE_NAME);
        Statement stmt = conn.createStatement();
        // BTABLE has 5 PK columns
        String query = "select * from " + BTABLE_NAME +
                       " where (a_string > '1' and a_string < '5') or (a_string > '6' and a_string < '9')";
        StatementContext context = compileStatement(query);
        Filter filter = context.getScan().getFilter();

        assertNotNull(filter);
        assertTrue(filter instanceof SkipScanFilter);
        ScanRanges scanRanges = context.getScanRanges();
        assertNotNull(scanRanges);
        List<List<KeyRange>> ranges = scanRanges.getRanges();
        assertEquals(1, ranges.size());
        List<List<KeyRange>> expectedRanges = Collections.singletonList(Arrays.asList(
                KeyRange.getKeyRange(Bytes.toBytes("1"), false, Bytes.toBytes("5"), false),
                KeyRange.getKeyRange(Bytes.toBytes("6"), false, Bytes.toBytes("9"), false)));
        assertEquals(expectedRanges, ranges);

        stmt.close();
        conn.close();
    }
    
    @Test
    public void testOrPKRangesNotOptimized() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        ensureTableCreated(getUrl(), TestUtil.BTABLE_NAME);
        Statement stmt = conn.createStatement();
        // BTABLE has 5 PK columns
        String[] queries = {
                "select * from " + BTABLE_NAME + " where (a_string > '1' and a_string < '5') or (a_string > '6' and a_string < '9' and a_id = 'foo')",
                "select * from " + BTABLE_NAME + " where (a_id > 'aaa' and a_id < 'ccc') or (a_id > 'jjj' and a_id < 'mmm')",
                };
        for (String query : queries) {
            StatementContext context = compileStatement(query);
            Iterator<Filter> it = ScanUtil.getFilterIterator(context.getScan());
            while (it.hasNext()) {
                assertFalse(it.next() instanceof SkipScanFilter);
            }
            TestUtil.assertNotDegenerate(context.getScan());
        }

        stmt.close();
        conn.close();
    }
    
    @Test
    public void testForceSkipScanOnSaltedTable() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute("CREATE TABLE IF NOT EXISTS user_messages (\n" + 
                "        SENDER_ID UNSIGNED_LONG NOT NULL,\n" + 
                "        RECIPIENT_ID UNSIGNED_LONG NOT NULL,\n" + 
                "        SENDER_IP VARCHAR,\n" + 
                "        IS_READ VARCHAR,\n" + 
                "        IS_DELETED VARCHAR,\n" + 
                "        M_TEXT VARCHAR,\n" + 
                "        M_TIMESTAMP timestamp  NOT NULL,\n" + 
                "        ROW_ID UNSIGNED_LONG NOT NULL\n" + 
                "        constraint rowkey primary key (SENDER_ID,RECIPIENT_ID,M_TIMESTAMP DESC,ROW_ID))\n" + 
                "SALT_BUCKETS=12\n");
        String query = "select /*+ SKIP_SCAN */ count(*) from user_messages where is_read='N' and recipient_id=5399179882";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        
        assertNotNull(filter);
        assertTrue(filter instanceof FilterList);
        FilterList filterList = (FilterList)filter;
        assertEquals(FilterList.Operator.MUST_PASS_ALL, filterList.getOperator());
        assertEquals(2, filterList.getFilters().size());
        assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
        assertTrue(filterList.getFilters().get(1) instanceof SingleKeyValueComparisonFilter);
        
        ScanRanges scanRanges = context.getScanRanges();
        assertNotNull(scanRanges);
        assertEquals(3,scanRanges.getRanges().size());
        assertEquals(1,scanRanges.getRanges().get(1).size());
        assertEquals(KeyRange.EVERYTHING_RANGE,scanRanges.getRanges().get(1).get(0));
        assertEquals(1,scanRanges.getRanges().get(2).size());
        assertTrue(scanRanges.getRanges().get(2).get(0).isSingleKey());
        assertEquals(Long.valueOf(5399179882L), PUnsignedLong.INSTANCE.toObject(scanRanges.getRanges().get(2).get(0).getLowerRange()));
    }
    
    @Test
    public void testForceRangeScanKeepsFilters() throws SQLException {
        ensureTableCreated(getUrl(), TestUtil.ENTITY_HISTORY_TABLE_NAME, TestUtil.ENTITY_HISTORY_TABLE_NAME);
        String tenantId = "000000000000001";
        String keyPrefix = "002";
        String query = "select /*+ RANGE_SCAN */ ORGANIZATION_ID, PARENT_ID, CREATED_DATE, ENTITY_HISTORY_ID from " + TestUtil.ENTITY_HISTORY_TABLE_NAME + 
                " where ORGANIZATION_ID=? and SUBSTR(PARENT_ID, 1, 3) = ? and  CREATED_DATE >= ? and CREATED_DATE < ? order by ORGANIZATION_ID, PARENT_ID, CREATED_DATE, ENTITY_HISTORY_ID limit 6";
        Date startTime = new Date(System.currentTimeMillis());
        Date stopTime = new Date(startTime.getTime() + MILLIS_IN_DAY);
        List<Object> binds = Arrays.<Object>asList(tenantId, keyPrefix, startTime, stopTime);
        StatementContext context = compileStatement(query, binds, 6);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertTrue(filter instanceof RowKeyComparisonFilter);

        byte[] expectedStartRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId), StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15), PDate.INSTANCE.toBytes(startTime));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        byte[] expectedStopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId), StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15));
        assertArrayEquals(expectedStopRow, scan.getStopRow());
    }

    @Test
    public void testBasicRVCExpression() throws SQLException {
        String tenantId = "000000000000001";
        String entityId = "002333333333331";
        String query = "select * from atable where (organization_id,entity_id) >= (?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, entityId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        assertNull(scan.getFilter());
        byte[] expectedStartRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId), PChar.INSTANCE.toBytes(entityId));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }

    
    @Test
    public void testRVCExpressionThroughOr() throws SQLException {
        String tenantId =  "000000000000001";
        String entityId =  "002333333333331";
        String entityId1 = "002333333333330";
        String entityId2 = "002333333333332";
        String query = "select * from atable where (organization_id,entity_id) >= (?,?) and organization_id = ? and  (entity_id = ? or entity_id = ?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, entityId, tenantId, entityId1, entityId2);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        byte[] expectedStartRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId1));
        byte[] expectedStopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId2), QueryConstants.SEPARATOR_BYTE_ARRAY);
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(expectedStopRow, scan.getStopRow());
        Filter filter = scan.getFilter();
        assertTrue(filter instanceof SkipScanFilter);
        SkipScanFilter skipScanFilter = (SkipScanFilter)filter;
        List<List<KeyRange>> skipScanRanges = Arrays.asList(
                Arrays.asList(KeyRange.getKeyRange(ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId1))),
                              KeyRange.getKeyRange(ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(entityId2)))));
        assertEquals(skipScanRanges, skipScanFilter.getSlots());
    }
    
    @Test
    public void testNotRepresentableBySkipScan() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES));
        String tableName = generateUniqueName();
        conn.createStatement().execute("CREATE TABLE " + tableName + "(a INTEGER NOT NULL, b INTEGER NOT NULL, CONSTRAINT pk PRIMARY KEY (a,b))");
        String query = "SELECT * FROM " + tableName + 
                " WHERE (a,b) >= (1,5) and (a,b) < (3,8) and (a = 1 or a = 3) and ((b >= 6 and b < 9) or (b > 3 and b <= 5))";
        StatementContext context = compileStatement(query);
        Scan scan = context.getScan();
        byte[] expectedStartRow = ByteUtil.concat(PInteger.INSTANCE.toBytes(1), PInteger.INSTANCE.toBytes(4));
        byte[] expectedStopRow = ByteUtil.concat(PInteger.INSTANCE.toBytes(3), PInteger.INSTANCE.toBytes(9));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(expectedStopRow, scan.getStopRow());
        Filter filter = scan.getFilter();
        assertTrue(filter instanceof FilterList);
        FilterList filterList = (FilterList)filter;
        // We can form a skip scan, but it's not exact, so we need the boolean expression filter
        // as well.
        assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
        assertTrue(filterList.getFilters().get(1) instanceof BooleanExpressionFilter);
        SkipScanFilter skipScanFilter = (SkipScanFilter)filterList.getFilters().get(0);
        List<List<KeyRange>> skipScanRanges = Arrays.asList(
                Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(1)),
                              KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(3))),
                Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(5), true),
                              KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(6), true, PInteger.INSTANCE.toBytes(9), false)));
        assertEquals(skipScanRanges, skipScanFilter.getSlots());
    }
    
    /**
     * With only a subset of row key cols present (which includes the leading key), 
     * Phoenix should have optimized the start row for the scan to include the 
     * row keys cols that occur contiguously in the RVC.
     * 
     * Table entity_history has the row key defined as (organization_id, parent_id, created_date, entity_history_id). 
     * This test uses (organization_id, parent_id, entity_id) in RVC. So the start row should be comprised of
     * organization_id and parent_id.
     * @throws SQLException
     */
    @Test
    public void testRVCExpressionWithSubsetOfPKCols() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = "000000000000002";
        String entityHistId = "000000000000003";
        
        String query = "select * from entity_history where (organization_id, parent_id, entity_history_id) >= (?,?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, parentId, entityHistId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertTrue(filter instanceof RowKeyComparisonFilter);
        byte[] expectedStartRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(parentId));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    /**
     * With the leading row key col missing Phoenix won't be able to optimize
     * and provide the start row for the scan.
     * 
     * Table entity_history has the row key defined as (organization_id, parent_id, created_date, entity_history_id). 
     * This test uses (parent_id, entity_id) in RVC. Start row should be empty.
     * @throws SQLException
     */
    
    @Test
    public void testRVCExpressionWithoutLeadingColOfRowKey() throws SQLException {
        
        String parentId = "000000000000002";
        String entityHistId = "000000000000003";
        
        String query = "select * from entity_history where (parent_id, entity_history_id) >= (?,?)";
        List<Object> binds = Arrays.<Object>asList(parentId, entityHistId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertTrue(filter instanceof RowKeyComparisonFilter);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testRVCExpressionWithNonFirstLeadingColOfRowKey() throws SQLException {
        String old_value = "value";
        String orgId = getOrganizationId();
        
        String query = "select * from entity_history where (old_value, organization_id) >= (?,?)";
        List<Object> binds = Arrays.<Object>asList(old_value, orgId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertTrue(filter instanceof SingleKeyValueComparisonFilter);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testMultiRVCExpressionsCombinedWithAnd() throws SQLException {
        String lowerTenantId = "000000000000001";
        String lowerParentId = "000000000000002";
        Date lowerCreatedDate = new Date(System.currentTimeMillis());
        String upperTenantId = "000000000000008";
        String upperParentId = "000000000000009";
        
        String query = "select * from entity_history where (organization_id, parent_id, created_date) >= (?, ?, ?) AND (organization_id, parent_id) <= (?, ?)";
        List<Object> binds = Arrays.<Object>asList(lowerTenantId, lowerParentId, lowerCreatedDate, upperTenantId, upperParentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        byte[] expectedStartRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(lowerTenantId), PVarchar.INSTANCE.toBytes(lowerParentId), PDate.INSTANCE.toBytes(lowerCreatedDate));
        byte[] expectedStopRow = ByteUtil.nextKey(ByteUtil.concat(PVarchar.INSTANCE.toBytes(upperTenantId), PVarchar.INSTANCE.toBytes(upperParentId)));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(expectedStopRow, scan.getStopRow());
    }
    
    @Test
    public void testMultiRVCExpressionsCombinedUsingLiteralExpressions() throws SQLException {
        String lowerTenantId = "000000000000001";
        String lowerParentId = "000000000000002";
        Date lowerCreatedDate = new Date(System.currentTimeMillis());
        
        String query = "select * from entity_history where (organization_id, parent_id, created_date) >= (?, ?, ?) AND (organization_id, parent_id) <= ('7', '7')";
        List<Object> binds = Arrays.<Object>asList(lowerTenantId, lowerParentId, lowerCreatedDate);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        byte[] expectedStartRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(lowerTenantId), PVarchar.INSTANCE.toBytes(lowerParentId), PDate.INSTANCE.toBytes(lowerCreatedDate));
        byte[] expectedStopRow = ByteUtil.nextKey(ByteUtil.concat(StringUtil.padChar(PVarchar.INSTANCE.toBytes("7"),15), StringUtil.padChar(
            PVarchar.INSTANCE.toBytes("7"), 15)));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(expectedStopRow, scan.getStopRow());
    }
    
    @Test
    public void testUseOfFunctionOnLHSInRVC() throws SQLException {
        String tenantId = "000000000000001";
        String subStringTenantId = tenantId.substring(0, 3);
        String parentId = "000000000000002";
        Date createdDate = new Date(System.currentTimeMillis());
        
        String query = "select * from entity_history where (substr(organization_id, 1, 3), parent_id, created_date) >= (?,?,?)";
        List<Object> binds = Arrays.<Object>asList(subStringTenantId, parentId, createdDate);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertTrue(filter instanceof RowKeyComparisonFilter);
        byte[] expectedStartRow = PVarchar.INSTANCE.toBytes(subStringTenantId);
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testUseOfFunctionOnLHSInMiddleOfRVC() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = "000000000000002";
        String subStringParentId = parentId.substring(0, 3);
        Date createdDate = new Date(System.currentTimeMillis());
        
        String query = "select * from entity_history where (organization_id, substr(parent_id, 1, 3), created_date) >= (?,?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, subStringParentId, createdDate);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertTrue(filter instanceof RowKeyComparisonFilter);
        byte[] expectedStartRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(subStringParentId));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testUseOfFunctionOnLHSInMiddleOfRVCForLTE() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = "000000000000002";
        String subStringParentId = parentId.substring(0, 3);
        Date createdDate = new Date(System.currentTimeMillis());
        
        String query = "select * from entity_history where (organization_id, substr(parent_id, 1, 3), created_date) <= (?,?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, subStringParentId, createdDate);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNotNull(filter);
        assertTrue(filter instanceof RowKeyComparisonFilter);
        byte[] expectedStopRow = ByteUtil.concat(
            PVarchar.INSTANCE.toBytes(tenantId), ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(subStringParentId)));
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStartRow());
        assertArrayEquals(expectedStopRow, scan.getStopRow());
    }
    
    @Test
    public void testNullAtEndOfRVC() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = "000000000000002";
        Date createdDate = null;
        
        String query = "select * from entity_history where (organization_id, parent_id, created_date) >= (?,?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, parentId, createdDate);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        byte[] expectedStartRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId), PVarchar.INSTANCE.toBytes(parentId));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testNullInMiddleOfRVC() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = null;
        Date createdDate = new Date(System.currentTimeMillis());
        
        String query = "select * from entity_history where (organization_id, parent_id, created_date) >= (?,?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, parentId, createdDate);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        byte[] expectedStartRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId), new byte[15], ByteUtil.previousKey(
            PDate.INSTANCE.toBytes(createdDate)));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testNullAtStartOfRVC() throws SQLException {
        String tenantId = null;
        String parentId = "000000000000002";
        Date createdDate = new Date(System.currentTimeMillis());
        
        String query = "select * from entity_history where (organization_id, parent_id, created_date) >= (?,?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, parentId, createdDate);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        byte[] expectedStartRow = ByteUtil.concat(new byte[15], ByteUtil.previousKey(PChar.INSTANCE.toBytes(parentId)), PDate.INSTANCE.toBytes(createdDate));
        assertArrayEquals(expectedStartRow, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testRVCInCombinationWithOtherNonRVC() throws SQLException {
        String firstOrgId = "000000000000001";
        String secondOrgId = "000000000000008";
        
        String parentId = "000000000000002";
        Date createdDate = new Date(System.currentTimeMillis());
        
        String query = "select * from entity_history where (organization_id, parent_id, created_date) >= (?,?,?) AND organization_id <= ?";
        List<Object> binds = Arrays.<Object>asList(firstOrgId, parentId, createdDate, secondOrgId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(ByteUtil.concat(PVarchar.INSTANCE.toBytes(firstOrgId), PVarchar.INSTANCE.toBytes(parentId), PDate.INSTANCE.toBytes(createdDate)), scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(secondOrgId)), scan.getStopRow());
    }
    
    @Test
    public void testGreaterThanEqualTo_NonRVCOnLHSAndRVCOnRHS_WithNonNullBindParams() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = "000000000000008";
        
        String query = "select * from entity_history where organization_id >= (?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, parentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testGreaterThan_NonRVCOnLHSAndRVCOnRHS_WithNonNullBindParams() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = "000000000000008";
        
        String query = "select * from entity_history where organization_id > (?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, parentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testGreaterThan() throws SQLException {
        String tenantId = "000000000000001";
        
        String query = "select * from entity_history where organization_id >?";
        List<Object> binds = Arrays.<Object>asList(tenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testLessThanEqualTo_NonRVCOnLHSAndRVCOnRHS_WithNonNullBindParams() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = "000000000000008";
        
        String query = "select * from entity_history where organization_id <= (?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, parentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }
    
    @Test
    public void testLessThan_NonRVCOnLHSAndRVCOnRHS_WithNonNullBindParams() throws SQLException {
        String tenantId = "000000000000001";
        String parentId = "000000000000008";
        
        String query = "select * from entity_history where organization_id < (?,?)";
        List<Object> binds = Arrays.<Object>asList(tenantId, parentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }
    
    @Test
    public void testQueryMoreRVC() throws SQLException {
        String ddl = "CREATE TABLE rvcTestIdx "
                + " (\n" + 
                "    pk1 VARCHAR NOT NULL,\n" + 
                "    v1 VARCHAR,\n" + 
                "    pk2 DECIMAL NOT NULL,\n" + 
                "    CONSTRAINT PK PRIMARY KEY \n" + 
                "    (\n" + 
                "        pk1,\n" + 
                "        v1,\n" + 
                "        pk2\n" + 
                "    )\n" + 
                ") MULTI_TENANT=true,IMMUTABLE_ROWS=true";
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES));
        conn.createStatement().execute(ddl);
        String query = "SELECT pk1, pk2, v1 FROM rvcTestIdx WHERE pk1 = 'a' AND\n" + 
                "(pk1, pk2) > ('a', 1)\n" + 
                "ORDER BY PK1, PK2\n" + 
                "LIMIT 2";
        StatementContext context = compileStatement(query, 2);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNotNull(filter);
        byte[] startRow = Bytes.toBytes("a");
        byte[] stopRow = ByteUtil.concat(startRow, ByteUtil.nextKey(QueryConstants.SEPARATOR_BYTE_ARRAY));
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(stopRow, scan.getStopRow());
    }
    
    @Test
    public void testCombiningRVCUsingOr() throws SQLException {
        String firstTenantId = "000000000000001";
        String secondTenantId = "000000000000005";
        String firstParentId = "000000000000011";
        String secondParentId = "000000000000015";
        
        String query = "select * from entity_history where (organization_id, parent_id) >= (?,?) OR (organization_id, parent_id) <= (?, ?)";
        List<Object> binds = Arrays.<Object>asList(firstTenantId, firstParentId, secondTenantId, secondParentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testCombiningRVCUsingOr2() throws SQLException {
        String firstTenantId = "000000000000001";
        String secondTenantId = "000000000000005";
        String firstParentId = "000000000000011";
        String secondParentId = "000000000000015";
        
        String query = "select * from entity_history where (organization_id, parent_id) >= (?,?) OR (organization_id, parent_id) >= (?, ?)";
        List<Object> binds = Arrays.<Object>asList(firstTenantId, firstParentId, secondTenantId, secondParentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(ByteUtil.concat(PVarchar.INSTANCE.toBytes(firstTenantId), PVarchar.INSTANCE.toBytes(firstParentId)), scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testCombiningRVCWithNonRVCUsingOr() throws SQLException {
        String firstTenantId = "000000000000001";
        String secondTenantId = "000000000000005";
        String firstParentId = "000000000000011";
        
        String query = "select * from entity_history where (organization_id, parent_id) >= (?,?) OR organization_id  >= ?";
        List<Object> binds = Arrays.<Object>asList(firstTenantId, firstParentId, secondTenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(ByteUtil.concat(PVarchar.INSTANCE.toBytes(firstTenantId), PVarchar.INSTANCE.toBytes(firstParentId)), scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testCombiningRVCWithNonRVCUsingOr2() throws SQLException {
        String firstTenantId = "000000000000001";
        String secondTenantId = "000000000000005";
        String firstParentId = "000000000000011";
        
        String query = "select * from entity_history where (organization_id, parent_id) >= (?,?) OR organization_id  <= ?";
        List<Object> binds = Arrays.<Object>asList(firstTenantId, firstParentId, secondTenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
    }
    
    @Test
    public void testCombiningRVCWithNonRVCUsingOr3() throws SQLException {
        String firstTenantId = "000000000000005";
        String secondTenantId = "000000000000001";
        String firstParentId = "000000000000011";
        String query = "select * from entity_history where (organization_id, parent_id) >= (?,?) OR organization_id  <= ?";
        List<Object> binds = Arrays.<Object>asList(firstTenantId, firstParentId, secondTenantId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertTrue(filter instanceof SkipScanFilter);
        assertArrayEquals(HConstants.EMPTY_START_ROW, scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
        SkipScanFilter skipScanFilter = (SkipScanFilter)filter;
        List<List<KeyRange>> keyRanges = skipScanFilter.getSlots();
        assertEquals(1, keyRanges.size());
        assertEquals(2, keyRanges.get(0).size());
        KeyRange range1 = keyRanges.get(0).get(0);
        KeyRange range2 = keyRanges.get(0).get(1);
        assertEquals(KeyRange.getKeyRange(KeyRange.UNBOUND, false, Bytes.toBytes(secondTenantId), true), range1);
        assertEquals(KeyRange.getKeyRange(ByteUtil.concat(Bytes.toBytes(firstTenantId), Bytes.toBytes(firstParentId)), true, KeyRange.UNBOUND, true), range2);
    }

    @Test
    public void testUsingRVCNonFullyQualifiedInClause() throws Exception {
        String firstOrgId = "000000000000001";
        String secondOrgId = "000000000000009";
        String firstParentId = "000000000000011";
        String secondParentId = "000000000000021";
        String query = "select * from entity_history where (organization_id, parent_id) IN ((?, ?), (?, ?))";
        List<Object> binds = Arrays.<Object>asList(firstOrgId, firstParentId, secondOrgId, secondParentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertTrue(filter instanceof SkipScanFilter);
        assertArrayEquals(ByteUtil.concat(PVarchar.INSTANCE.toBytes(firstOrgId), PVarchar.INSTANCE.toBytes(firstParentId)), scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(PVarchar.INSTANCE.toBytes(secondOrgId), PVarchar.INSTANCE.toBytes(secondParentId))), scan.getStopRow());
    }
    
    @Test
    public void testUsingRVCFullyQualifiedInClause() throws Exception {
        String firstOrgId = "000000000000001";
        String secondOrgId = "000000000000009";
        String firstParentId = "000000000000011";
        String secondParentId = "000000000000021";
        String query = "select * from atable where (organization_id, entity_id) IN ((?, ?), (?, ?))";
        List<Object> binds = Arrays.<Object>asList(firstOrgId, firstParentId, secondOrgId, secondParentId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertTrue(filter instanceof SkipScanFilter);
        List<List<KeyRange>> skipScanRanges = Collections.singletonList(Arrays.asList(
                KeyRange.getKeyRange(ByteUtil.concat(PChar.INSTANCE.toBytes(firstOrgId), PChar.INSTANCE.toBytes(firstParentId))),
                KeyRange.getKeyRange(ByteUtil.concat(PChar.INSTANCE.toBytes(secondOrgId), PChar.INSTANCE.toBytes(secondParentId)))));
        assertEquals(skipScanRanges, context.getScanRanges().getRanges());
        assertArrayEquals(ByteUtil.concat(PChar.INSTANCE.toBytes(firstOrgId), PChar.INSTANCE.toBytes(firstParentId)), scan.getStartRow());
        assertArrayEquals(ByteUtil.concat(PChar.INSTANCE.toBytes(secondOrgId), PChar.INSTANCE.toBytes(secondParentId), QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
    }
    
    @Test
    public void testFullyQualifiedRVCWithTenantSpecificViewAndConnection() throws Exception {
    	String baseTableDDL = "CREATE TABLE BASE_MULTI_TENANT_TABLE(\n " + 
                "  tenant_id VARCHAR(5) NOT NULL,\n" + 
                "  userid INTEGER NOT NULL,\n" + 
                "  username VARCHAR NOT NULL,\n" +
                "  col VARCHAR\n " + 
                "  CONSTRAINT pk PRIMARY KEY (tenant_id, userid, username)) MULTI_TENANT=true";
    	Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(baseTableDDL);
        conn.close();
        
        String tenantId = "tenantId";
        String tenantViewDDL = "CREATE VIEW TENANT_VIEW AS SELECT * FROM BASE_MULTI_TENANT_TABLE";
        Properties tenantProps = new Properties();
        tenantProps.put(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
        conn = DriverManager.getConnection(getUrl(), tenantProps);
        conn.createStatement().execute(tenantViewDDL);
        
        String query = "SELECT * FROM TENANT_VIEW WHERE (userid, username) IN ((?, ?), (?, ?))";
        List<Object> binds = Arrays.<Object>asList(1, "uname1", 2, "uname2");
        
        StatementContext context = compileStatementTenantSpecific(tenantId, query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertEquals(SkipScanFilter.class, filter.getClass());
    }
    
    @Test
    public void testFullyQualifiedRVCWithNonTenantSpecificView() throws Exception {
    	String baseTableDDL = "CREATE TABLE BASE_TABLE(\n " + 
                "  tenant_id VARCHAR(5) NOT NULL,\n" + 
                "  userid INTEGER NOT NULL,\n" + 
                "  username VARCHAR NOT NULL,\n" +
                "  col VARCHAR\n " + 
                "  CONSTRAINT pk PRIMARY KEY (tenant_id, userid, username))";
    	Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(baseTableDDL);
        conn.close();
        
        String viewDDL = "CREATE VIEW VIEWXYZ AS SELECT * FROM BASE_TABLE";
        conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(viewDDL);
        
        String query = "SELECT * FROM VIEWXYZ WHERE (tenant_id, userid, username) IN ((?, ?, ?), (?, ?, ?))";
        List<Object> binds = Arrays.<Object>asList("tenantId", 1, "uname1", "tenantId", 2, "uname2");
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertEquals(SkipScanFilter.class, filter.getClass());
    }
    
    @Test
    public void testRVCWithCompareOpsForRowKeyColumnValuesSmallerThanSchema() throws SQLException {
        String orgId = "0000005";
        String entityId = "011";
        String orgId2 = "000005";
        String entityId2 = "11";
        
        // CASE 1: >=
        String query = "select * from atable where (organization_id, entity_id) >= (?,?)";
        List<Object> binds = Arrays.<Object>asList(orgId, entityId);
        StatementContext context = compileStatement(query, binds);
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(ByteUtil.concat(StringUtil.padChar(PChar.INSTANCE.toBytes(orgId), 15), StringUtil.padChar(
            PChar.INSTANCE.toBytes(entityId), 15)), scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
        
        // CASE 2: >
        query = "select * from atable where (organization_id, entity_id) > (?,?)";
        binds = Arrays.<Object>asList(orgId, entityId);
        context = compileStatement(query, binds);
        scan = context.getScan();
        filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(StringUtil.padChar(PChar.INSTANCE.toBytes(orgId), 15), StringUtil.padChar(
            PChar.INSTANCE.toBytes(entityId), 15))), scan.getStartRow());
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow());
        
        // CASE 3: <=
        query = "select * from atable where (organization_id, entity_id) <= (?,?)";
        binds = Arrays.<Object>asList(orgId, entityId);
        context = compileStatement(query, binds);
        scan = context.getScan();
        filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(ByteUtil.concat(StringUtil.padChar(PChar.INSTANCE.toBytes(orgId), 15), StringUtil.padChar(
            PChar.INSTANCE.toBytes(entityId), 15))), scan.getStopRow());
        
        // CASE 4: <
        query = "select * from atable where (organization_id, entity_id) < (?,?)";
        binds = Arrays.<Object>asList(orgId, entityId);
        context = compileStatement(query, binds);
        scan = context.getScan();
        filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStartRow());
        assertArrayEquals(ByteUtil.concat(StringUtil.padChar(PChar.INSTANCE.toBytes(orgId), 15), StringUtil.padChar(
            PChar.INSTANCE.toBytes(entityId), 15)), scan.getStopRow());
        
        // CASE 5: =
        // For RVC, this will only occur if there's more than one key in the IN
        query = "select * from atable where (organization_id, entity_id) IN ((?,?),(?,?))";
        binds = Arrays.<Object>asList(orgId, entityId, orgId2, entityId2);
        context = compileStatement(query, binds);
        scan = context.getScan();
        filter = scan.getFilter();
        assertTrue(filter instanceof SkipScanFilter);
        ScanRanges scanRanges = context.getScanRanges();
        assertEquals(2,scanRanges.getPointLookupCount());
        Iterator<KeyRange> iterator = scanRanges.getPointLookupKeyIterator();
        KeyRange k1 = iterator.next();
        assertTrue(k1.isSingleKey());
        assertArrayEquals(ByteUtil.concat(StringUtil.padChar(PChar.INSTANCE.toBytes(orgId), 15), StringUtil.padChar(
            PChar.INSTANCE.toBytes(entityId), 15)), k1.getLowerRange());
        KeyRange k2 = iterator.next();
        assertTrue(k2.isSingleKey());
        assertArrayEquals(ByteUtil.concat(StringUtil.padChar(PChar.INSTANCE.toBytes(orgId2), 15), StringUtil.padChar(
            PChar.INSTANCE.toBytes(entityId2), 15)), k2.getLowerRange());
    }
    
    
    @Test
    public void testRVCInView() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute("CREATE TABLE TEST_TABLE.TEST1 (\n" + 
                "PK1 CHAR(3) NOT NULL, \n" + 
                "PK2 CHAR(3) NOT NULL,\n" + 
                "DATA1 CHAR(10)\n" + 
                "CONSTRAINT PK PRIMARY KEY (PK1, PK2))");
        conn.createStatement().execute("CREATE VIEW TEST_TABLE.FOO AS SELECT * FROM TEST_TABLE.TEST1 WHERE PK1 = 'FOO'");
        String query = "SELECT * FROM TEST_TABLE.FOO WHERE PK2 < '004' AND (PK1,PK2) > ('FOO','002') LIMIT 2";
        Scan scan = compileStatement(query, Collections.emptyList(), 2).getScan();
        byte[] startRow = ByteUtil.nextKey(ByteUtil.concat(PChar.INSTANCE.toBytes("FOO"),
                PVarchar.INSTANCE.toBytes("002")));
        assertArrayEquals(startRow, scan.getStartRow());
        byte[] stopRow = ByteUtil.concat(PChar.INSTANCE.toBytes("FOO"),
                PChar.INSTANCE.toBytes("004"));
        assertArrayEquals(stopRow, scan.getStopRow());
    }

    private static StatementContext compileStatementTenantSpecific(String tenantId, String query, List<Object> binds) throws Exception {
    	PhoenixConnection pconn = getTenantSpecificConnection("tenantId").unwrap(PhoenixConnection.class);
        PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
        TestUtil.bindParams(pstmt, binds);
        QueryPlan plan = pstmt.compileQuery();
        return  plan.getContext();
    }
    
    private static Connection getTenantSpecificConnection(String tenantId) throws Exception {
    	Properties tenantProps = new Properties();
        tenantProps.put(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
        Connection conn = DriverManager.getConnection(getUrl(), tenantProps);
        return conn;
    }
    
    @Test
    public void testTrailingIsNull() throws Exception {
        String baseTableDDL = "CREATE TABLE t(\n " + 
                "  a VARCHAR,\n" + 
                "  b VARCHAR,\n" + 
                "  CONSTRAINT pk PRIMARY KEY (a, b))";
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(baseTableDDL);
        conn.close();
        
        String query = "SELECT * FROM t WHERE a = 'a' and b is null";
        StatementContext context = compileStatement(query, Collections.<Object>emptyList());
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertNull(filter);
        assertArrayEquals(Bytes.toBytes("a"), scan.getStartRow());
        assertArrayEquals(ByteUtil.concat(Bytes.toBytes("a"), QueryConstants.SEPARATOR_BYTE_ARRAY, QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
    }
    
    
    @Test
    public void testTrailingIsNullWithOr() throws Exception {
        String baseTableDDL = "CREATE TABLE t(\n " + 
                "  a VARCHAR,\n" + 
                "  b VARCHAR,\n" + 
                "  CONSTRAINT pk PRIMARY KEY (a, b))";
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(baseTableDDL);
        conn.close();
        
        String query = "SELECT * FROM t WHERE a = 'a' and (b is null or b = 'b')";
        StatementContext context = compileStatement(query, Collections.<Object>emptyList());
        Scan scan = context.getScan();
        Filter filter = scan.getFilter();
        assertTrue(filter instanceof SkipScanFilter);
        SkipScanFilter skipScan = (SkipScanFilter)filter;
        List<List<KeyRange>>slots = skipScan.getSlots();
        assertEquals(2,slots.size());
        assertEquals(1,slots.get(0).size());
        assertEquals(2,slots.get(1).size());
        assertEquals(KeyRange.getKeyRange(Bytes.toBytes("a")), slots.get(0).get(0));
        assertTrue(KeyRange.IS_NULL_RANGE == slots.get(1).get(0));
        assertEquals(KeyRange.getKeyRange(Bytes.toBytes("b")), slots.get(1).get(1));
        assertArrayEquals(Bytes.toBytes("a"), scan.getStartRow());
        assertArrayEquals(ByteUtil.concat(Bytes.toBytes("a"), QueryConstants.SEPARATOR_BYTE_ARRAY, Bytes.toBytes("b"), QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
    }
    
    @Test
    public void testAndWithRVC() throws Exception {
        String ddl;
        String query;
        StatementContext context;        
        Connection conn = DriverManager.getConnection(getUrl());

        ddl = "create table t (a integer not null, b integer not null, c integer constraint pk primary key (a,b))";
        conn.createStatement().execute(ddl);
        
        query = "select c from t where a in (1,2) and b = 3 and (a,b) in ( (1,2) , (1,3))";
        context = compileStatement(query, Collections.<Object>emptyList());
        assertArrayEquals(ByteUtil.concat(PInteger.INSTANCE.toBytes(1), PInteger.INSTANCE.toBytes(3)), context.getScan().getStartRow());
        assertArrayEquals(ByteUtil.concat(PInteger.INSTANCE.toBytes(1), ByteUtil.nextKey(PInteger.INSTANCE.toBytes(3))), context.getScan().getStopRow());

        query = "select c from t where (a,b) in ( (1,2) , (1,3) ) and b = 4";
        context = compileStatement(query, Collections.<Object>emptyList());
        assertDegenerate(context.getScan());
        
        query = "select c from t where a = 1 and b = 3 and (a,b) in ( (1,2) , (1,3))";
        context = compileStatement(query, Collections.<Object>emptyList());
        assertArrayEquals(ByteUtil.concat(PInteger.INSTANCE.toBytes(1), PInteger.INSTANCE.toBytes(3)), context.getScan().getStartRow());
        assertArrayEquals(ByteUtil.concat(PInteger.INSTANCE.toBytes(1), ByteUtil.nextKey(PInteger.INSTANCE.toBytes(3))), context.getScan().getStopRow());

        // Test with RVC occurring later in the PK
        ddl = "create table t1 (d varchar, e char(3) not null, a integer not null, b integer not null, c integer constraint pk primary key (d, e, a,b))";
        conn.createStatement().execute(ddl);
        
        query = "select c from t1 where d = 'a' and e = 'foo' and a in (1,2) and b = 3 and (a,b) in ( (1,2) , (1,3))";
        context = compileStatement(query, Collections.<Object>emptyList());
        Scan scan = context.getScan();
        assertArrayEquals(ByteUtil.concat(PVarchar.INSTANCE.toBytes("a"), QueryConstants.SEPARATOR_BYTE_ARRAY, PChar.INSTANCE.toBytes("foo"), PInteger.INSTANCE.toBytes(1), PInteger.INSTANCE.toBytes(3)), scan.getStartRow());
        assertArrayEquals(ByteUtil.concat(PVarchar.INSTANCE.toBytes("a"), QueryConstants.SEPARATOR_BYTE_ARRAY, PChar.INSTANCE.toBytes("foo"), PInteger.INSTANCE.toBytes(1), ByteUtil.nextKey(PInteger.INSTANCE.toBytes(3))), scan.getStopRow());
        
        conn.close();
    }

    @Test
    public void testNoAggregatorForOrderBy() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES));
        conn.createStatement().execute("create table test (pk1 integer not null, pk2 integer not null, constraint pk primary key (pk1,pk2))");
        StatementContext context = compileStatement("select count(distinct pk1) from test order by count(distinct pk2)");
        assertEquals(1, context.getAggregationManager().getAggregators().getAggregatorCount());
        context = compileStatement("select sum(pk1) from test order by count(distinct pk2)");
        assertEquals(1, context.getAggregationManager().getAggregators().getAggregatorCount());
        context = compileStatement("select min(pk1) from test order by count(distinct pk2)");
        assertEquals(1, context.getAggregationManager().getAggregators().getAggregatorCount());
        context = compileStatement("select max(pk1) from test order by count(distinct pk2)");
        assertEquals(1, context.getAggregationManager().getAggregators().getAggregatorCount());
        // here the ORDER BY is not optimized away
        context = compileStatement("select avg(pk1) from test order by count(distinct pk2)");
        assertEquals(2, context.getAggregationManager().getAggregators().getAggregatorCount());
    }
    
    @Test
    public void testPartialRVCWithLeadingPKEq() throws SQLException {
        String tenantId = "o1";
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute("CREATE TABLE COMMUNITIES.TEST (\n" + 
                "    ORGANIZATION_ID CHAR(2) NOT NULL,\n" + 
                "    SCORE DOUBLE NOT NULL,\n" + 
                "    ENTITY_ID CHAR(2) NOT NULL\n" + 
                "    CONSTRAINT PAGE_SNAPSHOT_PK PRIMARY KEY (\n" + 
                "        ORGANIZATION_ID,\n" + 
                "        SCORE,\n" + 
                "        ENTITY_ID\n" + 
                "    )\n" + 
                ") VERSIONS=1, MULTI_TENANT=TRUE");
        String query = "SELECT entity_id, score\n" + 
                "FROM communities.test\n" + 
                "WHERE organization_id = '" + tenantId + "'\n" + 
                "AND (score, entity_id) > (2.0, '04')\n" + 
                "ORDER BY score, entity_id";
        Scan scan = compileStatement(query).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.nextKey(ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId), PDouble.INSTANCE.toBytes(2.0), PChar.INSTANCE.toBytes("04")));
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }

    @Test
    public void testPartialRVCWithLeadingPKEqDesc() throws SQLException {
        String tenantId = "o1";
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute("CREATE TABLE COMMUNITIES.TEST (\n" + 
                "    ORGANIZATION_ID CHAR(2) NOT NULL,\n" + 
                "    SCORE DOUBLE NOT NULL,\n" + 
                "    ENTITY_ID CHAR(2) NOT NULL\n" + 
                "    CONSTRAINT PAGE_SNAPSHOT_PK PRIMARY KEY (\n" + 
                "        ORGANIZATION_ID,\n" + 
                "        SCORE DESC,\n" + 
                "        ENTITY_ID DESC\n" + 
                "    )\n" + 
                ") VERSIONS=1, MULTI_TENANT=TRUE");
        String query = "SELECT entity_id, score\n" + 
                "FROM communities.test\n" + 
                "WHERE organization_id = '" + tenantId + "'\n" + 
                "AND (score, entity_id) < (2.0, '04')\n" + 
                "ORDER BY score DESC, entity_id DESC";
        Scan scan = compileStatement(query).getScan();
        assertNull(scan.getFilter());

        byte[] startRow = ByteUtil.nextKey(ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId), PDouble.INSTANCE.toBytes(2.0, SortOrder.DESC), PChar.INSTANCE.toBytes("04", SortOrder.DESC)));
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }

    @Test
    public void testFullRVCWithLeadingPKEqDesc() throws SQLException {
        String tenantId = "o1";
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute("CREATE TABLE COMMUNITIES.TEST (\n" + 
                "    ORGANIZATION_ID CHAR(2) NOT NULL,\n" + 
                "    SCORE DOUBLE NOT NULL,\n" + 
                "    ENTITY_ID CHAR(2) NOT NULL\n" + 
                "    CONSTRAINT PAGE_SNAPSHOT_PK PRIMARY KEY (\n" + 
                "        ORGANIZATION_ID,\n" + 
                "        SCORE DESC,\n" + 
                "        ENTITY_ID DESC\n" + 
                "    )\n" + 
                ") VERSIONS=1, MULTI_TENANT=TRUE");
        String query = "SELECT entity_id, score\n" + 
                "FROM communities.test\n" + 
                "WHERE organization_id = '" + tenantId + "'\n" + 
                "AND (organization_id, score, entity_id) < ('" + tenantId + "',2.0, '04')\n" + 
                "ORDER BY score DESC, entity_id DESC";
        Scan scan = compileStatement(query).getScan();
        assertNull(scan.getFilter());

        // TODO: end to end test that confirms this start row is accurate
        byte[] startRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId), PDouble.INSTANCE.toBytes(2.0, SortOrder.DESC), ByteUtil.nextKey(PChar.INSTANCE.toBytes("04", SortOrder.DESC)));
        assertArrayEquals(startRow, scan.getStartRow());
        assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
    }
    
    @Test
    public void testTrimTrailing() throws Exception {
        try (Connection conn= DriverManager.getConnection(getUrl())) {
            String sql="CREATE TABLE T("+
                    "A CHAR(1) NOT NULL,"+
                    "B CHAR(1) NOT NULL,"+
                    "C CHAR(1) NOT NULL,"+
                    "D CHAR(1) NOT NULL,"+
                    "DATA INTEGER, "+
                    "CONSTRAINT TEST_PK PRIMARY KEY (A,B,C,D))";
            conn.createStatement().execute(sql);

            // Will cause trailing part of RVC to (A,B,C) to be trimmed allowing us to perform a skip scan
            sql="select * from T where (A,B,C) >= ('A','A','A') and (A,B,C) < ('D','D','D') and (B,C) > ('E','E')";
            QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            Scan scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SkipScanFilter);
            List<List<KeyRange>> rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(
                                KeyRange.getKeyRange(PChar.INSTANCE.toBytes("A"), true, PChar.INSTANCE.toBytes("D"), false)
                                ),
                        Arrays.asList(
                                KeyRange.getKeyRange(PChar.INSTANCE.toBytes("EE"), false, KeyRange.UNBOUND, false)
                                )
                            ),
                     rowKeyRanges
                    );
            assertArrayEquals(scan.getStartRow(), PChar.INSTANCE.toBytes("AEF"));
            assertArrayEquals(scan.getStopRow(), PChar.INSTANCE.toBytes("D"));
            sql="select * from T where (A,B,C) > ('A','A','A') and (A,B,C) <= ('D','D','D') and (B,C) >= ('E','E')";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SkipScanFilter);
            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(
                                KeyRange.getKeyRange(PChar.INSTANCE.toBytes("A"), false, PChar.INSTANCE.toBytes("D"), true)
                                ),
                        Arrays.asList(
                                KeyRange.getKeyRange(PChar.INSTANCE.toBytes("EE"), true, KeyRange.UNBOUND, false)
                                )
                            ),
                     rowKeyRanges
                    );
            assertArrayEquals(PChar.INSTANCE.toBytes("BEE"), scan.getStartRow());
            assertArrayEquals(PChar.INSTANCE.toBytes("E"), scan.getStopRow());
        }
    }

    @Test
    public void testMultiSlotTrailingIntersect() throws Exception {
        try (Connection conn= DriverManager.getConnection(getUrl())) {
            String sql="CREATE TABLE T("+
                    "A CHAR(1) NOT NULL,"+
                    "B CHAR(1) NOT NULL,"+
                    "C CHAR(1) NOT NULL,"+
                    "D CHAR(1) NOT NULL,"+
                    "DATA INTEGER, "+
                    "CONSTRAINT TEST_PK PRIMARY KEY (A,B,C,D))";
            conn.createStatement().execute(sql);

            sql = "select * from t where (a,b) in (('A','B'),('B','A'),('B','B'),('A','A')) and (a,b,c) in ( ('A','B','C') , ('A','C','D'), ('B','B','E'))";
            QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            Scan scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SkipScanFilter);
            List<List<KeyRange>> rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(
                                KeyRange.POINT.apply(PChar.INSTANCE.toBytes("ABC")),
                                KeyRange.POINT.apply(PChar.INSTANCE.toBytes("BBE"))
                                )
                            ),
                     rowKeyRanges
                    );
            assertArrayEquals(scan.getStartRow(), PChar.INSTANCE.toBytes("ABC"));
            assertArrayEquals(scan.getStopRow(), PChar.INSTANCE.toBytes("BBF"));
        }
    }

    @Test
    public void testEqualityAndGreaterThanRVC() throws SQLException {
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
            conn.createStatement().execute("CREATE TABLE T (\n" + 
                    "    A CHAR(1) NOT NULL,\n" + 
                    "    B CHAR(1) NOT NULL,\n" + 
                    "    C CHAR(1) NOT NULL,\n" + 
                    "    D CHAR(1) NOT NULL,\n" + 
                    "    CONSTRAINT PK PRIMARY KEY (\n" + 
                    "        A,\n" + 
                    "        B,\n" + 
                    "        C,\n" + 
                    "        D\n" + 
                    "    )\n" + 
                    ")");
            String query = "SELECT * FROM T WHERE A = 'C' and (A,B,C) > ('C','B','X') and C='C'";
            QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, query);
            Scan scan = queryPlan.getContext().getScan();
            assertArrayEquals(ByteUtil.concat(PChar.INSTANCE.toBytes("C"), PChar.INSTANCE.toBytes("C"), PChar.INSTANCE.toBytes("C")), scan.getStartRow());
            assertArrayEquals(PChar.INSTANCE.toBytes("D"), scan.getStopRow());
        }
    }
    
    @Test
    public void testOrExpressionNonLeadingPKPushToScanBug4602() throws Exception {
        Connection conn = null;
        try {
            conn= DriverManager.getConnection(getUrl());
            String testTableName="OR_NO_LEADING_PK4602";
            String sql="CREATE TABLE "+ testTableName +"("+
                    "PK1 INTEGER NOT NULL,"+
                    "PK2 INTEGER NOT NULL,"+
                    "PK3 INTEGER NOT NULL,"+
                    "DATA INTEGER, "+
                    "CONSTRAINT TEST_PK PRIMARY KEY (PK1,PK2,PK3))";
            conn.createStatement().execute(sql);
            
            //case 1: pk1 is equal,pk2 is multiRange
            sql="select * from "+testTableName+" t where (t.pk1 = 2) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
            QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            Scan scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SkipScanFilter);
            List<List<KeyRange>> rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(KeyRange.POINT.apply(PInteger.INSTANCE.toBytes(2))),
                        Arrays.asList(
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false),
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false)
                                )
                            ),
                     rowKeyRanges
                    );

            assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2), PInteger.INSTANCE.toBytes(4)));
            assertArrayEquals(scan.getStopRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2), PInteger.INSTANCE.toBytes(9)));

            //case 2: pk1 is range,pk2 is multiRange
            sql="select * from "+testTableName+" t where (t.pk1 >=2 and t.pk1<5) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SkipScanFilter);
            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2), true, PInteger.INSTANCE.toBytes(5), false)),
                        Arrays.asList(
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false),
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false)
                                )
                            ),
                     rowKeyRanges
                    );
            assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2), PInteger.INSTANCE.toBytes(4)));
            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(5));

             //case 3 : pk1 has multiRange,,pk2 is multiRange
            sql="select * from "+testTableName+" t where ((t.pk1 >=2 and t.pk1<5) or (t.pk1 >=7 and t.pk1 <9)) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SkipScanFilter);
            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2), true, PInteger.INSTANCE.toBytes(5), false),
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(7), true, PInteger.INSTANCE.toBytes(9), false)
                                ),
                        Arrays.asList(
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false),
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false)
                                )
                            ),
                     rowKeyRanges
                    );
            assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2), PInteger.INSTANCE.toBytes(4)));
            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));

            //case4 : only pk1 and pk3, no pk2
            sql="select * from "+testTableName+" t where ((t.pk1 >=2 and t.pk1<5) or (t.pk1 >=7 and t.pk1 <9)) and ((t.pk3 >= 4 and t.pk3 <6) or (t.pk3 >= 8 and t.pk3 <9))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            /**
             * This sql use skipScan, and all the whereExpressions are in SkipScanFilter,
             * so there is no other RowKeyComparisonFilter needed.
             */
            assertTrue(scan.getFilter() instanceof SkipScanFilter);

            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2), true, PInteger.INSTANCE.toBytes(5), false),
                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(7), true, PInteger.INSTANCE.toBytes(9), false)
                                ),
                       Arrays.asList(KeyRange.EVERYTHING_RANGE),
                       Arrays.asList(
                               KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false),
                               KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false)
                               )
                            ),
                     rowKeyRanges
                    );
            assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(2));
            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));

            //case 5: pk1 or data column
            sql="select * from "+testTableName+" t where ((t.pk1 >=2) or (t.data >= 4 and t.data <9))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SingleCQKeyValueComparisonFilter);
            Expression pk1Expression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK1").getPosition()).newColumnExpression();
            Expression dataExpression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("DATA").getPosition()).newColumnExpression();
            assertEquals(
                    TestUtil.singleKVFilter(
                        TestUtil.or(
                                TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk1Expression, 2),
                                TestUtil.and(
                                        TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, dataExpression, 4),
                                        TestUtil.constantComparison(CompareOp.LESS, dataExpression, 9)
                                        )
                                )
                    ),
                    scan.getFilter());
            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);

            //case 6: pk1 or pk2,but pk2 is empty range
            sql ="select * from "+testTableName+" t where (t.pk1 >=2 and t.pk1<5) or ((t.pk2 >= 4 and t.pk2 <6) and (t.pk2 >= 8 and t.pk2 <9))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertNull(scan.getFilter());
            assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(2));
            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(5));

            //case 7: pk1 or pk2,but pk2 is all range
            sql ="select * from "+testTableName+" t where (t.pk1 >=2 and t.pk1<5) or (t.pk2 >=7 or t.pk2 <9)";
            queryPlan= TestUtil.getOptimizeQueryPlan(conn, sql);

            scan = queryPlan.getContext().getScan();
            Expression pk2Expression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK2").getPosition()).newColumnExpression();
            assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
            assertEquals(
                      TestUtil.rowKeyFilter(
                            TestUtil.or(
                                    TestUtil.and(
                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk1Expression, 2),
                                            TestUtil.constantComparison(CompareOp.LESS,pk1Expression, 5)),
                                    TestUtil.or(
                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk2Expression, 7),
                                            TestUtil.constantComparison(CompareOp.LESS,pk2Expression, 9))
                                    )
                              ),
                     scan.getFilter());
            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);

            //case 8: pk1 and pk2, but pk1 has a or allRange
            sql="select * from "+testTableName+" t where ((t.pk1 >=2 and t.pk1<5) or (t.pk1 >=7 or t.pk1 <9)) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
            assertEquals(
                      TestUtil.rowKeyFilter(
                            TestUtil.or(
                                    TestUtil.and(
                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk2Expression, 4),
                                            TestUtil.constantComparison(CompareOp.LESS,pk2Expression, 6)),
                                    TestUtil.and(
                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk2Expression, 8),
                                            TestUtil.constantComparison(CompareOp.LESS,pk2Expression, 9))
                                    )
                              ),
                     scan.getFilter());

            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);

            //case 9:  pk1 and pk2, but pk2 has a or allRange
            sql="select * from "+testTableName+" t where ((t.pk1 >= 4 and t.pk1 <6) or (t.pk1 >= 8 and t.pk1 <9)) and ((t.pk2 >=2 and t.pk2<5) or (t.pk2 >=7 or t.pk2 <9))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SkipScanFilter);
            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                            Arrays.asList(
                                    KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false),
                                    KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false)
                                    ),
                            Arrays.asList(KeyRange.EVERYTHING_RANGE)),
                    rowKeyRanges);
            assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(4));
            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));

            //case 10: only pk2
            sql = "select * from "+testTableName+" t where (pk2 <=7 or pk2>9)";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            pk2Expression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK2").getPosition()).newColumnExpression();
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
                assertEquals(
                      TestUtil.rowKeyFilter(
                            TestUtil.or(
                                    TestUtil.constantComparison(CompareOp.LESS_OR_EQUAL,pk2Expression, 7),
                                    TestUtil.constantComparison(CompareOp.GREATER,pk2Expression, 9))),
                     scan.getFilter());
            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
            
            //case 11: pk1 and pk2, but pk1 has a or allRange and force skip scan
            sql="select /*+ SKIP_SCAN */ * from "+testTableName+" t where ((t.pk1 >=2 and t.pk1<5) or (t.pk1 >=7 or t.pk1 <9)) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof SkipScanFilter);
            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
            assertEquals(
                    Arrays.asList(
                            Arrays.asList(KeyRange.EVERYTHING_RANGE),
                            Arrays.asList(
                                    KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false),
                                    KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false)
                                    )
                            ),
                    rowKeyRanges);
            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
        }
        finally {
            if(conn!=null) {
                conn.close();
            }
        }
    }

    @Test
    public void testLastPkColumnIsVariableLengthAndDescBug5307() throws Exception {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(getUrl());
            String sql =  "CREATE TABLE t1 (\n" +
                    "OBJECT_VERSION VARCHAR NOT NULL,\n" +
                    "LOC VARCHAR,\n" +
                    "CONSTRAINT PK PRIMARY KEY (OBJECT_VERSION DESC))";
            conn.createStatement().execute(sql);

            byte[] startKey = ByteUtil.concat(
                    PVarchar.INSTANCE.toBytes("2222", SortOrder.DESC),
                    QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
            byte[] endKey = ByteUtil.concat(
                    PVarchar.INSTANCE.toBytes("1111", SortOrder.DESC),
                    QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
            ByteUtil.nextKey(endKey, endKey.length);
            sql = "SELECT /*+ RANGE_SCAN */ OBJ.OBJECT_VERSION, OBJ.LOC from t1 AS OBJ "+
                    "where OBJ.OBJECT_VERSION in ('1111','2222')";
            QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            Scan scan = queryPlan.getContext().getScan();
            assertArrayEquals(startKey, scan.getStartRow());
            assertArrayEquals(endKey, scan.getStopRow());

            sql =  "CREATE TABLE t2 (\n" +
                    "OBJECT_ID VARCHAR NOT NULL,\n" +
                    "OBJECT_VERSION VARCHAR NOT NULL,\n" +
                    "LOC VARCHAR,\n" +
                    "CONSTRAINT PK PRIMARY KEY (OBJECT_ID, OBJECT_VERSION DESC))";
            conn.createStatement().execute(sql);

            startKey = ByteUtil.concat(
                    PVarchar.INSTANCE.toBytes("obj1", SortOrder.ASC),
                    QueryConstants.SEPARATOR_BYTE_ARRAY,
                    PVarchar.INSTANCE.toBytes("2222", SortOrder.DESC),
                    QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
            /**
             * For following sql, queryPlan would use SkipScan and is regarded as PointLookup,
             * so the endKey is computed as {@link SchemaUtil#VAR_BINARY_SCHEMA},see {@link ScanRanges#create}.
             */
            endKey =  ByteUtil.concat(
                    PVarchar.INSTANCE.toBytes("obj3", SortOrder.ASC),
                    QueryConstants.SEPARATOR_BYTE_ARRAY,
                    PVarchar.INSTANCE.toBytes("1111", SortOrder.DESC),
                    QueryConstants.DESC_SEPARATOR_BYTE_ARRAY,
                    QueryConstants.SEPARATOR_BYTE_ARRAY);

            sql = "SELECT OBJ.OBJECT_ID, OBJ.OBJECT_VERSION, OBJ.LOC from t2 AS OBJ "+
                    "where (OBJ.OBJECT_ID, OBJ.OBJECT_VERSION) in (('obj1', '2222'),('obj2', '1111'),('obj3', '1111'))";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            FilterList filterList = (FilterList)scan.getFilter();
            assertTrue(filterList.getOperator() == Operator.MUST_PASS_ALL);
            assertEquals(filterList.getFilters().size(),2);
            assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
            assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
            RowKeyComparisonFilter rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
            assertTrue(rowKeyComparisonFilter.toString().equals(
                    "(OBJECT_ID, OBJECT_VERSION) IN ([111,98,106,49,0,205,205,205,205],[111,98,106,50,0,206,206,206,206],[111,98,106,51,0,206,206,206,206])"));

            assertTrue(queryPlan.getContext().getScanRanges().isPointLookup());
            assertArrayEquals(startKey, scan.getStartRow());
            assertArrayEquals(endKey, scan.getStopRow());
        }
        finally {
            if(conn != null) {
                conn.close();
            }
        }
    }

    @Test
    public void testRVCClipBug5753() throws Exception {
        String tableName = generateUniqueName();
        try (Connection conn = DriverManager.getConnection(getUrl())) {
            conn.setAutoCommit(true);
            Statement stmt = conn.createStatement();

            String sql = "CREATE TABLE "+tableName+" (" +
                         " pk1 INTEGER NOT NULL , " +
                         " pk2 INTEGER NOT NULL, " +
                         " pk3 INTEGER NOT NULL, " +
                         " pk4 INTEGER NOT NULL, " +
                         " pk5 INTEGER NOT NULL, " +
                         " pk6 INTEGER NOT NULL, " +
                         " pk7 INTEGER NOT NULL, " +
                         " pk8 INTEGER NOT NULL, " +
                         " v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4,pk5,pk6 desc,pk7,pk8))";;

            stmt.execute(sql);

            List<List<KeyRange>> rowKeyRanges = null;
            RowKeyComparisonFilter rowKeyComparisonFilter = null;
            QueryPlan queryPlan = null;
            Scan scan = null;

            sql = "SELECT  /*+ RANGE_SCAN */ * FROM  "+ tableName +
                  " WHERE (pk1, pk2) IN ((2, 3), (2, 4)) AND pk3 = 5";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
            rowKeyComparisonFilter = (RowKeyComparisonFilter)scan.getFilter();
            assertTrue(rowKeyComparisonFilter.toString().equals(
                    "((PK1, PK2) IN ([128,0,0,2,128,0,0,3],[128,0,0,2,128,0,0,4]) AND PK3 = 5)"));
            assertArrayEquals(
                    scan.getStartRow(),
                    ByteUtil.concat(
                            PInteger.INSTANCE.toBytes(2),
                            PInteger.INSTANCE.toBytes(3),
                            PInteger.INSTANCE.toBytes(5, SortOrder.DESC)));
            assertArrayEquals(
                    scan.getStopRow(),
                    ByteUtil.concat(
                            PInteger.INSTANCE.toBytes(2),
                            PInteger.INSTANCE.toBytes(4),
                            ByteUtil.nextKey(PInteger.INSTANCE.toBytes(5, SortOrder.DESC))));

            sql = "select * from " + tableName +
                    " where (pk1 >=1 and pk1<=2) and (pk2>=2 and pk2<=3) and (pk3,pk4) < (3,5)";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof FilterList);
            FilterList filterList = (FilterList)scan.getFilter();

            assertTrue(filterList.getOperator() == Operator.MUST_PASS_ALL);
            assertEquals(filterList.getFilters().size(),2);
            assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
            rowKeyRanges = ((SkipScanFilter)(filterList.getFilters().get(0))).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                    PInteger.INSTANCE.toBytes(1),
                                    true,
                                    PInteger.INSTANCE.toBytes(2),
                                    true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                    PInteger.INSTANCE.toBytes(2),
                                    true,
                                    PInteger.INSTANCE.toBytes(3),
                                    true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                    PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
                                    true,
                                    KeyRange.UNBOUND,
                                    false))
                    ),
                    rowKeyRanges);
            assertArrayEquals(
                    scan.getStartRow(),
                    ByteUtil.concat(
                            PInteger.INSTANCE.toBytes(1),
                            PInteger.INSTANCE.toBytes(2),
                            PInteger.INSTANCE.toBytes(3, SortOrder.DESC)));
            assertArrayEquals(
                    scan.getStopRow(),
                    ByteUtil.concat(
                    PInteger.INSTANCE.toBytes(2),
                    PInteger.INSTANCE.toBytes(4)));

            assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
            rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
            assertTrue(rowKeyComparisonFilter.toString().equals(
                    "(TO_INTEGER(PK3), PK4) < (TO_INTEGER(TO_INTEGER(3)), 5)"));

            /**
             * RVC is singleKey
             */
            sql = "select * from " + tableName +
                    " where (pk1 >=1 and pk1<=2) and (pk2>=2 and pk2<=3) and (pk3,pk4) in ((3,4),(4,5)) and "+
                    " (pk5,pk6,pk7) in ((5,6,7),(6,7,8)) and pk8 > 8";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof FilterList);
            filterList = (FilterList)scan.getFilter();

            assertTrue(filterList.getOperator() == Operator.MUST_PASS_ALL);
            assertEquals(filterList.getFilters().size(),2);
            assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
            rowKeyRanges = ((SkipScanFilter)(filterList.getFilters().get(0))).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(1),
                                        true,
                                        PInteger.INSTANCE.toBytes(2),
                                        true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(2),
                                        true,
                                        PInteger.INSTANCE.toBytes(3),
                                        true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(4, SortOrder.DESC),
                                        true,
                                        PInteger.INSTANCE.toBytes(4, SortOrder.DESC),
                                        true),
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
                                        true,
                                        PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
                                        true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(4),
                                        true,
                                        PInteger.INSTANCE.toBytes(4),
                                        true),
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(5),
                                        true,
                                        PInteger.INSTANCE.toBytes(5),
                                        true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(5),
                                        true,
                                        PInteger.INSTANCE.toBytes(5),
                                        true),
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(6),
                                        true,
                                        PInteger.INSTANCE.toBytes(6),
                                        true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(7, SortOrder.DESC),
                                        true,
                                        PInteger.INSTANCE.toBytes(7, SortOrder.DESC),
                                        true),
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(6, SortOrder.DESC),
                                        true,
                                        PInteger.INSTANCE.toBytes(6, SortOrder.DESC),
                                        true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(7),
                                        true,
                                        PInteger.INSTANCE.toBytes(7),
                                        true),
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(8),
                                        true,
                                        PInteger.INSTANCE.toBytes(8),
                                        true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                        PInteger.INSTANCE.toBytes(9),
                                        true,
                                        KeyRange.UNBOUND,
                                        false))
                    ),
                    rowKeyRanges);
            assertArrayEquals(
                    scan.getStartRow(),
                    ByteUtil.concat(
                            PInteger.INSTANCE.toBytes(1),
                            PInteger.INSTANCE.toBytes(2),
                            PInteger.INSTANCE.toBytes(4, SortOrder.DESC),
                            PInteger.INSTANCE.toBytes(4),
                            PInteger.INSTANCE.toBytes(5),
                            PInteger.INSTANCE.toBytes(7, SortOrder.DESC),
                            PInteger.INSTANCE.toBytes(7),
                            PInteger.INSTANCE.toBytes(9)));
            assertArrayEquals(
                    scan.getStopRow(),
                    ByteUtil.concat(
                    PInteger.INSTANCE.toBytes(2),
                    PInteger.INSTANCE.toBytes(3),
                    PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
                    PInteger.INSTANCE.toBytes(5),
                    PInteger.INSTANCE.toBytes(6),
                    PInteger.INSTANCE.toBytes(6, SortOrder.DESC),
                    PInteger.INSTANCE.toBytes(9)));

            assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
            rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
            assertTrue(rowKeyComparisonFilter.toString().equals(
                    "((PK3, PK4) IN ([127,255,255,251,128,0,0,5],[127,255,255,252,128,0,0,4])"+
                    " AND (PK5, PK6, PK7) IN ([128,0,0,5,127,255,255,249,128,0,0,7],[128,0,0,6,127,255,255,248,128,0,0,8]))"));
            /**
             * RVC is not singleKey
             */
            sql = "select * from " + tableName +
                  " where (pk1 >=1 and pk1<=2) and (pk2>=2 and pk2<=3) and (pk3,pk4) < (3,4) and "+
                  " (pk5,pk6,pk7) < (5,6,7) and pk8 > 8";
            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
            scan = queryPlan.getContext().getScan();
            assertTrue(scan.getFilter() instanceof FilterList);
            filterList = (FilterList)scan.getFilter();

            assertTrue(filterList.getOperator() == Operator.MUST_PASS_ALL);
            assertEquals(filterList.getFilters().size(),2);
            assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
            rowKeyRanges = ((SkipScanFilter)(filterList.getFilters().get(0))).getSlots();
            assertEquals(
                    Arrays.asList(
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                    PInteger.INSTANCE.toBytes(1),
                                    true,
                                    PInteger.INSTANCE.toBytes(2),
                                    true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                    PInteger.INSTANCE.toBytes(2),
                                    true,
                                    PInteger.INSTANCE.toBytes(3),
                                    true)),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                    PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
                                    true,
                                    KeyRange.UNBOUND,
                                    false)),
                        Arrays.asList(KeyRange.EVERYTHING_RANGE),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                    KeyRange.UNBOUND,
                                    false,
                                    PInteger.INSTANCE.toBytes(5),
                                    true)),
                        Arrays.asList(KeyRange.EVERYTHING_RANGE),
                        Arrays.asList(KeyRange.EVERYTHING_RANGE),
                        Arrays.asList(
                                KeyRange.getKeyRange(
                                    PInteger.INSTANCE.toBytes(9),
                                    true,
                                    KeyRange.UNBOUND,
                                    false))
                    ),
                    rowKeyRanges);
            assertArrayEquals(
                    scan.getStartRow(),
                    ByteUtil.concat(
                            PInteger.INSTANCE.toBytes(1),
                            PInteger.INSTANCE.toBytes(2),
                            PInteger.INSTANCE.toBytes(3, SortOrder.DESC)));
            assertArrayEquals(
                    scan.getStopRow(),
                    ByteUtil.concat(
                    PInteger.INSTANCE.toBytes(2),
                    PInteger.INSTANCE.toBytes(4)));

            assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
            rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
            assertTrue(rowKeyComparisonFilter.toString().equals(
                    "((TO_INTEGER(PK3), PK4) < (TO_INTEGER(TO_INTEGER(3)), 4) AND "+
                    "(PK5, TO_INTEGER(PK6), PK7) < (5, TO_INTEGER(TO_INTEGER(6)), 7))"));
        }
    }
}