/*
 * 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.ignite.internal.processors.query.calcite.integration;

import java.sql.Date;
import java.util.LinkedHashMap;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.cache.QueryEntity;
import org.apache.ignite.cache.QueryIndex;
import org.apache.ignite.cache.QueryIndexType;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.internal.processors.query.QueryUtils;
import org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessorTest;
import org.apache.ignite.internal.util.typedef.F;
import org.junit.Ignore;
import org.junit.Test;

import static java.util.Arrays.asList;
import static java.util.Collections.singletonList;
import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.containsAnyProject;
import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.containsAnyScan;
import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.containsIndexScan;
import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.containsSubPlan;
import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.containsTableScan;
import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.containsUnion;
import static org.apache.ignite.internal.processors.query.schema.management.SchemaManager.generateProxyIdxName;
import static org.hamcrest.CoreMatchers.not;

/**
 * Basic index tests.
 */
public class CalciteBasicSecondaryIndexIntegrationTest extends AbstractBasicIntegrationTest {
    /** */
    private static final String PK_IDX_NAME = QueryUtils.PRIMARY_KEY_INDEX;

    /** */
    private static final String AFFINITY_KEY_IDX_NAME = QueryUtils.AFFINITY_KEY_INDEX;

    /** */
    private static final String DEPID_IDX = "DEPID_IDX";

    /** */
    private static final String NAME_CITY_IDX = "NAME_CITY_IDX";

    /** */
    private static final String NAME_DEPID_CITY_IDX = "NAME_DEPID_CITY_IDX";

    /** */
    private static final String DATE_IDX = "DATE_IDX";

    /** */
    private static final String NAME_DATE_IDX = "NAME_DATE_IDX";

