You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ma...@apache.org on 2016/02/02 05:00:23 UTC
[49/50] [abbrv] phoenix git commit: Sync with master;
Bug fix for ResultSet object of Float type; Walkaround for PHOENIX-2647
http://git-wip-us.apache.org/repos/asf/phoenix/blob/74409be8/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java
----------------------------------------------------------------------
diff --cc phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java
index acd040d,0000000..41d0542
mode 100644,000000..100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java
@@@ -1,1206 -1,0 +1,1206 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.calcite;
+
+import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.Timestamp;
+import java.text.SimpleDateFormat;
+import java.util.Properties;
+
+import org.junit.Before;
+import org.junit.Ignore;
+import org.junit.Test;
+
+/**
+ * Integration test for queries powered by Calcite.
+ */
+public class CalciteIT extends BaseCalciteIT {
+
+ @Before
+ public void initTable() throws Exception {
+ final String url = getUrl();
+ initATableValues(getOrganizationId(), null, url);
+ initJoinTableValues(url, null, null);
+ initArrayTable();
+ initSaltedTables(null);
+ final Connection connection = DriverManager.getConnection(url);
+ connection.createStatement().execute("CREATE VIEW IF NOT EXISTS v AS SELECT * from aTable where a_string = 'a'");
+ connection.createStatement().execute("CREATE SEQUENCE IF NOT EXISTS seq0 START WITH 1 INCREMENT BY 1");
+ connection.createStatement().execute("CREATE SEQUENCE IF NOT EXISTS my.seq1 START WITH 2 INCREMENT BY 2");
+ connection.createStatement().execute("UPDATE STATISTICS ATABLE");
+ connection.createStatement().execute("UPDATE STATISTICS " + JOIN_CUSTOMER_TABLE_FULL_NAME);
+ connection.createStatement().execute("UPDATE STATISTICS " + JOIN_ITEM_TABLE_FULL_NAME);
+ connection.createStatement().execute("UPDATE STATISTICS " + JOIN_SUPPLIER_TABLE_FULL_NAME);
+ connection.createStatement().execute("UPDATE STATISTICS " + JOIN_ORDER_TABLE_FULL_NAME);
+ connection.createStatement().execute("UPDATE STATISTICS " + SCORES_TABLE_NAME);
+ connection.createStatement().execute("UPDATE STATISTICS " + SALTED_TABLE_NAME);
+ connection.close();
+ }
+
+ private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+
+ @Test public void testTableScan() throws Exception {
+ start(false).sql("select * from aTable where a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"}})
+ .close();
+
+ // FIXME: Should be 14:22:56 instead. Wrong due to time zone.
+ start(false).sql("select \"DATE\" from " + JOIN_ORDER_TABLE_FULL_NAME + " where \"order_id\" = '000000000000001'")
- .resultIs(new Object[][]{
++ .resultIs(false, new Object[][]{
+ {new Timestamp(format.parse("2013-11-22 19:22:56").getTime())}})
+ .close();
+
+ start(false).sql("select student_id, scores from " + SCORES_TABLE_NAME)
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {1, new Integer[] {85, 80, 82}},
+ {2, null},
+ {3, new Integer[] {87, 88, 80}}})
+ .close();
+ }
+
+ @Test public void testProject() throws Exception {
+ start(false).sql("select entity_id, a_string, organization_id from aTable where a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1], A_STRING=[$2], ORGANIZATION_ID=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00A123122312312", "a", "00D300000000XHP"},
+ {"00A223122312312", "a", "00D300000000XHP"},
+ {"00A323122312312", "a", "00D300000000XHP"},
+ {"00A423122312312", "a", "00D300000000XHP"}})
+ .close();
+ }
+
+ @Test public void testJoin() throws Exception {
+ start(false).sql("select t1.entity_id, t2.a_string, t1.organization_id from aTable t1 join aTable t2 on t1.entity_id = t2.entity_id and t1.organization_id = t2.organization_id where t1.a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(ENTITY_ID=[$4], A_STRING=[$2], ORGANIZATION_ID=[$3])\n" +
+ " PhoenixServerJoin(condition=[AND(=($4, $1), =($3, $0))], joinType=[inner])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00A123122312312", "a", "00D300000000XHP"},
+ {"00A223122312312", "a", "00D300000000XHP"},
+ {"00A323122312312", "a", "00D300000000XHP"},
+ {"00A423122312312", "a", "00D300000000XHP"}})
+ .close();
+
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], supplier_id=[$3], NAME0=[$4])\n" +
+ " PhoenixServerJoin(condition=[=($2, $3)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"0000000001", "T1", "0000000001", "S1"},
+ {"0000000002", "T2", "0000000001", "S1"},
+ {"0000000003", "T3", "0000000002", "S2"},
+ {"0000000004", "T4", "0000000002", "S2"},
+ {"0000000005", "T5", "0000000005", "S5"},
+ {"0000000006", "T6", "0000000006", "S6"}})
+ .close();
+
+ start(false).sql("SELECT * FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND supp.name = 'S5'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], PRICE=[$2], DISCOUNT1=[$3], DISCOUNT2=[$4], supplier_id=[$5], DESCRIPTION=[$6], supplier_id0=[$7], NAME0=[$8], PHONE=[$9], ADDRESS=[$10], LOC_ID=[$11])\n" +
+ " PhoenixServerJoin(condition=[=($5, $7)], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1], PHONE=[$2], ADDRESS=[$3], LOC_ID=[$4], NAME5=[CAST($1):VARCHAR(2) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\"])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]], filter=[=(CAST($1):VARCHAR(2) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\", 'S5')])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"0000000005", "T5", 500, 8, 15, "0000000005", "Item T5", "0000000005", "S5", "888-888-5555", "505 YYY Street", "10005"}})
+ .close();
+
+ start(false).sql("SELECT \"order_id\", i.name, i.price, discount2, quantity FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o INNER JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" AND o.price = (i.price * (100 - discount2)) / 100.0 WHERE quantity < 5000")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(order_id=[$5], NAME=[$1], PRICE=[$2], DISCOUNT2=[$3], QUANTITY=[$7])\n" +
+ " PhoenixServerJoin(condition=[AND(=($6, $0), =($8, $4))], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], PRICE=[$2], DISCOUNT2=[$4], $f7=[/(*($2, -(100, $4)), 100.0)])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(order_id=[$0], item_id=[$2], QUANTITY=[$4], PRICE7=[CAST($3):DECIMAL(17, 6)])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]], filter=[<($4, 5000)])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"000000000000004", "T6", 600, 15, 4000}})
+ .close();
+ }
+
+ @Test public void testRightOuterJoin() throws Exception {
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item RIGHT OUTER JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$2], NAME=[$3], supplier_id=[$0], NAME0=[$1])\n" +
+ " PhoenixServerJoin(condition=[=($4, $0)], joinType=[left])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"0000000001", "T1", "0000000001", "S1"},
+ {"0000000002", "T2", "0000000001", "S1"},
+ {"0000000003", "T3", "0000000002", "S2"},
+ {"0000000004", "T4", "0000000002", "S2"},
+ {null, null, "0000000003", "S3"},
+ {null, null, "0000000004", "S4"},
+ {"0000000005", "T5", "0000000005", "S5"},
+ {"0000000006", "T6", "0000000006", "S6"}})
+ .close();
+ }
+
+ @Test public void testClientJoin() throws Exception {
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item FULL OUTER JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" order by \"item_id\", supp.name")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientSort(sort0=[$0], sort1=[$3], dir0=[ASC], dir1=[ASC])\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], supplier_id=[$3], NAME0=[$4])\n" +
+ " PhoenixClientJoin(condition=[=($2, $3)], joinType=[full])\n" +
+ " PhoenixServerSort(sort0=[$2], dir0=[ASC])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"0000000001", "T1", "0000000001", "S1"},
+ {"0000000002", "T2", "0000000001", "S1"},
+ {"0000000003", "T3", "0000000002", "S2"},
+ {"0000000004", "T4", "0000000002", "S2"},
+ {"0000000005", "T5", "0000000005", "S5"},
+ {"0000000006", "T6", "0000000006", "S6"},
+ {"invalid001", "INVALID-1", null, null},
+ {null, null, "0000000003", "S3"},
+ {null, null, "0000000004", "S4"}})
+ .close();
+
+ start(false).sql("select t1.entity_id, t2.a_string, t1.organization_id from aTable t1 join aTable t2 on t1.organization_id = t2.organization_id and t1.entity_id = t2.entity_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(ENTITY_ID=[$1], A_STRING=[$4], ORGANIZATION_ID=[$0])\n" +
+ " PhoenixClientJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[FORWARD])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00A123122312312", "a", "00D300000000XHP"},
+ {"00A223122312312", "a", "00D300000000XHP"},
+ {"00A323122312312", "a", "00D300000000XHP"},
+ {"00A423122312312", "a", "00D300000000XHP"},
+ {"00B523122312312", "b", "00D300000000XHP"},
+ {"00B623122312312", "b", "00D300000000XHP"},
+ {"00B723122312312", "b", "00D300000000XHP"},
+ {"00B823122312312", "b", "00D300000000XHP"},
+ {"00C923122312312", "c", "00D300000000XHP"}})
+ .close();
+ }
+
+ @Test public void testJoinPlanningWithCollation() throws Exception {
+ // Server-join with LHS sorted on order-by fields
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" order by supp.\"supplier_id\"")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$2], NAME=[$3], supplier_id=[$0], NAME0=[$1])\n" +
+ " PhoenixServerJoin(condition=[=($4, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]], scanOrder=[FORWARD])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n")
+ .close();
+
+ // Server-join with LHS reversely sorted on order-by fields
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" order by supp.\"supplier_id\" DESC")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$2], NAME=[$3], supplier_id=[$0], NAME0=[$1])\n" +
+ " PhoenixServerJoin(condition=[=($4, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]], scanOrder=[REVERSE])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n")
+ .close();
+
+ // Join key being order-by fields with the other side sorted on order-by fields
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" order by item.\"supplier_id\"")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], supplier_id=[$3], NAME0=[$4], supplier_id0=[$2])\n" +
+ " PhoenixClientJoin(condition=[=($2, $3)], joinType=[inner])\n" +
+ " PhoenixServerSort(sort0=[$2], dir0=[ASC])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]], scanOrder=[FORWARD])\n")
+ .close();
+
+ start(false).sql("SELECT \"order_id\", i.name, i.price, discount2, quantity FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o LEFT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" limit 2")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(order_id=[$0], NAME=[$4], PRICE=[$5], DISCOUNT2=[$6], QUANTITY=[$2])\n" +
+ " PhoenixLimit(fetch=[2])\n" +
+ " PhoenixClientJoin(condition=[=($1, $3)], joinType=[left])\n" +
+ " PhoenixClientSort(sort0=[$1], dir0=[ASC])\n" +
+ " PhoenixLimit(fetch=[2])\n" +
+ " PhoenixServerProject(order_id=[$0], item_id=[$2], QUANTITY=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], PRICE=[$2], DISCOUNT2=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], scanOrder=[FORWARD])\n")
+ .close();
+ }
+
+ @Test public void testMultiJoin() throws Exception {
+ start(false).sql("select t1.entity_id, t2.a_string, t3.organization_id from aTable t1 join aTable t2 on t1.entity_id = t2.entity_id and t1.organization_id = t2.organization_id join atable t3 on t1.entity_id = t3.entity_id and t1.organization_id = t3.organization_id where t1.a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(ENTITY_ID=[$4], A_STRING=[$2], ORGANIZATION_ID=[$6])\n" +
+ " PhoenixServerJoin(condition=[AND(=($4, $1), =($3, $0))], joinType=[inner])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixClientProject(ORGANIZATION_ID=[$2], ENTITY_ID=[$3], A_STRING=[$4], ORGANIZATION_ID0=[$0], ENTITY_ID0=[$1])\n" +
+ " PhoenixServerJoin(condition=[AND(=($3, $1), =($2, $0))], joinType=[inner])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00A123122312312", "a", "00D300000000XHP"},
+ {"00A223122312312", "a", "00D300000000XHP"},
+ {"00A323122312312", "a", "00D300000000XHP"},
+ {"00A423122312312", "a", "00D300000000XHP"}})
+ .close();
+
+ start(false).sql("select t1.entity_id, t2.a_string, t3.organization_id from aTable t1 join aTable t2 on t1.entity_id = t2.entity_id and t1.organization_id = t2.organization_id join atable t3 on t1.entity_id = t3.entity_id and t1.organization_id = t3.organization_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(ENTITY_ID=[$4], A_STRING=[$2], ORGANIZATION_ID=[$5])\n" +
+ " PhoenixServerJoin(condition=[AND(=($4, $1), =($3, $0))], joinType=[inner])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixServerJoin(condition=[AND(=($1, $3), =($0, $2))], joinType=[inner])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00A123122312312", "a", "00D300000000XHP"},
+ {"00A223122312312", "a", "00D300000000XHP"},
+ {"00A323122312312", "a", "00D300000000XHP"},
+ {"00A423122312312", "a", "00D300000000XHP"},
+ {"00B523122312312", "b", "00D300000000XHP"},
+ {"00B623122312312", "b", "00D300000000XHP"},
+ {"00B723122312312", "b", "00D300000000XHP"},
+ {"00B823122312312", "b", "00D300000000XHP"},
+ {"00C923122312312", "c", "00D300000000XHP"}})
+ .close();
+ }
+
+ @Test public void testAggregate() throws Exception {
+ start(false).sql("select count(b_string) from atable")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{}], EXPR$0=[COUNT($3)])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {9L}})
+ .close();
+
+ start(false).sql("select organization_id, count(b_string) from atable group by organization_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{0}], EXPR$1=[COUNT($3)], isOrdered=[true])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00D300000000XHP", 9L}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, count(b_string) from atable group by entity_id ,organization_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{0, 1}], EXPR$2=[COUNT($3)], isOrdered=[true])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00D300000000XHP", "00A123122312312", 1L},
+ {"00D300000000XHP", "00A223122312312", 1L},
+ {"00D300000000XHP", "00A323122312312", 1L},
+ {"00D300000000XHP", "00A423122312312", 1L},
+ {"00D300000000XHP", "00B523122312312", 1L},
+ {"00D300000000XHP", "00B623122312312", 1L},
+ {"00D300000000XHP", "00B723122312312", 1L},
+ {"00D300000000XHP", "00B823122312312", 1L},
+ {"00D300000000XHP", "00C923122312312", 1L}})
+ .close();
+
+ start(false).sql("select entity_id, count(b_string) from atable group by entity_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{1}], EXPR$1=[COUNT($3)], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00A123122312312", 1L},
+ {"00A223122312312", 1L},
+ {"00A323122312312", 1L},
+ {"00A423122312312", 1L},
+ {"00B523122312312", 1L},
+ {"00B623122312312", 1L},
+ {"00B723122312312", 1L},
+ {"00B823122312312", 1L},
+ {"00C923122312312", 1L}})
+ .close();
+
+ start(false).sql("select a_string, count(b_string) from atable group by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$1=[COUNT($3)], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"a", 4L},
+ {"b", 4L},
+ {"c", 1L}})
+ .close();
+
+ start(false).sql("select count(entity_id), a_string from atable group by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(EXPR$0=[$1], A_STRING=[$0])\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$0=[COUNT()], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {4L, "a"},
+ {4L, "b"},
+ {1L, "c"}})
+ .close();
+
+ start(false).sql("select s.name, count(\"item_id\") from " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s join " + JOIN_ITEM_TABLE_FULL_NAME + " i on s.\"supplier_id\" = i.\"supplier_id\" group by s.name")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$1=[COUNT()], isOrdered=[false])\n" +
+ " PhoenixServerJoin(condition=[=($1, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"S1", 2L},
+ {"S2", 2L},
+ {"S5", 1L},
+ {"S6", 1L}})
+ .close();
+
+ // test PhoenixOrderedAggregateRule
+ start(false).sql("select s.\"supplier_id\", count(*) from " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s join " + JOIN_ITEM_TABLE_FULL_NAME + " i on s.\"supplier_id\" = i.\"supplier_id\" group by s.\"supplier_id\"")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{0}], EXPR$1=[COUNT()], isOrdered=[true])\n" +
+ " PhoenixServerJoin(condition=[=($0, $1)], joinType=[inner])\n" +
+ " PhoenixServerProject(supplier_id=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]], scanOrder=[FORWARD])\n" +
+ " PhoenixServerProject(supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"0000000001", 2L},
+ {"0000000002", 2L},
+ {"0000000005", 1L},
+ {"0000000006", 1L}})
+ .close();
+
+ start(false).sql("select a_string, sum(a_integer) from aTable group by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$1=[SUM($4)], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"a", 10L},
+ {"b", 26L},
+ {"c", 9L}})
+ .close();
+
+ start(false).sql("select mypk0, avg(mypk1) from " + SALTED_TABLE_NAME + " group by mypk0")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(MYPK0=[$0], EXPR$1=[CAST(/($1, $2)):INTEGER NOT NULL])\n" +
+ " PhoenixServerAggregate(group=[{0}], agg#0=[$SUM0($1)], agg#1=[COUNT()], isOrdered=[true])\n" +
+ " PhoenixTableScan(table=[[phoenix, SALTED_TEST_TABLE]], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {1, 2},
+ {2, 3},
+ {3, 4}})
+ .close();
+ }
+
+ @Test public void testDistinct() throws Exception {
+ start(false).sql("select distinct a_string from aTable")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{2}], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][]{
++ .resultIs(false, new Object[][]{
+ {"a"},
+ {"b"},
+ {"c"}})
+ .close();
+ }
+
+ @Test public void testSort() throws Exception {
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by a_string, entity_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerSort(sort0=[$2], sort1=[$1], dir0=[ASC], dir1=[ASC])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00B523122312312", "b"},
+ {"00D300000000XHP", "00B623122312312", "b"},
+ {"00D300000000XHP", "00B723122312312", "b"},
+ {"00D300000000XHP", "00B823122312312", "b"},
+ {"00D300000000XHP", "00C923122312312", "c"}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by organization_id, entity_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00B523122312312", "b"},
+ {"00D300000000XHP", "00B623122312312", "b"},
+ {"00D300000000XHP", "00B723122312312", "b"},
+ {"00D300000000XHP", "00B823122312312", "b"},
+ {"00D300000000XHP", "00C923122312312", "c"}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by organization_id DESC")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[REVERSE])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00C923122312312", "c"},
+ {"00D300000000XHP", "00B823122312312", "b"},
+ {"00D300000000XHP", "00B723122312312", "b"},
+ {"00D300000000XHP", "00B623122312312", "b"},
+ {"00D300000000XHP", "00B523122312312", "b"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A123122312312", "a"}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by organization_id DESC, entity_id DESC")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[REVERSE])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00C923122312312", "c"},
+ {"00D300000000XHP", "00B823122312312", "b"},
+ {"00D300000000XHP", "00B723122312312", "b"},
+ {"00D300000000XHP", "00B623122312312", "b"},
+ {"00D300000000XHP", "00B523122312312", "b"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A123122312312", "a"}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by organization_id ASC, entity_id DESC")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[DESC])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00C923122312312", "c"},
+ {"00D300000000XHP", "00B823122312312", "b"},
+ {"00D300000000XHP", "00B723122312312", "b"},
+ {"00D300000000XHP", "00B623122312312", "b"},
+ {"00D300000000XHP", "00B523122312312", "b"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A123122312312", "a"}})
+ .close();
+
+ start(false).sql("select count(entity_id), a_string from atable group by a_string order by count(entity_id), a_string desc")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(EXPR$0=[$1], A_STRING=[$0])\n" +
+ " PhoenixCompactClientSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[DESC])\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$0=[COUNT()], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {1L, "c"},
+ {4L, "b"},
+ {4L, "a"}})
+ .close();
+
+ start(false).sql("select s.name, count(\"item_id\") from " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s join " + JOIN_ITEM_TABLE_FULL_NAME + " i on s.\"supplier_id\" = i.\"supplier_id\" group by s.name order by count(\"item_id\"), s.name desc")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixCompactClientSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[DESC])\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$1=[COUNT()], isOrdered=[false])\n" +
+ " PhoenixServerJoin(condition=[=($1, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"S6", 1L},
+ {"S5", 1L},
+ {"S2", 2L},
+ {"S1", 2L}})
+ .close();
+
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" order by item.name desc")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], supplier_id=[$3], NAME0=[$4])\n" +
+ " PhoenixServerSort(sort0=[$1], dir0=[DESC])\n" +
+ " PhoenixServerJoin(condition=[=($2, $3)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"0000000006", "T6", "0000000006", "S6"},
+ {"0000000005", "T5", "0000000005", "S5"},
+ {"0000000004", "T4", "0000000002", "S2"},
+ {"0000000003", "T3", "0000000002", "S2"},
+ {"0000000002", "T2", "0000000001", "S1"},
+ {"0000000001", "T1", "0000000001", "S1"}})
+ .close();
+ }
+
+ @Test public void testSortWithLimit() throws Exception {
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by a_string, entity_id limit 5")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[5])\n" +
+ " PhoenixServerSort(sort0=[$2], sort1=[$1], dir0=[ASC], dir1=[ASC])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by organization_id, entity_id limit 5")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[5])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by organization_id DESC limit 5")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[5])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[REVERSE])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00C923122312312", "c"},
+ {"00D300000000XHP", "00B823122312312", "b"},
+ {"00D300000000XHP", "00B723122312312", "b"},
+ {"00D300000000XHP", "00B623122312312", "b"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by organization_id DESC, entity_id DESC limit 5")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[5])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[REVERSE])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00C923122312312", "c"},
+ {"00D300000000XHP", "00B823122312312", "b"},
+ {"00D300000000XHP", "00B723122312312", "b"},
+ {"00D300000000XHP", "00B623122312312", "b"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start(false).sql("select organization_id, entity_id, a_string from aTable order by organization_id ASC, entity_id DESC limit 5")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[5])\n" +
+ " PhoenixServerSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[DESC])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00D300000000XHP", "00C923122312312", "c"},
+ {"00D300000000XHP", "00B823122312312", "b"},
+ {"00D300000000XHP", "00B723122312312", "b"},
+ {"00D300000000XHP", "00B623122312312", "b"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start(false).sql("select count(entity_id), a_string from atable group by a_string order by count(entity_id), a_string desc limit 2")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(EXPR$0=[$1], A_STRING=[$0])\n" +
+ " PhoenixLimit(fetch=[2])\n" +
+ " PhoenixCompactClientSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[DESC])\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$0=[COUNT()], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {1L, "c"},
+ {4L, "b"}})
+ .close();
+
+ start(false).sql("select s.name, count(\"item_id\") from " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s join " + JOIN_ITEM_TABLE_FULL_NAME + " i on s.\"supplier_id\" = i.\"supplier_id\" group by s.name order by count(\"item_id\"), s.name desc limit 3")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[3])\n" +
+ " PhoenixCompactClientSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[DESC])\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$1=[COUNT()], isOrdered=[false])\n" +
+ " PhoenixServerJoin(condition=[=($1, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"S6", 1L},
+ {"S5", 1L},
+ {"S2", 2L}})
+ .close();
+
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" order by item.name desc limit 3")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], supplier_id=[$3], NAME0=[$4])\n" +
+ " PhoenixLimit(fetch=[3])\n" +
+ " PhoenixServerSort(sort0=[$1], dir0=[DESC])\n" +
+ " PhoenixServerJoin(condition=[=($2, $3)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"0000000006", "T6", "0000000006", "S6"},
+ {"0000000005", "T5", "0000000005", "S5"},
+ {"0000000004", "T4", "0000000002", "S2"}})
+ .close();
+ }
+
+ @Test public void testLimit() throws Exception {
+ start(false).sql("select organization_id, entity_id, a_string from aTable limit 5")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[5])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start(false).sql("select count(entity_id), a_string from atable group by a_string limit 2")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(EXPR$0=[$1], A_STRING=[$0])\n" +
+ " PhoenixLimit(fetch=[2])\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$0=[COUNT()], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {4L, "a"},
+ {4L, "b"}})
+ .close();
+
+ start(false).sql("select s.name, count(\"item_id\") from " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s join " + JOIN_ITEM_TABLE_FULL_NAME + " i on s.\"supplier_id\" = i.\"supplier_id\" group by s.name limit 3")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[3])\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$1=[COUNT()], isOrdered=[false])\n" +
+ " PhoenixServerJoin(condition=[=($1, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"S1", 2L},
+ {"S2", 2L},
+ {"S5", 1L}})
+ .close();
+
+ start(false).sql("SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" limit 3")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], supplier_id=[$3], NAME0=[$4])\n" +
+ " PhoenixLimit(fetch=[3])\n" +
+ " PhoenixServerJoin(condition=[=($2, $3)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"0000000001", "T1", "0000000001", "S1"},
+ {"0000000002", "T2", "0000000001", "S1"},
+ {"0000000003", "T3", "0000000002", "S2"}})
+ .close();
+
+ start(false).sql("SELECT x from (values (1, 2), (2, 4), (3, 6)) as t(x, y) limit 2")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(X=[$0])\n" +
+ " PhoenixLimit(fetch=[2])\n" +
+ " PhoenixValues(tuples=[[{ 1, 2 }, { 2, 4 }, { 3, 6 }]])\n")
- .resultIs(new Object[][] {{1}, {2}})
++ .resultIs(false, new Object[][] {{1}, {2}})
+ .close();
+ }
+
+ @Ignore // CALCITE-1045
+ @Test public void testScalarSubquery() throws Exception {
+ start(false).sql("select \"item_id\", name, (select max(quantity) sq \n"
+ + "from " + JOIN_ORDER_TABLE_FULL_NAME + " o where o.\"item_id\" = i.\"item_id\")\n"
+ + "from " + JOIN_ITEM_TABLE_FULL_NAME + " i")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], EXPR$2=[$8])\n" +
+ " PhoenixServerJoin(condition=[=($0, $7)], joinType=[left], isSingleValueRhs=[true])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerAggregate(group=[{0}], SQ=[MAX($5)], isOrdered=[true])\n" +
+ " PhoenixServerJoin(condition=[=($3, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], scanOrder=[FORWARD])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ new Object[] {"0000000001", "T1", 1000},
+ new Object[] {"0000000002", "T2", 3000},
+ new Object[] {"0000000003", "T3", 5000},
+ new Object[] {"0000000004", "T4", null},
+ new Object[] {"0000000005", "T5", null},
+ new Object[] {"0000000006", "T6", 4000},
+ new Object[] {"invalid001", "INVALID-1", null}})
+ .close();
+
+ start(false).sql("select \"item_id\", name, (select quantity sq \n"
+ + "from " + JOIN_ORDER_TABLE_FULL_NAME + " o where o.\"item_id\" = i.\"item_id\")\n"
+ + "from " + JOIN_ITEM_TABLE_FULL_NAME + " i where \"item_id\" < '0000000006'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1], EXPR$2=[$8])\n" +
+ " PhoenixServerJoin(condition=[=($0, $7)], joinType=[left], isSingleValueRhs=[true])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], filter=[<($0, '0000000006')])\n" +
+ " PhoenixClientProject(item_id0=[$7], SQ=[$4])\n" +
+ " PhoenixServerJoin(condition=[=($2, $7)], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixServerProject(item_id=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], filter=[<($0, '0000000006')])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ new Object[] {"0000000001", "T1", 1000},
+ new Object[] {"0000000002", "T2", 3000},
+ new Object[] {"0000000003", "T3", 5000},
+ new Object[] {"0000000004", "T4", null},
+ new Object[] {"0000000005", "T5", null}})
+ .close();;
+ }
+
+ @Test public void testValues() throws Exception {
+ start(false).sql("select p0+p1 from (values (2, 1)) as t(p0, p1)")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(EXPR$0=[+($0, $1)])\n" +
+ " PhoenixValues(tuples=[[{ 2, 1 }]])\n")
+ .close();
+ start(false).sql("select count(p0), max(p1) from (values (2, 1), (3, 4), (5, 2)) as t(p0, p1)")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientAggregate(group=[{}], EXPR$0=[COUNT()], EXPR$1=[MAX($1)])\n" +
+ " PhoenixValues(tuples=[[{ 2, 1 }, { 3, 4 }, { 5, 2 }]])\n")
- .resultIs(new Object[][] {{3L, 4}})
++ .resultIs(false, new Object[][] {{3L, 4}})
+ .close();
+ }
+
+ @Test public void testUnion() throws Exception {
+ start(false).sql("select entity_id from atable where a_string = 'a' union all select entity_id from atable where a_string = 'b'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixUnion(all=[true])\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'b')])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00A123122312312"},
+ {"00A223122312312"},
+ {"00A323122312312"},
+ {"00A423122312312"},
+ {"00B523122312312"},
+ {"00B623122312312"},
+ {"00B723122312312"},
+ {"00B823122312312"}})
+ .close();
+
+ start(false).sql("select entity_id, a_string from atable where a_string = 'a' union all select entity_id, a_string from atable where a_string = 'c' order by entity_id desc limit 3")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[3])\n" +
+ " PhoenixMergeSortUnion(all=[true])\n" +
+ " PhoenixLimit(fetch=[3])\n" +
+ " PhoenixServerSort(sort0=[$0], dir0=[DESC])\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n" +
+ " PhoenixLimit(fetch=[3])\n" +
+ " PhoenixServerSort(sort0=[$0], dir0=[DESC])\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'c')])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00C923122312312", "c"},
+ {"00A423122312312", "a"},
+ {"00A323122312312", "a"}})
+ .close();
+
+ start(false).sql("select entity_id, a_string from atable where a_string = 'a' union all select entity_id, a_string from atable where a_string = 'c' order by entity_id desc")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixMergeSortUnion(all=[true])\n" +
+ " PhoenixServerSort(sort0=[$0], dir0=[DESC])\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n" +
+ " PhoenixServerSort(sort0=[$0], dir0=[DESC])\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'c')])\n")
- .resultIs(new Object[][] {
++ .resultIs(true, new Object[][] {
+ {"00C923122312312", "c"},
+ {"00A423122312312", "a"},
+ {"00A323122312312", "a"},
+ {"00A223122312312", "a"},
+ {"00A123122312312", "a"}})
+ .close();
+ }
+
+ @Test public void testUnnest() throws Exception {
+ start(false).sql("SELECT t.s FROM UNNEST((SELECT scores FROM " + SCORES_TABLE_NAME + ")) AS t(s)")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixUncollect\n" +
+ " PhoenixServerProject(EXPR$0=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, SCORES]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {85},
+ {80},
+ {82},
+ {87},
+ {88},
+ {80}})
+ .close();
+ start(false).sql("SELECT s.student_id, t.score FROM " + SCORES_TABLE_NAME + " s, UNNEST((SELECT scores FROM " + SCORES_TABLE_NAME + " s2 where s.student_id = s2.student_id)) AS t(score)")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(STUDENT_ID=[$0], SCORE=[$3])\n" +
+ " PhoenixCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{0}])\n" +
+ " PhoenixTableScan(table=[[phoenix, SCORES]])\n" +
+ " PhoenixUncollect\n" +
+ " PhoenixServerProject(EXPR$0=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, SCORES]], filter=[=($cor0.STUDENT_ID, $0)])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {1, 85},
+ {1, 80},
+ {1, 82},
+ {3, 87},
+ {3, 88},
+ {3, 80}})
+ .close();
+ }
+
+ @Test public void testCorrelateAndDecorrelation() throws Exception {
+ Properties correlProps = getConnectionProps(false);
+ correlProps.setProperty("forceDecorrelate", Boolean.FALSE.toString());
+ Properties decorrelProps = getConnectionProps(false);
+ decorrelProps.setProperty("forceDecorrelate", Boolean.TRUE.toString());
+
+ String q1 = "select \"order_id\", quantity from " + JOIN_ORDER_TABLE_FULL_NAME + " o where quantity = (select max(quantity) from " + JOIN_ORDER_TABLE_FULL_NAME + " o2 where o.\"item_id\" = o2.\"item_id\")";
+ Object[][] r1 = new Object[][] {
+ {"000000000000001", 1000},
+ {"000000000000003", 3000},
+ {"000000000000004", 4000},
+ {"000000000000005", 5000}};
+ String p1Correlate =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(order_id=[$0], QUANTITY=[$2])\n" +
+ " PhoenixFilter(condition=[=($2, $3)])\n" +
+ " PhoenixCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{1}])\n" +
+ " PhoenixServerProject(order_id=[$0], item_id=[$2], QUANTITY=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixServerAggregate(group=[{}], EXPR$0=[MAX($4)])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]], filter=[=($cor0.item_id, $2)])\n";
+ String p1Decorrelated =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(order_id=[$0], QUANTITY=[$2])\n" +
+ " PhoenixServerJoin(condition=[AND(=($1, $3), =($2, $4))], joinType=[inner])\n" +
+ " PhoenixServerProject(order_id=[$0], item_id=[$2], QUANTITY=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$0=[MAX($1)], isOrdered=[false])\n" +
+ " PhoenixServerJoin(condition=[=($2, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$2], QUANTITY=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixServerAggregate(group=[{2}], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n";
- start(correlProps).sql(q1).explainIs(p1Correlate).resultIs(r1).close();
- start(decorrelProps).sql(q1).explainIs(p1Decorrelated).resultIs(r1).close();
++ start(correlProps).sql(q1).explainIs(p1Correlate).resultIs(false, r1).close();
++ start(decorrelProps).sql(q1).explainIs(p1Decorrelated).resultIs(false, r1).close();
+
+ String q2 = "select name from " + JOIN_ITEM_TABLE_FULL_NAME + " i where price = (select max(price) from " + JOIN_ITEM_TABLE_FULL_NAME + " i2 where i.\"item_id\" = i2.\"item_id\" and i.name = i2.name and i2.\"item_id\" <> 'invalid001')";
+ Object[][] r2 = new Object[][]{
+ {"T1"},
+ {"T2"},
+ {"T3"},
+ {"T4"},
+ {"T5"},
+ {"T6"}};
+ String p2Correlate =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(NAME=[$1])\n" +
+ " PhoenixFilter(condition=[=($2, $3)])\n" +
+ " PhoenixCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{0, 1}])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], PRICE=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerAggregate(group=[{}], EXPR$0=[MAX($2)])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], filter=[AND(=($cor0.item_id, $0), =($cor0.NAME, $1), <>($0, 'invalid001'))])\n";
+ String p2Decorrelated =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(NAME=[$1])\n" +
+ " PhoenixServerJoin(condition=[AND(=($0, $3), =($1, $4), =($2, $5))], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], PRICE=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerAggregate(group=[{0, 1}], EXPR$0=[MAX($4)], isOrdered=[false])\n" +
+ " PhoenixServerJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], PRICE=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], filter=[<>($0, 'invalid001')])\n";
- start(correlProps).sql(q2).explainIs(p2Correlate).resultIs(r2).close();
- start(decorrelProps).sql(q2).explainIs(p2Decorrelated).resultIs(r2).close();
++ start(correlProps).sql(q2).explainIs(p2Correlate).resultIs(false, r2).close();
++ start(decorrelProps).sql(q2).explainIs(p2Decorrelated).resultIs(false, r2).close();
+
+ // Test PhoenixClientSemiJoin
+ String q3 = "select \"item_id\", name from " + JOIN_ITEM_TABLE_FULL_NAME + " i where exists (select 1 from " + JOIN_ORDER_TABLE_FULL_NAME + " o where i.\"item_id\" = o.\"item_id\")";
+ Object[][] r3 = new Object[][] {
+ {"0000000001", "T1"},
+ {"0000000002", "T2"},
+ {"0000000003", "T3"},
+ {"0000000006", "T6"}};
+ String p3Correlate =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(item_id=[$0], NAME=[$1])\n" +
+ " PhoenixCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{0}])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerAggregate(group=[{0}], isOrdered=[false])\n" +
+ " PhoenixServerProject(i=[true])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]], filter=[=($cor0.item_id, $2)])\n";
+ String p3Decorrelated =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixClientSemiJoin(condition=[=($0, $2)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], scanOrder=[FORWARD])\n" +
+ " PhoenixServerJoin(condition=[=($0, $1)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], scanOrder=[FORWARD])\n" +
+ " PhoenixServerProject(item_id=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n";
- start(correlProps).sql(q3).explainIs(p3Correlate).resultIs(r3).close();
- start(decorrelProps).sql(q3).explainIs(p3Decorrelated).resultIs(r3).close();
++ start(correlProps).sql(q3).explainIs(p3Correlate).resultIs(false, r3).close();
++ start(decorrelProps).sql(q3).explainIs(p3Decorrelated).resultIs(false, r3).close();
+
+ String q4 = "select \"item_id\", name from " + JOIN_ITEM_TABLE_FULL_NAME + " i where \"item_id\" in (select \"item_id\" from " + JOIN_ORDER_TABLE_FULL_NAME + ")";
+ Object[][] r4 = new Object[][] {
+ {"0000000001", "T1"},
+ {"0000000002", "T2"},
+ {"0000000003", "T3"},
+ {"0000000006", "T6"}};
+ String p4Decorrelated =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixServerSemiJoin(condition=[=($0, $4)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n";
- start(decorrelProps).sql(q4).explainIs(p4Decorrelated).resultIs(r4).close();
++ start(decorrelProps).sql(q4).explainIs(p4Decorrelated).resultIs(false, r4).close();
+
+ // CALCITE-864
+ //String q5 = "select \"order_id\" from " + JOIN_ITEM_TABLE_FULL_NAME + " i JOIN " + JOIN_ORDER_TABLE_FULL_NAME + " o on o.\"item_id\" = i.\"item_id\" where quantity = (select max(quantity) from " + JOIN_ORDER_TABLE_FULL_NAME + " o2 JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " i2 on o2.\"item_id\" = i2.\"item_id\" where i.\"supplier_id\" = i2.\"supplier_id\")";
+ //Object [][] r5 = new Object[][] {
+ // {"000000000000003"},
+ // {"000000000000005"},
+ // {"000000000000004"}};
+ //String p5Correlate =
+ // "PhoenixToEnumerableConverter\n" +
+ // " PhoenixClientProject(order_id=[$7])\n" +
+ // " PhoenixFilter(condition=[=($11, $14)])\n" +
+ // " PhoenixCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{5}])\n" +
+ // " PhoenixServerJoin(condition=[=($9, $0)], joinType=[inner])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ // " PhoenixServerAggregate(group=[{}], EXPR$0=[MAX($4)])\n" +
+ // " PhoenixServerJoin(condition=[=($2, $7)], joinType=[inner])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], filter=[=($cor0.supplier_id, $5)])\n";
+ //String p5Decorrelated =
+ // "PhoenixToEnumerableConverter\n" +
+ // " PhoenixClientProject(order_id=[$7])\n" +
+ // " PhoenixServerJoin(condition=[AND(=($9, $0), =($5, $14))], joinType=[inner])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ // " PhoenixServerJoin(condition=[=($4, $8)], joinType=[inner])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ // " PhoenixServerAggregate(group=[{14}], EXPR$0=[MAX($4)], isOrdered=[false])\n" +
+ // " PhoenixServerJoin(condition=[=($2, $7)], joinType=[inner])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ // " PhoenixServerJoin(condition=[=($7, $5)], joinType=[inner])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ // " PhoenixServerAggregate(group=[{5}], isOrdered=[false])\n" +
+ // " PhoenixServerJoin(condition=[=($9, $0)], joinType=[inner])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ // " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n";
- //start(correlProps).sql(q5).explainIs(p5Correlate).resultIs(r5).close();
- //start(decorrelProps).sql(q5).explainIs(p5Decorrelated).resultIs(r5).close();
++ //start(correlProps).sql(q5).explainIs(p5Correlate).resultIs(false, r5).close();
++ //start(decorrelProps).sql(q5).explainIs(p5Decorrelated).resultIs(false, r5).close();
+
+ String q6 = "select organization_id, entity_id, a_integer from v v1 where a_integer = (select min(a_integer) from v v2 where v1.organization_id = v2.organization_id and v1.b_string = v2.b_string)";
+ Object[][] r6 = new Object[][] {
+ {"00D300000000XHP", "00A123122312312", 1},
+ {"00D300000000XHP", "00A223122312312", 2},
+ {"00D300000000XHP", "00A323122312312", 3}};
+ String p6Correlate =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_INTEGER=[$3])\n" +
+ " PhoenixFilter(condition=[=($3, $4)])\n" +
+ " PhoenixCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{0, 2}])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], B_STRING=[$3], A_INTEGER=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n" +
+ " PhoenixServerAggregate(group=[{}], EXPR$0=[MIN($4)])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[AND(=($2, 'a'), =($cor0.ORGANIZATION_ID, $0), =($cor0.B_STRING, $3))])\n";
+ String p6Decorrelated =
+ "PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_INTEGER=[$3])\n" +
+ " PhoenixServerJoin(condition=[AND(=($0, $4), =($2, $5), =($3, $6))], joinType=[inner])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], B_STRING=[$3], A_INTEGER=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n" +
+ " PhoenixServerAggregate(group=[{3, 4}], EXPR$0=[MIN($2)], isOrdered=[false])\n" +
+ " PhoenixServerJoin(condition=[AND(=($3, $0), =($4, $1))], joinType=[inner])\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], B_STRING=[$3], A_INTEGER=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n" +
+ " PhoenixServerAggregate(group=[{0, 3}], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n";
- start(correlProps).sql(q6).explainIs(p6Correlate).resultIs(r6).close();
- start(decorrelProps).sql(q6).explainIs(p6Decorrelated).resultIs(r6).close();
++ start(correlProps).sql(q6).explainIs(p6Correlate).resultIs(false, r6).close();
++ start(decorrelProps).sql(q6).explainIs(p6Decorrelated).resultIs(false, r6).close();
+ }
+
+ @Test public void testInValueList() throws Exception {
+ start(false).sql("select entity_id from aTable where organization_id = '00D300000000XHP' and entity_id in ('00A123122312312', '00A223122312312', '00B523122312312', '00B623122312312', '00C923122312312')")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[AND(=($0, '00D300000000XHP'), OR(=($1, '00A123122312312'), =($1, '00A223122312312'), =($1, '00B523122312312'), =($1, '00B623122312312'), =($1, '00C923122312312')))])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00A123122312312"},
+ {"00A223122312312"},
+ {"00B523122312312"},
+ {"00B623122312312"},
+ {"00C923122312312"}})
+ .close();
+ }
+
+ @Test public void testSelectFromView() throws Exception {
+ start(false).sql("select * from v")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"}})
+ .close();
+ }
+
+ @Test public void testSequence() throws Exception {
+ start(false).sql("select NEXT VALUE FOR seq0, c0 from (values (1), (1)) as t(c0)")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(EXPR$0=[NEXT_VALUE('\"SEQ0\"')], C0=[$0])\n" +
+ " PhoenixValues(tuples=[[{ 1 }, { 1 }]])\n")
- .resultIs(new Object[][]{
++ .resultIs(false, new Object[][]{
+ {1L, 1},
+ {2L, 1}})
+ .close();
+
+ start(false).sql("select NEXT VALUE FOR my.seq1, entity_id from aTable where a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(EXPR$0=[NEXT_VALUE('\"MY\".\"SEQ1\"')], ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
- .resultIs(new Object[][]{
++ .resultIs(false, new Object[][]{
+ {2L, "00A123122312312"},
+ {4L, "00A223122312312"},
+ {6L, "00A323122312312"},
+ {8L, "00A423122312312"}})
+ .close();
+
+ start(false).sql("SELECT NEXT VALUE FOR seq0, item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(EXPR$0=[NEXT_VALUE('\"SEQ0\"')], item_id=[$0], NAME=[$1], supplier_id=[$3], NAME0=[$4])\n" +
+ " PhoenixServerJoin(condition=[=($2, $3)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
- .resultIs(new Object[][] {
++ .resultIs(false, new Object[][] {
+ {3L, "0000000001", "T1", "0000000001", "S1"},
+ {4L, "0000000002", "T2", "0000000001", "S1"},
+ {5L, "0000000003", "T3", "0000000002", "S2"},
+ {6L, "0000000004", "T4", "0000000002", "S2"},
+ {7L, "0000000005", "T5", "0000000005", "S5"},
+ {8L, "0000000006", "T6", "0000000006", "S6"}})
+ .close();
+ }
+
+ /** Tests a simple command that is defined in Phoenix's extended SQL parser.
+ * @throws Exception */
+ @Ignore
+ @Test public void testCommit() throws Exception {
+ start(false).sql("commit").execute();
+ }
+
+ @Test public void testCreateView() throws Exception {
+ start(false).sql("create view v as select * from (values (1, 'a'), (2, 'b')) as t(x, y)").execute();
+ }
+
+ @Ignore // CALCITE-1045
+ @Test public void testConnectJoinHsqldb() throws Exception {
+ final Start start = new Start(getConnectionProps(false)) {
+ @Override
+ Connection createConnection() throws Exception {
+ return connectWithHsqldbUsingModel(props);
+ }
+ };
+ start.sql("select the_year, quantity as q, (select count(*) cnt \n"
+ + "from \"foodmart\".\"time_by_day\" t where t.\"the_year\" = c.the_year)\n"
+ + "from " + JOIN_ORDER_TABLE_FULL_NAME + " c")
+ .explainIs("EnumerableCalc(expr#0..8=[{inputs}], THE_YEAR=[$t6], Q=[$t4], EXPR$2=[$t8])\n" +
+ " EnumerableJoin(condition=[=($6, $7)], joinType=[left])\n" +
+ " PhoenixToEnumerableConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " EnumerableAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])\n" +
+ " EnumerableAggregate(group=[{0}], CNT=[COUNT()])\n" +
+ " EnumerableJoin(condition=[=($0, $11)], joinType=[inner])\n" +
+ " PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{6}], isOrdered=[false])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " JdbcToEnumerableConverter\n" +
+ " JdbcProject(time_id=[$0], th
<TRUNCATED>