You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2015/08/20 22:02:45 UTC
[2/2] phoenix git commit: Rename integration test with IT suffix
Rename integration test with IT suffix
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/108da2cd
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/108da2cd
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/108da2cd
Branch: refs/heads/calcite
Commit: 108da2cdd2c3a61975ecb04b1ed2a3a8edd17320
Parents: 65ec1bf
Author: James Taylor <jt...@salesforce.com>
Authored: Thu Aug 20 13:02:33 2015 -0700
Committer: James Taylor <jt...@salesforce.com>
Committed: Thu Aug 20 13:02:33 2015 -0700
----------------------------------------------------------------------
.../org/apache/phoenix/calcite/CalciteIT.java | 1054 ++++++++++++++++++
.../org/apache/phoenix/calcite/CalciteTest.java | 1054 ------------------
2 files changed, 1054 insertions(+), 1054 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/108da2cd/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java
new file mode 100644
index 0000000..e7f4561
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIT.java
@@ -0,0 +1,1054 @@
+/*
+ * 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 com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import org.apache.calcite.config.CalciteConnectionProperty;
+import org.apache.calcite.jdbc.CalciteConnection;
+import org.apache.calcite.schema.SchemaPlus;
+import org.apache.phoenix.end2end.BaseClientManagedTimeIT;
+import org.apache.phoenix.schema.TableAlreadyExistsException;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Before;
+import org.junit.Ignore;
+import org.junit.Test;
+
+import java.io.File;
+import java.io.FileWriter;
+import java.io.PrintWriter;
+import java.sql.*;
+import java.util.List;
+import java.util.Map;
+import java.util.Properties;
+
+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 static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.*;
+
+/**
+ * Integration test for queries powered by Calcite.
+ */
+public class CalciteIT extends BaseClientManagedTimeIT {
+ public static final String ATABLE_NAME = "ATABLE";
+
+ public static Start start() {
+ return new Start(getConnectionProps(false), false);
+ }
+
+ public static Start start(Properties props, boolean connectUsingModel) {
+ return new Start(props, connectUsingModel);
+ }
+
+ public static class Start {
+ protected final Properties props;
+ protected final boolean connectUsingModel;
+ private Connection connection;
+
+ Start(Properties props, boolean connectUsingModel) {
+ this.props = props;
+ this.connectUsingModel = connectUsingModel;
+ }
+
+ Connection createConnection() throws Exception {
+ return connectUsingModel ?
+ CalciteIT.connectUsingModel(props)
+ : CalciteIT.createConnection(props);
+ }
+
+ public Sql sql(String sql) {
+ return new Sql(this, sql);
+ }
+
+ public Connection getConnection() {
+ if (connection == null) {
+ try {
+ connection = createConnection();
+ } catch (Exception e) {
+ throw new RuntimeException(e);
+ }
+ }
+ return connection;
+ }
+
+ public void close() {
+ if (connection != null) {
+ try {
+ connection.close();
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+ }
+ }
+
+ /** Fluid class for a test that has specified a SQL query. */
+ static class Sql {
+ private final Start start;
+ private final String sql;
+
+ public Sql(Start start, String sql) {
+ this.start = start;
+ this.sql = sql;
+ }
+
+ public static List<Object[]> getResult(ResultSet resultSet) throws SQLException {
+ final List<Object[]> list = Lists.newArrayList();
+ populateResult(resultSet, list);
+ return list;
+ }
+
+ private static void populateResult(ResultSet resultSet, List<Object[]> list) throws SQLException {
+ final int columnCount = resultSet.getMetaData().getColumnCount();
+ while (resultSet.next()) {
+ Object[] row = new Object[columnCount];
+ for (int i = 0; i < columnCount; i++) {
+ row[i] = resultSet.getObject(i + 1);
+ }
+ list.add(row);
+ }
+ }
+
+ public Sql explainIs(String expected) {
+ final List<Object[]> list = getResult("explain plan for " + sql);
+ if (list.size() != 1) {
+ fail("explain should return 1 row, got " + list.size());
+ }
+ String explain = (String) (list.get(0)[0]);
+ assertEquals(explain, expected);
+ return this;
+ }
+
+
+ public boolean execute() {
+ try {
+ final Statement statement = start.getConnection().createStatement();
+ final boolean execute = statement.execute(sql);
+ statement.close();
+ return execute;
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ public List<Object[]> getResult(String sql) {
+ try {
+ final Statement statement = start.getConnection().createStatement();
+ final ResultSet resultSet = statement.executeQuery(sql);
+ List<Object[]> list = getResult(resultSet);
+ resultSet.close();
+ statement.close();
+ return list;
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ public void close() {
+ start.close();
+ }
+
+ public Sql resultIs(Object[]... expected) {
+ try {
+ final Statement statement = start.getConnection().createStatement();
+ final ResultSet resultSet = statement.executeQuery(sql);
+ for (int i = 0; i < expected.length; i++) {
+ assertTrue(resultSet.next());
+ Object[] row = expected[i];
+ for (int j = 0; j < row.length; j++) {
+ assertEquals(row[j], resultSet.getObject(j + 1));
+ }
+ }
+ assertFalse(resultSet.next());
+ resultSet.close();
+ statement.close();
+ return this;
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+ }
+
+ private static final String FOODMART_SCHEMA = " {\n"
+ + " type: 'jdbc',\n"
+ + " name: 'foodmart',\n"
+ + " jdbcDriver: 'org.hsqldb.jdbcDriver',\n"
+ + " jdbcUser: 'FOODMART',\n"
+ + " jdbcPassword: 'FOODMART',\n"
+ + " jdbcUrl: 'jdbc:hsqldb:res:foodmart',\n"
+ + " jdbcCatalog: null,\n"
+ + " jdbcSchema: 'foodmart'\n"
+ + " }";
+
+ private static final String PHOENIX_SCHEMA = " {\n"
+ + " name: 'phoenix',\n"
+ + " type: 'custom',\n"
+ + " factory: 'org.apache.phoenix.calcite.PhoenixSchema$Factory',\n"
+ + " operand: {\n"
+ + " url: \"" + getUrl() + "\"\n"
+ + " }\n"
+ + " }";
+
+ private static Connection connectUsingModel(Properties props) throws Exception {
+ final File file = File.createTempFile("model", ".json");
+ final String url = getUrl();
+ final PrintWriter pw = new PrintWriter(new FileWriter(file));
+ pw.print(
+ "{\n"
+ + " version: '1.0',\n"
+ + " defaultSchema: 'HR',\n"
+ + " schemas: [\n"
+ + " {\n"
+ + " name: 'HR',\n"
+ + " type: 'custom',\n"
+ + " factory: 'org.apache.phoenix.calcite.PhoenixSchema$Factory',\n"
+ + " operand: {\n"
+ + " url: \"" + url + "\",\n"
+ + " user: \"scott\",\n"
+ + " password: \"tiger\"\n"
+ + " }\n"
+ + " }\n"
+ + " ]\n"
+ + "}\n");
+ pw.close();
+ final Connection connection =
+ DriverManager.getConnection("jdbc:phoenixcalcite:model=" + file.getAbsolutePath(), props == null ? new Properties() : props);
+ return connection;
+ }
+
+ private static Connection createConnection(Properties props) throws SQLException {
+ final Connection connection = DriverManager.getConnection(
+ "jdbc:phoenixcalcite:", props);
+ final CalciteConnection calciteConnection =
+ connection.unwrap(CalciteConnection.class);
+ final String url = getUrl();
+ Map<String, Object> operand = Maps.newHashMap();
+ operand.put("url", url);
+ SchemaPlus rootSchema = calciteConnection.getRootSchema();
+ rootSchema.add("phoenix",
+ PhoenixSchema.FACTORY.create(rootSchema, "phoenix", operand));
+ calciteConnection.setSchema("phoenix");
+ return connection;
+ }
+
+ private static Connection connectWithHsqldbUsingModel() throws Exception {
+ final File file = File.createTempFile("model", ".json");
+ final PrintWriter pw = new PrintWriter(new FileWriter(file));
+ pw.print(
+ "{\n"
+ + " version: '1.0',\n"
+ + " defaultSchema: 'phoenix',\n"
+ + " schemas: [\n"
+ + PHOENIX_SCHEMA + ",\n"
+ + FOODMART_SCHEMA + "\n"
+ + " ]\n"
+ + "}\n");
+ pw.close();
+ final Connection connection =
+ DriverManager.getConnection("jdbc:phoenixcalcite:model=" + file.getAbsolutePath());
+ return connection;
+ }
+
+ private static Properties getConnectionProps(boolean enableMaterialization) {
+ Properties props = new Properties();
+ props.setProperty(
+ CalciteConnectionProperty.MATERIALIZATIONS_ENABLED.camelName(),
+ Boolean.toString(enableMaterialization));
+ props.setProperty(
+ CalciteConnectionProperty.CREATE_MATERIALIZATIONS.camelName(),
+ Boolean.toString(false));
+ return props;
+ }
+
+ @Before
+ public void initTable() throws Exception {
+ final String url = getUrl();
+ ensureTableCreated(url, ATABLE_NAME);
+ initATableValues(getOrganizationId(), null, url);
+ initJoinTableValues(url, null, null);
+ createIndices(
+ "CREATE INDEX IDX1 ON aTable (a_string) INCLUDE (b_string, x_integer)",
+ "CREATE INDEX IDX2 ON aTable (b_string) INCLUDE (a_string, y_integer)",
+ "CREATE INDEX IDX_FULL ON aTable (b_string) INCLUDE (a_string, a_integer, a_date, a_time, a_timestamp, x_decimal, x_long, x_integer, y_integer, a_byte, a_short, a_float, a_double, a_unsigned_float, a_unsigned_double)");
+ final Connection connection = DriverManager.getConnection(url);
+ 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 IDX1");
+ connection.createStatement().execute("UPDATE STATISTICS IDX2");
+ connection.createStatement().execute("UPDATE STATISTICS IDX_FULL");
+ connection.close();
+ }
+
+ protected void createIndices(String... indexDDL) throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ for (String ddl : indexDDL) {
+ try {
+ conn.createStatement().execute(ddl);
+ } catch (TableAlreadyExistsException e) {
+ }
+ }
+ conn.close();
+ }
+
+ @Test public void testTableScan() throws Exception {
+ start().sql("select * from aTable where a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
+ .resultIs(new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"}})
+ .close();
+ }
+
+ @Test public void testProject() throws Exception {
+ start().sql("select entity_id, a_string, organization_id from aTable where a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1], A_STRING=[$2], ORGANIZATION_ID=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
+ .resultIs(new Object[][] {
+ {"00A123122312312", "a", "00D300000000XHP"},
+ {"00A223122312312", "a", "00D300000000XHP"},
+ {"00A323122312312", "a", "00D300000000XHP"},
+ {"00A423122312312", "a", "00D300000000XHP"}})
+ .close();
+ }
+
+ @Test public void testJoin() throws Exception {
+ start().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" +
+ " PhoenixToClientConverter\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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
+ .resultIs(new Object[][] {
+ {"00A123122312312", "a", "00D300000000XHP"},
+ {"00A223122312312", "a", "00D300000000XHP"},
+ {"00A323122312312", "a", "00D300000000XHP"},
+ {"00A423122312312", "a", "00D300000000XHP"}})
+ .close();
+
+ start().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[=($2, $3)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(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().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[=($5, $7)], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1], PHONE=[$2], ADDRESS=[$3], LOC_ID=[$4], $f5=[CAST($1):VARCHAR(2) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\" NOT NULL])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]], filter=[=(CAST($1):VARCHAR(2) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\" NOT NULL, 'S5')])\n")
+ .resultIs(new Object[][] {
+ {"0000000005", "T5", 500, 8, 15, "0000000005", "Item T5", "0000000005", "S5", "888-888-5555", "505 YYY Street", "10005"}})
+ .close();
+
+ start().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" +
+ " PhoenixToClientConverter\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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(order_id=[$0], item_id=[$2], QUANTITY=[$4], $f7=[CAST($3):DECIMAL(17, 6) NOT NULL])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]], filter=[<($4, 5000)])\n")
+ .resultIs(new Object[][] {
+ {"000000000000004", "T6", 600, 15, 4000}})
+ .close();
+ }
+
+ @Test public void testRightOuterJoin() throws Exception {
+ start().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[=($4, $0)], joinType=[left])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n")
+ .resultIs(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().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(new Object[][] {
+ {null, null, "0000000003", "S3"},
+ {null, null, "0000000004", "S4"},
+ {"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}})
+ .close();
+
+ start().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"
+ +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1])\n"
+ +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n"
+ +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(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().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[=($4, $0)], joinType=[inner])\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n" +
+ " PhoenixToClientConverter\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().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])\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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .close();
+ }
+
+ @Test public void testMultiJoin() throws Exception {
+ start().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=[$19], A_STRING=[$38], ORGANIZATION_ID=[$0])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[AND(=($19, $1), =($18, $0))], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixClientProject(ORGANIZATION_ID=[$18], ENTITY_ID=[$19], A_STRING=[$20], B_STRING=[$21], A_INTEGER=[$22], A_DATE=[$23], A_TIME=[$24], A_TIMESTAMP=[$25], X_DECIMAL=[$26], X_LONG=[$27], X_INTEGER=[$28], Y_INTEGER=[$29], A_BYTE=[$30], A_SHORT=[$31], A_FLOAT=[$32], A_DOUBLE=[$33], A_UNSIGNED_FLOAT=[$34], A_UNSIGNED_DOUBLE=[$35], ORGANIZATION_ID0=[$0], ENTITY_ID0=[$1], A_STRING0=[$2], B_STRING0=[$3], A_INTEGER0=[$4], A_DATE0=[$5], A_TIME0=[$6], A_TIMESTAMP0=[$7], X_DECIMAL0=[$8], X_LONG0=[$9], X_INTEGER0=[$10], Y_INTEGER0=[$11], A_BYTE0=[$12], A_SHORT0=[$13], A_FLOAT0=[$14], A_DOUBLE0=[$15], A_UNSIGNED_FLOAT0=[$16], A_UNSIGNED_DOUBLE0=[$17])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[AND(=($19, $1), =($18, $0))], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n")
+ .resultIs(new Object[][] {
+ {"00A123122312312", "a", "00D300000000XHP"},
+ {"00A223122312312", "a", "00D300000000XHP"},
+ {"00A323122312312", "a", "00D300000000XHP"},
+ {"00A423122312312", "a", "00D300000000XHP"}})
+ .close();
+
+ start().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=[$19], A_STRING=[$2], ORGANIZATION_ID=[$36])\n"
+ +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[AND(=($19, $1), =($18, $0))], joinType=[inner])\n"
+ +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[AND(=($1, $19), =($0, $18))], joinType=[inner])\n"
+ +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n"
+ +
+ " PhoenixToClientConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(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() {
+ start().sql("select a_string, count(entity_id) from atable group by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{2}], EXPR$1=[COUNT()])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(new Object[][] {
+ {"a", 4L},
+ {"b", 4L},
+ {"c", 1L}})
+ .close();
+
+ start().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()])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(new Object[][] {
+ {4L, "a"},
+ {4L, "b"},
+ {1L, "c"}})
+ .close();
+
+ start().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=[{3}], EXPR$1=[COUNT()])\n" +
+ " PhoenixServerJoin(condition=[=($2, $1)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(new Object[][] {
+ {"S1", 2L},
+ {"S2", 2L},
+ {"S5", 1L},
+ {"S6", 1L}})
+ .close();
+ }
+
+ @Test public void testDistinct() {
+ start().sql("select distinct a_string from aTable")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{2}])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(new Object[][]{
+ {"a"},
+ {"b"},
+ {"c"}})
+ .close();
+ }
+
+ @Test public void testSort() {
+ start().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[][] {
+ {"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().sql("select organization_id, entity_id, a_string from aTable order by organization_id, entity_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(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().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()])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(new Object[][] {
+ {1L, "c"},
+ {4L, "b"},
+ {4L, "a"}})
+ .close();
+
+ start().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=[{3}], EXPR$1=[COUNT()])\n" +
+ " PhoenixServerJoin(condition=[=($2, $1)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(new Object[][] {
+ {"S6", 1L},
+ {"S5", 1L},
+ {"S2", 2L},
+ {"S1", 2L}})
+ .close();
+
+ start().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(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() {
+ start().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[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start().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()])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(new Object[][] {
+ {1L, "c"},
+ {4L, "b"}})
+ .close();
+
+ start().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=[{3}], EXPR$1=[COUNT()])\n" +
+ " PhoenixServerJoin(condition=[=($2, $1)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(new Object[][] {
+ {"S6", 1L},
+ {"S5", 1L},
+ {"S2", 2L}})
+ .close();
+
+ start().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(new Object[][] {
+ {"0000000006", "T6", "0000000006", "S6"},
+ {"0000000005", "T5", "0000000005", "S5"},
+ {"0000000004", "T4", "0000000002", "S2"}})
+ .close();
+ }
+
+ @Test public void testLimit() {
+ start().sql("select organization_id, entity_id, a_string from aTable limit 5")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixLimit(fetch=[5])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$0], ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(new Object[][] {
+ {"00D300000000XHP", "00A123122312312", "a"},
+ {"00D300000000XHP", "00A223122312312", "a"},
+ {"00D300000000XHP", "00A323122312312", "a"},
+ {"00D300000000XHP", "00A423122312312", "a"},
+ {"00D300000000XHP", "00B523122312312", "b"}})
+ .close();
+
+ start().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()])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .resultIs(new Object[][] {
+ {4L, "a"},
+ {4L, "b"}})
+ .close();
+
+ start().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=[{3}], EXPR$1=[COUNT()])\n" +
+ " PhoenixServerJoin(condition=[=($2, $1)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(new Object[][] {
+ {"S1", 2L},
+ {"S2", 2L},
+ {"S5", 1L}})
+ .close();
+
+ start().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[=($2, $3)], joinType=[inner])\n" +
+ " PhoenixServerProject(item_id=[$0], NAME=[$1], supplier_id=[$5])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(supplier_id=[$0], NAME=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, SupplierTable]])\n")
+ .resultIs(new Object[][] {
+ {"0000000001", "T1", "0000000001", "S1"},
+ {"0000000002", "T2", "0000000001", "S1"},
+ {"0000000003", "T3", "0000000002", "S2"}})
+ .close();
+
+ start().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}})
+ .close();
+ }
+
+ @Test public void testSubquery() {
+ start().sql("SELECT \"order_id\", quantity FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o WHERE quantity = (SELECT max(quantity) FROM " + JOIN_ORDER_TABLE_FULL_NAME + " q WHERE o.\"item_id\" = q.\"item_id\")")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(order_id=[$0], QUANTITY=[$4])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[AND(=($2, $7), =($4, $8))], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixServerAggregate(group=[{7}], EXPR$0=[MAX($4)])\n" +
+ " PhoenixServerJoin(condition=[=($7, $2)], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixServerAggregate(group=[{2}])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n")
+ .resultIs(new Object[][]{
+ {"000000000000001", 1000},
+ {"000000000000003", 3000},
+ {"000000000000004", 4000},
+ {"000000000000005", 5000}})
+ .close();
+ }
+
+ @Test public void testScalarSubquery() {
+ start().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[=($0, $7)], joinType=[left], isSingleValueRhs=[true])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n" +
+ " PhoenixServerAggregate(group=[{7}], SQ=[MAX($4)])\n" +
+ " PhoenixServerJoin(condition=[=($2, $7)], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(item_id=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]])\n")
+ .resultIs(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().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" +
+ " PhoenixToClientConverter\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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerJoin(condition=[=($2, $7)], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " PhoenixServerAggregate(group=[{0}])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, ItemTable]], filter=[<($0, '0000000006')])\n")
+ .resultIs(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 testIndex() {
+ final Start start = start(getConnectionProps(true), false);
+ start.sql("select * from aTable where b_string = 'b'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ORGANIZATION_ID=[$1], ENTITY_ID=[$2], A_STRING=[$3], B_STRING=[$0], A_INTEGER=[$4], A_DATE=[$5], A_TIME=[$6], A_TIMESTAMP=[$7], X_DECIMAL=[$8], X_LONG=[$9], X_INTEGER=[$10], Y_INTEGER=[$11], A_BYTE=[$12], A_SHORT=[$13], A_FLOAT=[$14], A_DOUBLE=[$15], A_UNSIGNED_FLOAT=[$16], A_UNSIGNED_DOUBLE=[$17])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_FULL]], filter=[=($0, 'b')])\n")
+ .close();
+ start.sql("select x_integer from aTable")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(X_INTEGER=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX1]])\n")
+ .close();
+ start.sql("select a_string from aTable order by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(A_STRING=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX1]])\n")
+ .close();
+ start.sql("select a_string from aTable order by organization_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .close();
+ start.sql("select a_integer from aTable order by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixClientProject(A_INTEGER=[$0])\n" +
+ " PhoenixServerSort(sort0=[$1], dir0=[ASC])\n" +
+ " PhoenixServerProject(A_INTEGER=[$4], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .close();
+ start.sql("select a_string, b_string from aTable where a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(A_STRING=[$0], B_STRING=[$3])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX1]], filter=[=($0, 'a')])\n")
+ .close();
+ start.sql("select a_string, b_string from aTable where b_string = 'b'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(A_STRING=[$3], B_STRING=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX2]], filter=[=($0, 'b')])\n")
+ .close();
+ start.sql("select a_string, b_string, x_integer, y_integer from aTable where b_string = 'b'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(A_STRING=[$3], B_STRING=[$0], X_INTEGER=[$10], Y_INTEGER=[$11])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_FULL]], filter=[=($0, 'b')])\n")
+ .close();
+ }
+
+ @Test public void testValues() {
+ start().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();
+ }
+
+ @Test public void testUnion() {
+ start().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" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'b')])\n")
+ .resultIs(new Object[][] {
+ {"00A123122312312"},
+ {"00A223122312312"},
+ {"00A323122312312"},
+ {"00A423122312312"},
+ {"00B523122312312"},
+ {"00B623122312312"},
+ {"00B723122312312"},
+ {"00B823122312312"}})
+ .close();
+
+ start().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" +
+ " PhoenixClientSort(sort0=[$0], dir0=[DESC])\n" +
+ " PhoenixUnion(all=[true])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'a')])\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixServerProject(ENTITY_ID=[$1], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], filter=[=($2, 'c')])\n")
+ .resultIs(new Object[][] {
+ {"00C923122312312", "c"},
+ {"00A423122312312", "a"},
+ {"00A323122312312", "a"}})
+ .close();
+ }
+
+ /** Tests a simple command that is defined in Phoenix's extended SQL parser. */
+ @Ignore
+ @Test public void testCommit() {
+ start().sql("commit").execute();
+ }
+
+ @Test public void testConnectJoinHsqldb() {
+ final Start start = new Start(new Properties(), false) {
+ @Override
+ Connection createConnection() throws Exception {
+ return connectWithHsqldbUsingModel();
+ }
+ };
+ 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" +
+ " PhoenixToClientConverter\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}])\n" +
+ " PhoenixTableScan(table=[[phoenix, Join, OrderTable]])\n" +
+ " JdbcToEnumerableConverter\n" +
+ " JdbcProject(time_id=[$0], the_date=[$1], the_day=[$2], the_month=[$3], the_year=[$4], day_of_month=[$5], week_of_year=[$6], month_of_year=[$7], quarter=[$8], fiscal_period=[$9], $f10=[CAST($4):INTEGER])\n" +
+ " JdbcTableScan(table=[[foodmart, time_by_day]])\n")
+ .resultIs(new Object[][] {
+ new Object[] {1997, 1000, 365L},
+ new Object[] {1997, 2000, 365L},
+ new Object[] {1997, 3000, 365L},
+ new Object[] {1998, 4000, 365L},
+ new Object[] {1998, 5000, 365L}})
+ .close();;
+ }
+
+ @Test public void testConnectUsingModel() throws Exception {
+ final Start start = start(new Properties(), true);
+ start.sql("select * from aTable")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixToClientConverter\n" +
+ " PhoenixTableScan(table=[[HR, ATABLE]])\n")
+ // .resultIs("Xx")
+ .close();
+ }
+}