    /** {@inheritDoc} */
    @Override protected void beforeTestsStarted() throws Exception {
        super.beforeTestsStarted();

        QueryEntity projEntity = new QueryEntity();
        projEntity.setKeyType(Integer.class.getName());
        projEntity.setKeyFieldName("id");
        projEntity.setValueType(Developer.class.getName());
        projEntity.addQueryField("id", Integer.class.getName(), null);
        projEntity.addQueryField("name", String.class.getName(), null);
        projEntity.addQueryField("depId", Integer.class.getName(), null);
        projEntity.addQueryField("city", String.class.getName(), null);
        projEntity.addQueryField("age", Integer.class.getName(), null);

        QueryIndex simpleIdx = new QueryIndex("depId", true);
        simpleIdx.setName(DEPID_IDX);

        LinkedHashMap<String, Boolean> fields1 = new LinkedHashMap<>();
        fields1.put("name", false);
        fields1.put("city", false);
        QueryIndex complexIdxNameId = new QueryIndex(fields1, QueryIndexType.SORTED);
        complexIdxNameId.setName(NAME_CITY_IDX);

        LinkedHashMap<String, Boolean> fields2 = new LinkedHashMap<>();
        fields2.put("name", true);
        fields2.put("depId", false);
        fields2.put("city", false);
        QueryIndex complexIdxNameVer = new QueryIndex(fields2, QueryIndexType.SORTED);
        complexIdxNameVer.setName(NAME_DEPID_CITY_IDX);

        projEntity.setIndexes(asList(simpleIdx, complexIdxNameId, complexIdxNameVer));
        projEntity.setTableName("Developer");

        CacheConfiguration<Integer, Developer> projCfg = cache(projEntity);

        IgniteCache<Integer, Developer> devCache = client.createCache(projCfg);

        devCache.put(1, new Developer("Mozart", 3, "Vienna", 33));
        devCache.put(2, new Developer("Beethoven", 2, "Vienna", 44));
        devCache.put(3, new Developer("Bach", 1, "Leipzig", 55));
        devCache.put(4, new Developer("Strauss", 2, "Munich", 66));

        devCache.put(5, new Developer("Vagner", 4, "Leipzig", 70));
        devCache.put(6, new Developer("Chaikovsky", 5, "Votkinsk", 53));
        devCache.put(7, new Developer("Verdy", 6, "Rankola", 88));
        devCache.put(8, new Developer("Stravinsky", 7, "Spt", 89));
        devCache.put(9, new Developer("Rahmaninov", 8, "Starorussky ud", 70));
        devCache.put(10, new Developer("Shubert", 9, "Vienna", 31));
        devCache.put(11, new Developer("Glinka", 10, "Smolenskaya gb", 53));

        devCache.put(12, new Developer("Einaudi", 11, "", -1));
        devCache.put(13, new Developer("Glass", 12, "", -1));
        devCache.put(14, new Developer("Rihter", 13, "", -1));

        devCache.put(15, new Developer("Marradi", 14, "", -1));
        devCache.put(16, new Developer("Zimmer", 15, "", -1));
        devCache.put(17, new Developer("Hasaishi", 16, "", -1));

        devCache.put(18, new Developer("Arnalds", 17, "", -1));
        devCache.put(19, new Developer("Yiruma", 18, "", -1));
        devCache.put(20, new Developer("O'Halloran", 19, "", -1));

        devCache.put(21, new Developer("Cacciapaglia", 20, "", -1));
        devCache.put(22, new Developer("Prokofiev", 21, "", -1));
        devCache.put(23, new Developer("Musorgskii", 22, "", -1));

        QueryEntity bdEntity = new QueryEntity();
        bdEntity.setKeyType(Integer.class.getName());
        bdEntity.setKeyFieldName("id");
        bdEntity.setValueType(Birthday.class.getName());
        bdEntity.addQueryField("id", Integer.class.getName(), null);
        bdEntity.addQueryField("name", String.class.getName(), null);
        bdEntity.addQueryField("birthday", Date.class.getName(), null);

        QueryIndex dateIdx = new QueryIndex("birthday", true);
        dateIdx.setName(DATE_IDX);

        LinkedHashMap<String, Boolean> nameDateFields = new LinkedHashMap<>();
        nameDateFields.put("name", false);
        nameDateFields.put("birthday", false);
        QueryIndex nameDateIdx = new QueryIndex(nameDateFields, QueryIndexType.SORTED);
        nameDateIdx.setName(NAME_DATE_IDX);

        bdEntity.setIndexes(asList(dateIdx, nameDateIdx));
        bdEntity.setTableName("Birthday");

        CacheConfiguration<Integer, Birthday> bdCfg = cache(bdEntity);

        IgniteCache<Integer, Birthday> bdCache = client.createCache(bdCfg);

        bdCache.put(1, new Birthday("Mozart", Date.valueOf("1756-01-27")));
        bdCache.put(2, new Birthday("Beethoven", null));
        bdCache.put(3, new Birthday("Bach", Date.valueOf("1685-03-31")));
        bdCache.put(4, new Birthday("Strauss", Date.valueOf("1864-06-11")));
        bdCache.put(5, new Birthday("Vagner", Date.valueOf("1813-05-22")));
        bdCache.put(6, new Birthday("Chaikovsky", Date.valueOf("1840-05-07")));
        bdCache.put(7, new Birthday("Verdy", Date.valueOf("1813-10-10")));

        IgniteCache<CalciteQueryProcessorTest.Key, CalciteQueryProcessorTest.Developer> tblWithAff =
            client.getOrCreateCache(new CacheConfiguration<CalciteQueryProcessorTest.Key, CalciteQueryProcessorTest.Developer>()
                .setName("TBL_WITH_AFF_KEY")
                .setSqlSchema("PUBLIC")
                .setBackups(1)
                .setQueryEntities(F.asList(new QueryEntity(CalciteQueryProcessorTest.Key.class, CalciteQueryProcessorTest.Developer.class)
                .setTableName("TBL_WITH_AFF_KEY")))
        );

        tblWithAff.put(new CalciteQueryProcessorTest.Key(1, 2), new CalciteQueryProcessorTest.Developer("Petr", 10));
        tblWithAff.put(new CalciteQueryProcessorTest.Key(2, 3), new CalciteQueryProcessorTest.Developer("Ivan", 11));

        IgniteCache<Integer, CalciteQueryProcessorTest.Developer> tblConstrPk =
            client.getOrCreateCache(new CacheConfiguration<Integer, CalciteQueryProcessorTest.Developer>()
                .setName("TBL_CONSTR_PK")
                .setSqlSchema("PUBLIC")
                .setBackups(0)
                .setQueryEntities(F.asList(new QueryEntity(Integer.class, CalciteQueryProcessorTest.Developer.class)
                    .setTableName("TBL_CONSTR_PK")
                    .setKeyFieldName("id")
                    .addQueryField("id", Integer.class.getName(), null)))
            );

        tblConstrPk.put(1, new CalciteQueryProcessorTest.Developer("Petr", 10));
        tblConstrPk.put(2, new CalciteQueryProcessorTest.Developer("Ivan", 11));

        executeSql("CREATE TABLE PUBLIC.UNWRAP_PK" + " (F1 VARCHAR, F2 BIGINT, F3 BIGINT, F4 BIGINT, " +
            "CONSTRAINT PK PRIMARY KEY (F2, F1)) WITH \"backups=0, affinity_key=F1\"");

        executeSql("INSERT INTO PUBLIC.UNWRAP_PK(F1, F2, F3, F4) values ('Petr', 1, 2, 3)");
        executeSql("INSERT INTO PUBLIC.UNWRAP_PK(F1, F2, F3, F4) values ('Ivan', 2, 2, 4)");

        executeSql("INSERT INTO PUBLIC.UNWRAP_PK(F1, F2, F3, F4) values ('Ivan1', 21, 2, 4)");
        executeSql("INSERT INTO PUBLIC.UNWRAP_PK(F1, F2, F3, F4) values ('Ivan2', 22, 2, 4)");
        executeSql("INSERT INTO PUBLIC.UNWRAP_PK(F1, F2, F3, F4) values ('Ivan3', 23, 2, 4)");
        executeSql("INSERT INTO PUBLIC.UNWRAP_PK(F1, F2, F3, F4) values ('Ivan4', 24, 2, 4)");
        executeSql("INSERT INTO PUBLIC.UNWRAP_PK(F1, F2, F3, F4) values ('Ivan5', 25, 2, 4)");

        awaitPartitionMapExchange();
    }

