Java Code Examples for org.apache.phoenix.query.QueryConstants#NAME_SEPARATOR

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 testMappedView() throws Exception {
    Properties props = new Properties();
    String schema = generateUniqueName();
    String tableName = generateUniqueName();
    String fullTablename = schema + QueryConstants.NAME_SEPARATOR + tableName;
    props.setProperty(QueryServices.SCHEMA_ATTRIB, schema);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    Admin admin = driver.getConnectionQueryServices(getUrl(), TestUtil.TEST_PROPERTIES).getAdmin();
    admin.createNamespace(NamespaceDescriptor.create(schema).build());
    admin.createTable(TableDescriptorBuilder.newBuilder(TableName.valueOf(fullTablename)).
            addColumnFamily(ColumnFamilyDescriptorBuilder.of(QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES)).build());
    Put put = new Put(PVarchar.INSTANCE.toBytes(fullTablename));
    put.addColumn(QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES, QueryConstants.EMPTY_COLUMN_BYTES,
            QueryConstants.EMPTY_COLUMN_VALUE_BYTES);
    Table phoenixSchematable = admin.getConnection().getTable(TableName.valueOf(fullTablename));
    phoenixSchematable.put(put);
    phoenixSchematable.close();
    conn.createStatement().execute("CREATE VIEW " + tableName + " (tablename VARCHAR PRIMARY KEY)");
    ResultSet rs = conn.createStatement().executeQuery("select tablename from " + tableName);
    assertTrue(rs.next());
    assertEquals(fullTablename, rs.getString(1));
    admin.close();
    conn.close();
}
 

public ColumnParseNode(TableName tableName, String name, String alias) {
    // Upper case here so our Maps can depend on this (and we don't have to upper case and create a string on every
    // lookup
    super(name);
    this.alias = alias;
    this.tableName = tableName;
    fullName = tableName == null ? getName() : tableName.toString() + QueryConstants.NAME_SEPARATOR + getName();
}
 

protected void helpTestSelectColOnlyInDataTable(boolean mutable, boolean localIndex) throws Exception {
    String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE;
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        conn.setAutoCommit(false);
        populateDataTable(conn, dataTableName);
        String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName
                + " (int_col1+1)";

        conn = DriverManager.getConnection(getUrl(), props);
        conn.setAutoCommit(false);
        PreparedStatement stmt = conn.prepareStatement(ddl);
        stmt.execute();
        String sql = "SELECT int_col1+1, int_col2 FROM " + fullDataTableName + " WHERE int_col1+1=2";
        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
        assertEquals("CLIENT PARALLEL 1-WAY "
                + (localIndex ? "RANGE SCAN OVER _LOCAL_IDX_" + fullDataTableName
                        + " [-32768,2]\n    SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT" : "FULL SCAN OVER "
                        + fullDataTableName + "\n    SERVER FILTER BY (A.INT_COL1 + 1) = 2"),
                QueryUtil.getExplainPlan(rs));
        rs = conn.createStatement().executeQuery(sql);
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertEquals(1, rs.getInt(2));
        assertFalse(rs.next());
        conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName);
    } finally {
        conn.close();
    }
}
 

