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 <[email protected]> Authored: Wed Dec 16 17:23:51 2015 -0500 Committer: maryannxue <[email protected]> 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(); + } + +}