    /** {@inheritDoc} */
    @Override protected void afterTest() {
        // Skip super method to keep caches after each test.
    }

    /** {@inheritDoc} */
    @Override protected void afterTestsStopped() {
        stopAllGrids();
    }

    /** */
    private <K, V> CacheConfiguration<K, V> cache(QueryEntity ent) {
        return new CacheConfiguration<K, V>(ent.getTableName())
            .setCacheMode(CacheMode.PARTITIONED)
            .setBackups(1)
            .setQueryEntities(singletonList(ent))
            .setSqlSchema("PUBLIC");
    }

    /** */
    @Test
    public void testEqualsFilterWithUnwrpKey() {
        assertQuery("SELECT F1 FROM UNWRAP_PK WHERE F2=2")
            .matches(containsIndexScan("PUBLIC", "UNWRAP_PK", QueryUtils.PRIMARY_KEY_INDEX))
            .returns("Ivan")
            .check();
    }

    /** */
    @Test
    public void testEqualsFilterWithUnwrpKeyAndAff() {
        assertQuery("SELECT F2 FROM UNWRAP_PK WHERE F1='Ivan'")
            .matches(containsIndexScan("PUBLIC", "UNWRAP_PK", QueryUtils.AFFINITY_KEY_INDEX))
            .check();
    }

    /** */
    @Test
    public void testIndexLoopJoin() {
        assertQuery("" +
            "SELECT /*+ DISABLE_RULE('MergeJoinConverter', 'NestedLoopJoinConverter') */ d1.name, d2.name " +
            "FROM Developer d1, Developer d2 WHERE d1.id = d2.id")
            .matches(containsSubPlan("IgniteCorrelatedNestedLoopJoin"))
            .returns("Bach", "Bach")
            .returns("Beethoven", "Beethoven")
            .returns("Mozart", "Mozart")
            .returns("Strauss", "Strauss")
            .returns("Vagner", "Vagner")
            .returns("Chaikovsky", "Chaikovsky")
            .returns("Verdy", "Verdy")
            .returns("Stravinsky", "Stravinsky")
            .returns("Rahmaninov", "Rahmaninov")
            .returns("Shubert", "Shubert")
            .returns("Glinka", "Glinka")
            .returns("Arnalds", "Arnalds")
            .returns("Glass", "Glass")
            .returns("O'Halloran", "O'Halloran")
            .returns("Prokofiev", "Prokofiev")
            .returns("Yiruma", "Yiruma")
            .returns("Cacciapaglia", "Cacciapaglia")
            .returns("Einaudi", "Einaudi")
            .returns("Hasaishi", "Hasaishi")
            .returns("Marradi", "Marradi")
            .returns("Musorgskii", "Musorgskii")
            .returns("Rihter", "Rihter")
            .returns("Zimmer", "Zimmer")
            .check();
    }

