ignite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From voze...@apache.org
Subject [44/50] [abbrv] incubator-ignite git commit: ignite-437: test for bigQuery sql
Date Thu, 09 Apr 2015 08:59:17 GMT
ignite-437: test for bigQuery sql


Project: http://git-wip-us.apache.org/repos/asf/incubator-ignite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-ignite/commit/fe743909
Tree: http://git-wip-us.apache.org/repos/asf/incubator-ignite/tree/fe743909
Diff: http://git-wip-us.apache.org/repos/asf/incubator-ignite/diff/fe743909

Branch: refs/heads/ignite-sprint-4
Commit: fe743909cf483d00ab5189049523ed3353bf5a3d
Parents: 2f458d2
Author: Artem Shutak <ashutak@gridgain.com>
Authored: Wed Apr 8 19:55:29 2015 +0300
Committer: Artem Shutak <ashutak@gridgain.com>
Committed: Wed Apr 8 19:55:29 2015 +0300

----------------------------------------------------------------------
 .../h2/sql/AbstractH2CompareQueryTest.java      | 341 ++++++++
 .../query/h2/sql/BaseH2CompareQueryTest.java    | 823 +++++++++++++++++
 .../query/h2/sql/H2CompareBigQueryTest.java     | 707 +++++++++++++++
 .../query/h2/sql/IgniteVsH2QueryTest.java       | 875 -------------------
 .../IgniteCacheQuerySelfTestSuite.java          |   5 +-
 .../processors/query/h2/sql/bigQuery.sql        |  55 ++
 6 files changed, 1929 insertions(+), 877 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fe743909/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/AbstractH2CompareQueryTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/AbstractH2CompareQueryTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/AbstractH2CompareQueryTest.java
new file mode 100644
index 0000000..c74493f
--- /dev/null
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/AbstractH2CompareQueryTest.java
@@ -0,0 +1,341 @@
+/*
+ * 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.h2.sql;
+
+import org.apache.ignite.*;
+import org.apache.ignite.cache.*;
+import org.apache.ignite.cache.query.*;
+import org.apache.ignite.configuration.*;
+import org.apache.ignite.internal.util.typedef.internal.*;
+import org.apache.ignite.marshaller.optimized.*;
+import org.apache.ignite.spi.discovery.tcp.*;
+import org.apache.ignite.spi.discovery.tcp.ipfinder.*;
+import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.*;
+import org.apache.ignite.testframework.junits.common.*;
+import org.jetbrains.annotations.*;
+
+import java.sql.*;
+import java.util.*;
+
+/**
+ * Abstract test framework to compare query results from h2 database instance and mixed ignite caches (replicated and partitioned)
+ * which have the same data models and data content. 
+ */
+public abstract class AbstractH2CompareQueryTest extends GridCommonAbstractTest {
+    /** */
+    private static final TcpDiscoveryIpFinder IP_FINDER = new TcpDiscoveryVmIpFinder(true);
+
+    /** Partitioned cache. */
+    protected static IgniteCache pCache;
+
+    /** Replicated cache. */
+    protected static IgniteCache rCache;
+    
+    /** H2 db connection. */
+    protected static Connection conn;
+
+    /** {@inheritDoc} */
+    @SuppressWarnings("unchecked")
+    @Override protected IgniteConfiguration getConfiguration(String gridName) throws Exception {
+        IgniteConfiguration c = super.getConfiguration(gridName);
+
+        TcpDiscoverySpi disco = new TcpDiscoverySpi();
+
+        disco.setIpFinder(IP_FINDER);
+
+        c.setDiscoverySpi(disco);
+
+        c.setMarshaller(new OptimizedMarshaller(true));
+
+        c.setCacheConfiguration(createCache("part", CacheMode.PARTITIONED),
+            createCache("repl", CacheMode.REPLICATED)
+        );
+
+        return c;
+    }
+
+    /**
+     * Creates new cache configuration.
+     *
+     * @param name Cache name.
+     * @param mode Cache mode.
+     * @return Cache configuration.
+     */
+    private CacheConfiguration createCache(String name, CacheMode mode) {
+        CacheConfiguration<?,?> cc = defaultCacheConfiguration();
+
+        cc.setName(name);
+        cc.setCacheMode(mode);
+        cc.setWriteSynchronizationMode(CacheWriteSynchronizationMode.FULL_SYNC);
+        cc.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
+
+        setIndexedTypes(cc, mode);
+
+        return cc;
+    }
+
+    /**
+     * @param cc Cache configuration.
+     * @param mode Cache Mode.
+     */
+    protected abstract void setIndexedTypes(CacheConfiguration<?, ?> cc, CacheMode mode) ;
+
+    /** {@inheritDoc} */
+    @Override protected void beforeTestsStarted() throws Exception {
+        super.beforeTestsStarted();
+
+        Ignite ignite = startGrids(4);
+
+        pCache = ignite.cache("part");
+        
+        rCache = ignite.cache("repl");
+
+        awaitPartitionMapExchange();
+        
+        conn = openH2Connection(false);
+
+        initializeH2Schema();
+
+        initCacheAndDbData();
+
+        checkAllDataEquals();
+    }
+    
+    /** {@inheritDoc} */
+    @Override protected void afterTestsStopped() throws Exception {
+        super.afterTestsStopped();
+        
+        conn.close();
+        
+        stopAllGrids();
+    }
+
+    /**
+     * Populate cache and h2 database with test data.
+     */
+    protected abstract void initCacheAndDbData() throws SQLException;
+
+    /**
+     * @throws Exception If failed.
+     */
+    protected abstract void checkAllDataEquals() throws Exception;
+
+    /**
+     * Initialize h2 database schema.
+     *
+     * @throws SQLException If exception.
+     */
+    protected abstract void initializeH2Schema() throws SQLException;
+
+    /**
+     * Gets connection from a pool.
+     *
+     * @param autocommit {@code true} If connection should use autocommit mode.
+     * @return Pooled connection.
+     * @throws SQLException In case of error.
+     */
+    private Connection openH2Connection(boolean autocommit) throws SQLException {
+        System.setProperty("h2.serializeJavaObject", "false");
+        
+        String dbName = "test";
+        
+        Connection conn = DriverManager.getConnection("jdbc:h2:mem:" + dbName + ";DB_CLOSE_DELAY=-1");
+
+        conn.setAutoCommit(autocommit);
+
+        return conn;
+    }
+
+    /**
+     * Execute given sql query on h2 database and on partitioned ignite cache and compare results.
+     *
+     * @param sql SQL query.
+     * @param args SQL arguments.
+     * then results will compare as ordered queries.
+     * @return Result set after SQL query execution. 
+     * @throws SQLException If exception.
+     */
+    protected final List<List<?>> compareQueryRes0(String sql, @Nullable Object... args) throws SQLException {
+        return compareQueryRes0(pCache, sql, args, Ordering.RANDOM);
+    }
+
+    /**
+     * Execute given sql query on h2 database and on ignite cache and compare results. 
+     * Expected that results are not ordered.
+     *
+     * @param cache Ignite cache.
+     * @param sql SQL query.
+     * @param args SQL arguments.
+     * then results will compare as ordered queries.
+     * @return Result set after SQL query execution. 
+     * @throws SQLException If exception.
+     */
+    protected final List<List<?>> compareQueryRes0(IgniteCache cache, String sql, @Nullable Object... args) throws SQLException {
+        return compareQueryRes0(cache, sql, args, Ordering.RANDOM);
+    }
+
+    /**
+     * Execute given sql query on h2 database and on partitioned ignite cache and compare results.
+     * Expected that results are ordered.
+     *
+     * @param sql SQL query.
+     * @param args SQL arguments.
+     * then results will compare as ordered queries.
+     * @return Result set after SQL query execution.
+     * @throws SQLException If exception.
+     */
+    protected final List<List<?>> compareOrderedQueryRes0(String sql, @Nullable Object... args) throws SQLException {
+        return compareQueryRes0(pCache, sql, args, Ordering.ORDERED);
+    }
+
+    /**
+     * Execute given sql query on h2 database and on ignite cache and compare results.
+     *
+     * @param cache Ignite cache.
+     * @param sql SQL query.
+     * @param args SQL arguments.
+     * @param ordering Expected ordering of SQL results. If {@link Ordering#ORDERED}
+     * then results will compare as ordered queries.
+     * @return Result set after SQL query execution.
+     * @throws SQLException If exception.
+     */    
+    @SuppressWarnings("unchecked")
+    protected final List<List<?>> compareQueryRes0(IgniteCache cache, String sql, @Nullable Object[] args, Ordering ordering) throws SQLException {
+        if (args == null)
+            args = new Object[] {null};
+        
+        info("Sql query:\n" + sql + "\nargs=" + Arrays.toString(args));
+
+        List<List<?>> h2Res = executeH2Query(sql, args);
+
+        List<List<?>> cacheRes = cache.query(new SqlFieldsQuery(sql).setArgs(args)).getAll();
+
+        assertRsEquals(h2Res, cacheRes, ordering);
+        
+        return h2Res;
+    }
+
+    /**
+     * Execute SQL query on h2 database.
+     *
+     * @param sql SQL query.
+     * @param args SQL arguments.
+     * @return Result of SQL query on h2 database.
+     * @throws SQLException If exception.
+     */
+    private List<List<?>> executeH2Query(String sql, Object[] args) throws SQLException {
+        List<List<?>> res = new ArrayList<>();
+        ResultSet rs = null;
+
+        try(PreparedStatement st = conn.prepareStatement(sql)) {
+            for (int idx = 0; idx < args.length; idx++)
+                st.setObject(idx + 1, args[idx]);
+
+            rs = st.executeQuery();
+
+            int colCnt = rs.getMetaData().getColumnCount();
+
+            while (rs.next()) {
+                List<Object> row = new ArrayList<>(colCnt);
+                
+                for (int i = 1; i <= colCnt; i++)
+                    row.add(rs.getObject(i));
+                
+                res.add(row);
+            }
+        }
+        finally {
+            U.closeQuiet(rs);
+        }
+
+        return res;
+    }
+
+    /**
+     * Assert equals of result sets according to expected ordering.
+     *
+     * @param rs1 Expected result set.
+     * @param rs2 Actual result set.
+     * @param ordering Expected ordering of SQL results. If {@link Ordering#ORDERED}
+     * then results will compare as ordered queries.
+     */
+    private void assertRsEquals(List<List<?>> rs1, List<List<?>> rs2, Ordering ordering) {
+        assertEquals("Rows count has to be equal.", rs1.size(), rs2.size());
+        
+        switch (ordering){
+            case ORDERED:
+                for (int rowNum = 0; rowNum < rs1.size(); rowNum++) {
+                    List<?> row1 = rs1.get(rowNum);
+                    List<?> row2 = rs2.get(rowNum);
+
+                    assertEquals("Columns count have to be equal.", row1.size(), row2.size());
+
+                    for (int colNum = 0; colNum < row1.size(); colNum++)
+                        assertEquals("Row=" + rowNum + ", column=" + colNum, row1.get(colNum), row2.get(colNum));
+                }
+
+                break;
+            case RANDOM:
+                Map<List<?>, Integer> rowsWithCnt1 = extractUniqueRowsWithCounts(rs1);
+                Map<List<?>, Integer> rowsWithCnt2 = extractUniqueRowsWithCounts(rs2);
+                
+                assertEquals("Unique rows count has to be equal.", rowsWithCnt1.size(), rowsWithCnt2.size());
+
+                for (Map.Entry<List<?>, Integer> entry1 : rowsWithCnt1.entrySet()) {
+                    List<?> row = entry1.getKey();
+                    Integer cnt1 = entry1.getValue();
+
+                    Integer cnt2 = rowsWithCnt2.get(row);
+
+                    assertEquals("Row has different occurance number.\nRow=" + row, cnt1, cnt2);
+                }
+                
+                break;
+            default: 
+                throw new IllegalStateException();
+        }
+    }
+
+    /**
+     * @param rs Result set.
+     * @return Map of unique rows at the result set to number of occuriances at the result set.
+     */
+    private Map<List<?>, Integer> extractUniqueRowsWithCounts(Iterable<List<?>> rs) {
+        Map<List<?>, Integer> res = new HashMap<>();
+
+        for (List<?> row : rs) {
+            Integer cnt = res.get(row);
+            
+            if (cnt == null)
+                cnt = 0;
+            
+            res.put(row, cnt + 1);
+        }
+        return res;
+    }
+    
+    /**
+     * Ordering type.
+     */
+    protected enum Ordering {
+        /** Random. */
+        RANDOM, 
+        /** Ordered. */
+        ORDERED
+    }
+}

