You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by sb...@apache.org on 2015/04/06 14:53:40 UTC
incubator-ignite git commit: ignite-437: testAll and BQ1
Repository: incubator-ignite
Updated Branches:
refs/heads/ignite-437-sqltests-p2 d76c20112 -> c6816a33f
ignite-437: testAll and BQ1
Project: http://git-wip-us.apache.org/repos/asf/incubator-ignite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-ignite/commit/c6816a33
Tree: http://git-wip-us.apache.org/repos/asf/incubator-ignite/tree/c6816a33
Diff: http://git-wip-us.apache.org/repos/asf/incubator-ignite/diff/c6816a33
Branch: refs/heads/ignite-437-sqltests-p2
Commit: c6816a33f044cce2264a3cefe280b792d7b16887
Parents: d76c201
Author: Artem Shutak <as...@gridgain.com>
Authored: Mon Apr 6 15:53:46 2015 +0300
Committer: Artem Shutak <as...@gridgain.com>
Committed: Mon Apr 6 15:53:46 2015 +0300
----------------------------------------------------------------------
.../query/h2/sql/BaseH2CompareQueryTest.java | 253 ++++++++++++++++++-
.../query/h2/sql/GridQueryParsingTest.java | 4 +-
.../query/h2/sql/H2CompareBigQueryTest.java | 92 ++++++-
3 files changed, 325 insertions(+), 24 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/c6816a33/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
index cbe2f6a..8ba1e68 100644
--- 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
@@ -16,6 +16,7 @@ import org.apache.ignite.configuration.*;
import java.io.*;
import java.sql.*;
+import java.sql.Date;
import java.util.*;
/**
@@ -23,6 +24,13 @@ import java.util.*;
* which have the same data models and data content.
*/
public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
+
+ public static final int ORG_CNT = 3;
+ public static final int ADDR_CNT = 10;
+ public static final int PERS_CNT = 5;
+ public static final int PROD_CNT = 10;
+ public static final int PURCH_CNT = PROD_CNT * 2;
+
/** {@inheritDoc} */
@Override protected void setIndexedTypes(CacheConfiguration<?, ?> cc, CacheMode mode) {
if (mode == CacheMode.PARTITIONED)
@@ -33,7 +41,8 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
);
else if (mode == CacheMode.REPLICATED)
cc.setIndexedTypes(
- Integer.class, Product.class
+ Integer.class, Product.class,
+ Integer.class, Address.class
);
else
throw new IllegalStateException("mode: " + mode);
@@ -47,7 +56,7 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
// Organizations.
List<Organization> organizations = new ArrayList<>();
- for (int i = 0; i < 3; i++) {
+ for (int i = 0; i < ORG_CNT; i++) {
int id = idGen++;
Organization org = new Organization(id, "Org" + id);
@@ -58,15 +67,30 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
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 < 5; i++) {
+ 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);
+ "name" + id, "lastName" + id, id * 100.0, addreses.get(i % addreses.size()));
// Add a Person without lastname.
if (id == organizations.size() + 1)
@@ -82,7 +106,7 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
// Products.
List<Product> products = new ArrayList<>();
- for (int i = 0; i < 10; i++) {
+ for (int i = 0; i < PROD_CNT; i++) {
int id = idGen++;
Product product = new Product(id, "Product" + id, id*1000);
@@ -95,7 +119,7 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
}
// Purchases.
- for (int i = 0; i < products.size() * 2; i++) {
+ for (int i = 0; i < PURCH_CNT; i++) {
int id = idGen++;
Purchase purchase = new Purchase(id, products.get(i % products.size()), persons.get(i % persons.size()));
@@ -118,6 +142,128 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
}
/**
+ * *
+ * @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");
+
+ 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 < ?");
+
+ 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 testSimpleReplSelect() throws Exception {
@@ -248,7 +394,10 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
" firstName varchar(255), " +
" lastName varchar(255)," +
" orgId int not null," +
- " salary double )");
+ " salary double," +
+ " addrId int," +
+ " old int," +
+ " date Date )");
st.execute("create table \"repl\".PRODUCT" +
" (_key int not null ," +
@@ -264,6 +413,12 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
" personId 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();
}
@@ -293,7 +448,7 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
*/
private void insertInDb(Person p) throws SQLException {
try(PreparedStatement st = conn.prepareStatement("insert into \"part\".PERSON " +
- "(_key, _val, id, firstName, lastName, orgId, salary) values(?, ?, ?, ?, ?, ?, ?)")) {
+ "(_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);
@@ -301,6 +456,9 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
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();
}
@@ -343,6 +501,34 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
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.
@@ -368,6 +554,19 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
@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.
*
@@ -376,12 +575,13 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
* @param lastName Last name.
* @param salary Salary.
*/
- Person(int id, Organization org, String firstName, String lastName, double 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;
}
/**
@@ -408,7 +608,8 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
", lastName=" + lastName +
", id=" + id +
", orgId=" + orgId +
- ", salary=" + salary + ']';
+ ", salary=" + salary +
+ ", addrId=" + addrId + ']';
}
}
@@ -537,7 +738,6 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
/** {@inheritDoc} */
@Override public boolean equals(Object o) {
return this == o || o instanceof Purchase && id == ((Purchase)o).id;
-
}
/** {@inheritDoc} */
@@ -550,4 +750,35 @@ public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
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/c6816a33/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java
index d1c59bb..b885a18 100644
--- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java
@@ -22,7 +22,6 @@ import org.apache.ignite.cache.*;
import org.apache.ignite.cache.query.annotations.*;
import org.apache.ignite.configuration.*;
import org.apache.ignite.internal.*;
-import org.apache.ignite.internal.processors.cache.*;
import org.apache.ignite.internal.processors.query.*;
import org.apache.ignite.internal.processors.query.h2.*;
import org.apache.ignite.internal.util.typedef.internal.*;
@@ -199,6 +198,9 @@ public class GridQueryParsingTest extends GridCommonAbstractTest {
checkQuery("select addr.street from Person p, (select a.street from Address a where a.street is not null) addr");
checkQuery("select p.name n from \"\".Person p order by p.old + 10");
+
+ checkQuery("select 'foo' as bar union select 'foo' as bar");
+ checkQuery("select 'foo' as bar union all select 'foo' as bar");
}
/**
http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/c6816a33/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
index 0385868..25c9c04 100644
--- 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
@@ -50,12 +50,12 @@ public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
@Override protected void initCacheAndDbData() throws SQLException {
int idGen = 0;
- final List<OrderT1> ordsT1 = new ArrayList<>();
+ final Collection<OrderT1> ordsT1 = new ArrayList<>();
for (int i = 0; i < 10; i++) {
int id = idGen++;
- OrderT1 order = new OrderT1();
+ OrderT1 order = new OrderT1(id, 2000 + id, id % 2 == 0 ? "CUSTOM" : "OTHER");
ordsT1.add(order);
@@ -64,12 +64,12 @@ public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
insertInDb(order);
}
- final List<OrderT2> ordsT2 = new ArrayList<>();
+ final Collection<OrderT2> ordsT2 = new ArrayList<>();
for (int i = 0; i < 10; i++) {
int id = idGen++;
- OrderT2 order = new OrderT2();
+ OrderT2 order = new OrderT2(id, 3000 + id, id % 2 == 0 ? "CUSTOM" : "OTHER");
ordsT2.add(order);
@@ -93,14 +93,65 @@ public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
public void testUnionAllOrders() throws Exception {
compareQueryRes0(
" select date, orderId, rootOrderId " +
- " from OrderT1 where alias='CUSTOM'" +
+ " from \"part\".OrderT1 where alias='CUSTOM'");
+
+ compareQueryRes0(
+ " select date, orderId, rootOrderId " +
+ " from \"part\".OrderT2 where alias='CUSTOM'");
+
+ compareQueryRes0(
+ " select 10" +
+
+ " union all" +
+
+ " select 20");
+
+ compareQueryRes0(
+ " select date, orderId, rootOrderId " +
+ " from \"part\".OrderT1 where alias='CUSTOM'" +
" union all" +
" select date, orderId, rootOrderId " +
- " from OrderT2 where alias='CUSTOM'");
+ " from \"part\".OrderT2 where alias='CUSTOM'");
}
+ /**
+ * @throws Exception If failed.
+ */
+ public void testBigQueryPart1() throws Exception {
+ compareQueryRes0(
+ " select" +
+ " root_order_id as cust_order_id," +
+ " co.date, co.order_id," +
+ " replace(co.alias,'_ALGO','') as alias, co.portfolio," +
+ " co.symbol, co.side, co.order_datetime, co.ord_size, co.currency, co.exchange," +
+ " co.ord_type, co.lim_price, co.comment, co.tif, co.exec_inst," +
+ " co.rule80, co.sndr_compid, co.target_subid," +
+ " co.OnBehalfOfSubID, co.SenderLocationID, co.ss_exempt, co.locate_reqd, co.locate_brkr," +
+ " cpp.parent_algo" +
+ " from (" +
+ " select" +
+ " date, order_id, order_datetime, orig_order_id, portfolio, alias, symbol," +
+ " side, currency, exchange, ord_size, ord_type, lim_price, comment, tif, exec_inst," +
+ " rule80, tag_22, tag_48, sndr_compid," +
+ " target_subid, OnBehalfOfSubID, SenderLocationID, ss_exempt, locate_reqd, locate_brkr," +
+ " source, arch_seq, 0 as bench, ext_order_id," +
+ " root_order_id" +
+ " from GetsCustOrder where alias='CUSTOM'" +
+ " union all" +
+ " select" +
+ " date, order_id, order_datetime, ref_order_id as orig_order_id, portfolio, alias, symbol," +
+ " side, currency, exchange, isnull(leaves_qty,ord_size), ord_type, lim_price, comment, tif, exec_inst," +
+ " isnull(rule80,'A'), tag_22, tag_48, sndr_compid," +
+ " target_subid, OnBehalfOfSubID, SenderLocationID, ss_exempt, locate_reqd, locate_brkr," +
+ " source, arch_seq, 0 as bench, ext_order_id," +
+ " root_order_id" +
+ " from GetsCustReplace where alias='CUSTOM'" +
+ " ) co, GetsOrderAlgoParams cpp" +
+ " where co.date = cpp.date and co.order_id = cpp.order_id " +
+ " and dateToLong(co.date)+co.arch_seq =(BigQueryPartSelectMax) and co.alias='CUSTOM'");
+ }
/** {@inheritDoc} */
@Override protected void initializeH2Schema() throws SQLException {
@@ -113,7 +164,7 @@ public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
" (" +
" _key int not null," +
" _val other not null," +
- " orderId int," +
+ " orderId int unique," +
" rootOrderId int," +
" date Date, " +
" alias varchar(255)" +
@@ -123,7 +174,7 @@ public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
" (" +
" _key int not null," +
" _val other not null," +
- " orderId int," +
+ " orderId int unique," +
" rootOrderId int," +
" date Date, " +
" alias varchar(255)" +
@@ -148,7 +199,7 @@ public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
private void insertInDb(OrderT2 o) throws SQLException {
try(PreparedStatement st = conn.prepareStatement(
- "insert into \"part\".OrderT1 (_key, _val, orderId, rootOrderId, date, alias) values(?, ?, ?, ?, ?, ?)")) {
+ "insert into \"part\".OrderT2 (_key, _val, orderId, rootOrderId, date, alias) values(?, ?, ?, ?, ?, ?)")) {
st.setObject(1, o.orderId);
st.setObject(2, o);
st.setObject(3, o.orderId);
@@ -171,12 +222,23 @@ public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
/** Date */
@QuerySqlField
- private Date date = new Date();
+ private Date date ;
/** */
@QuerySqlField
private String alias = "CUSTOM";
+ /**
+ * @param orderId Order id.
+ * @param rootOrderId Root order id.
+ * @param alias Alias.
+ */
+ OrderT1(int orderId, int rootOrderId, String alias) {
+ this.orderId = orderId;
+ this.rootOrderId = rootOrderId;
+ this.alias = alias;
+ }
+
/** {@inheritDoc} */
@Override public boolean equals(Object o) {
return this == o || o instanceof OrderT1 && orderId == ((OrderT1)o).orderId;
@@ -199,15 +261,21 @@ public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
/** Date */
@QuerySqlField
- private Date date = new Date();
+ private Date date ;
/** */
@QuerySqlField
private String alias = "CUSTOM";
+ OrderT2(int orderId, int rootOrderId, String alias) {
+ this.orderId = orderId;
+ this.rootOrderId = rootOrderId;
+ this.alias = alias;
+ }
+
/** {@inheritDoc} */
@Override public boolean equals(Object o) {
- return this == o || o instanceof OrderT1 && orderId == ((OrderT1)o).orderId;
+ return this == o || o instanceof OrderT2 && orderId == ((OrderT2)o).orderId;
}
/** {@inheritDoc} */