http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bf95354/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java new file mode 100644 index 0000000..695225f --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java @@ -0,0 +1,880 @@ +package org.apache.phoenix.end2end.index; + +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.assertNull; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + +import java.math.BigDecimal; +import java.sql.Connection; +import java.sql.Date; +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.Collection; +import java.util.Properties; + +import org.apache.hadoop.hbase.HTableDescriptor; +import org.apache.phoenix.compile.ColumnResolver; +import org.apache.phoenix.compile.FromCompiler; +import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT; +import org.apache.phoenix.exception.SQLExceptionCode; +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.parse.NamedTableNode; +import org.apache.phoenix.parse.TableName; +import org.apache.phoenix.query.BaseTest; +import org.apache.phoenix.schema.PTable; +import org.apache.phoenix.util.PropertiesUtil; +import org.apache.phoenix.util.QueryUtil; +import org.apache.phoenix.util.SchemaUtil; +import org.apache.phoenix.util.TestUtil; +import org.junit.Ignore; +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 IndexIT extends BaseHBaseManagedTimeIT { + + private final boolean localIndex; + private final String tableDDLOptions; + + public IndexIT(boolean localIndex, boolean mutable) { + this.localIndex = localIndex; + StringBuilder optionBuilder = new StringBuilder(); + if (!mutable) + optionBuilder.append(" IMMUTABLE_ROWS=true "); + this.tableDDLOptions = optionBuilder.toString(); + } + + @Parameters(name="localIndex = {0} , mutable = {1}") + public static Collection<Boolean[]> data() { + return Arrays.asList(new Boolean[][] { + { false, false }, { false, true }, + { true, false }, { true, true } + }); + } + + @Test + public void testIndexWithNullableFixedWithCols() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions; + Statement stmt = conn.createStatement(); + stmt.execute(ddl); + BaseTest.populateTestTable(fullTableName); + ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + + " (char_col1 ASC, int_col1 ASC)" + + " INCLUDE (long_col1, long_col2)"; + stmt.execute(ddl); + + String query = "SELECT d.char_col1, int_col1 from " + fullTableName + " as d"; + ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex) { + assertEquals( + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + tableName + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT", + QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals("chara", rs.getString("char_col1")); + assertEquals(2, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(3, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(4, rs.getInt(2)); + assertFalse(rs.next()); + + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName); + + query = "SELECT char_col1, int_col1 from " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + + query = "SELECT char_col1, int_col1 from "+indexName; + try{ + rs = conn.createStatement().executeQuery(query); + fail(); + } catch (SQLException e) { + assertEquals(SQLExceptionCode.TABLE_UNDEFINED.getErrorCode(), e.getErrorCode()); + } + } + } + + @Test + @Ignore("Failing due to zero byte incorrectly being stripped from row key") // FIXME: fixed in master, so remove this ignore tag when merged. + public void testDeleteFromAllPKColumnIndex() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions; + Statement stmt = conn.createStatement(); + stmt.execute(ddl); + BaseTest.populateTestTable(fullTableName); + ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + + " (long_pk, varchar_pk)" + + " INCLUDE (long_col1, long_col2)"; + stmt.execute(ddl); + + ResultSet rs; + + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullTableName); + assertTrue(rs.next()); + assertEquals(3,rs.getInt(1)); + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexName); + assertTrue(rs.next()); + assertEquals(3,rs.getInt(1)); + + String dml = "DELETE from " + fullTableName + " WHERE long_col2 = 4"; + assertEquals(1,conn.createStatement().executeUpdate(dml)); + conn.commit(); + + String query = "SELECT /*+ NO_INDEX */ long_pk FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(1L, rs.getLong(1)); + assertTrue(rs.next()); + assertEquals(3L, rs.getLong(1)); + assertFalse(rs.next()); + + query = "SELECT long_pk FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(1L, rs.getLong(1)); + assertTrue(rs.next()); + assertEquals(3L, rs.getLong(1)); + assertFalse(rs.next()); + + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(1L, rs.getLong(1)); + assertTrue(rs.next()); + assertEquals(3L, rs.getLong(1)); + assertFalse(rs.next()); + + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName); + } + } + + @Test + public void testDeleteFromNonPKColumnIndex() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions; + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + Statement stmt = conn.createStatement(); + stmt.execute(ddl); + BaseTest.populateTestTable(fullTableName); + ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + + " (long_col1, long_col2)" + + " INCLUDE (decimal_col1, decimal_col2)"; + stmt.execute(ddl); + } + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + ResultSet rs; + + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullTableName); + assertTrue(rs.next()); + assertEquals(3,rs.getInt(1)); + rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexName); + assertTrue(rs.next()); + assertEquals(3,rs.getInt(1)); + + String dml = "DELETE from " + fullTableName + " WHERE long_col2 = 4"; + assertEquals(1,conn.createStatement().executeUpdate(dml)); + conn.commit(); + + // query the data table + String query = "SELECT /*+ NO_INDEX */ long_pk FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(1L, rs.getLong(1)); + assertTrue(rs.next()); + assertEquals(3L, rs.getLong(1)); + assertFalse(rs.next()); + + // query the index table + query = "SELECT long_pk FROM " + fullTableName + " ORDER BY long_col1"; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(1L, rs.getLong(1)); + assertTrue(rs.next()); + assertEquals(3L, rs.getLong(1)); + assertFalse(rs.next()); + + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName); + } + } + + @Test + public void testGroupByCount() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions; + Statement stmt = conn.createStatement(); + stmt.execute(ddl); + BaseTest.populateTestTable(fullTableName); + ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col2)"; + stmt.execute(ddl); + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT int_col2, COUNT(*) FROM " + fullTableName + " GROUP BY int_col2"); + assertTrue(rs.next()); + assertEquals(1,rs.getInt(2)); + } + } + + @Test + public void testSelectDistinctOnTableWithSecondaryImmutableIndex() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions; + Statement stmt = conn.createStatement(); + stmt.execute(ddl); + BaseTest.populateTestTable(fullTableName); + ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col2)"; + PreparedStatement pstmt = conn.prepareStatement(ddl); + pstmt.execute(); + ResultSet rs = conn.createStatement().executeQuery("SELECT distinct int_col2 FROM " + fullTableName + " where int_col2 > 0"); + assertTrue(rs.next()); + assertEquals(3, rs.getInt(1)); + assertTrue(rs.next()); + assertEquals(4, rs.getInt(1)); + assertTrue(rs.next()); + assertEquals(5, rs.getInt(1)); + assertFalse(rs.next()); + } + } + + @Test + public void testInClauseWithIndexOnColumnOfUsignedIntType() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions; + Statement stmt = conn.createStatement(); + stmt.execute(ddl); + BaseTest.populateTestTable(fullTableName); + ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col1)"; + stmt.execute(ddl); + ResultSet rs = conn.createStatement().executeQuery("SELECT int_col1 FROM " + fullTableName + " where int_col1 IN (1, 2, 3, 4)"); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + assertTrue(rs.next()); + assertEquals(3, rs.getInt(1)); + assertTrue(rs.next()); + assertEquals(4, rs.getInt(1)); + assertFalse(rs.next()); + } + } + + @Test + public void createIndexOnTableWithSpecifiedDefaultCF() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + String ddl ="CREATE TABLE " + fullTableName + + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) DEFAULT_COLUMN_FAMILY='A'" + (!tableDDLOptions.isEmpty() ? "," + tableDDLOptions : ""); + Statement stmt = conn.createStatement(); + stmt.execute(ddl); + + query = "SELECT * FROM " + tableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + String options = localIndex ? "SALT_BUCKETS=10, MULTI_TENANT=true, IMMUTABLE_ROWS=true, DISABLE_WAL=true" : ""; + conn.createStatement().execute( + "CREATE INDEX " + indexName + " ON " + fullTableName + " (v1) INCLUDE (v2) " + options); + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + //check options set correctly on index + TableName indexTableName = TableName.create(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + NamedTableNode indexNode = NamedTableNode.create(null, indexTableName, null); + ColumnResolver resolver = FromCompiler.getResolver(indexNode, conn.unwrap(PhoenixConnection.class)); + PTable indexTable = resolver.getTables().get(0).getTable(); + // Can't set IMMUTABLE_ROWS, MULTI_TENANT or DEFAULT_COLUMN_FAMILY_NAME on an index + assertNull(indexTable.getDefaultFamilyName()); + assertFalse(indexTable.isMultiTenant()); + assertFalse(indexTable.isImmutableRows()); + if(localIndex) { + assertEquals(10, indexTable.getBucketNum().intValue()); + assertTrue(indexTable.isWALDisabled()); + } + } + } + + @Test + public void testIndexWithNullableDateCol() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + Date date = new Date(System.currentTimeMillis()); + + createMultiCFTestTable(fullTableName, tableDDLOptions); + populateMultiCFTestTable(fullTableName, date); + String ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (date_col)"; + PreparedStatement stmt = conn.prepareStatement(ddl); + stmt.execute(); + + String query = "SELECT int_pk from " + fullTableName ; + ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if (localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName +" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + assertTrue(rs.next()); + assertEquals(3, rs.getInt(1)); + assertFalse(rs.next()); + + query = "SELECT date_col from " + fullTableName + " order by date_col" ; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if (localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(date, rs.getDate(1)); + assertTrue(rs.next()); + assertEquals(new Date(date.getTime() + TestUtil.MILLIS_IN_DAY), rs.getDate(1)); + assertTrue(rs.next()); + assertEquals(new Date(date.getTime() + 2 * TestUtil.MILLIS_IN_DAY), rs.getDate(1)); + assertFalse(rs.next()); + } + } + + @Test + public void testSelectAllAndAliasWithIndex() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + + String ddl = "CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) " + tableDDLOptions; + conn.createStatement().execute(ddl); + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v2 DESC) INCLUDE (v1)"; + conn.createStatement().execute(ddl); + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); + stmt.setString(1,"a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + stmt.setString(1,"b"); + stmt.setString(2, "y"); + stmt.setString(3, "2"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex){ + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("b",rs.getString(1)); + assertEquals("y",rs.getString(2)); + assertEquals("2",rs.getString(3)); + assertEquals("b",rs.getString("k")); + assertEquals("y",rs.getString("v1")); + assertEquals("2",rs.getString("v2")); + assertTrue(rs.next()); + assertEquals("a",rs.getString(1)); + assertEquals("x",rs.getString(2)); + assertEquals("1",rs.getString(3)); + assertEquals("a",rs.getString("k")); + assertEquals("x",rs.getString("v1")); + assertEquals("1",rs.getString("v2")); + assertFalse(rs.next()); + + query = "SELECT v1 as foo FROM " + fullTableName + " WHERE v2 = '1' ORDER BY foo"; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex){ + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" +fullTableName + " [-32768,~'1']\n" + + " SERVER SORTED BY [\"V1\"]\n" + + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " +fullIndexName + " [~'1']\n" + + " SERVER SORTED BY [\"V1\"]\n" + + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("x",rs.getString(1)); + assertEquals("x",rs.getString("foo")); + assertFalse(rs.next()); + } + } + + @Test + public void testSelectCF() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + + String ddl = "CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, a.v1 VARCHAR, a.v2 VARCHAR, b.v1 VARCHAR) " + tableDDLOptions; + conn.createStatement().execute(ddl); + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v2 DESC) INCLUDE (a.v1)"; + conn.createStatement().execute(ddl); + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?,?)"); + stmt.setString(1,"a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.setString(4, "A"); + stmt.execute(); + stmt.setString(1,"b"); + stmt.setString(2, "y"); + stmt.setString(3, "2"); + stmt.setString(4, "B"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullTableName, QueryUtil.getExplainPlan(rs)); + + query = "SELECT a.* FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); + } + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("y",rs.getString(1)); + assertEquals("2",rs.getString(2)); + assertEquals("y",rs.getString("v1")); + assertEquals("2",rs.getString("v2")); + assertTrue(rs.next()); + assertEquals("x",rs.getString(1)); + assertEquals("1",rs.getString(2)); + assertEquals("x",rs.getString("v1")); + assertEquals("1",rs.getString("v2")); + assertFalse(rs.next()); + } + } + + @Test + public void testUpsertAfterIndexDrop() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + + // make sure that the tables are empty, but reachable + conn.createStatement().execute( + "CREATE TABLE " + fullTableName + + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions); + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + conn.createStatement().execute( + "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + " (v1, v2)"); + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + // load some data into the table + PreparedStatement stmt = + conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + conn.commit(); + + // make sure the index is working as expected + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("x", rs.getString(1)); + assertEquals("1", rs.getString(2)); + assertEquals("a", rs.getString(3)); + assertFalse(rs.next()); + + String ddl = "DROP INDEX " + indexName + " ON " + fullTableName; + stmt = conn.prepareStatement(ddl); + stmt.execute(); + + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k, v1) VALUES(?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "y"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullTableName; + + // check that the data table matches as expected + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("a", rs.getString(1)); + assertEquals("y", rs.getString(2)); + assertFalse(rs.next()); + } + } + + @Test + public void testMultipleUpdatesAcrossRegions() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + + // make sure that the tables are empty, but reachable + conn.createStatement().execute( + "CREATE TABLE " + fullTableName + + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) " + HTableDescriptor.MAX_FILESIZE + "=1, " + HTableDescriptor.MEMSTORE_FLUSHSIZE + "=1 " + + (!tableDDLOptions.isEmpty() ? "," + tableDDLOptions : "") + "SPLIT ON ('b')"); + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + conn.createStatement().execute( + "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + " (v1, v2)"); + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + // load some data into the table + PreparedStatement stmt = + conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + stmt.setString(1, "b"); + stmt.setString(2, "y"); + stmt.setString(3, "2"); + stmt.execute(); + stmt.setString(1, "c"); + stmt.setString(2, "z"); + stmt.setString(3, "3"); + stmt.execute(); + conn.commit(); + + // make sure the index is working as expected + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("x", rs.getString(1)); + assertEquals("1", rs.getString(2)); + assertEquals("a", rs.getString(3)); + assertTrue(rs.next()); + assertEquals("y", rs.getString(1)); + assertEquals("2", rs.getString(2)); + assertEquals("b", rs.getString(3)); + assertTrue(rs.next()); + assertEquals("z", rs.getString(1)); + assertEquals("3", rs.getString(2)); + assertEquals("c", rs.getString(3)); + assertFalse(rs.next()); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if (localIndex) { + assertEquals("CLIENT PARALLEL 2-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT", + QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", + QueryUtil.getExplainPlan(rs)); + } + + // check that the data table matches as expected + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("a", rs.getString(1)); + assertEquals("x", rs.getString(2)); + assertEquals("1", rs.getString(3)); + assertTrue(rs.next()); + assertEquals("b", rs.getString(1)); + assertEquals("y", rs.getString(2)); + assertEquals("2", rs.getString(3)); + assertTrue(rs.next()); + assertEquals("c", rs.getString(1)); + assertEquals("z", rs.getString(2)); + assertEquals("3", rs.getString(3)); + assertFalse(rs.next()); + } + } + + @Test + public void testIndexWithCaseSensitiveCols() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + + conn.createStatement().execute("CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, \"V1\" VARCHAR, \"v2\" VARCHAR)"+tableDDLOptions); + query = "SELECT * FROM "+fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + conn.createStatement().execute( + "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + "(\"v2\") INCLUDE (\"V1\")"); + query = "SELECT * FROM "+fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); + stmt.setString(1,"a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + stmt.setString(1,"b"); + stmt.setString(2, "y"); + stmt.setString(3, "2"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullTableName + " WHERE \"v2\" = '1'"; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex){ + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName + " [-32768,'1']\n" + + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullIndexName + " ['1']", QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("a",rs.getString(1)); + assertEquals("x",rs.getString(2)); + assertEquals("1",rs.getString(3)); + assertEquals("a",rs.getString("k")); + assertEquals("x",rs.getString("V1")); + assertEquals("1",rs.getString("v2")); + assertFalse(rs.next()); + + query = "SELECT \"V1\", \"V1\" as foo1, \"v2\" as foo, \"v2\" as \"Foo1\", \"v2\" FROM " + fullTableName + " ORDER BY foo"; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex){ + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName + " [-32768]\nCLIENT MERGE SORT", + QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER "+fullIndexName, QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("x",rs.getString(1)); + assertEquals("x",rs.getString("V1")); + assertEquals("x",rs.getString(2)); + assertEquals("x",rs.getString("foo1")); + assertEquals("1",rs.getString(3)); + assertEquals("1",rs.getString("Foo")); + assertEquals("1",rs.getString(4)); + assertEquals("1",rs.getString("Foo1")); + assertEquals("1",rs.getString(5)); + assertEquals("1",rs.getString("v2")); + assertTrue(rs.next()); + assertEquals("y",rs.getString(1)); + assertEquals("y",rs.getString("V1")); + assertEquals("y",rs.getString(2)); + assertEquals("y",rs.getString("foo1")); + assertEquals("2",rs.getString(3)); + assertEquals("2",rs.getString("Foo")); + assertEquals("2",rs.getString(4)); + assertEquals("2",rs.getString("Foo1")); + assertEquals("2",rs.getString(5)); + assertEquals("2",rs.getString("v2")); + assertFalse(rs.next()); + } + } + + @Test + public void testInFilterOnIndexedTable() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + + String ddl = "CREATE TABLE " + fullTableName +" (PK1 CHAR(2) NOT NULL PRIMARY KEY, CF1.COL1 BIGINT) " + tableDDLOptions; + conn.createStatement().execute(ddl); + ddl = "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + "(COL1)"; + conn.createStatement().execute(ddl); + + query = "SELECT COUNT(COL1) FROM " + fullTableName +" WHERE COL1 IN (1,25,50,75,100)"; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + } + } + + @Test + public void testIndexWithDecimalCol() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + Date date = new Date(System.currentTimeMillis()); + + createMultiCFTestTable(fullTableName, tableDDLOptions); + populateMultiCFTestTable(fullTableName, date); + String ddl = null; + ddl = "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + " (decimal_pk) INCLUDE (decimal_col1, decimal_col2)"; + PreparedStatement stmt = conn.prepareStatement(ddl); + stmt.execute(); + + query = "SELECT decimal_pk, decimal_col1, decimal_col2 from " + fullTableName ; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(new BigDecimal("1.1"), rs.getBigDecimal(1)); + assertEquals(new BigDecimal("2.1"), rs.getBigDecimal(2)); + assertEquals(new BigDecimal("3.1"), rs.getBigDecimal(3)); + assertTrue(rs.next()); + assertEquals(new BigDecimal("2.2"), rs.getBigDecimal(1)); + assertEquals(new BigDecimal("3.2"), rs.getBigDecimal(2)); + assertEquals(new BigDecimal("4.2"), rs.getBigDecimal(3)); + assertTrue(rs.next()); + assertEquals(new BigDecimal("3.3"), rs.getBigDecimal(1)); + assertEquals(new BigDecimal("4.3"), rs.getBigDecimal(2)); + assertEquals(new BigDecimal("5.3"), rs.getBigDecimal(3)); + assertFalse(rs.next()); + } + } + +} \ No newline at end of file
http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bf95354/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableIndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableIndexIT.java deleted file mode 100644 index fe17dbc..0000000 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableIndexIT.java +++ /dev/null @@ -1,26 +0,0 @@ -/* - * 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.end2end.index; - -public class LocalMutableIndexIT extends BaseMutableIndexIT { - - public LocalMutableIndexIT() { - super(true); - } - -} http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bf95354/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/MutableIndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/MutableIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/MutableIndexIT.java new file mode 100644 index 0000000..b269212 --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/MutableIndexIT.java @@ -0,0 +1,599 @@ +/* + * 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.end2end.index; + +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.assertNull; +import static org.junit.Assert.assertTrue; + +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.Collection; +import java.util.Properties; + +import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT; +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.schema.PTableKey; +import org.apache.phoenix.util.PropertiesUtil; +import org.apache.phoenix.util.QueryUtil; +import org.apache.phoenix.util.SchemaUtil; +import org.apache.phoenix.util.TestUtil; +import org.junit.Test; +import org.junit.runner.RunWith; +import org.junit.runners.Parameterized; +import org.junit.runners.Parameterized.Parameters; + +import com.google.common.primitives.Doubles; + +@RunWith(Parameterized.class) +public class MutableIndexIT extends BaseHBaseManagedTimeIT { + + protected final boolean localIndex; + private final String tableDDLOptions; + + public MutableIndexIT(boolean localIndex) { + this.localIndex = localIndex; + StringBuilder optionBuilder = new StringBuilder(); + this.tableDDLOptions = optionBuilder.toString(); + } + + @Parameters(name="localIndex = {0}") + public static Collection<Boolean[]> data() { + return Arrays.asList(new Boolean[][] { + { false }, {true} + }); + } + + @Test + public void testCoveredColumnUpdates() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + + createMultiCFTestTable(fullTableName, tableDDLOptions); + populateMultiCFTestTable(fullTableName); + PreparedStatement stmt = conn.prepareStatement("CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + + " (char_col1 ASC, int_col1 ASC) INCLUDE (long_col1, long_col2)"); + stmt.execute(); + + String query = "SELECT char_col1, int_col1, long_col2 from " + fullTableName; + ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if (localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName +" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(2, rs.getInt(2)); + assertEquals(3L, rs.getLong(3)); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(3, rs.getInt(2)); + assertEquals(4L, rs.getLong(3)); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(4, rs.getInt(2)); + assertEquals(5L, rs.getLong(3)); + assertFalse(rs.next()); + + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + + "(varchar_pk, char_pk, int_pk, long_pk , decimal_pk, long_col2) SELECT varchar_pk, char_pk, int_pk, long_pk , decimal_pk, long_col2*2 FROM " + + fullTableName + " WHERE long_col2=?"); + stmt.setLong(1,4L); + assertEquals(1,stmt.executeUpdate()); + conn.commit(); + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(2, rs.getInt(2)); + assertEquals(3L, rs.getLong(3)); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(3, rs.getInt(2)); + assertEquals(8L, rs.getLong(3)); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(4, rs.getInt(2)); + assertEquals(5L, rs.getLong(3)); + assertFalse(rs.next()); + + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + + "(varchar_pk, char_pk, int_pk, long_pk , decimal_pk, long_col2) SELECT varchar_pk, char_pk, int_pk, long_pk , decimal_pk, null FROM " + + fullTableName + " WHERE long_col2=?"); + stmt.setLong(1,3L); + assertEquals(1,stmt.executeUpdate()); + conn.commit(); + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(2, rs.getInt(2)); + assertEquals(0, rs.getLong(3)); + assertTrue(rs.wasNull()); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(3, rs.getInt(2)); + assertEquals(8L, rs.getLong(3)); + assertTrue(rs.next()); + assertEquals("chara", rs.getString(1)); + assertEquals(4, rs.getInt(2)); + assertEquals(5L, rs.getLong(3)); + assertFalse(rs.next()); + if(localIndex) { + query = "SELECT b.* from " + fullTableName + " where int_col1 = 4"; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName +" [-32768]\n" + + " SERVER FILTER BY TO_INTEGER(\"INT_COL1\") = 4\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("varchar_b", rs.getString(1)); + assertEquals("charb", rs.getString(2)); + assertEquals(5, rs.getInt(3)); + assertEquals(5, rs.getLong(4)); + assertFalse(rs.next()); + + } + } + } + + @Test + public void testCoveredColumns() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + conn.createStatement().execute("CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions); + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + conn.createStatement().execute("CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v1) INCLUDE (v2)"); + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); + stmt.setString(1,"a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("x",rs.getString(1)); + assertEquals("a",rs.getString(2)); + assertEquals("1",rs.getString(3)); + assertFalse(rs.next()); + + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k,v2) VALUES(?,?)"); + stmt.setString(1,"a"); + stmt.setString(2, null); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("x",rs.getString(1)); + assertEquals("a",rs.getString(2)); + assertNull(rs.getString(3)); + assertFalse(rs.next()); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("a",rs.getString(1)); + assertEquals("x",rs.getString(2)); + assertNull(rs.getString(3)); + assertFalse(rs.next()); + + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k,v2) VALUES(?,?)"); + stmt.setString(1,"a"); + stmt.setString(2,"3"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName + " [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("a",rs.getString(1)); + assertEquals("x",rs.getString(2)); + assertEquals("3",rs.getString(3)); + assertFalse(rs.next()); + + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k,v2) VALUES(?,?)"); + stmt.setString(1,"a"); + stmt.setString(2,"4"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs)); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("a",rs.getString(1)); + assertEquals("x",rs.getString(2)); + assertEquals("4",rs.getString(3)); + assertFalse(rs.next()); + } + } + + @Test + public void testCompoundIndexKey() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + + // make sure that the tables are empty, but reachable + conn.createStatement().execute("CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions); + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + conn.createStatement().execute("CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v1, v2)"); + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + // load some data into the table + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); + stmt.setString(1,"a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("x",rs.getString(1)); + assertEquals("1",rs.getString(2)); + assertEquals("a",rs.getString(3)); + assertFalse(rs.next()); + + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); + stmt.setString(1,"a"); + stmt.setString(2, "y"); + stmt.setString(3, null); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("y",rs.getString(1)); + assertNull(rs.getString(2)); + assertEquals("a",rs.getString(3)); + assertFalse(rs.next()); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if (localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); + } + //make sure the data table looks like what we expect + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("a",rs.getString(1)); + assertEquals("y",rs.getString(2)); + assertNull(rs.getString(3)); + assertFalse(rs.next()); + + // Upsert new row with null leading index column + stmt.setString(1,"b"); + stmt.setString(2, null); + stmt.setString(3, "3"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals(null,rs.getString(1)); + assertEquals("3",rs.getString(2)); + assertEquals("b",rs.getString(3)); + assertTrue(rs.next()); + assertEquals("y",rs.getString(1)); + assertNull(rs.getString(2)); + assertEquals("a",rs.getString(3)); + assertFalse(rs.next()); + + // Update row with null leading index column to have a value + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?)"); + stmt.setString(1,"b"); + stmt.setString(2, "z"); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("y",rs.getString(1)); + assertNull(rs.getString(2)); + assertEquals("a",rs.getString(3)); + assertTrue(rs.next()); + assertEquals("z",rs.getString(1)); + assertEquals("3",rs.getString(2)); + assertEquals("b",rs.getString(3)); + assertFalse(rs.next()); + } + + } + + /** + * There was a case where if there were multiple updates to a single row in the same batch, the + * index wouldn't be updated correctly as each element of the batch was evaluated with the state + * previous to the batch, rather than with the rest of the batch. This meant you could do a put + * and a delete on a row in the same batch and the index result would contain the current + put + * and current + delete, but not current + put + delete. + * @throws Exception on failure + */ + @Test + public void testMultipleUpdatesToSingleRow() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + String query; + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + + // make sure that the tables are empty, but reachable + conn.createStatement().execute( + "CREATE TABLE " + fullTableName + + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions); + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + conn.createStatement().execute("CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v1, v2)"); + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + // load some data into the table + PreparedStatement stmt = + conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "x"); + stmt.setString(3, "1"); + stmt.execute(); + conn.commit(); + + // make sure the index is working as expected + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("x", rs.getString(1)); + assertEquals("1", rs.getString(2)); + assertEquals("a", rs.getString(3)); + assertFalse(rs.next()); + + // do multiple updates to the same row, in the same batch + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k, v1) VALUES(?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, "y"); + stmt.execute(); + stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k,v2) VALUES(?,?)"); + stmt.setString(1, "a"); + stmt.setString(2, null); + stmt.execute(); + conn.commit(); + + query = "SELECT * FROM " + fullIndexName; + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("y", rs.getString(1)); + assertNull(rs.getString(2)); + assertEquals("a", rs.getString(3)); + assertFalse(rs.next()); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery("EXPLAIN " + query); + if(localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT", + QueryUtil.getExplainPlan(rs)); + } else { + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", + QueryUtil.getExplainPlan(rs)); + } + + // check that the data table matches as expected + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("a", rs.getString(1)); + assertEquals("y", rs.getString(2)); + assertNull(rs.getString(3)); + assertFalse(rs.next()); + } + } + + @Test + public void testUpsertingNullForIndexedColumns() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + ResultSet rs; + // create unique table and index names for each parameterized test + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String indexName = "IDX" + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexeName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + Statement stmt = conn.createStatement(); + stmt.execute("CREATE TABLE " + fullTableName + "(v1 VARCHAR PRIMARY KEY, v2 DOUBLE, v3 VARCHAR) "+tableDDLOptions); + stmt.execute("CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (v2) INCLUDE(v3)"); + + //create a row with value null for indexed column v2 + stmt.executeUpdate("upsert into " + fullTableName + " values('cc1', null, 'abc')"); + conn.commit(); + + //assert values in index table + rs = stmt.executeQuery("select * from " + fullIndexeName); + assertTrue(rs.next()); + assertEquals(0, Doubles.compare(0, rs.getDouble(1))); + assertTrue(rs.wasNull()); + assertEquals("cc1", rs.getString(2)); + assertEquals("abc", rs.getString(3)); + assertFalse(rs.next()); + + //assert values in data table + rs = stmt.executeQuery("select v1, v2, v3 from " + fullTableName); + assertTrue(rs.next()); + assertEquals("cc1", rs.getString(1)); + assertEquals(0, Doubles.compare(0, rs.getDouble(2))); + assertTrue(rs.wasNull()); + assertEquals("abc", rs.getString(3)); + assertFalse(rs.next()); + + //update the previously null value for indexed column v2 to a non-null value 1.23 + stmt.executeUpdate("upsert into " + fullTableName + " values('cc1', 1.23, 'abc')"); + conn.commit(); + + //assert values in data table + rs = stmt.executeQuery("select /*+ NO_INDEX */ v1, v2, v3 from " + fullTableName); + assertTrue(rs.next()); + assertEquals("cc1", rs.getString(1)); + assertEquals(0, Doubles.compare(1.23, rs.getDouble(2))); + assertEquals("abc", rs.getString(3)); + assertFalse(rs.next()); + + //assert values in index table + rs = stmt.executeQuery("select * from " + fullIndexeName); + assertTrue(rs.next()); + assertEquals(0, Doubles.compare(1.23, rs.getDouble(1))); + assertEquals("cc1", rs.getString(2)); + assertEquals("abc", rs.getString(3)); + assertFalse(rs.next()); + + //update the value for indexed column v2 back to null + stmt.executeUpdate("upsert into " + fullTableName + " values('cc1', null, 'abc')"); + conn.commit(); + + //assert values in index table + rs = stmt.executeQuery("select * from " + fullIndexeName); + assertTrue(rs.next()); + assertEquals(0, Doubles.compare(0, rs.getDouble(1))); + assertTrue(rs.wasNull()); + assertEquals("cc1", rs.getString(2)); + assertEquals("abc", rs.getString(3)); + assertFalse(rs.next()); + + //assert values in data table + rs = stmt.executeQuery("select v1, v2, v3 from " + fullTableName); + assertTrue(rs.next()); + assertEquals("cc1", rs.getString(1)); + assertEquals(0, Doubles.compare(0, rs.getDouble(2))); + assertEquals("abc", rs.getString(3)); + assertFalse(rs.next()); + } + } + + + private void assertImmutableRows(Connection conn, String fullTableName, boolean expectedValue) throws SQLException { + PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class); + assertEquals(expectedValue, pconn.getTable(new PTableKey(pconn.getTenantId(), fullTableName)).isImmutableRows()); + } + + @Test + public void testAlterTableWithImmutability() throws Exception { + String query; + ResultSet rs; + String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + conn.setAutoCommit(false); + conn.createStatement().execute( + "CREATE TABLE " + fullTableName +" (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) " + tableDDLOptions); + + query = "SELECT * FROM " + fullTableName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + assertImmutableRows(conn,fullTableName, false); + conn.createStatement().execute("ALTER TABLE " + fullTableName +" SET IMMUTABLE_ROWS=true"); + assertImmutableRows(conn,fullTableName, true); + + + conn.createStatement().execute("ALTER TABLE " + fullTableName +" SET immutable_rows=false"); + assertImmutableRows(conn,fullTableName, false); + } + } + +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bf95354/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java index 5265afc..1f9b20c 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java @@ -136,6 +136,7 @@ import org.apache.phoenix.schema.PTableType; import org.apache.phoenix.schema.TableAlreadyExistsException; import org.apache.phoenix.schema.TableNotFoundException; import org.apache.phoenix.util.ConfigUtil; +import org.apache.phoenix.util.DateUtil; import org.apache.phoenix.util.PhoenixRuntime; import org.apache.phoenix.util.PropertiesUtil; import org.apache.phoenix.util.QueryUtil; @@ -164,6 +165,26 @@ import com.google.common.collect.Sets; * */ public abstract class BaseTest { + protected static final String TEST_TABLE_SCHEMA = "(" + + " varchar_pk VARCHAR NOT NULL, " + + " char_pk CHAR(6) NOT NULL, " + + " int_pk INTEGER NOT NULL, "+ + " long_pk BIGINT NOT NULL, " + + " decimal_pk DECIMAL(31, 10) NOT NULL, " + + " date_pk DATE NOT NULL, " + + " a.varchar_col1 VARCHAR, " + + " a.char_col1 CHAR(10), " + + " a.int_col1 INTEGER, " + + " a.long_col1 BIGINT, " + + " a.decimal_col1 DECIMAL(31, 10), " + + " a.date1 DATE, " + + " b.varchar_col2 VARCHAR, " + + " b.char_col2 CHAR(10), " + + " b.int_col2 INTEGER, " + + " b.long_col2 BIGINT, " + + " b.decimal_col2 DECIMAL(31, 10), " + + " b.date2 DATE " + + " CONSTRAINT pk PRIMARY KEY (varchar_pk, char_pk, int_pk, long_pk DESC, decimal_pk, date_pk)) "; private static final Map<String,String> tableDDLMap; private static final Logger logger = LoggerFactory.getLogger(BaseTest.class); @@ -1714,7 +1735,81 @@ public abstract class BaseTest { return utility; } - protected static void createMultiCFTestTable(String tableName) throws SQLException { + // Populate the test table with data. + public static void populateTestTable(String fullTableName) throws SQLException { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + try (Connection conn = DriverManager.getConnection(getUrl(), props)) { + String upsert = "UPSERT INTO " + fullTableName + + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; + PreparedStatement stmt = conn.prepareStatement(upsert); + stmt.setString(1, "varchar1"); + stmt.setString(2, "char1"); + stmt.setInt(3, 1); + stmt.setLong(4, 1L); + stmt.setBigDecimal(5, new BigDecimal(1.0)); + Date date = DateUtil.parseDate("2015-01-01 00:00:00"); + stmt.setDate(6, date); + stmt.setString(7, "varchar_a"); + stmt.setString(8, "chara"); + stmt.setInt(9, 2); + stmt.setLong(10, 2L); + stmt.setBigDecimal(11, new BigDecimal(2.0)); + stmt.setDate(12, date); + stmt.setString(13, "varchar_b"); + stmt.setString(14, "charb"); + stmt.setInt(15, 3); + stmt.setLong(16, 3L); + stmt.setBigDecimal(17, new BigDecimal(3.0)); + stmt.setDate(18, date); + stmt.executeUpdate(); + + stmt.setString(1, "varchar2"); + stmt.setString(2, "char2"); + stmt.setInt(3, 2); + stmt.setLong(4, 2L); + stmt.setBigDecimal(5, new BigDecimal(2.0)); + date = DateUtil.parseDate("2015-01-02 00:00:00"); + stmt.setDate(6, date); + stmt.setString(7, "varchar_a"); + stmt.setString(8, "chara"); + stmt.setInt(9, 3); + stmt.setLong(10, 3L); + stmt.setBigDecimal(11, new BigDecimal(3.0)); + stmt.setDate(12, date); + stmt.setString(13, "varchar_b"); + stmt.setString(14, "charb"); + stmt.setInt(15, 4); + stmt.setLong(16, 4L); + stmt.setBigDecimal(17, new BigDecimal(4.0)); + stmt.setDate(18, date); + stmt.executeUpdate(); + + stmt.setString(1, "varchar3"); + stmt.setString(2, "char3"); + stmt.setInt(3, 3); + stmt.setLong(4, 3L); + stmt.setBigDecimal(5, new BigDecimal(3.0)); + date = DateUtil.parseDate("2015-01-03 00:00:00"); + stmt.setDate(6, date); + stmt.setString(7, "varchar_a"); + stmt.setString(8, "chara"); + stmt.setInt(9, 4); + stmt.setLong(10, 4L); + stmt.setBigDecimal(11, new BigDecimal(4.0)); + stmt.setDate(12, date); + stmt.setString(13, "varchar_b"); + stmt.setString(14, "charb"); + stmt.setInt(15, 5); + stmt.setLong(16, 5L); + stmt.setBigDecimal(17, new BigDecimal(5.0)); + stmt.setDate(18, date); + stmt.executeUpdate(); + + conn.commit(); + } + } + + protected static void createMultiCFTestTable(String tableName, String options) throws SQLException { String ddl = "create table if not exists " + tableName + "(" + " varchar_pk VARCHAR NOT NULL, " + " char_pk CHAR(5) NOT NULL, " + @@ -1732,13 +1827,14 @@ public abstract class BaseTest { " b.long_col2 BIGINT, " + " b.decimal_col2 DECIMAL, " + " b.date_col DATE " + - " CONSTRAINT pk PRIMARY KEY (varchar_pk, char_pk, int_pk, long_pk DESC, decimal_pk))"; + " CONSTRAINT pk PRIMARY KEY (varchar_pk, char_pk, int_pk, long_pk DESC, decimal_pk)) " + + (options!=null? options : ""); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.createStatement().execute(ddl); conn.close(); } - + // Populate the test table with data. protected static void populateMultiCFTestTable(String tableName) throws SQLException { populateMultiCFTestTable(tableName, null);