http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fe743909/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/BaseH2CompareQueryTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/BaseH2CompareQueryTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/BaseH2CompareQueryTest.java
new file mode 100644
index 0000000..eea834d
--- /dev/null
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/BaseH2CompareQueryTest.java
@@ -0,0 +1,823 @@
+/*
+ * 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.h2.sql;
+
+import org.apache.ignite.cache.*;
+import org.apache.ignite.cache.affinity.*;
+import org.apache.ignite.cache.query.annotations.*;
+import org.apache.ignite.configuration.*;
+
+import java.io.*;
+import java.sql.*;
+import java.sql.Date;
+import java.util.*;
+
+/**
+ * Base set of queries to compare query results from h2 database instance and mixed ignite caches (replicated and partitioned)
+ * which have the same data models and data content.
+ */
+public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
+    /** Org count. */
+    public static final int ORG_CNT = 30;
+    
+    /** Address count. */
+    public static final int ADDR_CNT = 10;
+    
+    /** Person count. */
+    public static final int PERS_CNT = 50;
+    
+    /** Product count. */
+    public static final int PROD_CNT = 100;
+
+    /** Purchase count. */
+    public static final int PURCH_CNT = PROD_CNT * 5;
+
+    /** {@inheritDoc} */
+    @Override protected void setIndexedTypes(CacheConfiguration<?, ?> cc, CacheMode mode) {
+        if (mode == CacheMode.PARTITIONED)
+            cc.setIndexedTypes(
+                Integer.class, Organization.class,
+                AffinityKey.class, Person.class,
+                AffinityKey.class, Purchase.class
+            );
+        else if (mode == CacheMode.REPLICATED)
+            cc.setIndexedTypes(
+                Integer.class, Product.class,
+                Integer.class, Address.class
+            );
+        else
+            throw new IllegalStateException("mode: " + mode);
+    }
+
+    /** {@inheritDoc} */
+    @SuppressWarnings("unchecked")
+    @Override protected void initCacheAndDbData() throws SQLException {
+        int idGen = 0;
+
+        // Organizations.
+        List<Organization> organizations = new ArrayList<>();
+
+        for (int i = 0; i < ORG_CNT; i++) {
+            int id = idGen++;
+
+            Organization org = new Organization(id, "Org" + id);
+
+            organizations.add(org);
+
+            pCache.put(org.id, org);
+
+            insertInDb(org);
+        }
+        
+       // Adresses.
+        List<Address> addreses = new ArrayList<>();
+
+        for (int i = 0; i < ADDR_CNT; i++) {
+            int id = idGen++;
+
+            Address addr = new Address(id, "Addr" + id);
+
+            addreses.add(addr);
+
+            rCache.put(addr.id, addr);
+
+            insertInDb(addr);
+        }
+
+        // Persons.
+        List<Person> persons = new ArrayList<>();
+
+        for (int i = 0; i < PERS_CNT; i++) {
+            int id = idGen++;
+
+            Person person = new Person(id, organizations.get(i % organizations.size()),
+                "name" + id, "lastName" + id, id * 100.0, addreses.get(i % addreses.size()));
+
+            // Add a Person without lastname.
+            if (id == organizations.size() + 1)
+                person.lastName = null;
+
+            persons.add(person);
+
+            pCache.put(person.key(), person);
+
+            insertInDb(person);
+        }
+
+        // Products.
+        List<Product> products = new ArrayList<>();
+
+        for (int i = 0; i < PROD_CNT; i++) {
+            int id = idGen++;
+
+            Product product = new Product(id, "Product" + id, id*1000);
+
+            products.add(product);
+
+            rCache.put(product.id, product);
+
+            insertInDb(product);
+        }
+
+        // Purchases.
+        for (int i = 0; i < PURCH_CNT; i++) {
+            int id = idGen++;
+
+            Person person = persons.get(i % persons.size());
+            
+            Purchase purchase = new Purchase(id, products.get(i % products.size()), person.orgId, person);
+
+            pCache.put(purchase.key(), purchase);
+
+            insertInDb(purchase);
+        }
+    }
+
+    /** {@inheritDoc} */
+    @Override protected void checkAllDataEquals() throws Exception {
+        compareQueryRes0("select _key, _val, id, name from \"part\".Organization");
+
+        compareQueryRes0("select _key, _val, id, firstName, lastName, orgId, salary from \"part\".Person");
+
+        compareQueryRes0("select _key, _val, id, personId, productId, organizationId from \"part\".Purchase");
+
+        compareQueryRes0(rCache, "select _key, _val, id, name, price from \"repl\".Product");
+    }
+
+    /**
+     * *
+     * @throws Exception
+     */
+    public void testAllExamples() throws Exception {
+//        compareQueryRes0("select ? limit ? offset ?");
+
+//        compareQueryRes0("select cool1()");
+//        compareQueryRes0("select cool1() z");
+//
+//        compareQueryRes0("select b,a from table0('aaa', 100)");
+//        compareQueryRes0("select * from table0('aaa', 100)");
+//        compareQueryRes0("select * from table0('aaa', 100) t0");
+//        compareQueryRes0("select x.a, y.b from table0('aaa', 100) x natural join table0('bbb', 100) y");
+//        compareQueryRes0("select * from table0('aaa', 100) x join table0('bbb', 100) y on x.a=y.a and x.b = 'bbb'");
+//        compareQueryRes0("select * from table0('aaa', 100) x left join table0('bbb', 100) y on x.a=y.a and x.b = 'bbb'");
+//        compareQueryRes0("select * from table0('aaa', 100) x left join table0('bbb', 100) y on x.a=y.a where x.b = 'bbb'");
+//        compareQueryRes0("select * from table0('aaa', 100) x left join table0('bbb', 100) y where x.b = 'bbb'");
+
+        final String addStreet = "Addr" + ORG_CNT + 1;
+        
+        List<List<?>> res = compareQueryRes0("select avg(old) from \"part\".Person left join \"repl\".Address " +
+            " on Person.addrId = Address.id where lower(Address.street) = lower(?)", addStreet);
+        
+        assertNotSame(0, res);
+
+        compareQueryRes0("select avg(old) from \"part\".Person join \"repl\".Address on Person.addrId = Address.id " +
+            "where lower(Address.street) = lower(?)", addStreet);
+
+        compareQueryRes0("select avg(old) from \"part\".Person left join \"repl\".Address where Person.addrId = Address.id " +
+            "and lower(Address.street) = lower(?)", addStreet);
+
+        compareQueryRes0("select avg(old) from \"part\".Person, \"repl\".Address where Person.addrId = Address.id " +
+            "and lower(Address.street) = lower(?)", addStreet);
+
+        compareQueryRes0("select firstName, date from \"part\".Person");
+        compareQueryRes0("select distinct firstName, date from \"part\".Person");
+        
+        final String star = " _key, _val, id, firstName, lastName, orgId, salary, addrId, old, date ";
+        
+        compareQueryRes0("select " + star + " from \"part\".Person p");
+        compareQueryRes0("select " + star + " from \"part\".Person");
+        compareQueryRes0("select distinct " + star + " from \"part\".Person");
+        compareQueryRes0("select p.firstName, date from \"part\".Person p");
+        
+        compareQueryRes0("select p._key, p._val, p.id, p.firstName, p.lastName, p.orgId, p.salary, p.addrId, p.old, " +
+            " p.date, a._key, a._val, a.id, a.street" +
+            " from \"part\".Person p, \"repl\".Address a");
+//        compareQueryRes0("select p.* from \"part\".Person p, \"repl\".Address a");
+//        compareQueryRes0("select person.* from \"part\".Person, \"repl\".Address a");
+//        compareQueryRes0("select p.*, street from \"part\".Person p, \"repl\".Address a");
+        compareQueryRes0("select p.firstName, a.street from \"part\".Person p, \"repl\".Address a");
+        compareQueryRes0("select distinct p.firstName, a.street from \"part\".Person p, \"repl\".Address a");
+        compareQueryRes0("select distinct firstName, street from \"part\".Person, \"repl\".Address group by firstName, street ");
+        compareQueryRes0("select distinct firstName, street from \"part\".Person, \"repl\".Address");
+        // TODO uncomment and investigate (Rows count has to be equal.: Expected :2500, Actual :900)
+//        compareQueryRes0("select p1.firstName, a2.street from \"part\".Person p1, \"repl\".Address a1, \"part\".Person p2, \"repl\".Address a2");
+
+        //TODO look at it (org.h2.jdbc.JdbcSQLException: Feature not supported: "VARCHAR +" // at H2)
+//        compareQueryRes0("select p.firstName n, a.street s from \"part\".Person p, \"repl\".Address a");
+        compareQueryRes0("select p.firstName, 1 as i, 'aaa' s from \"part\".Person p");
+
+//        compareQueryRes0("select p.firstName + 'a', 1 * 3 as i, 'aaa' s, -p.old, -p.old as old from \"part\".Person p");
+//        compareQueryRes0("select p.firstName || 'a' + p.firstName, (p.old * 3) % p.old - p.old / p.old, p.firstName = 'aaa', " +
+//            " p.firstName is p.firstName, p.old > 0, p.old >= 0, p.old < 0, p.old <= 0, p.old <> 0, p.old is not p.old, " +
+//            " p.old is null, p.old is not null " +
+//            " from \"part\".Person p");
+
+        compareQueryRes0("select p.firstName from \"part\".Person p where firstName <> 'ivan'");
+        compareQueryRes0("select p.firstName from \"part\".Person p where firstName like 'i%'");
+        compareQueryRes0("select p.firstName from \"part\".Person p where firstName regexp 'i%'");
+        compareQueryRes0("select p.firstName from \"part\".Person p, \"repl\".Address a where p.firstName <> 'ivan' and a.id > 10 or not (a.id = 100)");
+
+        compareQueryRes0("select case p.firstName when 'a' then 1 when 'a' then 2 end as a from \"part\".Person p");
+        compareQueryRes0("select case p.firstName when 'a' then 1 when 'a' then 2 else -1 end as a from \"part\".Person p");
+
+        compareQueryRes0("select abs(p.old)  from \"part\".Person p");
+        compareQueryRes0("select cast(p.old as numeric(10, 2)) from \"part\".Person p");
+        compareQueryRes0("select cast(p.old as numeric(10, 2)) z from \"part\".Person p");
+        compareQueryRes0("select cast(p.old as numeric(10, 2)) as z from \"part\".Person p");
+
+        // TODO alanyse
+//        compareQueryRes0("select " + star + " from \"part\".Person p where p.firstName in ('a', 'b', '_' + RAND())"); // test ConditionIn
+        compareQueryRes0("select " + star + " from \"part\".Person p where p.firstName in ('a', 'b', 'c')"); // test ConditionInConstantSet
+        compareQueryRes0("select " + star + " from \"part\".Person p where p.firstName in (select a.street from \"repl\".Address a)"); // test ConditionInConstantSet
+
+        compareQueryRes0("select (select a.street from \"repl\".Address a where a.id = p.addrId) from \"part\".Person p"); // test ConditionInConstantSet
+
+        compareQueryRes0("select p.firstName, ? from \"part\".Person p where firstName regexp ? and p.old < ?", 10, "Iv*n", 40);
+
+        compareQueryRes0("select count(*) as a from \"part\".Person");
+        compareQueryRes0("select count(*) as a, count(p.*), count(p.firstName) from \"part\".Person p");
+        compareQueryRes0("select count(distinct p.firstName) from \"part\".Person p");
+
+        compareQueryRes0("select p.firstName, avg(p.old), max(p.old) from \"part\".Person p group by p.firstName");
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName");
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by n");
+
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.addrId, p.firstName");
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName, p.addrId");
+        compareQueryRes0("select p.firstName n, max(p.old) + min(p.old) / count(distinct p.old) from \"part\".Person p group by p.firstName");
+        compareQueryRes0("select p.firstName n, max(p.old) maxOld, min(p.old) minOld from \"part\".Person p group by p.firstName having maxOld > 10 and min(p.old) < 1");
+
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by n");
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName");
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName, m");
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName, max(p.old) desc");
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName nulls first");
+        compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName nulls last");
+        compareQueryRes0("select p.firstName n from \"part\".Person p order by p.old + 10");
+        compareQueryRes0("select p.firstName n from \"part\".Person p order by p.old + 10, p.firstName");
+        compareQueryRes0("select p.firstName n from \"part\".Person p order by p.old + 10, p.firstName desc");
+
+        compareQueryRes0("select p.firstName n from \"part\".Person p, (select a.street from \"repl\".Address a where a.street is not null) ");
+        compareQueryRes0("select street from \"part\".Person p, (select a.street from \"repl\".Address a where a.street is not null) ");
+        compareQueryRes0("select addr.street from \"part\".Person p, (select a.street from \"repl\".Address a where a.street is not null) addr");
+
+        compareQueryRes0("select p.firstName n from \"part\".Person p order by p.old + 10");
+
+        compareQueryRes0("select 'foo' as bar union select 'foo' as bar");
+        compareQueryRes0("select 'foo' as bar union all select 'foo' as bar");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testParamSubstitution() throws Exception {
+        compareQueryRes0("select ? from \"part\".Person", "Some arg");
+    }
+
+    /**
+     * @throws SQLException If failed.
+     */
+    public void testAggregateOrderBy() throws SQLException {
+        compareOrderedQueryRes0("select firstName name, count(*) cnt from \"part\".Person group by name order by cnt desc");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testNullParamSubstitution() throws Exception {
+        List<List<?>> rs1 = compareQueryRes0("select id from \"part\".Person where lastname is ?", null);
+
+        // Ensure we find something.
+        assertNotSame(0, rs1.size());
+    }
+
+    /**
+     *
+     */
+    public void testUnion() throws SQLException {
+        String base = "select _val v from \"part\".Person";
+
+        compareQueryRes0(base + " union all " + base);
+        compareQueryRes0(base + " union " + base);
+
+        base = "select firstName||lastName name, salary from \"part\".Person";
+
+        assertEquals(10, compareOrderedQueryRes0(base + " union all " + base + " order by salary desc").size());
+        assertEquals(5, compareOrderedQueryRes0(base + " union " + base + " order by salary desc").size());
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testEmptyResult() throws Exception {
+        compareQueryRes0("select id from \"part\".Person where 0 = 1");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testSqlQueryWithAggregation() throws Exception {
+        compareQueryRes0("select avg(salary) from \"part\".Person, \"part\".Organization where Person.orgId = Organization.id and "
+            + "lower(Organization.name) = lower(?)", "Org1");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testSqlFieldsQuery() throws Exception {
+        compareQueryRes0("select concat(firstName, ' ', lastName) from \"part\".Person");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testSqlFieldsQueryWithJoin() throws Exception {
+        compareQueryRes0("select concat(firstName, ' ', lastName), "
+            + "Organization.name from \"part\".Person, \"part\".Organization where "
+            + "Person.orgId = Organization.id");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testOrdered() throws Exception {
+        compareOrderedQueryRes0("select firstName, lastName" +
+                " from \"part\".Person" +
+                " order by lastName, firstName"
+        );
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testSimpleJoin() throws Exception {
+        // Have expected results.
+        compareQueryRes0("select id, firstName, lastName" +
+            "  from \"part\".Person" +
+            "  where Person.id = ?", 3);
+
+        // Ignite cache return 0 results...
+        compareQueryRes0("select pe.firstName" +
+            "  from \"part\".Person pe join \"part\".Purchase pu on pe.id = pu.personId " +
+            "  where pe.id = ?", 3);
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testSimpleReplicatedSelect() throws Exception {
+        compareQueryRes0(rCache, "select id, name from \"repl\".Product");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void _testCrossCache() throws Exception {
+        //TODO Investigate (should be 20 results instead of 0).
+        compareQueryRes0("select firstName, lastName" +
+            "  from \"part\".Person, \"part\".Purchase" +
+            "  where Person.id = Purchase.personId");
+
+        //TODO Investigate.
+        compareQueryRes0("select concat(firstName, ' ', lastName), Product.name " +
+            "  from \"part\".Person, \"part\".Purchase, \"repl\".Product " +
+            "  where Person.id = Purchase.personId and Purchase.productId = Product.id" +
+            "  group by Product.id");
+
+        //TODO Investigate.
+        compareQueryRes0("select concat(firstName, ' ', lastName), count (Product.id) " +
+            "  from \"part\".Person, \"part\".Purchase, \"repl\".Product " +
+            "  where Person.id = Purchase.personId and Purchase.productId = Product.id" +
+            "  group by Product.id");
+    }
+
+    /** {@inheritDoc} */
+    @Override protected void initializeH2Schema() throws SQLException {
+        Statement st = conn.createStatement();
+
+        st.execute("CREATE SCHEMA \"part\"");
+        st.execute("CREATE SCHEMA \"repl\"");
+
+        st.execute("create table \"part\".ORGANIZATION" +
+            "  (_key int not null," +
+            "  _val other not null," +
+            "  id int unique," +
+            "  name varchar(255))");
+
+        st.execute("create table \"part\".PERSON" +
+            "  (_key other not null ," +
+            "   _val other not null ," +
+            "  id int unique, " +
+            "  firstName varchar(255), " +
+            "  lastName varchar(255)," +
+            "  orgId int not null," +
+            "  salary double," +
+            "  addrId int," +
+            "  old int," +
+            "  date Date )");
+
+        st.execute("create table \"repl\".PRODUCT" +
+            "  (_key int not null ," +
+            "   _val other not null ," +
+            "  id int unique, " +
+            "  name varchar(255), " +
+            "  price int)");
+
+        st.execute("create table \"part\".PURCHASE" +
+            "  (_key other not null ," +
+            "   _val other not null ," +
+            "  id int unique, " +
+            "  personId int, " +
+            "  organizationId int, " +
+            "  productId int)");
+
+        st.execute("create table \"repl\".ADDRESS" +
+            "  (_key int not null ," +
+            "   _val other not null ," +
+            "  id int unique, " +
+            "  street varchar(255))");
+
+        conn.commit();
+    }
+    
+    /**
+     * Insert {@link Organization} at h2 database.
+     *
+     * @param org Organization.
+     * @throws SQLException If exception.
+     */
+    private void insertInDb(Organization org) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"part\".ORGANIZATION (_key, _val, id, name) values(?, ?, ?, ?)")) {
+            st.setObject(1, org.id);
+            st.setObject(2, org);
+            st.setObject(3, org.id);
+            st.setObject(4, org.name);
+
+            st.executeUpdate();
+        }
+    }
+
+    /**
+     * Insert {@link Person} at h2 database.
+     *
+     * @param p Person.
+     * @throws SQLException If exception.
+     */
+    private void insertInDb(Person p) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement("insert into \"part\".PERSON " +
+            "(_key, _val, id, firstName, lastName, orgId, salary, addrId, old, date) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
+            st.setObject(1, p.key());
+            st.setObject(2, p);
+            st.setObject(3, p.id);
+            st.setObject(4, p.firstName);
+            st.setObject(5, p.lastName);
+            st.setObject(6, p.orgId);
+            st.setObject(7, p.salary);
+            st.setObject(8, p.addrId);
+            st.setObject(9, p.old);
+            st.setObject(10, p.date);
+
+            st.executeUpdate();
+        }
+    }
+
+    /**
+     * Insert {@link Product} at h2 database.
+     *
+     * @param p Product.
+     * @throws SQLException If exception.
+     */
+    private void insertInDb(Product p) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"repl\".PRODUCT (_key, _val, id, name, price) values(?, ?, ?, ?, ?)")) {
+            st.setObject(1, p.id);
+            st.setObject(2, p);
+            st.setObject(3, p.id);
+            st.setObject(4, p.name);
+            st.setObject(5, p.price);
+
+            st.executeUpdate();
+        }
+    }
+
+    /**
+     * Insert {@link Purchase} at h2 database.
+     *
+     * @param p Purchase.
+     * @throws SQLException If exception.
+     */
+    private void insertInDb(Purchase p) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"part\".PURCHASE (_key, _val, id, personId, productId, organizationId) values(?, ?, ?, ?, ?, ?)")) {
+            st.setObject(1, p.key());
+            st.setObject(2, p);
+            st.setObject(3, p.id);
+            st.setObject(4, p.personId);
+            st.setObject(5, p.productId);
+            st.setObject(6, p.organizationId);
+
+            st.executeUpdate();
+        }
+    }
+    
+    /**
+     * Insert {@link Address} at h2 database.
+     *
+     * @param a Address.
+     * @throws SQLException If exception.
+     */
+    private void insertInDb(Address a) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"repl\".ADDRESS (_key, _val, id, street) values(?, ?, ?, ?)")) {
+            st.setObject(1, a.id);
+            st.setObject(2, a);
+            st.setObject(3, a.id);
+            st.setObject(4, a.street);
+
+            st.executeUpdate();
+        }
+    }
+
+    @QuerySqlFunction
+    public static int cool1() {
+        return 1;
+    }
+
+    @QuerySqlFunction
+    public static ResultSet table0(Connection c, String a, int b) throws SQLException {
+        return c.createStatement().executeQuery("select '" + a + "' as a, " +  b + " as b");
+    }
+
+    /**
+     * Person class. Stored at partitioned cache.
+     */
+    private static class Person implements Serializable {
+        /** Person ID (indexed). */
+        @QuerySqlField(index = true)
+        private int id;
+
+        /** Organization ID (indexed). */
+        @QuerySqlField(index = true)
+        private int orgId;
+
+        /** First name (not-indexed). */
+        @QuerySqlField
+        private String firstName;
+
+        /** Last name (not indexed). */
+        @QuerySqlField
+        private String lastName;
+
+        /** Salary (indexed). */
+        @QuerySqlField(index = true)
+        private double salary;
+
+        /** Address Id (indexed). */
+        @QuerySqlField(index = true)
+        private int addrId;
+
+        /** Date. */
+        @QuerySqlField(index = true)
+        public Date date = new Date(System.currentTimeMillis());
+
+        /** Old. */
+        @QuerySqlField(index = true)
+        public int old = 17;
+
+
+        /**
+         * Constructs person record.
+         *
+         * @param org Organization.
+         * @param firstName First name.
+         * @param lastName Last name.
+         * @param salary Salary.
+         */
+        Person(int id, Organization org, String firstName, String lastName, double salary, Address addr) {
+            this.id = id;
+            this.firstName = firstName;
+            this.lastName = lastName;
+            this.salary = salary;
+            orgId = org.id;
+            addrId = addr.id;
+        }
+
+        /**
+         * @return Custom affinity key to guarantee that person is always collocated with organization.
+         */
+        public AffinityKey<Integer> key() {
+            return new AffinityKey<>(id, orgId);
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof Person && id == ((Person)o).id;
+
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public String toString() {
+            return "Person [firstName=" + firstName +
+                ", lastName=" + lastName +
+                ", id=" + id +
+                ", orgId=" + orgId +
+                ", salary=" + salary + 
+                ", addrId=" + addrId + ']';
+        }
+    }
+
+    /**
+     * Organization class. Stored at partitioned cache.
+     */
+    private static class Organization implements Serializable {
+        /** Organization ID (indexed). */
+        @QuerySqlField(index = true)
+        private int id;
+
+        /** Organization name (indexed). */
+        @QuerySqlField(index = true)
+        private String name;
+
+        /**
+         * Create Organization.
+         *
+         * @param id Organization ID.
+         * @param name Organization name.
+         */
+        Organization(int id, String name) {
+            this.id = id;
+            this.name = name;
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof Organization && id == ((Organization)o).id;
+
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public String toString() {
+            return "Organization [id=" + id + ", name=" + name + ']';
+        }
+    }
+
+    /**
+     * Product class. Stored at replicated cache.
+     */
+    private static class Product implements Serializable {
+        /** Primary key. */
+        @QuerySqlField(index = true)
+        private int id;
+
+        /** Product name. */
+        @QuerySqlField
+        private String name;
+
+        /** Product price */
+        @QuerySqlField
+        private int price;
+
+        /**
+         * Create Product.
+         *
+         * @param id Product ID.
+         * @param name Product name.
+         * @param price Product price.
+         */
+        Product(int id, String name, int price) {
+            this.id = id;
+            this.name = name;
+            this.price = price;
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof Product && id == ((Product)o).id;
+
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public String toString() {
+            return "Product [id=" + id + ", name=" + name + ", price=" + price + ']';
+        }
+    }
+
+    /**
+     * Purchase class. Stored at partitioned cache.
+     */
+    private static class Purchase implements Serializable {
+        /** Primary key. */
+        @QuerySqlField(index = true)
+        private int id;
+
+        /** Product ID. */
+        @QuerySqlField
+        private int productId;
+
+        /** Person ID. */
+        @QuerySqlField
+        private int personId;
+        
+        /** Organization id. */
+        @QuerySqlField
+        private int organizationId;
+
+        /**
+         * Create Purchase.
+         *  @param id Purchase ID.
+         * @param product Purchase product.
+         * @param organizationId Organization Id.
+         * @param person Purchase person.
+         */
+        Purchase(int id, Product product, int organizationId, Person person) {
+            this.id = id;
+            productId = product.id;
+            personId = person.id;
+            this.organizationId = organizationId;
+        }
+
+        /**
+         * @return Custom affinity key to guarantee that purchase is always collocated with person.
+         */
+        public AffinityKey<Integer> key() {
+            return new AffinityKey<>(id, organizationId);
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof Purchase && id == ((Purchase)o).id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public String toString() {
+            return "Purchase [id=" + id + ", productId=" + productId + ", personId=" + personId + ']';
+        }
+    }
+
+    /**
+     * Address class. Stored at replicated cache.
+     */
+    private static class Address implements Serializable {
+        @QuerySqlField(index = true)
+        private int id;
+
+        @QuerySqlField(index = true)
+        private String street;
+
+        Address(int id, String street) {
+            this.id = id;
+            this.street = street;
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof Address && id == ((Address)o).id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public String toString() {
+            return "Address [id=" + id + ", street=" + street + ']';
+        }
+    }
+}

http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fe743909/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/H2CompareBigQueryTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/H2CompareBigQueryTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/H2CompareBigQueryTest.java
new file mode 100644
index 0000000..c5a2cad
--- /dev/null
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/H2CompareBigQueryTest.java
@@ -0,0 +1,707 @@
+/*
+ * 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.h2.sql;
+
+import org.apache.ignite.cache.*;
+import org.apache.ignite.cache.affinity.*;
+import org.apache.ignite.cache.query.annotations.*;
+import org.apache.ignite.configuration.*;
+
+import java.io.*;
+import java.sql.Date;
+import java.sql.*;
+import java.util.*;
+import java.util.concurrent.atomic.*;
+
+/**
+ * Executes one big query (and subqueries of the big query) to compare query results from h2 database instance and 
+ * mixed ignite caches (replicated and partitioned) which have the same data models and data content.
+ * 
+ * 
+ * <pre> 
+ *
+ *  -------------------------------------> rootOrderId (virtual) <--------------------------
+ *  |                                                                |                     |
+ *  |                                                                |                     |
+ *  |                                ---------------------           |                     |
+ *  |  ------------------            --part.ReplaceOrder--           |                     |
+ *  |  --part.CustOrder--            ---------------------           |                     |
+ *  |  ------------------            -  id  PK           -           |                     |
+ *  |  -  orderId  PK   - <--  <---> -  orderId          -           |                     |
+ *  -- -  rootOrderId   -   |        -  rootOrderId      - -----------                     |
+ *     -  origOrderId   -   |        -  refOrderId       -   // = origOrderId              |
+ *     -  date          -   |        -  date             -                                 |
+ *     -  alias         -   |        -  alias            -                                 |
+ *     -  archSeq       -   |        -  archSeq          -          -------------------    |
+ *     ------------------   |        ---------------------          ----repl.Exec------    |
+ *                          |                                       -------------------    |
+ *     -----------------    |                                       -  rootOrderId PK - ----
+ *     ---part.Cancel---    |                                       -  date           -
+ *     -----------------    |        ---------------------          -  execShares int -
+ *     -  id       PK  -    |        --part.OrderParams---          -  price      int -
+ *     -  refOrderId   - ---|        ---------------------          -  lastMkt    int -
+ *     -  date         -    |        -  id  PK           -          -------------------
+ *     -----------------    -------  - orderId           -
+ *                                   - date              -
+ *                                   - parentAlgo        -
+ *                                   ---------------------
+ *  </pre>
+ *
+ */
+public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
+    /** Root order count. */
+    private static final int ROOT_ORDER_CNT = 1000;
+
+    /** Dates count. */
+    private static final int DATES_CNT = 5;
+    
+    /** Full the big query. */
+    private String bigQry = getBigQry();
+
+    /**
+     * Extracts the big query from file.
+     *  
+     * @return Big query.
+     */
+    private String getBigQry() {
+        String res = "";
+
+        Reader isr = new InputStreamReader(getClass().getResourceAsStream("bigQuery.sql"));
+        
+        try(BufferedReader reader = new BufferedReader(isr)) {
+            for(String line; (line = reader.readLine()) != null; )
+                if (!line.startsWith("--")) // Skip commented lines.
+                    res += line + '\n';
+        }
+        catch (Throwable e) {
+            e.printStackTrace();
+            
+            fail();
+        }
+
+        return res;
+    }
+
+    /** {@inheritDoc} */
+    @Override protected void setIndexedTypes(CacheConfiguration<?, ?> cc, CacheMode mode) {
+        if (mode == CacheMode.PARTITIONED)
+            cc.setIndexedTypes(
+                Integer.class, CustOrder.class,
+                AffinityKey.class, ReplaceOrder.class,
+                AffinityKey.class, OrderParams.class,
+                AffinityKey.class, Cancel.class
+            );
+        else if (mode == CacheMode.REPLICATED)
+            cc.setIndexedTypes(
+                Integer.class, Exec.class
+            );
+        else
+            throw new IllegalStateException("mode: " + mode);
+    }
+
+    /** {@inheritDoc} */
+    @SuppressWarnings("unchecked")
+    @Override protected void initCacheAndDbData() throws SQLException {
+        final AtomicInteger idGen = new AtomicInteger();
+
+        final Iterable<Integer> rootOrderIds = new ArrayList<Integer>(){{
+            for (int i = 0; i < ROOT_ORDER_CNT; i++)
+                add(idGen.incrementAndGet());
+        }};
+
+        final Date curDate = new Date(new java.util.Date().getTime());
+
+        final List<Date> dates = new ArrayList<Date>(){{
+            for (int i = 0; i < DATES_CNT; i++)
+                add(new Date(curDate.getTime() - i * 24 * 60 * 60 * 1000)); // Minus i days.
+        }};
+
+        final Iterable<CustOrder> orders = new ArrayList<CustOrder>(){{
+            for (int rootOrderId : rootOrderIds) {
+                // Generate 1 - 5 orders for 1 root order.
+                for (int i = 0; i < rootOrderId % 5; i++) {
+                    int orderId = idGen.incrementAndGet();
+                    
+                    CustOrder order = new CustOrder(orderId, rootOrderId, dates.get(orderId % dates.size()) , 
+                        orderId % 2 == 0 ? "CUSTOM" : "OTHER", orderId);
+
+                    add(order);
+
+                    pCache.put(order.orderId, order);
+
+                    insertInDb(order);
+                }                
+            }
+        }};
+
+        final Collection<OrderParams> params = new ArrayList<OrderParams>(){{
+            for (CustOrder o : orders) {
+                OrderParams op = new OrderParams(idGen.incrementAndGet(), o.orderId, o.date, 
+                    o.orderId % 2 == 0 ? "Algo 1" : "Algo 2");
+
+                add(op);
+
+                pCache.put(op.key(), op);
+
+                insertInDb(op);
+            }
+        }};
+        
+        final Collection<ReplaceOrder> replaces = new ArrayList<ReplaceOrder>(){{
+            for (CustOrder o : orders) {
+                if (o.orderId % 7 == 0) {
+                    ReplaceOrder replace = new ReplaceOrder(idGen.incrementAndGet(), o.orderId, o.rootOrderId, o.alias,
+                        new Date(o.date.getTime() + 12 * 60 * 60 * 1000), o.orderId); // Plus a half of day.
+
+                    add(replace);
+
+                    pCache.put(replace.key(), replace);
+
+                    insertInDb(replace);
+                }
+            }
+        }};
+
+        final Collection<Cancel> cancels = new ArrayList<Cancel>(){{
+            for (CustOrder o : orders) {
+                if (o.orderId % 9 == 0) {
+                    Cancel c = new Cancel(idGen.incrementAndGet(), o.orderId, 
+                        new Date(o.date.getTime() + 12 * 60 * 60 * 1000));// Plus a half of day.
+
+                    add(c);
+
+                    pCache.put(c.key(), c); 
+
+                    insertInDb(c);
+                }
+            }
+        }};
+
+        final Collection<Exec> execs = new ArrayList<Exec>(){{
+            for (int rootOrderId : rootOrderIds) {
+                int execShares = 10000 + rootOrderId;
+                int price = 1000 + rootOrderId;
+                int latsMkt = 3000 + rootOrderId;
+                
+                Exec exec = new Exec(rootOrderId, dates.get(rootOrderId % dates.size()), execShares, price, latsMkt);
+
+                add(exec);
+
+                rCache.put(exec.rootOrderId, exec);
+
+                insertInDb(exec);
+            }
+        }};
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    @Override protected void checkAllDataEquals() throws Exception {
+        compareQueryRes0("select _key, _val, date, orderId, rootOrderId, alias, archSeq, origOrderId " +
+            "from \"part\".CustOrder");
+        compareQueryRes0("select _key, _val, id, date, orderId, rootOrderId, alias, archSeq, refOrderId " +
+            "from \"part\".ReplaceOrder");
+        compareQueryRes0("select _key, _val, id, date, orderId, parentAlgo from \"part\".OrderParams\n");
+        compareQueryRes0("select _key, _val, id, date, refOrderId from \"part\".Cancel\n");
+        compareQueryRes0(rCache, "select _key, _val, date, rootOrderId, execShares, price, lastMkt from \"repl\".Exec\n");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testUnionAllOrders() throws Exception {
+        compareQueryRes0(
+            "   select  date, orderId, rootOrderId " +
+                "   from \"part\".CustOrder where alias='CUSTOM'");
+                
+        compareQueryRes0(
+                "   select  date, orderId, rootOrderId " +
+                "   from \"part\".ReplaceOrder where alias='CUSTOM'");
+        
+        compareQueryRes0(
+            "   select 10" +
+
+                "   union all" +
+
+                "   select  20");
+        
+        compareQueryRes0(
+            "   select  date, orderId, rootOrderId " +
+                "   from \"part\".CustOrder where alias='CUSTOM'" +
+
+                "   union all" +
+
+                "   select  date, orderId, rootOrderId " +
+                "   from \"part\".ReplaceOrder where alias='CUSTOM'");
+    }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testBigQuery() throws Exception {
+        List<List<?>> res = compareQueryRes0(bigQry);
+        
+        assertTrue(!res.isEmpty()); // Ensure we set good testing data at database.
+    }
+    
+    /** {@inheritDoc} */
+    @Override protected void initializeH2Schema() throws SQLException {
+        Statement st = conn.createStatement();
+
+        st.execute("CREATE SCHEMA \"part\"");
+        st.execute("CREATE SCHEMA \"repl\"");
+
+        st.execute("create table \"part\".CustOrder" +
+            "  (" +
+            "  _key int not null," +
+            "  _val other not null," +
+            "  orderId int unique," +
+            "  rootOrderId int," +
+            "  origOrderId int," +
+            "  archSeq int," +
+            "  date Date, " +
+            "  alias varchar(255)" +
+            "  )");
+
+        st.execute("create table \"part\".ReplaceOrder" +
+            "  (" +
+            "  _key other not null," +
+            "  _val other not null," +
+            "  id int unique," +
+            "  orderId int ," +
+            "  rootOrderId int," +
+            "  refOrderId int," +
+            "  archSeq int," +
+            "  date Date, " +
+            "  alias varchar(255)" +
+            "  )");
+
+        st.execute("create table \"part\".OrderParams" +
+            "  (" +
+            "  _key other not null," +
+            "  _val other not null," +
+            "  id int unique," +
+            "  orderId int ," +
+            "  date Date, " +
+            "  parentAlgo varchar(255)" +
+            "  )");
+
+        st.execute("create table \"part\".Cancel" +
+            "  (" +
+            "  _key other not null," +
+            "  _val other not null," +
+            "  id int unique," +
+            "  date Date, " +
+            "  refOrderId int" +
+            "  )");
+
+        st.execute("create table \"repl\".Exec" +
+            "  (" +
+            "  _key int not null," +
+            "  _val other not null," +
+            "  rootOrderId int unique," +
+            "  date Date, " +
+            "  execShares int," +
+            "  price int," +
+            "  lastMkt int" +
+            "  )");
+        
+        conn.commit();
+    }
+
+    /**
+     * Insert {@link CustOrder} at h2 database.
+     *  
+     * @param o CustOrder.
+     */
+    private void insertInDb(CustOrder o) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"part\".CustOrder (_key, _val, orderId, rootOrderId, date, alias, archSeq, origOrderId) " +
+                "values(?, ?, ?, ?, ?, ?, ?, ?)")) {
+            int i = 0;
+            
+            st.setObject(++i, o.orderId);
+            st.setObject(++i, o);
+            st.setObject(++i, o.orderId);
+            st.setObject(++i, o.rootOrderId);
+            st.setObject(++i, o.date);
+            st.setObject(++i, o.alias);
+            st.setObject(++i, o.archSeq);
+            st.setObject(++i, o.origOrderId);
+
+            st.executeUpdate();
+        }
+    }
+
+    /**
+     * Insert {@link ReplaceOrder} at h2 database.
+     *
+     * @param o ReplaceOrder.
+     */
+    private void insertInDb(ReplaceOrder o) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"part\".ReplaceOrder (_key, _val, id, orderId, rootOrderId, date, alias, archSeq, refOrderId) " +
+                "values(?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
+            int i = 0;
+            
+            st.setObject(++i, o.key());
+            st.setObject(++i, o);
+            st.setObject(++i, o.id);
+            st.setObject(++i, o.orderId);
+            st.setObject(++i, o.rootOrderId);
+            st.setObject(++i, o.date);
+            st.setObject(++i, o.alias);
+            st.setObject(++i, o.archSeq);
+            st.setObject(++i, o.refOrderId);
+
+            st.executeUpdate();
+        }
+    }
+
+    /**
+     * Insert {@link OrderParams} at h2 database.
+     *
+     * @param o OrderParams.
+     */
+    private void insertInDb(OrderParams o) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"part\".OrderParams (_key, _val, id, date, orderId, parentAlgo) values(?, ?, ?, ?, ?, ?)")) {
+            int i = 0;
+            
+            st.setObject(++i, o.key());
+            st.setObject(++i, o);
+            st.setObject(++i, o.id);
+            st.setObject(++i, o.date);
+            st.setObject(++i, o.orderId);
+            st.setObject(++i, o.parentAlgo);
+
+            st.executeUpdate();
+        }
+    }
+
+    /**
+     * Insert {@link Cancel} at h2 database.
+     *
+     * @param o Cancel.
+     */
+    private void insertInDb(Cancel o) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"part\".Cancel (_key, _val, id, date, refOrderId) values(?, ?, ?, ?, ?)")) {
+            int i = 0;
+            
+            st.setObject(++i, o.key());
+            st.setObject(++i, o);
+            st.setObject(++i, o.id);
+            st.setObject(++i, o.date);
+            st.setObject(++i, o.refOrderId);
+
+            st.executeUpdate();
+        }
+    }
+
+    /**
+     * Insert {@link Exec} at h2 database.
+     *
+     * @param o Execution.
+     */
+    private void insertInDb(Exec o) throws SQLException {
+        try(PreparedStatement st = conn.prepareStatement(
+            "insert into \"repl\".Exec (_key, _val, date, rootOrderId, execShares, price, lastMkt) " +
+                "values(?, ?, ?, ?, ?, ?, ?)")) {
+            int i = 0;
+            
+            st.setObject(++i, o.rootOrderId);
+            st.setObject(++i, o);
+            st.setObject(++i, o.date);
+            st.setObject(++i, o.rootOrderId);
+            st.setObject(++i, o.execShares);
+            st.setObject(++i, o.price);
+            st.setObject(++i, o.lastMkt);
+
+            st.executeUpdate();
+        }
+    }
+
+    /**
+     * Custom Order.
+     */
+    static class CustOrder implements Serializable {
+        /** Primary key. */
+        @QuerySqlField(index = true)
+        private int orderId;
+
+        /** Root order ID*/
+        @QuerySqlField
+        private int rootOrderId;
+
+        /** Orig order ID*/
+        @QuerySqlField
+        private int origOrderId;
+
+        /** Date */
+        @QuerySqlField
+        private Date date ;
+
+        /**  */
+        @QuerySqlField
+        private String alias = "CUSTOM";
+
+        /**  */
+        @QuerySqlField
+        private int archSeq = 11; // TODO: use it.
+
+        /**
+         * @param orderId Order id.
+         * @param rootOrderId Root order id.
+         * @param date Date.
+         * @param alias Alias.
+         * @param origOrderId Orig order id.
+         */
+        CustOrder(int orderId, int rootOrderId, Date date, String alias, int origOrderId) {
+            this.orderId = orderId;
+            this.rootOrderId = rootOrderId;
+            this.origOrderId = origOrderId;
+            this.date = date;
+            this.alias = alias;
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof CustOrder && orderId == ((CustOrder)o).orderId;
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return orderId;
+        }
+    }
+
+    /**
+     * Replace Order.
+     */
+    static class ReplaceOrder implements Serializable {
+        /** Primary key. */
+        @QuerySqlField(index = true)
+        private int id;
+        
+        /** Order id. */
+        @QuerySqlField(index = true)
+        private int orderId;
+
+        /** Root order ID*/
+        @QuerySqlField
+        private int rootOrderId;
+
+        /** Ref order ID*/
+        @QuerySqlField
+        private int refOrderId;
+
+        /** Date */
+        @QuerySqlField
+        private Date date ;
+
+        /**  */
+        @QuerySqlField
+        private String alias = "CUSTOM";
+
+        /**  */
+        @QuerySqlField
+        private int archSeq = 111; // TODO: use it.
+
+        /**
+         * @param id Id.
+         * @param orderId Order id.
+         * @param rootOrderId Root order id.
+         * @param alias Alias.
+         * @param date Date.
+         * @param refOrderId Reference order id.
+         */
+        ReplaceOrder(int id, int orderId, int rootOrderId, String alias, Date date, int refOrderId) {
+            this.id = id;
+            this.orderId = orderId;
+            this.rootOrderId = rootOrderId;
+            this.refOrderId = refOrderId;
+            this.date = date;
+            this.alias = alias;
+        }
+
+        /**
+         * @return Afinity key.
+         */
+        public AffinityKey<Integer> key() {
+            return new AffinityKey<>(id, orderId);
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof ReplaceOrder && id == ((ReplaceOrder)o).id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return id;
+        }
+    }
+
+    /**
+     * Order params.
+     */
+    static class OrderParams implements Serializable {
+        /** Primary key. */
+        @QuerySqlField(index = true)
+        private int id;
+
+        /** Order id. */
+        @QuerySqlField(index = true)
+        private int orderId;
+
+        /** Date */
+        @QuerySqlField
+        private Date date ;
+
+        /**  */
+        @QuerySqlField
+        private String parentAlgo = "CUSTOM_ALGO";
+
+        /**
+         * @param id Id.
+         * @param orderId Order id.
+         * @param date Date.
+         * @param parentAlgo Parent algo.
+         */
+        OrderParams(int id, int orderId, Date date, String parentAlgo) {
+            this.id = id;
+            this.orderId = orderId;
+            this.date = date;
+            this.parentAlgo = parentAlgo;
+        }
+
+        /**
+         * @return Afinity key.
+         */
+        public AffinityKey<Integer> key() {
+            return new AffinityKey<>(id, orderId);
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof OrderParams && id == ((OrderParams)o).id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return id;
+        }
+    }
+
+    /**
+     * Cancel Order.
+     */
+    static class Cancel implements Serializable {
+        /** Primary key. */
+        @QuerySqlField(index = true)
+        private int id;
+
+        /** Order id. */
+        @QuerySqlField(index = true)
+        private int refOrderId;
+
+        /** Date */
+        @QuerySqlField
+        private Date date;
+
+        /**
+         * @param id
+         * @param refOrderId Reference order id.
+         * @param date Date.
+         */
+        Cancel(int id, int refOrderId, Date date) {
+            this.id = id;
+            this.refOrderId = refOrderId;
+            this.date = date;
+        }
+
+        /**
+         * @return Afinity key.
+         */
+        public AffinityKey<Integer> key() {
+            return new AffinityKey<>(id, refOrderId);
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof Cancel && id == ((Cancel)o).id;
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return id;
+        }
+    }
+
+    /**
+     * Execute information about root query.
+     */
+    static class Exec implements Serializable {
+        /** Primary key. */
+        @QuerySqlField
+        private int rootOrderId;
+
+        /** Date */
+        @QuerySqlField
+        private Date date ;
+
+        /** */
+        @QuerySqlField
+        private int execShares;
+
+        /** */
+        @QuerySqlField
+        private int price;
+
+        /** */
+        @QuerySqlField
+        private int lastMkt;
+
+        /**
+         * @param rootOrderId Root order id.
+         * @param date Date.
+         * @param execShares Execute shares.
+         * @param price Price.
+         * @param lastMkt Last mkt.
+         */
+        Exec(int rootOrderId, Date date, int execShares, int price, int lastMkt) {
+            this.rootOrderId = rootOrderId;
+            this.date = date;
+            this.execShares = execShares;
+            this.price = price;
+            this.lastMkt = lastMkt;
+        }
+
+        /** {@inheritDoc} */
+        @Override public boolean equals(Object o) {
+            return this == o || o instanceof Exec && rootOrderId == ((Exec)o).rootOrderId;
+        }
+
+        /** {@inheritDoc} */
+        @Override public int hashCode() {
+            return rootOrderId;
+        }
+    }
+}


Mime
View raw message