/* * 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.INDEX_DATA_SCHEMA; import static org.apache.phoenix.util.TestUtil.INDEX_DATA_TABLE; 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.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.util.Properties; import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT; import org.apache.phoenix.exception.SQLExceptionCode; import org.apache.phoenix.jdbc.PhoenixConnection; import org.apache.phoenix.query.QueryConstants; import org.apache.phoenix.schema.PTableKey; import org.apache.phoenix.util.DateUtil; import org.apache.phoenix.util.PropertiesUtil; import org.apache.phoenix.util.QueryUtil; import org.junit.Test; public class ImmutableIndexIT extends BaseHBaseManagedTimeIT { // Populate the test table with data. private static void populateTestTable() throws SQLException { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { String upsert = "UPSERT INTO " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " 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(); } finally { conn.close(); } } @Test public void testIndexWithNullableFixedWithCols() throws Exception { testIndexWithNullableFixedWithCols(false); } @Test public void testLocalIndexWithNullableFixedWithCols() throws Exception { testIndexWithNullableFixedWithCols(true); } public void testIndexWithNullableFixedWithCols(boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); ensureTableCreated(getUrl(), INDEX_DATA_TABLE); populateTestTable(); String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " (char_col1 ASC, int_col1 ASC)" + " INCLUDE (long_col1, long_col2)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); String query = "SELECT char_col1, int_col1 from " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE; ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query); if(localIndex) { assertEquals( "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_INDEX_TEST.INDEX_DATA_TABLE [-32768]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); } else { assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST.IDX\n" + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); } rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); assertEquals("chara", rs.getString(1)); 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 IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE); query = "SELECT char_col1, int_col1 from " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE; rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); query = "SELECT char_col1, int_col1 from IDX "; try{ rs = conn.createStatement().executeQuery(query); fail(); } catch (SQLException e) { assertEquals(SQLExceptionCode.TABLE_UNDEFINED.getErrorCode(), e.getErrorCode()); } } private void assertImmutableRows(Connection conn, String fullTableName, boolean expectedValue) throws SQLException { PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class); assertEquals(expectedValue, pconn.getMetaDataCache().getTable(new PTableKey(pconn.getTenantId(), fullTableName)).isImmutableRows()); } @Test public void testAlterTableWithImmutability() throws Exception { String query; ResultSet rs; String fullTableName = "T"; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); conn.createStatement().execute( "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) "); query = "SELECT * FROM t"; rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); assertImmutableRows(conn,fullTableName, false); conn.createStatement().execute("ALTER TABLE t SET IMMUTABLE_ROWS=true"); assertImmutableRows(conn,fullTableName, true); conn.createStatement().execute("ALTER TABLE t SET immutable_rows=false"); assertImmutableRows(conn,fullTableName, false); } @Test public void testDeleteFromAllPKColumnIndex() throws Exception { testDeleteFromAllPKColumnIndex(false); } @Test public void testDeleteFromAllPKColumnLocalIndex() throws Exception { testDeleteFromAllPKColumnIndex(true); } public void testDeleteFromAllPKColumnIndex(boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); ensureTableCreated(getUrl(), INDEX_DATA_TABLE); populateTestTable(); String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " (long_pk, varchar_pk)" + " INCLUDE (long_col1, long_col2)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); ResultSet rs; rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE); assertTrue(rs.next()); assertEquals(3,rs.getInt(1)); rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + "IDX"); assertTrue(rs.next()); assertEquals(3,rs.getInt(1)); String dml = "DELETE from " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " WHERE long_col2 = 4"; assertEquals(1,conn.createStatement().executeUpdate(dml)); conn.commit(); String query = "SELECT /*+ NO_INDEX */ long_pk FROM " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE; 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 " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE; 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 " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + "IDX" ; 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 IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE); } @Test public void testDropIfImmutableKeyValueColumn() throws Exception { testDropIfImmutableKeyValueColumn(false); } @Test public void testDropIfImmutableKeyValueColumnWithLocalIndex() throws Exception { testDropIfImmutableKeyValueColumn(true); } public void testDropIfImmutableKeyValueColumn(boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); ensureTableCreated(getUrl(), INDEX_DATA_TABLE); populateTestTable(); String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " (long_col1)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); ResultSet rs; rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE); assertTrue(rs.next()); assertEquals(3,rs.getInt(1)); rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + "IDX"); assertTrue(rs.next()); assertEquals(3,rs.getInt(1)); conn.setAutoCommit(true); String dml = "DELETE from " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " WHERE long_col2 = 4"; try { conn.createStatement().execute(dml); fail(); } catch (SQLException e) { assertEquals(SQLExceptionCode.INVALID_FILTER_ON_IMMUTABLE_ROWS.getErrorCode(), e.getErrorCode()); } conn.createStatement().execute("DROP TABLE " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE); } @Test public void testGroupByCount() throws Exception { testGroupByCount(false); } @Test public void testGroupByCountWithLocalIndex() throws Exception { testGroupByCount(true); } public void testGroupByCount(boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); ensureTableCreated(getUrl(), INDEX_DATA_TABLE); populateTestTable(); String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " (int_col2)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); ResultSet rs; rs = conn.createStatement().executeQuery("SELECT int_col2, COUNT(*) FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " GROUP BY int_col2"); assertTrue(rs.next()); assertEquals(1,rs.getInt(2)); } @Test public void testSelectDistinctOnTableWithSecondaryImmutableIndex() throws Exception { testSelectDistinctOnTableWithSecondaryImmutableIndex(false); } @Test public void testSelectDistinctOnTableWithSecondaryImmutableLocalIndex() throws Exception { testSelectDistinctOnTableWithSecondaryImmutableIndex(true); } public void testSelectDistinctOnTableWithSecondaryImmutableIndex(boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); ensureTableCreated(getUrl(), INDEX_DATA_TABLE); populateTestTable(); String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " (int_col2)"; Connection conn = null; PreparedStatement stmt = null; try { try { conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); stmt = conn.prepareStatement(ddl); stmt.execute(); ResultSet rs = conn.createStatement().executeQuery("SELECT distinct int_col2 FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " 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()); } finally { if (stmt != null) { stmt.close(); } } } finally { if (conn != null) { conn.close(); } } } @Test public void testInClauseWithIndexOnColumnOfUsignedIntType() throws Exception { testInClauseWithIndexOnColumnOfUsignedIntType(false); } @Test public void testInClauseWithLocalIndexOnColumnOfUsignedIntType() throws Exception { testInClauseWithIndexOnColumnOfUsignedIntType(true); } public void testInClauseWithIndexOnColumnOfUsignedIntType(boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = null; PreparedStatement stmt = null; ensureTableCreated(getUrl(), INDEX_DATA_TABLE); populateTestTable(); String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " (int_col1)"; try { try { conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); stmt = conn.prepareStatement(ddl); stmt.execute(); ResultSet rs = conn.createStatement().executeQuery("SELECT int_col1 FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " 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()); } finally { if(stmt != null) { stmt.close(); } } } finally { if(conn != null) { conn.close(); } } } }