protected void helpTestSelectAliasAndOrderByWithIndex(boolean mutable, boolean localIndex) throws Exception {
    String dataTableName = generateUniqueName();
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    String indexName = generateUniqueName();

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        conn.setAutoCommit(false);
        createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true");
        populateDataTable(conn, fullDataTableName);
        String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
                + " (int_col1+1)";

        conn.createStatement().execute(ddl);
        String sql = "SELECT int_col1+1 AS foo FROM " + fullDataTableName + " ORDER BY foo";
        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
        assertEquals("CLIENT PARALLEL 1-WAY "
                + (localIndex ? "RANGE SCAN OVER " + fullDataTableName
                        + " [1]\n    SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT"
                        : "FULL SCAN OVER INDEX_TEST." + indexName + "\n    SERVER FILTER BY FIRST KEY ONLY"),
                QueryUtil.getExplainPlan(rs));
        rs = conn.createStatement().executeQuery(sql);
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDelete() throws Exception {
    String transTableName = generateUniqueName();
    String fullTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + transTableName;
    String selectSQL = "SELECT * FROM " + fullTableName;
    try (Connection conn = DriverManager.getConnection(getUrl());
            Connection conn1 = DriverManager.getConnection(getUrl()); 
            Connection conn2 = DriverManager.getConnection(getUrl())) {
        TestUtil.createTransactionalTable(conn, fullTableName, "TRANSACTION_PROVIDER='"+txProvider+"'");
        conn1.setAutoCommit(false);
        ResultSet rs = conn1.createStatement().executeQuery(selectSQL);
        assertFalse(rs.next());
        
        String upsert = "UPSERT INTO " + fullTableName + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = conn1.prepareStatement(upsert);
        // upsert two rows
        TestUtil.setRowKeyColumns(stmt, 1);
        stmt.execute();
        conn1.commit();
        
        TestUtil.setRowKeyColumns(stmt, 2);
        stmt.execute();
        
        // verify rows can be read even though commit has not been called
        int rowsDeleted = conn1.createStatement().executeUpdate("DELETE FROM " + fullTableName);
        assertEquals(2, rowsDeleted);
        
        // Delete and second upsert not committed yet, so there should be one row.
        rs = conn2.createStatement().executeQuery("SELECT count(*) FROM " + fullTableName);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        
        conn1.commit();
        
        // verify rows are deleted after commit
        rs = conn1.createStatement().executeQuery(selectSQL);
        assertFalse(rs.next());
    }
}
 

public static String getEscapedFullColumnName(String fullColumnName) {
    if(fullColumnName.startsWith(ESCAPE_CHARACTER)) {
        return fullColumnName;
    }
    int index = fullColumnName.indexOf(QueryConstants.NAME_SEPARATOR);
    if (index < 0) {
        return getEscapedArgument(fullColumnName); 
    }
    String columnFamily = fullColumnName.substring(0,index);
    String columnName = fullColumnName.substring(index+1);
    return getEscapedArgument(columnFamily) + QueryConstants.NAME_SEPARATOR + getEscapedArgument(columnName) ;
}
 

/**
 * Normalizes the fulltableName . Uses {@linkplain normalizeIdentifier}
 * @param fullTableName
 * @return
 */
public static String normalizeFullTableName(String fullTableName) {
    String schemaName = SchemaUtil.getSchemaNameFromFullName(fullTableName);
    String tableName = SchemaUtil.getTableNameFromFullName(fullTableName);
    String normalizedTableName = StringUtil.EMPTY_STRING;
    if(!schemaName.isEmpty()) {
        normalizedTableName =  normalizeIdentifier(schemaName) + QueryConstants.NAME_SEPARATOR;
    }
    return normalizedTableName + normalizeIdentifier(tableName);
}
 

private static List<String> getColumnNames(List<Pair<String, String>> columns) {
    List<String> columnNames = new ArrayList<String>(columns.size());
    for (Pair<String, String> col : columns) {
        String familyName = col.getFirst();
        String columnName = col.getSecond();
        if (familyName != null) {
            columnName = familyName + QueryConstants.NAME_SEPARATOR + columnName;
        }
        columnNames.add(columnName);
    }
    return columnNames;
}
 

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();
        }
    }
}
 

@Test
public void testNoConflictDetectionForImmutableRows() throws Exception {
    if (tableDDLOptions.contains("IMMUTABLE_ROWS=true")) {
        // only need to test this for immutable rows
        String transTableName = generateUniqueName();
        String fullTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + transTableName;
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute("create table " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions + (tableDDLOptions.length() > 0 ? "," : "") + "TRANSACTIONAL=true");
        testRowConflicts(fullTableName);
    }
}
 