    /** */
    @Test
    public void testMergeJoin() {
        assertQuery("" +
            "SELECT /*+ DISABLE_RULE('CorrelatedNestedLoopJoin') */ d1.name, d2.name FROM Developer d1, Developer d2 " +
            "WHERE d1.depId = d2.depId")
            .matches(containsSubPlan("IgniteMergeJoin"))
            .returns("Bach", "Bach")
            .returns("Beethoven", "Beethoven")
            .returns("Beethoven", "Strauss")
            .returns("Mozart", "Mozart")
            .returns("Strauss", "Strauss")
            .returns("Strauss", "Beethoven")
            .returns("Vagner", "Vagner")
            .returns("Chaikovsky", "Chaikovsky")
            .returns("Verdy", "Verdy")
            .returns("Stravinsky", "Stravinsky")
            .returns("Rahmaninov", "Rahmaninov")
            .returns("Shubert", "Shubert")
            .returns("Glinka", "Glinka")
            .returns("Arnalds", "Arnalds")
            .returns("Glass", "Glass")
            .returns("O'Halloran", "O'Halloran")
            .returns("Prokofiev", "Prokofiev")
            .returns("Yiruma", "Yiruma")
            .returns("Cacciapaglia", "Cacciapaglia")
            .returns("Einaudi", "Einaudi")
            .returns("Hasaishi", "Hasaishi")
            .returns("Marradi", "Marradi")
            .returns("Musorgskii", "Musorgskii")
            .returns("Rihter", "Rihter")
            .returns("Zimmer", "Zimmer")
            .check();
    }

    // ===== No filter =====

    /** */
    @Test
    public void testNoFilter() {
        assertQuery("SELECT * FROM Developer")
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .returns(12, "Einaudi", 11, "", -1)
            .returns(13, "Glass", 12, "", -1)
            .returns(14, "Rihter", 13, "", -1)
            .returns(15, "Marradi", 14, "", -1)
            .returns(16, "Zimmer", 15, "", -1)
            .returns(17, "Hasaishi", 16, "", -1)
            .returns(18, "Arnalds", 17, "", -1)
            .returns(19, "Yiruma", 18, "", -1)
            .returns(20, "O'Halloran", 19, "", -1)
            .returns(21, "Cacciapaglia", 20, "", -1)
            .returns(22, "Prokofiev", 21, "", -1)
            .returns(23, "Musorgskii", 22, "", -1)
            .check();
    }

    // ===== _key filter =====

