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 2015/12/16 23:24:03 UTC
phoenix git commit: PHOENIX-2386 Add new test cases to verify
existing functionalities in Phoenix / Calcite integration (Part 1: local
index)
Repository: phoenix
Updated Branches:
refs/heads/calcite cdc400876 -> 1f43b8fc5
PHOENIX-2386 Add new test cases to verify existing functionalities in Phoenix / Calcite integration (Part 1: local index)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/1f43b8fc
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/1f43b8fc
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/1f43b8fc
Branch: refs/heads/calcite
Commit: 1f43b8fc5ae9e162ded987141ccd91fbb00e9fce
Parents: cdc40087
Author: maryannxue <ma...@gmail.com>
Authored: Wed Dec 16 17:23:51 2015 -0500
Committer: maryannxue <ma...@gmail.com>
Committed: Wed Dec 16 17:23:51 2015 -0500
----------------------------------------------------------------------
.../apache/phoenix/calcite/BaseCalciteIT.java | 440 +++++++++++++
.../org/apache/phoenix/calcite/CalciteIT.java | 635 +------------------
.../apache/phoenix/calcite/CalciteIndexIT.java | 291 +++++++++
3 files changed, 734 insertions(+), 632 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/1f43b8fc/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java b/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java
new file mode 100644
index 0000000..d288429
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/calcite/BaseCalciteIT.java
@@ -0,0 +1,440 @@
+/*
+ * 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.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.io.File;
+import java.io.FileWriter;
+import java.io.PrintWriter;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Arrays;
+import java.util.List;
+import java.util.Properties;
+
+import org.apache.calcite.avatica.util.ArrayImpl;
+import org.apache.calcite.config.CalciteConnectionProperty;
+import org.apache.phoenix.end2end.BaseClientManagedTimeIT;
+import org.apache.phoenix.schema.TableAlreadyExistsException;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.PropertiesUtil;
+
+import com.google.common.collect.Lists;
+
+public class BaseCalciteIT extends BaseClientManagedTimeIT {
+
+ public static Start start(boolean materializationEnabled) {
+ return new Start(getConnectionProps(materializationEnabled));
+ }
+
+ public static Start start(Properties props) {
+ return new Start(props);
+ }
+
+ public static class Start {
+ protected final Properties props;
+ private Connection connection;
+
+ Start(Properties props) {
+ this.props = props;
+ }
+
+ Connection createConnection() throws Exception {
+ return DriverManager.getConnection(
+ "jdbc:phoenixcalcite:"
+ + getUrl().substring(PhoenixRuntime.JDBC_PROTOCOL.length() + 1),
+ 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) throws SQLException {
+ 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() throws SQLException {
+ final Statement statement = start.getConnection().createStatement();
+ final boolean execute = statement.execute(sql);
+ statement.close();
+ return execute;
+ }
+
+ public List<Object[]> getResult(String sql) throws SQLException {
+ final Statement statement = start.getConnection().createStatement();
+ final ResultSet resultSet = statement.executeQuery(sql);
+ List<Object[]> list = getResult(resultSet);
+ resultSet.close();
+ statement.close();
+ return list;
+ }
+
+ public void close() {
+ start.close();
+ }
+
+ public Sql resultIs(Object[]... expected) throws SQLException {
+ 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++) {
+ Object obj = resultSet.getObject(j + 1);
+ if (obj instanceof ArrayImpl) {
+ assertEquals(
+ Arrays.toString((Object[]) row[j]),
+ obj.toString());
+ } else {
+ assertEquals(row[j], obj);
+ }
+ }
+ }
+ assertFalse(resultSet.next());
+ resultSet.close();
+ statement.close();
+ return this;
+ }
+ }
+
+ 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 getPhoenixSchema() {
+ return " {\n"
+ + " name: 'phoenix',\n"
+ + " type: 'custom',\n"
+ + " factory: 'org.apache.phoenix.calcite.PhoenixSchema$Factory',\n"
+ + " operand: {\n"
+ + " url: \"" + getUrl() + "\"\n"
+ + " }\n"
+ + " }";
+ }
+
+ protected 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);
+ return connection;
+ }
+
+ protected static Connection connectWithHsqldbUsingModel(Properties props) 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"
+ + getPhoenixSchema() + ",\n"
+ + FOODMART_SCHEMA + "\n"
+ + " ]\n"
+ + "}\n");
+ pw.close();
+ final Connection connection =
+ DriverManager.getConnection("jdbc:phoenixcalcite:model=" + file.getAbsolutePath(), props);
+ return connection;
+ }
+
+ protected 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;
+ }
+
+ protected static final String SCORES_TABLE_NAME = "scores";
+
+ protected void initArrayTable() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.createStatement().execute(
+ "CREATE TABLE " + SCORES_TABLE_NAME
+ + "(student_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, scores INTEGER[] CONSTRAINT pk PRIMARY KEY (student_id, subject_id))");
+ PreparedStatement stmt = conn.prepareStatement(
+ "UPSERT INTO " + SCORES_TABLE_NAME
+ + " VALUES(?, ?, ?)");
+ stmt.setInt(1, 1);
+ stmt.setInt(2, 1);
+ stmt.setArray(3, conn.createArrayOf("INTEGER", new Integer[] {85, 80, 82}));
+ stmt.execute();
+ stmt.setInt(1, 2);
+ stmt.setInt(2, 1);
+ stmt.setArray(3, null);
+ stmt.execute();
+ stmt.setInt(1, 3);
+ stmt.setInt(2, 2);
+ stmt.setArray(3, conn.createArrayOf("INTEGER", new Integer[] {87, 88, 80}));
+ stmt.execute();
+ conn.commit();
+ } catch (TableAlreadyExistsException e) {
+ }
+ conn.close();
+ }
+
+ protected static final String NOSALT_TABLE_NAME = "nosalt_test_table";
+ protected static final String NOSALT_TABLE_SALTED_INDEX_NAME = "idxsalted_nosalt_test_table";
+ protected static final String SALTED_TABLE_NAME = "salted_test_table";
+ protected static final String SALTED_TABLE_NOSALT_INDEX_NAME = "idx_salted_test_table";
+ protected static final String SALTED_TABLE_SALTED_INDEX_NAME = "idxsalted_salted_test_table";
+
+ protected void initSaltedTables(String index) throws SQLException {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.createStatement().execute(
+ "CREATE TABLE " + NOSALT_TABLE_NAME + " (mypk0 INTEGER NOT NULL, mypk1 INTEGER NOT NULL, col0 INTEGER, col1 INTEGER CONSTRAINT pk PRIMARY KEY (mypk0, mypk1))");
+ PreparedStatement stmt = conn.prepareStatement(
+ "UPSERT INTO " + NOSALT_TABLE_NAME
+ + " VALUES(?, ?, ?, ?)");
+ stmt.setInt(1, 1);
+ stmt.setInt(2, 2);
+ stmt.setInt(3, 3);
+ stmt.setInt(4, 4);
+ stmt.execute();
+ stmt.setInt(1, 2);
+ stmt.setInt(2, 3);
+ stmt.setInt(3, 4);
+ stmt.setInt(4, 5);
+ stmt.execute();
+ stmt.setInt(1, 3);
+ stmt.setInt(2, 4);
+ stmt.setInt(3, 5);
+ stmt.setInt(4, 6);
+ stmt.execute();
+ conn.commit();
+
+ if (index != null) {
+ conn.createStatement().execute("CREATE " + index + " " + NOSALT_TABLE_SALTED_INDEX_NAME + " ON " + NOSALT_TABLE_NAME + " (col0) SALT_BUCKETS=4");
+ conn.commit();
+ }
+
+ conn.createStatement().execute(
+ "CREATE TABLE " + SALTED_TABLE_NAME + " (mypk0 INTEGER NOT NULL, mypk1 INTEGER NOT NULL, col0 INTEGER, col1 INTEGER CONSTRAINT pk PRIMARY KEY (mypk0, mypk1)) SALT_BUCKETS=4");
+ stmt = conn.prepareStatement(
+ "UPSERT INTO " + SALTED_TABLE_NAME
+ + " VALUES(?, ?, ?, ?)");
+ stmt.setInt(1, 1);
+ stmt.setInt(2, 2);
+ stmt.setInt(3, 3);
+ stmt.setInt(4, 4);
+ stmt.execute();
+ stmt.setInt(1, 2);
+ stmt.setInt(2, 3);
+ stmt.setInt(3, 4);
+ stmt.setInt(4, 5);
+ stmt.execute();
+ stmt.setInt(1, 3);
+ stmt.setInt(2, 4);
+ stmt.setInt(3, 5);
+ stmt.setInt(4, 6);
+ stmt.execute();
+ conn.commit();
+
+ if (index != null) {
+ conn.createStatement().execute("CREATE " + index + " " + SALTED_TABLE_NOSALT_INDEX_NAME + " ON " + SALTED_TABLE_NAME + " (col0)");
+ conn.createStatement().execute("CREATE " + index + " " + SALTED_TABLE_SALTED_INDEX_NAME + " ON " + SALTED_TABLE_NAME + " (col1) INCLUDE (col0) SALT_BUCKETS=4");
+ conn.commit();
+ }
+ } catch (TableAlreadyExistsException e) {
+ }
+ conn.close();
+ }
+
+ protected static final String MULTI_TENANT_TABLE = "multitenant_test_table";
+ protected static final String MULTI_TENANT_TABLE_INDEX = "idx_multitenant_test_table";
+ protected static final String MULTI_TENANT_VIEW1 = "multitenant_test_view1";
+ protected static final String MULTI_TENANT_VIEW1_INDEX = "idx_multitenant_test_view1";
+ protected static final String MULTI_TENANT_VIEW2 = "multitenant_test_view2";
+ protected static final String MULTI_TENANT_VIEW2_INDEX = "idx_multitenant_test_view2";
+
+ protected void initMultiTenantTables(String index) throws SQLException {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.createStatement().execute(
+ "CREATE TABLE " + MULTI_TENANT_TABLE + " (tenant_id VARCHAR NOT NULL, id VARCHAR NOT NULL, col0 INTEGER, col1 INTEGER, col2 INTEGER CONSTRAINT pk PRIMARY KEY (tenant_id, id)) MULTI_TENANT=true");
+ PreparedStatement stmt = conn.prepareStatement(
+ "UPSERT INTO " + MULTI_TENANT_TABLE
+ + " VALUES(?, ?, ?, ?, ?)");
+ stmt.setString(1, "10");
+ stmt.setString(2, "2");
+ stmt.setInt(3, 3);
+ stmt.setInt(4, 4);
+ stmt.setInt(5, 5);
+ stmt.execute();
+ stmt.setString(1, "15");
+ stmt.setString(2, "3");
+ stmt.setInt(3, 4);
+ stmt.setInt(4, 5);
+ stmt.setInt(5, 6);
+ stmt.execute();
+ stmt.setString(1, "20");
+ stmt.setString(2, "4");
+ stmt.setInt(3, 5);
+ stmt.setInt(4, 6);
+ stmt.setInt(5, 7);
+ stmt.execute();
+ stmt.setString(1, "20");
+ stmt.setString(2, "5");
+ stmt.setInt(3, 6);
+ stmt.setInt(4, 7);
+ stmt.setInt(5, 8);
+ stmt.execute();
+ conn.commit();
+
+ if (index != null) {
+ conn.createStatement().execute(
+ "CREATE " + index + " " + MULTI_TENANT_TABLE_INDEX
+ + " ON " + MULTI_TENANT_TABLE + "(col1) INCLUDE (col0, col2)");
+ conn.commit();
+ }
+
+ conn.close();
+ props.setProperty("TenantId", "10");
+ conn = DriverManager.getConnection(getUrl(), props);
+ conn.createStatement().execute("CREATE VIEW " + MULTI_TENANT_VIEW1
+ + " AS select * from " + MULTI_TENANT_TABLE);
+ conn.commit();
+
+ if (index != null) {
+ conn.createStatement().execute(
+ "CREATE " + index + " " + MULTI_TENANT_VIEW1_INDEX
+ + " ON " + MULTI_TENANT_VIEW1 + "(col0)");
+ conn.commit();
+ }
+
+ conn.close();
+ props.setProperty("TenantId", "20");
+ conn = DriverManager.getConnection(getUrl(), props);
+ conn.createStatement().execute("CREATE VIEW " + MULTI_TENANT_VIEW2
+ + " AS select * from " + MULTI_TENANT_TABLE + " where col2 > 7");
+ conn.commit();
+
+ if (index != null) {
+ conn.createStatement().execute(
+ "CREATE " + index + " " + MULTI_TENANT_VIEW2_INDEX
+ + " ON " + MULTI_TENANT_VIEW2 + "(col0)");
+ conn.commit();
+ }
+ } catch (TableAlreadyExistsException e) {
+ } finally {
+ conn.close();
+ }
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/1f43b8fc/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
index 54cb7b6..026d99e 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
@@ -21,264 +21,30 @@ 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.assertEquals;
-import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertTrue;
-import static org.junit.Assert.fail;
-import java.io.File;
-import java.io.FileWriter;
-import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
-import java.util.Arrays;
-import java.util.List;
import java.util.Properties;
-import org.apache.calcite.avatica.util.ArrayImpl;
-import org.apache.calcite.config.CalciteConnectionProperty;
-import org.apache.phoenix.end2end.BaseClientManagedTimeIT;
-import org.apache.phoenix.schema.TableAlreadyExistsException;
-import org.apache.phoenix.util.PhoenixRuntime;
-import org.apache.phoenix.util.PropertiesUtil;
import org.junit.Before;
import org.junit.Ignore;
import org.junit.Test;
-import com.google.common.collect.Lists;
-
/**
* Integration test for queries powered by Calcite.
*/
-public class CalciteIT extends BaseClientManagedTimeIT {
-
- public static Start start(boolean materializationEnabled) {
- return new Start(getConnectionProps(materializationEnabled));
- }
-
- public static Start start(Properties props) {
- return new Start(props);
- }
-
- public static class Start {
- protected final Properties props;
- private Connection connection;
-
- Start(Properties props) {
- this.props = props;
- }
-
- Connection createConnection() throws Exception {
- return DriverManager.getConnection(
- "jdbc:phoenixcalcite:"
- + getUrl().substring(PhoenixRuntime.JDBC_PROTOCOL.length() + 1),
- 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) throws SQLException {
- 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() throws SQLException {
- final Statement statement = start.getConnection().createStatement();
- final boolean execute = statement.execute(sql);
- statement.close();
- return execute;
- }
-
- public List<Object[]> getResult(String sql) throws SQLException {
- final Statement statement = start.getConnection().createStatement();
- final ResultSet resultSet = statement.executeQuery(sql);
- List<Object[]> list = getResult(resultSet);
- resultSet.close();
- statement.close();
- return list;
- }
-
- public void close() {
- start.close();
- }
-
- public Sql resultIs(Object[]... expected) throws SQLException {
- 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++) {
- Object obj = resultSet.getObject(j + 1);
- if (obj instanceof ArrayImpl) {
- assertEquals(
- Arrays.toString((Object[]) row[j]),
- obj.toString());
- } else {
- assertEquals(row[j], obj);
- }
- }
- }
- assertFalse(resultSet.next());
- resultSet.close();
- statement.close();
- return this;
- }
- }
-
- 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);
- return connection;
- }
-
- private static Connection connectWithHsqldbUsingModel(Properties props) 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(), props);
- 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;
- }
+public class CalciteIT extends BaseCalciteIT {
@Before
public void initTable() throws Exception {
final String url = getUrl();
- ensureTableCreated(url, "ATABLE");
initATableValues(getOrganizationId(), null, url);
initJoinTableValues(url, null, null);
initArrayTable();
- initSaltedTables();
- initMultiTenantTables();
+ initSaltedTables(null);
final Connection connection = DriverManager.getConnection(url);
- connection.createStatement().execute("CREATE INDEX IF NOT EXISTS IDX1 ON aTable (a_string) INCLUDE (b_string, x_integer)");
- connection.createStatement().execute("CREATE INDEX IF NOT EXISTS IDX2 ON aTable (b_string) INCLUDE (a_string, y_integer)");
- connection.createStatement().execute("CREATE INDEX IF NOT EXISTS 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)");
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");
@@ -289,184 +55,9 @@ public class CalciteIT extends BaseClientManagedTimeIT {
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.createStatement().execute("UPDATE STATISTICS IDX_" + SALTED_TABLE_NAME);
- connection.createStatement().execute("UPDATE STATISTICS IDX1");
- connection.createStatement().execute("UPDATE STATISTICS IDX2");
- connection.createStatement().execute("UPDATE STATISTICS IDX_FULL");
connection.close();
}
- protected static final String SCORES_TABLE_NAME = "scores";
-
- protected void initArrayTable() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- conn.createStatement().execute(
- "CREATE TABLE " + SCORES_TABLE_NAME
- + "(student_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, scores INTEGER[] CONSTRAINT pk PRIMARY KEY (student_id, subject_id))");
- PreparedStatement stmt = conn.prepareStatement(
- "UPSERT INTO " + SCORES_TABLE_NAME
- + " VALUES(?, ?, ?)");
- stmt.setInt(1, 1);
- stmt.setInt(2, 1);
- stmt.setArray(3, conn.createArrayOf("INTEGER", new Integer[] {85, 80, 82}));
- stmt.execute();
- stmt.setInt(1, 2);
- stmt.setInt(2, 1);
- stmt.setArray(3, null);
- stmt.execute();
- stmt.setInt(1, 3);
- stmt.setInt(2, 2);
- stmt.setArray(3, conn.createArrayOf("INTEGER", new Integer[] {87, 88, 80}));
- stmt.execute();
- conn.commit();
- } catch (TableAlreadyExistsException e) {
- }
- conn.close();
- }
-
- protected static final String NOSALT_TABLE_NAME = "nosalt_test_table";
- protected static final String NOSALT_TABLE_SALTED_INDEX_NAME = "idxsalted_nosalt_test_table";
- protected static final String SALTED_TABLE_NAME = "salted_test_table";
- protected static final String SALTED_TABLE_NOSALT_INDEX_NAME = "idx_salted_test_table";
- protected static final String SALTED_TABLE_SALTED_INDEX_NAME = "idxsalted_salted_test_table";
-
- protected void initSaltedTables() throws SQLException {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- conn.createStatement().execute(
- "CREATE TABLE " + NOSALT_TABLE_NAME + " (mypk0 INTEGER NOT NULL, mypk1 INTEGER NOT NULL, col0 INTEGER, col1 INTEGER CONSTRAINT pk PRIMARY KEY (mypk0, mypk1))");
- PreparedStatement stmt = conn.prepareStatement(
- "UPSERT INTO " + NOSALT_TABLE_NAME
- + " VALUES(?, ?, ?, ?)");
- stmt.setInt(1, 1);
- stmt.setInt(2, 2);
- stmt.setInt(3, 3);
- stmt.setInt(4, 4);
- stmt.execute();
- stmt.setInt(1, 2);
- stmt.setInt(2, 3);
- stmt.setInt(3, 4);
- stmt.setInt(4, 5);
- stmt.execute();
- stmt.setInt(1, 3);
- stmt.setInt(2, 4);
- stmt.setInt(3, 5);
- stmt.setInt(4, 6);
- stmt.execute();
- conn.commit();
-
- conn.createStatement().execute("CREATE INDEX " + NOSALT_TABLE_SALTED_INDEX_NAME + " ON " + NOSALT_TABLE_NAME + " (col0) SALT_BUCKETS=4");
- conn.commit();
-
- conn.createStatement().execute(
- "CREATE TABLE " + SALTED_TABLE_NAME + " (mypk0 INTEGER NOT NULL, mypk1 INTEGER NOT NULL, col0 INTEGER, col1 INTEGER CONSTRAINT pk PRIMARY KEY (mypk0, mypk1)) SALT_BUCKETS=4");
- stmt = conn.prepareStatement(
- "UPSERT INTO " + SALTED_TABLE_NAME
- + " VALUES(?, ?, ?, ?)");
- stmt.setInt(1, 1);
- stmt.setInt(2, 2);
- stmt.setInt(3, 3);
- stmt.setInt(4, 4);
- stmt.execute();
- stmt.setInt(1, 2);
- stmt.setInt(2, 3);
- stmt.setInt(3, 4);
- stmt.setInt(4, 5);
- stmt.execute();
- stmt.setInt(1, 3);
- stmt.setInt(2, 4);
- stmt.setInt(3, 5);
- stmt.setInt(4, 6);
- stmt.execute();
- conn.commit();
-
- conn.createStatement().execute("CREATE INDEX " + SALTED_TABLE_NOSALT_INDEX_NAME + " ON " + SALTED_TABLE_NAME + " (col0)");
- conn.createStatement().execute("CREATE INDEX " + SALTED_TABLE_SALTED_INDEX_NAME + " ON " + SALTED_TABLE_NAME + " (col1) INCLUDE (col0) SALT_BUCKETS=4");
- conn.commit();
- } catch (TableAlreadyExistsException e) {
- }
- conn.close();
- }
-
- protected static final String MULTI_TENANT_TABLE = "multitenant_test_table";
- protected static final String MULTI_TENANT_TABLE_INDEX = "idx_multitenant_test_table";
- protected static final String MULTI_TENANT_VIEW1 = "multitenant_test_view1";
- protected static final String MULTI_TENANT_VIEW1_INDEX = "idx_multitenant_test_view1";
- protected static final String MULTI_TENANT_VIEW2 = "multitenant_test_view2";
- protected static final String MULTI_TENANT_VIEW2_INDEX = "idx_multitenant_test_view2";
-
- protected void initMultiTenantTables() throws SQLException {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- conn.createStatement().execute(
- "CREATE TABLE " + MULTI_TENANT_TABLE + " (tenant_id VARCHAR NOT NULL, id VARCHAR NOT NULL, col0 INTEGER, col1 INTEGER, col2 INTEGER CONSTRAINT pk PRIMARY KEY (tenant_id, id)) MULTI_TENANT=true");
- PreparedStatement stmt = conn.prepareStatement(
- "UPSERT INTO " + MULTI_TENANT_TABLE
- + " VALUES(?, ?, ?, ?, ?)");
- stmt.setString(1, "10");
- stmt.setString(2, "2");
- stmt.setInt(3, 3);
- stmt.setInt(4, 4);
- stmt.setInt(5, 5);
- stmt.execute();
- stmt.setString(1, "15");
- stmt.setString(2, "3");
- stmt.setInt(3, 4);
- stmt.setInt(4, 5);
- stmt.setInt(5, 6);
- stmt.execute();
- stmt.setString(1, "20");
- stmt.setString(2, "4");
- stmt.setInt(3, 5);
- stmt.setInt(4, 6);
- stmt.setInt(5, 7);
- stmt.execute();
- stmt.setString(1, "20");
- stmt.setString(2, "5");
- stmt.setInt(3, 6);
- stmt.setInt(4, 7);
- stmt.setInt(5, 8);
- stmt.execute();
- conn.commit();
-
- conn.createStatement().execute(
- "CREATE INDEX " + MULTI_TENANT_TABLE_INDEX
- + " ON " + MULTI_TENANT_TABLE + "(col1) INCLUDE (col0, col2)");
- conn.commit();
-
- conn.close();
- props.setProperty("TenantId", "10");
- conn = DriverManager.getConnection(getUrl(), props);
- conn.createStatement().execute("CREATE VIEW " + MULTI_TENANT_VIEW1
- + " AS select * from " + MULTI_TENANT_TABLE);
- conn.commit();
-
- conn.createStatement().execute(
- "CREATE INDEX " + MULTI_TENANT_VIEW1_INDEX
- + " ON " + MULTI_TENANT_VIEW1 + "(col0)");
- conn.commit();
-
- conn.close();
- props.setProperty("TenantId", "20");
- conn = DriverManager.getConnection(getUrl(), props);
- conn.createStatement().execute("CREATE VIEW " + MULTI_TENANT_VIEW2
- + " AS select * from " + MULTI_TENANT_TABLE + " where col2 > 7");
- conn.commit();
-
- conn.createStatement().execute(
- "CREATE INDEX " + MULTI_TENANT_VIEW2_INDEX
- + " ON " + MULTI_TENANT_VIEW2 + "(col0)");
- conn.commit();
- } catch (TableAlreadyExistsException e) {
- } finally {
- conn.close();
- }
- }
-
private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
@Test public void testTableScan() throws Exception {
@@ -1204,55 +795,6 @@ public class CalciteIT extends BaseClientManagedTimeIT {
.close();;
}
- @Test public void testIndex() throws Exception {
- start(true).sql("select * from aTable where b_string = 'b'")
- .explainIs("PhoenixToEnumerableConverter\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(true).sql("select x_integer from aTable")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(X_INTEGER=[$4])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX1]])\n")
- .close();
- start(true).sql("select a_string from aTable order by a_string")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(A_STRING=[$0])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX1]], scanOrder=[FORWARD])\n")
- .close();
- start(true).sql("select a_string from aTable order by organization_id")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(A_STRING=[$2], ORGANIZATION_ID=[$0])\n" +
- " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[FORWARD])\n")
- .close();
- start(true).sql("select a_integer from aTable order by a_string")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerSort(sort0=[$1], dir0=[ASC])\n" +
- " PhoenixServerProject(A_INTEGER=[$4], A_STRING=[$2])\n" +
- " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
- .close();
- start(true).sql("select a_string, b_string from aTable where a_string = 'a'")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(A_STRING=[$0], B_STRING=[$3])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX1]], filter=[=($0, 'a')])\n")
- .close();
- start(true).sql("select a_string, b_string from aTable where b_string = 'b'")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(A_STRING=[$3], B_STRING=[$0])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX2]], filter=[=($0, 'b')])\n")
- .close();
- start(true).sql("select a_string, b_string, x_integer, y_integer from aTable where b_string = 'b'")
- .explainIs("PhoenixToEnumerableConverter\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();
- start(true).sql("select a_string, count(*) from aTable group by a_string")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerAggregate(group=[{0}], EXPR$1=[COUNT()], isOrdered=[true])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX1]], scanOrder=[FORWARD])\n")
- .close();
- }
-
@Test public void testValues() throws Exception {
start(false).sql("select p0+p1 from (values (2, 1)) as t(p0, p1)")
.explainIs("PhoenixToEnumerableConverter\n" +
@@ -1480,7 +1022,7 @@ public class CalciteIT extends BaseClientManagedTimeIT {
String p5Decorrelated =
"PhoenixToEnumerableConverter\n" +
" PhoenixClientProject(order_id=[$7])\n" +
- " PhoenixServerJoin(condition=[AND(=($5, $14), =($9, $0))], joinType=[inner])\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" +
@@ -1549,177 +1091,6 @@ public class CalciteIT extends BaseClientManagedTimeIT {
.close();
}
- @Test public void testSaltedIndex() throws Exception {
- start(true).sql("select count(*) from " + NOSALT_TABLE_NAME + " where col0 > 3")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerAggregate(group=[{}], EXPR$0=[COUNT()])\n" +
- " PhoenixTableScan(table=[[phoenix, IDXSALTED_NOSALT_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 3)])\n")
- .resultIs(new Object[][]{{2L}})
- .close();
- start(true).sql("select mypk0, mypk1, col0 from " + NOSALT_TABLE_NAME + " where col0 <= 4")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(MYPK0=[$1], MYPK1=[$2], COL0=[CAST($0):INTEGER])\n" +
- " PhoenixTableScan(table=[[phoenix, IDXSALTED_NOSALT_TEST_TABLE]], filter=[<=(CAST($0):INTEGER, 4)])\n")
- .resultIs(new Object[][] {
- {2, 3, 4},
- {1, 2, 3}})
- .close();
- start(true).sql("select * from " + SALTED_TABLE_NAME + " where mypk0 < 3")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixTableScan(table=[[phoenix, SALTED_TEST_TABLE]], filter=[<($0, 3)])\n")
- .resultIs(new Object[][] {
- {1, 2, 3, 4},
- {2, 3, 4, 5}})
- .close();
- start(true).sql("select count(*) from " + SALTED_TABLE_NAME + " where col0 > 3")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerAggregate(group=[{}], EXPR$0=[COUNT()])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_SALTED_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 3)])\n")
- .resultIs(new Object[][]{{2L}})
- .close();
- start(true).sql("select mypk0, mypk1, col0 from " + SALTED_TABLE_NAME + " where col0 <= 4")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(MYPK0=[$1], MYPK1=[$2], COL0=[CAST($0):INTEGER])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_SALTED_TEST_TABLE]], filter=[<=(CAST($0):INTEGER, 4)])\n")
- .resultIs(new Object[][] {
- {2, 3, 4},
- {1, 2, 3}})
- .close();
- start(true).sql("select count(*) from " + SALTED_TABLE_NAME + " where col1 > 4")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerAggregate(group=[{}], EXPR$0=[COUNT()])\n" +
- " PhoenixTableScan(table=[[phoenix, IDXSALTED_SALTED_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 4)])\n")
- .resultIs(new Object[][]{{2L}})
- .close();
- start(true).sql("select * from " + SALTED_TABLE_NAME + " where col1 <= 5 order by col1")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(MYPK0=[$1], MYPK1=[$2], COL0=[$3], COL1=[CAST($0):INTEGER])\n" +
- " PhoenixTableScan(table=[[phoenix, IDXSALTED_SALTED_TEST_TABLE]], filter=[<=(CAST($0):INTEGER, 5)], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
- {1, 2, 3, 4},
- {2, 3, 4, 5}})
- .close();
- start(true).sql("select * from " + SALTED_TABLE_NAME + " s1, " + SALTED_TABLE_NAME + " s2 where s1.mypk0 = s2.mypk0 and s1.mypk1 = s2.mypk1 and s1.mypk0 > 1 and s2.col1 < 6")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerJoin(condition=[AND(=($0, $4), =($1, $5))], joinType=[inner])\n" +
- " PhoenixTableScan(table=[[phoenix, SALTED_TEST_TABLE]], filter=[>($0, 1)])\n" +
- " PhoenixServerProject(MYPK0=[$1], MYPK1=[$2], COL0=[$3], COL1=[CAST($0):INTEGER])\n" +
- " PhoenixTableScan(table=[[phoenix, IDXSALTED_SALTED_TEST_TABLE]], filter=[<(CAST($0):INTEGER, 6)])\n")
- .resultIs(new Object[][] {
- {2, 3, 4, 5, 2, 3, 4, 5}})
- .close();
- }
-
- @Test public void testMultiTenant() throws Exception {
- Properties props = getConnectionProps(true);
- start(props).sql("select * from " + MULTI_TENANT_TABLE)
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixTableScan(table=[[phoenix, MULTITENANT_TEST_TABLE]])\n")
- .resultIs(new Object[][] {
- {"10", "2", 3, 4, 5},
- {"15", "3", 4, 5, 6},
- {"20", "4", 5, 6, 7},
- {"20", "5", 6, 7, 8}})
- .close();
-
- start(props).sql("select * from " + MULTI_TENANT_TABLE + " where tenant_id = '20' and col1 > 1")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(TENANT_ID=[$0], ID=[$2], COL0=[$3], COL1=[CAST($1):INTEGER], COL2=[$4])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_TABLE]], filter=[AND(=(CAST($0):VARCHAR(2) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\" NOT NULL, '20'), >(CAST($1):INTEGER, 1))])\n")
- .resultIs(new Object[][] {
- {"20", "4", 5, 6, 7},
- {"20", "5", 6, 7, 8}})
- .close();
-
- try {
- start(props).sql("select * from " + MULTI_TENANT_VIEW1)
- .explainIs("")
- .close();
- fail("Should have got SQLException.");
- } catch (SQLException e) {
- }
-
- props.setProperty("TenantId", "15");
- start(props).sql("select * from " + MULTI_TENANT_TABLE)
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixTableScan(table=[[phoenix, MULTITENANT_TEST_TABLE]])\n")
- .resultIs(new Object[][] {
- {"3", 4, 5, 6}})
- .close();
-
- start(props).sql("select * from " + MULTI_TENANT_TABLE + " where col1 > 1")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(ID=[$1], COL0=[$2], COL1=[CAST($0):INTEGER], COL2=[$3])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 1)])\n")
- .resultIs(new Object[][] {
- {"3", 4, 5, 6}})
- .close();
-
- try {
- start(props).sql("select * from " + MULTI_TENANT_VIEW1)
- .explainIs("")
- .close();
- fail("Should have got SQLException.");
- } catch (SQLException e) {
- }
-
- props.setProperty("TenantId", "10");
- start(props).sql("select * from " + MULTI_TENANT_VIEW1)
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixTableScan(table=[[phoenix, MULTITENANT_TEST_TABLE]])\n")
- .resultIs(new Object[][] {
- {"2", 3, 4, 5}})
- .close();
-
- start(props).sql("select * from " + MULTI_TENANT_TABLE + " where col1 > 1")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(ID=[$1], COL0=[$2], COL1=[CAST($0):INTEGER], COL2=[$3])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 1)])\n")
- .resultIs(new Object[][] {
- {"2", 3, 4, 5}})
- .close();
-
- start(props).sql("select id, col0 from " + MULTI_TENANT_TABLE + " where col0 > 1")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(ID=[$1], COL0=[CAST($0):INTEGER])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_VIEW1]], filter=[>(CAST($0):INTEGER, 1)])\n")
- .resultIs(new Object[][] {
- {"2", 3}})
- .close();
-
- start(props).sql("select id, col0 from " + MULTI_TENANT_VIEW1 + " where col0 > 1")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(ID=[$1], COL0=[CAST($0):INTEGER])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_VIEW1]], filter=[>(CAST($0):INTEGER, 1)])\n")
- .resultIs(new Object[][] {
- {"2", 3}})
- .close();
-
- props.setProperty("TenantId", "20");
- start(props).sql("select * from " + MULTI_TENANT_VIEW2)
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixTableScan(table=[[phoenix, MULTITENANT_TEST_TABLE]], filter=[>($3, 7)])\n")
- .resultIs(new Object[][] {
- {"5", 6, 7, 8}})
- .close();
-
- start(props).sql("select id, col0 from " + MULTI_TENANT_VIEW2 + " where col0 > 1")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(ID=[$1], COL0=[CAST($0):INTEGER])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_VIEW2]], filter=[>(CAST($0):INTEGER, 1)])\n")
- .resultIs(new Object[][] {
- {"5", 6}})
- .close();
-
- start(props).sql("select id, col0 from " + MULTI_TENANT_VIEW2 + " order by col0")
- .explainIs("PhoenixToEnumerableConverter\n" +
- " PhoenixServerProject(ID=[$1], COL0=[CAST($0):INTEGER])\n" +
- " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_VIEW2]], scanOrder=[FORWARD])\n")
- .resultIs(new Object[][] {
- {"5", 6}})
- .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" +
http://git-wip-us.apache.org/repos/asf/phoenix/blob/1f43b8fc/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIndexIT.java
new file mode 100644
index 0000000..88ebd52
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteIndexIT.java
@@ -0,0 +1,291 @@
+/*
+ * 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.junit.Assert.fail;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Properties;
+
+import org.junit.Before;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+@RunWith(Parameterized.class)
+public class CalciteIndexIT extends BaseCalciteIT {
+
+ private final boolean localIndex;
+
+ public CalciteIndexIT(boolean localIndex) {
+ this.localIndex = localIndex;
+ }
+
+ @Parameters(name="localIndex = {0}")
+ public static Collection<Boolean[]> data() {
+ return Arrays.asList(new Boolean[][] {
+ { false }, { true }
+ });
+ }
+
+ @Before
+ public void initTable() throws Exception {
+ final String url = getUrl();
+ final String index = localIndex ? "LOCAL INDEX" : "INDEX";
+ initATableValues(getOrganizationId(), null, url);
+ initSaltedTables(index);
+ initMultiTenantTables(index);
+ final Connection connection = DriverManager.getConnection(url);
+ connection.createStatement().execute("CREATE " + index + " IF NOT EXISTS IDX1 ON aTable (a_string) INCLUDE (b_string, x_integer)");
+ connection.createStatement().execute("CREATE " + index + " IF NOT EXISTS IDX2 ON aTable (b_string) INCLUDE (a_string, y_integer)");
+ connection.createStatement().execute("CREATE " + index + " IF NOT EXISTS 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)");
+ connection.createStatement().execute("UPDATE STATISTICS ATABLE");
+ connection.createStatement().execute("UPDATE STATISTICS " + SALTED_TABLE_NAME);
+ connection.createStatement().execute("UPDATE STATISTICS IDX_" + SALTED_TABLE_NAME);
+ connection.createStatement().execute("UPDATE STATISTICS IDX1");
+ connection.createStatement().execute("UPDATE STATISTICS IDX2");
+ connection.createStatement().execute("UPDATE STATISTICS IDX_FULL");
+ connection.close();
+ }
+
+ @Test public void testIndex() throws Exception {
+ start(true).sql("select * from aTable where b_string = 'b'")
+ .explainIs("PhoenixToEnumerableConverter\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(true).sql("select x_integer from aTable")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(X_INTEGER=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX1]])\n")
+ .close();
+ start(true).sql("select a_string from aTable order by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(A_STRING=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX1]], scanOrder=[FORWARD])\n")
+ .close();
+ start(true).sql("select a_string from aTable order by organization_id")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(A_STRING=[$2], ORGANIZATION_ID=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]], scanOrder=[FORWARD])\n")
+ .close();
+ start(true).sql("select a_integer from aTable order by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerSort(sort0=[$1], dir0=[ASC])\n" +
+ " PhoenixServerProject(A_INTEGER=[$4], A_STRING=[$2])\n" +
+ " PhoenixTableScan(table=[[phoenix, ATABLE]])\n")
+ .close();
+ start(true).sql("select a_string, b_string from aTable where a_string = 'a'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(A_STRING=[$0], B_STRING=[$3])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX1]], filter=[=($0, 'a')])\n")
+ .close();
+ start(true).sql("select a_string, b_string from aTable where b_string = 'b'")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(A_STRING=[$3], B_STRING=[$0])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX2]], filter=[=($0, 'b')])\n")
+ .close();
+ start(true).sql("select a_string, b_string, x_integer, y_integer from aTable where b_string = 'b'")
+ .explainIs("PhoenixToEnumerableConverter\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();
+ start(true).sql("select a_string, count(*) from aTable group by a_string")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{0}], EXPR$1=[COUNT()], isOrdered=[true])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX1]], scanOrder=[FORWARD])\n")
+ .close();
+ }
+
+ @Test public void testSaltedIndex() throws Exception {
+ start(true).sql("select count(*) from " + NOSALT_TABLE_NAME + " where col0 > 3")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{}], EXPR$0=[COUNT()])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDXSALTED_NOSALT_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 3)])\n")
+ .resultIs(new Object[][]{{2L}})
+ .close();
+ start(true).sql("select mypk0, mypk1, col0 from " + NOSALT_TABLE_NAME + " where col0 <= 4")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(MYPK0=[$1], MYPK1=[$2], COL0=[CAST($0):INTEGER])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDXSALTED_NOSALT_TEST_TABLE]], filter=[<=(CAST($0):INTEGER, 4)])\n")
+ .resultIs(new Object[][] {
+ {2, 3, 4},
+ {1, 2, 3}})
+ .close();
+ start(true).sql("select * from " + SALTED_TABLE_NAME + " where mypk0 < 3")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, SALTED_TEST_TABLE]], filter=[<($0, 3)])\n")
+ .resultIs(new Object[][] {
+ {1, 2, 3, 4},
+ {2, 3, 4, 5}})
+ .close();
+ start(true).sql("select count(*) from " + SALTED_TABLE_NAME + " where col0 > 3")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{}], EXPR$0=[COUNT()])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_SALTED_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 3)])\n")
+ .resultIs(new Object[][]{{2L}})
+ .close();
+ start(true).sql("select mypk0, mypk1, col0 from " + SALTED_TABLE_NAME + " where col0 <= 4")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(MYPK0=[$1], MYPK1=[$2], COL0=[CAST($0):INTEGER])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_SALTED_TEST_TABLE]], filter=[<=(CAST($0):INTEGER, 4)])\n")
+ .resultIs(new Object[][] {
+ {2, 3, 4},
+ {1, 2, 3}})
+ .close();
+ start(true).sql("select count(*) from " + SALTED_TABLE_NAME + " where col1 > 4")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerAggregate(group=[{}], EXPR$0=[COUNT()])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDXSALTED_SALTED_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 4)])\n")
+ .resultIs(new Object[][]{{2L}})
+ .close();
+ start(true).sql("select * from " + SALTED_TABLE_NAME + " where col1 <= 5 order by col1")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(MYPK0=[$1], MYPK1=[$2], COL0=[$3], COL1=[CAST($0):INTEGER])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDXSALTED_SALTED_TEST_TABLE]], filter=[<=(CAST($0):INTEGER, 5)], scanOrder=[FORWARD])\n")
+ .resultIs(new Object[][] {
+ {1, 2, 3, 4},
+ {2, 3, 4, 5}})
+ .close();
+ start(true).sql("select * from " + SALTED_TABLE_NAME + " s1, " + SALTED_TABLE_NAME + " s2 where s1.mypk0 = s2.mypk0 and s1.mypk1 = s2.mypk1 and s1.mypk0 > 1 and s2.col1 < 6")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerJoin(condition=[AND(=($0, $4), =($1, $5))], joinType=[inner])\n" +
+ " PhoenixTableScan(table=[[phoenix, SALTED_TEST_TABLE]], filter=[>($0, 1)])\n" +
+ " PhoenixServerProject(MYPK0=[$1], MYPK1=[$2], COL0=[$3], COL1=[CAST($0):INTEGER])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDXSALTED_SALTED_TEST_TABLE]], filter=[<(CAST($0):INTEGER, 6)])\n")
+ .resultIs(new Object[][] {
+ {2, 3, 4, 5, 2, 3, 4, 5}})
+ .close();
+ }
+
+ @Test public void testMultiTenant() throws Exception {
+ Properties props = getConnectionProps(true);
+ start(props).sql("select * from " + MULTI_TENANT_TABLE)
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, MULTITENANT_TEST_TABLE]])\n")
+ .resultIs(new Object[][] {
+ {"10", "2", 3, 4, 5},
+ {"15", "3", 4, 5, 6},
+ {"20", "4", 5, 6, 7},
+ {"20", "5", 6, 7, 8}})
+ .close();
+
+ start(props).sql("select * from " + MULTI_TENANT_TABLE + " where tenant_id = '20' and col1 > 1")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(TENANT_ID=[$0], ID=[$2], COL0=[$3], COL1=[CAST($1):INTEGER], COL2=[$4])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_TABLE]], filter=[AND(=(CAST($0):VARCHAR(2) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\" NOT NULL, '20'), >(CAST($1):INTEGER, 1))])\n")
+ .resultIs(new Object[][] {
+ {"20", "4", 5, 6, 7},
+ {"20", "5", 6, 7, 8}})
+ .close();
+
+ try {
+ start(props).sql("select * from " + MULTI_TENANT_VIEW1)
+ .explainIs("")
+ .close();
+ fail("Should have got SQLException.");
+ } catch (SQLException e) {
+ }
+
+ props.setProperty("TenantId", "15");
+ start(props).sql("select * from " + MULTI_TENANT_TABLE)
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, MULTITENANT_TEST_TABLE]])\n")
+ .resultIs(new Object[][] {
+ {"3", 4, 5, 6}})
+ .close();
+
+ start(props).sql("select * from " + MULTI_TENANT_TABLE + " where col1 > 1")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ID=[$1], COL0=[$2], COL1=[CAST($0):INTEGER], COL2=[$3])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 1)])\n")
+ .resultIs(new Object[][] {
+ {"3", 4, 5, 6}})
+ .close();
+
+ try {
+ start(props).sql("select * from " + MULTI_TENANT_VIEW1)
+ .explainIs("")
+ .close();
+ fail("Should have got SQLException.");
+ } catch (SQLException e) {
+ }
+
+ props.setProperty("TenantId", "10");
+ start(props).sql("select * from " + MULTI_TENANT_VIEW1)
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, MULTITENANT_TEST_TABLE]])\n")
+ .resultIs(new Object[][] {
+ {"2", 3, 4, 5}})
+ .close();
+
+ start(props).sql("select * from " + MULTI_TENANT_TABLE + " where col1 > 1")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ID=[$1], COL0=[$2], COL1=[CAST($0):INTEGER], COL2=[$3])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_TABLE]], filter=[>(CAST($0):INTEGER, 1)])\n")
+ .resultIs(new Object[][] {
+ {"2", 3, 4, 5}})
+ .close();
+
+ start(props).sql("select id, col0 from " + MULTI_TENANT_TABLE + " where col0 > 1")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ID=[$1], COL0=[CAST($0):INTEGER])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_VIEW1]], filter=[>(CAST($0):INTEGER, 1)])\n")
+ .resultIs(new Object[][] {
+ {"2", 3}})
+ .close();
+
+ start(props).sql("select id, col0 from " + MULTI_TENANT_VIEW1 + " where col0 > 1")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ID=[$1], COL0=[CAST($0):INTEGER])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_VIEW1]], filter=[>(CAST($0):INTEGER, 1)])\n")
+ .resultIs(new Object[][] {
+ {"2", 3}})
+ .close();
+
+ props.setProperty("TenantId", "20");
+ start(props).sql("select * from " + MULTI_TENANT_VIEW2)
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixTableScan(table=[[phoenix, MULTITENANT_TEST_TABLE]], filter=[>($3, 7)])\n")
+ .resultIs(new Object[][] {
+ {"5", 6, 7, 8}})
+ .close();
+
+ start(props).sql("select id, col0 from " + MULTI_TENANT_VIEW2 + " where col0 > 1")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ID=[$1], COL0=[CAST($0):INTEGER])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_VIEW2]], filter=[>(CAST($0):INTEGER, 1)])\n")
+ .resultIs(new Object[][] {
+ {"5", 6}})
+ .close();
+
+ start(props).sql("select id, col0 from " + MULTI_TENANT_VIEW2 + " order by col0")
+ .explainIs("PhoenixToEnumerableConverter\n" +
+ " PhoenixServerProject(ID=[$1], COL0=[CAST($0):INTEGER])\n" +
+ " PhoenixTableScan(table=[[phoenix, IDX_MULTITENANT_TEST_VIEW2]], scanOrder=[FORWARD])\n")
+ .resultIs(new Object[][] {
+ {"5", 6}})
+ .close();
+ }
+
+}