private static void populateINDEX_DATA_TABLETable(String indexDataTableName) throws SQLException {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    Date date = DateUtil.parseDate("2015-01-01 00:00:00");
    try {
        String upsert = "UPSERT INTO " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexDataTableName
            + " 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));
        stmt.setDate(6, 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));
        stmt.setDate(6, 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));
        stmt.setDate(6, date);
        stmt.executeUpdate();

        conn.commit();
    } finally {
        conn.close();
    }
}
 

public ColumnParseNode(TableName tableName, String name, String alias) {
    // Upper case here so our Maps can depend on this (and we don't have to upper case and create a string on every
    // lookup
    super(name);
    this.alias = alias;
    this.tableName = tableName;
    fullName = tableName == null ? getName() : tableName.toString() + QueryConstants.NAME_SEPARATOR + getName();
}
 

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);
}
 

protected void helpTestSelectDistinct(boolean mutable, boolean localIndex) throws Exception {
    String dataTableName = generateUniqueName();
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    String indexName = generateUniqueName();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        conn.setAutoCommit(false);
        createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true");
        populateDataTable(conn, fullDataTableName);
        String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
                + " (int_col1+1)";
        conn.createStatement().execute(ddl);
        String sql = "SELECT distinct int_col1+1 FROM " + fullDataTableName + " where int_col1+1 > 0";
        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
        String expectedPlan = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
                + (localIndex ? fullDataTableName + " [1,0] - [1,*]"
                        : "INDEX_TEST." + indexName + " [0] - [*]")
                + "\n    SERVER FILTER BY FIRST KEY ONLY\n    SERVER DISTINCT PREFIX FILTER OVER [TO_BIGINT(\"(A.INT_COL1 + 1)\")]\n    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [TO_BIGINT(\"(A.INT_COL1 + 1)\")]"
                + (localIndex ? "\nCLIENT MERGE SORT" : "");
        assertEquals(expectedPlan, QueryUtil.getExplainPlan(rs));
        rs = conn.createStatement().executeQuery(sql);
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

protected void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) throws Exception {
    String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE;
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        conn.setAutoCommit(false);
        populateDataTable(conn, dataTableName);

        // create an expression index
        String ddl = "CREATE "
                + (localIndex ? "LOCAL" : "")
                + " INDEX IDX ON "
                + fullDataTableName
                + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2)),"
                + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )"
                + " INCLUDE (long_col1, long_col2)";
        PreparedStatement stmt = conn.prepareStatement(ddl);
        stmt.execute();

        // run select query with expression in WHERE clause
        String whereSql = "SELECT long_col1, long_col2 from "
                + fullDataTableName
                + " WHERE UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2) = ?"
                + " AND decimal_pk+int_pk+decimal_col2+int_col1=?"
                // since a.date1 and b.date2 are NULLABLE and date is fixed width, these expressions are stored as
                // DECIMAL in the index (which is not fixed width)
                + " AND date_pk+1=? AND date1+1=? AND date2+1=?";
        stmt = conn.prepareStatement(whereSql);
        stmt.setString(1, "VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ");
        stmt.setInt(2, 3);
        Date date = DateUtil.parseDate("2015-01-02 00:00:00");
        stmt.setDate(3, date);
        stmt.setDate(4, date);
        stmt.setDate(5, date);

        // verify that the query does a range scan on the index table
        ResultSet rs = stmt.executeQuery("EXPLAIN " + whereSql);
        assertEquals(
                localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_INDEX_TEST."
                        + dataTableName
                        + " [-32768,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]\nCLIENT MERGE SORT"
                        : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST.IDX ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]",
                QueryUtil.getExplainPlan(rs));

        // verify that the correct results are returned
        rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(1, rs.getInt(2));
        assertFalse(rs.next());

        // verify all rows in data table are present in index table
        String indexSelectSql = "SELECT UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2), "
                + "decimal_pk+int_pk+decimal_col2+int_col1, "
                + "date_pk+1, date1+1, date2+1, "
                + "varchar_pk, char_pk, int_pk, long_pk, decimal_pk, "
                + "long_col1, long_col2 "
                + "from "
                + fullDataTableName;
        rs = conn.createStatement().executeQuery("EXPLAIN " + indexSelectSql);
        assertEquals(localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullDataTableName
                + " [-32768]\nCLIENT MERGE SORT" : "CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST.IDX",
                QueryUtil.getExplainPlan(rs));
        rs = conn.createStatement().executeQuery(indexSelectSql);
        verifyResult(rs, 1);
        verifyResult(rs, 2);

        // Insert two more rows to the index data table
        String upsert = "UPSERT INTO " + fullDataTableName
                + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(upsert);
        insertRow(stmt, 3);
        insertRow(stmt, 4);
        conn.commit();

        rs = conn.createStatement().executeQuery(indexSelectSql);
        verifyResult(rs, 1);
        verifyResult(rs, 2);
        // verify that two rows added after index was created were also added to
        // the index table
        verifyResult(rs, 3);
        verifyResult(rs, 4);

        conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName);
    } finally {
        conn.close();
    }
}
 