    /** */
    @Test
    public void testKeyColumnEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE _key=1")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", PK_IDX_NAME))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();

        assertQuery("SELECT * FROM Developer WHERE id=1")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", generateProxyIdxName(PK_IDX_NAME)))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testEqualsFilterWithAffIdx() {
        assertQuery("SELECT * FROM TBL_WITH_AFF_KEY WHERE affinityKey=3")
            .matches(containsIndexScan("PUBLIC", "TBL_WITH_AFF_KEY", AFFINITY_KEY_IDX_NAME))
            .returns(2, 3, "Ivan", 11)
            .check();
    }

    /** */
    @Test
    public void testEqualsFilterWithPkIdx1() {
        assertQuery("SELECT * FROM TBL_CONSTR_PK WHERE id=2")
            .matches(containsIndexScan("PUBLIC", "TBL_CONSTR_PK", generateProxyIdxName(PK_IDX_NAME)))
            .returns("Ivan", 11, 2)
            .check();
    }

    /** */
    @Test
    public void testKeyColumnGreaterThanFilter() {
        assertQuery("SELECT * FROM Developer WHERE _key>3 and _key<12")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", PK_IDX_NAME))
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .check();
    }

    /** */
    @Test
    public void testKeyColumnGreaterThanOrEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE _key>=? and _key<=?")
            .withParams(3, 11)
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", PK_IDX_NAME))
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .check();
    }

    /** */
    @Test
    public void testKeyColumnLessThanFilter() {
        assertQuery("SELECT * FROM Developer WHERE _key<?")
            .withParams(3)
            .matches(containsAnyScan("PUBLIC", "DEVELOPER"))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .check();
    }

    /** */
    @Test
    public void testKeyColumnLessThanOrEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE _key<=2")
            .matches(containsAnyScan("PUBLIC", "DEVELOPER"))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .check();
    }

    // ===== alias filter =====

    /** */
    @Test
    public void testKeyAliasEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE id=2")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", generateProxyIdxName(PK_IDX_NAME)))
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .check();
    }

    /** */
    @Test
    public void testKeyAliasGreaterThanFilter() {
        assertQuery("SELECT * FROM Developer WHERE id>? and id<?")
            .withParams(3, 12)
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", generateProxyIdxName(PK_IDX_NAME)))
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .check();
    }

    /** */
    @Test
    public void testKeyAliasGreaterThanOrEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE id>=3 and id<12")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", generateProxyIdxName(PK_IDX_NAME)))
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .check();
    }

    /** */
    @Test
    public void testKeyAliasLessThanFilter() {
        assertQuery("SELECT * FROM Developer WHERE id<3")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", generateProxyIdxName(PK_IDX_NAME)))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .check();
    }

    /** */
    @Test
    public void testKeyAliasLessThanOrEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE id<=2")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", generateProxyIdxName(PK_IDX_NAME)))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .check();
    }

    // ===== indexed field filter =====

    /** */
    @Test
    public void testIndexedFieldEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE depId=2")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(4, "Strauss", 2, "Munich", 66)
            .check();
    }

    /** */
    @Test
    public void testIndexedDateFieldEqualsFilter() {
        assertQuery("SELECT * FROM Birthday WHERE birthday = DATE '1813-05-22'")
            .matches(containsIndexScan("PUBLIC", "BIRTHDAY", DATE_IDX))
            .returns(5, "Vagner", Date.valueOf("1813-05-22"))
            .check();
    }

    /** */
    @Test
    public void testIndexedDateFieldEqualsParameterFilter() {
        assertQuery("SELECT * FROM Birthday WHERE birthday = ?")
            .withParams(Date.valueOf("1813-05-22"))
            .returns(5, "Vagner", Date.valueOf("1813-05-22"))
            .check();
    }

    /** */
    @Test
    public void testIndexedDateFieldGreaterThanFilter() {
        assertQuery("SELECT * FROM Birthday WHERE birthday > DATE '1813-05-22'")
            .matches(containsIndexScan("PUBLIC", "BIRTHDAY", DATE_IDX))
            .returns(4, "Strauss", Date.valueOf("1864-06-11"))
            .returns(6, "Chaikovsky", Date.valueOf("1840-05-07"))
            .returns(7, "Verdy", Date.valueOf("1813-10-10"))
            .check();
    }

    /** */
    @Test
    public void testIndexedDateFieldLessThanOrEqualFilter() {
        assertQuery("SELECT * FROM Birthday WHERE birthday <= DATE '1756-01-27'")
            .matches(containsIndexScan("PUBLIC", "BIRTHDAY", DATE_IDX))
            .returns(1, "Mozart", Date.valueOf("1756-01-27"))
            .returns(3, "Bach", Date.valueOf("1685-03-31"))
            .check();
    }

    /** */
    @Test
    public void testIndexedDateFieldBetweenFilter() {
        assertQuery("SELECT * FROM Birthday WHERE birthday BETWEEN DATE '1756-01-27' AND DATE '1813-10-10'")
            .matches(containsIndexScan("PUBLIC", "BIRTHDAY", DATE_IDX))
            .returns(1, "Mozart", Date.valueOf("1756-01-27"))
            .returns(5, "Vagner", Date.valueOf("1813-05-22"))
            .returns(7, "Verdy", Date.valueOf("1813-10-10"))
            .check();
    }

    /** */
    @Test
    public void testIndexedNameDateFieldEqualsFilter() {
        assertQuery("SELECT * FROM Birthday WHERE name = 'Vagner' AND birthday = DATE '1813-05-22'")
            .matches(containsIndexScan("PUBLIC", "BIRTHDAY", NAME_DATE_IDX))
            .returns(5, "Vagner", Date.valueOf("1813-05-22"))
            .check();
    }

    /** */
    @Test
    public void testIndexedFieldGreaterThanFilter() {
        assertQuery("SELECT * FROM Developer WHERE depId>21")
            .withParams(3)
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(23, "Musorgskii", 22, "", -1)
            .check();
    }

    /** */
    @Test
    public void testIndexedFieldGreaterThanOrEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE depId>=21")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(22, "Prokofiev", 21, "", -1)
            .returns(23, "Musorgskii", 22, "", -1)
            .check();
    }

    /** */
    @Test
    public void testIndexedFieldLessThanFilter() {
        assertQuery("SELECT * FROM Developer WHERE depId<?")
            .withParams(3)
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .check();
    }

    /** */
    @Test
    public void testIndexedFieldLessThanOrEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE depId<=?")
            .withParams(2)
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .check();
    }

    // ===== non-indexed field filter =====

    /** */
    @Test
    public void testNonIndexedFieldEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE age=?")
            .withParams(44)
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .check();
    }

    /** */
    @Test
    public void testNonIndexedFieldGreaterThanFilter() {
        assertQuery("SELECT * FROM Developer WHERE age>?")
            .withParams(50)
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .check();
    }

    /** */
    @Test
    public void testNonIndexedFieldGreaterThanOrEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE age>=?")
            .withParams(34)
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .check();
    }

    /** */
    @Test
    public void testNonIndexedFieldLessThanFilter() {
        assertQuery("SELECT * FROM Developer WHERE age<?")
            .withParams(56)
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .returns(12, "Einaudi", 11, "", -1)
            .returns(13, "Glass", 12, "", -1)
            .returns(14, "Rihter", 13, "", -1)
            .returns(15, "Marradi", 14, "", -1)
            .returns(16, "Zimmer", 15, "", -1)
            .returns(17, "Hasaishi", 16, "", -1)
            .returns(18, "Arnalds", 17, "", -1)
            .returns(19, "Yiruma", 18, "", -1)
            .returns(20, "O'Halloran", 19, "", -1)
            .returns(21, "Cacciapaglia", 20, "", -1)
            .returns(22, "Prokofiev", 21, "", -1)
            .returns(23, "Musorgskii", 22, "", -1)
            .check();
    }

    /** */
    @Test
    public void testNonIndexedFieldLessThanOrEqualsFilter() {
        assertQuery("SELECT * FROM Developer WHERE age<=?")
            .withParams(55)
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .returns(12, "Einaudi", 11, "", -1)
            .returns(13, "Glass", 12, "", -1)
            .returns(14, "Rihter", 13, "", -1)
            .returns(15, "Marradi", 14, "", -1)
            .returns(16, "Zimmer", 15, "", -1)
            .returns(17, "Hasaishi", 16, "", -1)
            .returns(18, "Arnalds", 17, "", -1)
            .returns(19, "Yiruma", 18, "", -1)
            .returns(20, "O'Halloran", 19, "", -1)
            .returns(21, "Cacciapaglia", 20, "", -1)
            .returns(22, "Prokofiev", 21, "", -1)
            .returns(23, "Musorgskii", 22, "", -1)
            .check();
    }

    // ===== various complex conditions =====

    /** */
    @Test
    public void testComplexIndexCondition1() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND depId=3")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition2() {
        assertQuery("SELECT * FROM Developer WHERE depId=? AND name=?")
            .withParams(3, "Mozart")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition3() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND depId=3 AND city='Vienna'")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition4() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND depId=3 AND city='Leipzig'")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_DEPID_CITY_IDX))
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition5() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND city='Vienna'")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition6() {
        assertQuery("SELECT * FROM Developer WHERE name>='Mozart' AND depId=3")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition7() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND depId>=2")
            .matches(containsAnyScan("PUBLIC", "DEVELOPER", NAME_CITY_IDX, NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition8() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND depId>=2 AND age>20")
            .matches(containsAnyScan("PUBLIC", "DEVELOPER", NAME_CITY_IDX, NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition9() {
        assertQuery("SELECT * FROM Developer WHERE name>='Mozart' AND depId>=2 AND city>='Vienna'")
            .matches(containsAnyScan("PUBLIC", "DEVELOPER", NAME_CITY_IDX, NAME_DEPID_CITY_IDX, DEPID_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition10() {
        assertQuery("SELECT * FROM Developer WHERE name>='Mozart' AND city>='Vienna'")
            .matches(containsAnyScan("PUBLIC", "DEVELOPER", NAME_CITY_IDX, NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition11() {
        assertQuery("SELECT * FROM Developer WHERE name>='Mozart' AND depId=3 AND city>='Vienna'")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition12() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND depId=3 AND city='Vienna'")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition13() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND depId>=3 AND city='Vienna'")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition14() {
        assertQuery("SELECT * FROM Developer WHERE name>='Mozart' AND depId=3 AND city>='Vienna'")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition15() {
        assertQuery("SELECT * FROM Developer WHERE age=33 AND city='Vienna'")
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testComplexIndexCondition16() {
        assertQuery("SELECT * FROM Developer WHERE age=33 AND (city='Vienna' AND depId=3)")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testEmptyResult() {
        assertQuery("SELECT * FROM Developer WHERE age=33 AND city='Leipzig'")
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .check();
    }

    /** */
    @Test
    public void testOrCondition1() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' OR age=55")
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .check();
    }

    /** */
    @Test
    @Ignore("https://issues.apache.org/jira/browse/IGNITE-13710")
    public void testOrCondition2() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND (depId=1 OR depId=3)")
            .matches(containsUnion(true))
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    @Ignore("https://issues.apache.org/jira/browse/IGNITE-13710")
    public void testOrCondition3() {
        assertQuery("SELECT * FROM Developer WHERE name='Mozart' AND (age > 22 AND (depId=1 OR depId=3))")
            .matches(containsUnion(true))
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .check();
    }

    /** */
    @Test
    public void testOrCondition4() {
        assertQuery("SELECT * FROM Developer WHERE depId=1 OR (name='Mozart' AND depId=3)")
            .matches(containsUnion(true))
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_DEPID_CITY_IDX))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .check();
    }

    /** */
    @Test
    public void testOrCondition5() {
        assertQuery("SELECT * FROM Developer WHERE depId=1 OR name='Mozart'")
            .matches(containsUnion(true))
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .check();
    }

    // ===== various complex conditions =====

    /** */
    @Ignore("TODO")
    @Test
    public void testOrderByKey() {
        assertQuery("SELECT id, name, depId, age FROM Developer ORDER BY _key")
            .matches(containsTableScan("PUBLIC", "DEVELOPER"))
            .matches(not(containsSubPlan("IgniteSort")))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .ordered()
            .check();
    }

    /** */
    @Test
    public void testOrderByKeyAlias() {
        assertQuery("SELECT * FROM Developer WHERE id<=4 ORDER BY id nulls first")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER"))
            .matches(not(containsSubPlan("IgniteSort")))
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .ordered()
            .check();
    }

    /** */
    @Test
    public void testOrderByDepId() {
        assertQuery("SELECT * FROM Developer ORDER BY depId nulls first")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
            .matches(not(containsSubPlan("IgniteSort")))
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)

            .returns(12, "Einaudi", 11, "", -1)
            .returns(13, "Glass", 12, "", -1)
            .returns(14, "Rihter", 13, "", -1)
            .returns(15, "Marradi", 14, "", -1)
            .returns(16, "Zimmer", 15, "", -1)
            .returns(17, "Hasaishi", 16, "", -1)
            .returns(18, "Arnalds", 17, "", -1)
            .returns(19, "Yiruma", 18, "", -1)
            .returns(20, "O'Halloran", 19, "", -1)
            .returns(21, "Cacciapaglia", 20, "", -1)
            .returns(22, "Prokofiev", 21, "", -1)
            .returns(23, "Musorgskii", 22, "", -1)

            .ordered()
            .check();
    }

    /** */
    @Test
    public void testOrderByNameCityAsc() {
        assertQuery("SELECT * FROM Developer ORDER BY name, city")
            .matches(containsAnyScan("PUBLIC", "DEVELOPER"))
            .matches(containsAnyScan("PUBLIC", "DEVELOPER"))
            .matches(containsSubPlan("IgniteSort"))
            .returns(18, "Arnalds", 17, "", -1)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(21, "Cacciapaglia", 20, "", -1)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(12, "Einaudi", 11, "", -1)
            .returns(13, "Glass", 12, "", -1)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .returns(17, "Hasaishi", 16, "", -1)
            .returns(15, "Marradi", 14, "", -1)
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(23, "Musorgskii", 22, "", -1)
            .returns(20, "O'Halloran", 19, "", -1)
            .returns(22, "Prokofiev", 21, "", -1)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(14, "Rihter", 13, "", -1)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(19, "Yiruma", 18, "", -1)
            .returns(16, "Zimmer", 15, "", -1)
            .ordered()
            .check();
    }

    /** */
    @Test
    public void testOrderByNameCityDesc() {
        assertQuery("SELECT * FROM Developer ORDER BY name DESC, city DESC")
            .matches(containsIndexScan("PUBLIC", "DEVELOPER", NAME_CITY_IDX))
            .matches(not(containsSubPlan("IgniteSort")))
            .returns(16, "Zimmer", 15, "", -1)
            .returns(19, "Yiruma", 18, "", -1)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(14, "Rihter", 13, "", -1)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(22, "Prokofiev", 21, "", -1)
            .returns(20, "O'Halloran", 19, "", -1)
            .returns(23, "Musorgskii", 22, "", -1)
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(15, "Marradi", 14, "", -1)
            .returns(17, "Hasaishi", 16, "", -1)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .returns(13, "Glass", 12, "", -1)
            .returns(12, "Einaudi", 11, "", -1)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(21, "Cacciapaglia", 20, "", -1)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(18, "Arnalds", 17, "", -1)
            .ordered()
            .check();
    }

    /** */
    @Test
    public void testOrderByNoIndexedColumn() {
        assertQuery("SELECT * FROM Developer ORDER BY age DESC, ID")
            .matches(containsAnyProject("PUBLIC", "DEVELOPER"))
            .matches(containsSubPlan("IgniteSort"))
            .returns(8, "Stravinsky", 7, "Spt", 89)
            .returns(7, "Verdy", 6, "Rankola", 88)
            .returns(5, "Vagner", 4, "Leipzig", 70)
            .returns(9, "Rahmaninov", 8, "Starorussky ud", 70)
            .returns(4, "Strauss", 2, "Munich", 66)
            .returns(3, "Bach", 1, "Leipzig", 55)
            .returns(6, "Chaikovsky", 5, "Votkinsk", 53)
            .returns(11, "Glinka", 10, "Smolenskaya gb", 53)
            .returns(2, "Beethoven", 2, "Vienna", 44)
            .returns(1, "Mozart", 3, "Vienna", 33)
            .returns(10, "Shubert", 9, "Vienna", 31)
            .returns(12, "Einaudi", 11, "", -1)
            .returns(13, "Glass", 12, "", -1)
            .returns(14, "Rihter", 13, "", -1)
            .returns(15, "Marradi", 14, "", -1)
            .returns(16, "Zimmer", 15, "", -1)
            .returns(17, "Hasaishi", 16, "", -1)
            .returns(18, "Arnalds", 17, "", -1)
            .returns(19, "Yiruma", 18, "", -1)
            .returns(20, "O'Halloran", 19, "", -1)
            .returns(21, "Cacciapaglia", 20, "", -1)
            .returns(22, "Prokofiev", 21, "", -1)
            .returns(23, "Musorgskii", 22, "", -1)
            .ordered()
            .check();
    }

    /**
     * Test verifies that ranges would be serialized and desirialized without any errors.
     */
    @Test
    public void testSelectWithRanges() {
        String sql = "select depId from Developer " +
            "where depId in (1,2,3,5,6,7,9,10,13,14,15,18,19,20,21,22,23,24,25,26,27,28,30,31,32,33) " +
            "   or depId between 7 and 8 order by depId limit 5";

        assertQuery(sql)
            .returns(1)
            .returns(2)
            .returns(2)
            .returns(3)
            .returns(5)
            .check();
    }

    /**
     * A test to verify that the planner is able to optimize such a query in
     * a reasonable amount of time.
     *
     * A "reasonable" here is the time less than test's timeout. Despite
     * timeout is too big, it's less than INF though.
     */
    @Test
    public void testToPlanQueryWithAllOperator() {
        assertQuery("SELECT name FROM Developer WHERE age > ALL ( SELECT 88 )")
            .returns("Stravinsky")
            .check();
    }

    /** */
    private static class Developer {
        /** */
        String name;

        /** */
        int depId;

        /** */
        String city;

        /** */
        int age;

        /** */
        public Developer(String name, int depId, String city, int age) {
            this.name = name;
            this.depId = depId;
            this.city = city;
            this.age = age;
        }

        /** {@inheritDoc} */
        @Override public String toString() {
            return "Project{" +
                "name='" + name + '\'' +
                ", ver=" + depId +
                '}';
        }
    }

    /** */
    private static class Birthday {
        /** */
        String name;

        /** */
        Date birthday;

        /** */
        public Birthday(String name, Date birthday) {
            this.name = name;
            this.birthday = birthday;
        }
    }

    /** */
    public static class Key {
        /** */
        @QuerySqlField
        public int id;

        /** */
        @QuerySqlField
        public int id2;

        /** */
        public Key(int id, int id2) {
            this.id = id;
            this.id2 = id2;
        }
    }
}