protected void helpTestDeleteIndexedExpression(boolean mutable, boolean localIndex) throws Exception {
    String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE;
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + "IDX";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        conn.setAutoCommit(false);
        ensureTableCreated(getUrl(), dataTableName);
        populateDataTable(conn, dataTableName);
        String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName
                + " (2*long_col2)";
        PreparedStatement stmt = conn.prepareStatement(ddl);
        stmt.execute();

        ResultSet rs;
        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullDataTableName);
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName);
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));

        conn.setAutoCommit(true);
        String dml = "DELETE from " + fullDataTableName + " WHERE long_col2 = 2";
        try {
            conn.createStatement().execute(dml);
            if (!mutable) {
                fail();
            }
        } catch (SQLException e) {
            if (!mutable) {
                assertEquals(SQLExceptionCode.INVALID_FILTER_ON_IMMUTABLE_ROWS.getErrorCode(), e.getErrorCode());
            }
        }

        if (!mutable) {
            dml = "DELETE from " + fullDataTableName + " WHERE 2*long_col2 = 4";
            conn.createStatement().execute(dml);
        }

        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullDataTableName);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName);
    } finally {
        conn.close();
    }
}
 

@Test
public void testIndexDefinitionWithNullableFixedWidthColInPK() throws Exception {
	// If we have nullable fixed width column in the PK, we convert those types into a compatible variable type
	// column. The definition is defined in IndexUtil.getIndexColumnDataType.
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);
    try {
        ensureTableCreated(getUrl(), INDEX_DATA_TABLE);
        String ddl = "CREATE INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
                + " (char_col1 ASC, int_col2 ASC, long_col2 DESC)"
                + " INCLUDE (int_col1)";
        PreparedStatement stmt = conn.prepareStatement(ddl);
        stmt.execute();
        
        // Verify the CHAR, INT and LONG are converted to right type.
        ResultSet rs = conn.getMetaData().getIndexInfo(null, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, false, false);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 1, "A:CHAR_COL1", Order.ASC, Types.VARCHAR);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 2, "B:INT_COL2", Order.ASC, Types.DECIMAL);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 3, "B:LONG_COL2", Order.DESC, Types.DECIMAL);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 4, ":VARCHAR_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 5, ":CHAR_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 6, ":INT_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 7, ":LONG_PK", Order.DESC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 8, ":DECIMAL_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 9, ":DATE_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX", 10, "A:INT_COL1", null);
        assertFalse(rs.next());
        
        rs = IndexTestUtil.readDataTableIndexRow(conn, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX");
        assertTrue(rs.next());
        assertEquals("IDX", rs.getString(1));
        assertFalse(rs.next());
        
        ddl = "ALTER INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " UNUSABLE";
        conn.createStatement().execute(ddl);
        // Verify the metadata for index is correct.
        rs = conn.getMetaData().getTables(null, StringUtil.escapeLike(INDEX_DATA_SCHEMA), "IDX", new String[] {PTableType.INDEX.toString()});
        assertTrue(rs.next());
        assertEquals("IDX", rs.getString(3));
        assertEquals(PIndexState.INACTIVE.toString(), rs.getString("INDEX_STATE"));
        assertFalse(rs.next());
        
        ddl = "DROP INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE;
        stmt = conn.prepareStatement(ddl);
        stmt.execute();
        
        // Assert the rows for index table is completely removed.
        rs = conn.getMetaData().getIndexInfo(null, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, false, false);
        assertFalse(rs.next());
        
        // Assert the row in the original data table is removed.
        // Verify that there is a row inserted into the data table for the index table.
        rs = IndexTestUtil.readDataTableIndexRow(conn, INDEX_DATA_SCHEMA, INDEX_DATA_TABLE, "IDX");
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testUpdateCacheForNonTxnSystemTable() throws Exception {
    String fullTableName = "\""+ QueryConstants.SYSTEM_SCHEMA_NAME + "\""+ QueryConstants.NAME_SEPARATOR + generateUniqueName();
    setupSystemTable(fullTableName);
    helpTestUpdateCache(fullTableName, new int[] {0, 0}, false);
}
 

@Test
public void testIndexDefinitionWithNullableFixedWidthColInPK() throws Exception {
	// If we have nullable fixed width column in the PK, we convert those types into a compatible variable type
	// column. The definition is defined in IndexUtil.getIndexColumnDataType.
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String indexDataTable = generateUniqueName();
    String indexName = generateUniqueName();
    conn.setAutoCommit(false);
    try {
        String fullTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexDataTable;
        conn.createStatement().execute("create table " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + "IMMUTABLE_ROWS=true");
        String ddl = "CREATE INDEX " + indexName + " ON " + fullTableName
                + " (char_col1 ASC, int_col2 ASC, long_col2 DESC)"
                + " INCLUDE (int_col1)";
        conn.createStatement().execute(ddl);
        
        // Verify the CHAR, INT and LONG are converted to right type.
        ResultSet rs = conn.getMetaData().getIndexInfo(null, INDEX_DATA_SCHEMA, indexDataTable, false, false);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 1, "A:CHAR_COL1", Order.ASC, Types.VARCHAR);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 2, "B:INT_COL2", Order.ASC, Types.DECIMAL);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 3, "B:LONG_COL2", Order.DESC, Types.DECIMAL);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 4, ":VARCHAR_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 5, ":CHAR_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 6, ":INT_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 7, ":LONG_PK", Order.DESC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 8, ":DECIMAL_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 9, ":DATE_PK", Order.ASC);
        assertIndexInfoMetadata(rs, INDEX_DATA_SCHEMA, indexDataTable, indexName , 10, "A:INT_COL1", null);
        assertFalse(rs.next());
        
        rs = IndexTestUtil.readDataTableIndexRow(conn, INDEX_DATA_SCHEMA, indexDataTable, indexName );
        assertTrue(rs.next());
        assertEquals(indexName , rs.getString(1));
        assertFalse(rs.next());
        
        ddl = "ALTER INDEX " + indexName + " ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexDataTable + " UNUSABLE";
        conn.createStatement().execute(ddl);
        // Verify the metadata for index is correct.
        rs = conn.getMetaData().getTables(null, StringUtil.escapeLike(INDEX_DATA_SCHEMA), indexName , new String[] {PTableType.INDEX.toString()});
        assertTrue(rs.next());
        assertEquals(indexName , rs.getString(3));
        assertEquals(PIndexState.INACTIVE.toString(), rs.getString("INDEX_STATE"));
        assertFalse(rs.next());
        
        ddl = "DROP INDEX " + indexName + " ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexDataTable;
        conn.createStatement().execute(ddl);
        
        // Assert the rows for index table is completely removed.
        rs = conn.getMetaData().getIndexInfo(null, INDEX_DATA_SCHEMA, indexDataTable, false, false);
        assertFalse(rs.next());
        
        // Assert the row in the original data table is removed.
        // Verify that there is a row inserted into the data table for the index table.
        rs = IndexTestUtil.readDataTableIndexRow(conn, INDEX_DATA_SCHEMA, indexDataTable, indexName );
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}