Examples with QueryPlan org.apache.phoenix.compile.QueryPlan used on opensource projects

Example 11 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class UpsertSelectIT method testRowTimestampColWithViewsIndexesAndSaltedTables.

@Test
public void testRowTimestampColWithViewsIndexesAndSaltedTables() throws Exception {
    String baseTable = "testRowTimestampColWithViewsIndexesAndSaltedTables".toUpperCase();
    String tenantView = "tenatView".toUpperCase();
    String globalView = "globalView".toUpperCase();
    String baseTableIdx = "table_idx".toUpperCase();
    String tenantViewIdx = "tenantView_idx".toUpperCase();
    long ts = nextTimestamp();
    try (Connection conn = getConnection(ts)) {
        conn.createStatement().execute("CREATE TABLE " + baseTable + " (TENANT_ID CHAR(15) NOT NULL, PK2 DATE NOT NULL, PK3 INTEGER NOT NULL, KV1 VARCHAR, KV2 VARCHAR, KV3 VARCHAR CONSTRAINT PK PRIMARY KEY(TENANT_ID, PK2 ROW_TIMESTAMP, PK3)) MULTI_TENANT = true, SALT_BUCKETS = 8");
    }
    ts = nextTimestamp();
    try (Connection conn = getConnection(ts)) {
        conn.createStatement().execute("CREATE INDEX " + baseTableIdx + " ON " + baseTable + " (PK2, KV3) INCLUDE (KV1)");
    }
    ts = nextTimestamp();
    try (Connection conn = getConnection(ts)) {
        conn.createStatement().execute("CREATE VIEW " + globalView + " AS SELECT * FROM " + baseTable + " WHERE KV1 = 'KV1'");
    }
    String tenantId = "tenant1";
    ts = nextTimestamp();
    try (Connection conn = getTenantConnection(tenantId, ts)) {
        conn.createStatement().execute("CREATE VIEW " + tenantView + " AS SELECT * FROM " + baseTable);
    }
    ts = nextTimestamp();
    try (Connection conn = getTenantConnection(tenantId, ts)) {
        conn.createStatement().execute("CREATE INDEX " + tenantViewIdx + " ON " + tenantView + " (PK2, KV2) INCLUDE (KV1)");
    }
    // upsert data into base table without specifying the row timestamp column PK2
    long upsertedTs = nextTimestamp();
    try (Connection conn = getConnection(upsertedTs)) {
        // Upsert select in the same table with the row_timestamp column PK2 not specified. This will end up
        // creating a new row whose timestamp is the SCN of the connection. The same SCN will be used
        // for the row key too.
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO  " + baseTable + " (TENANT_ID, PK3, KV1, KV2, KV3) VALUES (?, ?, ?, ?, ?)");
        stmt.setString(1, tenantId);
        stmt.setInt(2, 3);
        stmt.setString(3, "KV1");
        stmt.setString(4, "KV2");
        stmt.setString(5, "KV3");
        stmt.executeUpdate();
        conn.commit();
    }
    // Verify that we can see data when querying through base table, global view and index on the base table
    try (Connection conn = getConnection(nextTimestamp())) {
        // Query the base table
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM  " + baseTable + " WHERE TENANT_ID = ? AND PK2 = ? AND PK3 = ?");
        stmt.setString(1, tenantId);
        stmt.setDate(2, new Date(upsertedTs));
        stmt.setInt(3, 3);
        ResultSet rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(tenantId, rs.getString("TENANT_ID"));
        assertEquals("KV1", rs.getString("KV1"));
        assertEquals("KV2", rs.getString("KV2"));
        assertEquals("KV3", rs.getString("KV3"));
        assertEquals(new Date(upsertedTs), rs.getDate("PK2"));
        assertFalse(rs.next());
        // Query the globalView
        stmt = conn.prepareStatement("SELECT * FROM  " + globalView + " WHERE TENANT_ID = ? AND PK2 = ? AND PK3 = ?");
        stmt.setString(1, tenantId);
        stmt.setDate(2, new Date(upsertedTs));
        stmt.setInt(3, 3);
        rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(tenantId, rs.getString("TENANT_ID"));
        assertEquals("KV1", rs.getString("KV1"));
        assertEquals("KV2", rs.getString("KV2"));
        assertEquals("KV3", rs.getString("KV3"));
        assertEquals(new Date(upsertedTs), rs.getDate("PK2"));
        assertFalse(rs.next());
        // Query using the index on base table
        stmt = conn.prepareStatement("SELECT KV1 FROM  " + baseTable + " WHERE PK2 = ? AND KV3 = ?");
        stmt.setDate(1, new Date(upsertedTs));
        stmt.setString(2, "KV3");
        rs = stmt.executeQuery();
        QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
        assertTrue(plan.getTableRef().getTable().getName().getString().equals(baseTableIdx));
        assertTrue(rs.next());
        assertEquals("KV1", rs.getString("KV1"));
        assertFalse(rs.next());
    }
    // Verify that data can be queried using tenant view and tenant view index
    try (Connection tenantConn = getTenantConnection(tenantId, nextTimestamp())) {
        // Query the tenant view
        PreparedStatement stmt = tenantConn.prepareStatement("SELECT * FROM  " + tenantView + " WHERE PK2 = ? AND PK3 = ?");
        stmt.setDate(1, new Date(upsertedTs));
        stmt.setInt(2, 3);
        ResultSet rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals("KV1", rs.getString("KV1"));
        assertEquals("KV2", rs.getString("KV2"));
        assertEquals("KV3", rs.getString("KV3"));
        assertEquals(new Date(upsertedTs), rs.getDate("PK2"));
        assertFalse(rs.next());
    // Query using the index on the tenantView
    //TODO: uncomment the code after PHOENIX-2277 is fixed
    //            stmt = tenantConn.prepareStatement("SELECT KV1 FROM  " + tenantView + " WHERE PK2 = ? AND KV2 = ?");
    //            stmt.setDate(1, new Date(upsertedTs));
    //            stmt.setString(2, "KV2");
    //            rs = stmt.executeQuery();
    //            QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
    //            assertTrue(plan.getTableRef().getTable().getName().getString().equals(tenantViewIdx));
    //            assertTrue(rs.next());
    //            assertEquals("KV1", rs.getString("KV1"));
    //            assertFalse(rs.next());
    }
    upsertedTs = nextTimestamp();
    try (Connection tenantConn = getTenantConnection(tenantId, upsertedTs)) {
        // Upsert into tenant view where the row_timestamp column PK2 is not specified
        PreparedStatement stmt = tenantConn.prepareStatement("UPSERT INTO  " + tenantView + " (PK3, KV1, KV2, KV3) VALUES (?, ?, ?, ?)");
        stmt.setInt(1, 33);
        stmt.setString(2, "KV13");
        stmt.setString(3, "KV23");
        stmt.setString(4, "KV33");
        stmt.executeUpdate();
        tenantConn.commit();
        // Upsert into tenant view where the row_timestamp column PK2 is specified
        stmt = tenantConn.prepareStatement("UPSERT INTO  " + tenantView + " (PK2, PK3, KV1, KV2, KV3) VALUES (?, ?, ?, ?, ?)");
        stmt.setDate(1, new Date(upsertedTs));
        stmt.setInt(2, 44);
        stmt.setString(3, "KV14");
        stmt.setString(4, "KV24");
        stmt.setString(5, "KV34");
        stmt.executeUpdate();
        tenantConn.commit();
    }
    // Verify that the data upserted using the tenant view can now be queried using base table and the base table index
    try (Connection conn = getConnection(upsertedTs + 10000)) {
        // Query the base table
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM  " + baseTable + " WHERE TENANT_ID = ? AND PK2 = ? AND PK3 = ? ");
        stmt.setString(1, tenantId);
        stmt.setDate(2, new Date(upsertedTs));
        stmt.setInt(3, 33);
        ResultSet rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(tenantId, rs.getString("TENANT_ID"));
        assertEquals("KV13", rs.getString("KV1"));
        assertEquals("KV23", rs.getString("KV2"));
        assertEquals("KV33", rs.getString("KV3"));
        assertFalse(rs.next());
        stmt = conn.prepareStatement("SELECT * FROM  " + baseTable + " WHERE TENANT_ID = ? AND PK2 = ? AND PK3 = ? ");
        stmt.setString(1, tenantId);
        stmt.setDate(2, new Date(upsertedTs));
        stmt.setInt(3, 44);
        rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(tenantId, rs.getString("TENANT_ID"));
        assertEquals("KV14", rs.getString("KV1"));
        assertEquals("KV24", rs.getString("KV2"));
        assertEquals("KV34", rs.getString("KV3"));
        assertFalse(rs.next());
        // Query using the index on base table
        stmt = conn.prepareStatement("SELECT KV1 FROM  " + baseTable + " WHERE (PK2, KV3) IN ((?, ?), (?, ?)) ORDER BY KV1");
        stmt.setDate(1, new Date(upsertedTs));
        stmt.setString(2, "KV33");
        stmt.setDate(3, new Date(upsertedTs));
        stmt.setString(4, "KV34");
        rs = stmt.executeQuery();
        QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
        assertTrue(plan.getTableRef().getTable().getName().getString().equals(baseTableIdx));
        assertTrue(rs.next());
        assertEquals("KV13", rs.getString("KV1"));
        assertTrue(rs.next());
        assertEquals("KV14", rs.getString("KV1"));
        assertFalse(rs.next());
    }
    // Verify that the data upserted using the tenant view can now be queried using tenant view
    try (Connection tenantConn = getTenantConnection(tenantId, upsertedTs + 10000)) {
        // Query the base table
        PreparedStatement stmt = tenantConn.prepareStatement("SELECT * FROM  " + tenantView + " WHERE (PK2, PK3) IN ((?, ?), (?, ?)) ORDER BY KV1");
        stmt.setDate(1, new Date(upsertedTs));
        stmt.setInt(2, 33);
        stmt.setDate(3, new Date(upsertedTs));
        stmt.setInt(4, 44);
        ResultSet rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals("KV13", rs.getString("KV1"));
        assertTrue(rs.next());
        assertEquals("KV14", rs.getString("KV1"));
        assertFalse(rs.next());
    //TODO: uncomment the code after PHOENIX-2277 is fixed
    //            // Query using the index on the tenantView
    //            stmt = tenantConn.prepareStatement("SELECT KV1 FROM  " + tenantView + " WHERE (PK2, KV2) IN (?, ?, ?, ?) ORDER BY KV1");
    //            stmt.setDate(1, new Date(upsertedTs));
    //            stmt.setString(2, "KV23");
    //            stmt.setDate(3, new Date(upsertedTs));
    //            stmt.setString(4, "KV24");
    //            rs = stmt.executeQuery();
    //            QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
    //            assertTrue(plan.getTableRef().getTable().getName().getString().equals(tenantViewIdx));
    //            assertTrue(rs.next());
    //            assertEquals("KV13", rs.getString("KV1"));
    //            assertTrue(rs.next());
    //            assertEquals("KV14", rs.getString("KV1"));
    //            assertFalse(rs.next());
    }
}

Example 12 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class QueryOptimizer method getApplicablePlans.

private List<QueryPlan> getApplicablePlans(QueryPlan dataPlan, PhoenixStatement statement, List<? extends PDatum> targetColumns, ParallelIteratorFactory parallelIteratorFactory, boolean stopAtBestPlan) throws SQLException {
    SelectStatement select = (SelectStatement) dataPlan.getStatement();
    // Exit early if we have a point lookup as we can't get better than that
    if (!useIndexes || (dataPlan.getContext().getScanRanges().isPointLookup() && stopAtBestPlan)) {
        return Collections.singletonList(dataPlan);
    }
    // For single query tuple projection, indexes are inherited from the original table to the projected
    // table; otherwise not. So we pass projected table here, which is enough to tell if this is from a
    // single query or a part of join query.
    List<PTable> indexes = Lists.newArrayList(dataPlan.getContext().getResolver().getTables().get(0).getTable().getIndexes());
    if (indexes.isEmpty() || dataPlan.isDegenerate() || dataPlan.getTableRef().hasDynamicCols() || select.getHint().hasHint(Hint.NO_INDEX)) {
        return Collections.singletonList(dataPlan);
    }
    // when the data table is used.
    if (targetColumns.isEmpty()) {
        List<? extends ColumnProjector> projectors = dataPlan.getProjector().getColumnProjectors();
        List<PDatum> targetDatums = Lists.newArrayListWithExpectedSize(projectors.size());
        for (ColumnProjector projector : projectors) {
            targetDatums.add(projector.getExpression());
        }
        targetColumns = targetDatums;
    }
    SelectStatement translatedIndexSelect = IndexStatementRewriter.translate(select, FromCompiler.getResolver(dataPlan.getTableRef()));
    List<QueryPlan> plans = Lists.newArrayListWithExpectedSize(1 + indexes.size());
    plans.add(dataPlan);
    QueryPlan hintedPlan = getHintedQueryPlan(statement, translatedIndexSelect, indexes, targetColumns, parallelIteratorFactory, plans);
    if (hintedPlan != null) {
        if (stopAtBestPlan) {
            return Collections.singletonList(hintedPlan);
        }
        plans.add(0, hintedPlan);
    }
    for (PTable index : indexes) {
        QueryPlan plan = addPlan(statement, translatedIndexSelect, index, targetColumns, parallelIteratorFactory, dataPlan, false);
        if (plan != null) {
            // Query can't possibly return anything so just return this plan.
            if (plan.isDegenerate()) {
                return Collections.singletonList(plan);
            }
            plans.add(plan);
        }
    }
    return hintedPlan == null ? orderPlansBestToWorst(select, plans, stopAtBestPlan) : plans;
}

Example 13 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class QueryOptimizer method getHintedQueryPlan.

private static QueryPlan getHintedQueryPlan(PhoenixStatement statement, SelectStatement select, List<PTable> indexes, List<? extends PDatum> targetColumns, ParallelIteratorFactory parallelIteratorFactory, List<QueryPlan> plans) throws SQLException {
    QueryPlan dataPlan = plans.get(0);
    String indexHint = select.getHint().getHint(Hint.INDEX);
    if (indexHint == null) {
        return null;
    }
    int startIndex = 0;
    String alias = dataPlan.getTableRef().getTableAlias();
    String prefix = HintNode.PREFIX + (alias == null ? dataPlan.getTableRef().getTable().getName().getString() : alias) + HintNode.SEPARATOR;
    while (startIndex < indexHint.length()) {
        startIndex = indexHint.indexOf(prefix, startIndex);
        if (startIndex < 0) {
            return null;
        }
        startIndex += prefix.length();
        // true when SUFFIX found
        boolean done = false;
        while (startIndex < indexHint.length() && !done) {
            int endIndex;
            int endIndex1 = indexHint.indexOf(HintNode.SEPARATOR, startIndex);
            int endIndex2 = indexHint.indexOf(HintNode.SUFFIX, startIndex);
            if (endIndex1 < 0 && endIndex2 < 0) {
                // Missing SUFFIX shouldn't happen
                endIndex = indexHint.length();
            } else if (endIndex1 < 0) {
                done = true;
                endIndex = endIndex2;
            } else if (endIndex2 < 0) {
                endIndex = endIndex1;
            } else {
                endIndex = Math.min(endIndex1, endIndex2);
                done = endIndex2 == endIndex;
            }
            String indexName = indexHint.substring(startIndex, endIndex);
            int indexPos = getIndexPosition(indexes, indexName);
            if (indexPos >= 0) {
                // Hinted index is applicable, so return it's index
                PTable index = indexes.get(indexPos);
                indexes.remove(indexPos);
                QueryPlan plan = addPlan(statement, select, index, targetColumns, parallelIteratorFactory, dataPlan, true);
                if (plan != null) {
                    return plan;
                }
            }
            startIndex = endIndex + 1;
        }
    }
    return null;
}

Example 14 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class ParallelIteratorsSplitTest method getSplits.

private static List<KeyRange> getSplits(final TableRef tableRef, final Scan scan, final List<HRegionLocation> regions, final ScanRanges scanRanges) throws SQLException {
    final List<TableRef> tableRefs = Collections.singletonList(tableRef);
    ColumnResolver resolver = new ColumnResolver() {

        @Override
        public List<PFunction> getFunctions() {
            return Collections.emptyList();
        }

        @Override
        public List<TableRef> getTables() {
            return tableRefs;
        }

        @Override
        public TableRef resolveTable(String schemaName, String tableName) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public ColumnRef resolveColumn(String schemaName, String tableName, String colName) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public PFunction resolveFunction(String functionName) throws SQLException {
            throw new UnsupportedOperationException();
        }

        @Override
        public boolean hasUDFs() {
            return false;
        }

        @Override
        public PSchema resolveSchema(String schemaName) throws SQLException {
            return null;
        }

        @Override
        public List<PSchema> getSchemas() {
            return null;
        }
    };
    PhoenixConnection connection = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    final PhoenixStatement statement = new PhoenixStatement(connection);
    final StatementContext context = new StatementContext(statement, resolver, scan, new SequenceManager(statement));
    context.setScanRanges(scanRanges);
    ParallelIterators parallelIterators = new ParallelIterators(new QueryPlan() {

        private final Set<TableRef> tableRefs = ImmutableSet.of(tableRef);

        @Override
        public StatementContext getContext() {
            return context;
        }

        @Override
        public ParameterMetaData getParameterMetaData() {
            return PhoenixParameterMetaData.EMPTY_PARAMETER_META_DATA;
        }

        @Override
        public ExplainPlan getExplainPlan() throws SQLException {
            return ExplainPlan.EMPTY_PLAN;
        }

        @Override
        public ResultIterator iterator(ParallelScanGrouper scanGrouper) throws SQLException {
            return ResultIterator.EMPTY_ITERATOR;
        }

        @Override
        public ResultIterator iterator(ParallelScanGrouper scanGrouper, Scan scan) throws SQLException {
            return ResultIterator.EMPTY_ITERATOR;
        }

        @Override
        public ResultIterator iterator() throws SQLException {
            return ResultIterator.EMPTY_ITERATOR;
        }

        @Override
        public long getEstimatedSize() {
            return 0;
        }

        @Override
        public Set<TableRef> getSourceRefs() {
            return tableRefs;
        }

        @Override
        public TableRef getTableRef() {
            return tableRef;
        }

        @Override
        public RowProjector getProjector() {
            return RowProjector.EMPTY_PROJECTOR;
        }

        @Override
        public Integer getLimit() {
            return null;
        }

        @Override
        public Integer getOffset() {
            return null;
        }

        @Override
        public OrderBy getOrderBy() {
            return OrderBy.EMPTY_ORDER_BY;
        }

        @Override
        public GroupBy getGroupBy() {
            return GroupBy.EMPTY_GROUP_BY;
        }

        @Override
        public List<KeyRange> getSplits() {
            return null;
        }

        @Override
        public FilterableStatement getStatement() {
            return SelectStatement.SELECT_ONE;
        }

        @Override
        public boolean isDegenerate() {
            return false;
        }

        @Override
        public boolean isRowKeyOrdered() {
            return true;
        }

        @Override
        public List<List<Scan>> getScans() {
            return null;
        }

        @Override
        public Operation getOperation() {
            return Operation.QUERY;
        }

        @Override
        public boolean useRoundRobinIterator() {
            return false;
        }

        @Override
        public Long getEstimatedRowsToScan() {
            return null;
        }

        @Override
        public Long getEstimatedBytesToScan() {
            return null;
        }
    }, null, new SpoolingResultIterator.SpoolingResultIteratorFactory(context.getConnection().getQueryServices()), context.getScan(), false);
    List<KeyRange> keyRanges = parallelIterators.getSplits();
    return keyRanges;
}

Example 15 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class AlterMultiTenantTableWithViewsIT method testAddPKColumnToBaseTableWhoseViewsHaveIndices.

@Test
public void testAddPKColumnToBaseTableWhoseViewsHaveIndices() throws Exception {
    String baseTable = "testAddPKColumnToBaseTableWhoseViewsHaveIndices";
    String baseViewName = generateUniqueName();
    String view1 = baseViewName + "_VIEW1";
    String view2 = baseViewName + "_VIEW2";
    String view3 = baseViewName + "_VIEW3";
    String tenant1 = baseViewName + "_T1";
    String tenant2 = baseViewName + "_T2";
    String view2Index = view2 + "_IDX";
    String view3Index = view3 + "_IDX";
    /*                          baseTable(mutli-tenant)
                                 /                           \                
                         view1(tenant1)                  view3(tenant2, index) 
                          /
                        view2(tenant1, index)  
         */
    try (Connection globalConn = DriverManager.getConnection(getUrl())) {
        // make sure that the tables are empty, but reachable
        globalConn.createStatement().execute("CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, K1 varchar not null, V1 VARCHAR, V2 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, K1)) MULTI_TENANT = true ");
    }
    try (Connection viewConn = getTenantConnection(tenant1)) {
        // create tenant specific view for tenant1 - view1
        viewConn.createStatement().execute("CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable);
        PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class);
        assertEquals(0, getTableSequenceNumber(phxConn, view1));
        assertEquals(2, getMaxKeySequenceNumber(phxConn, view1));
        // create a view - view2 on view - view1
        viewConn.createStatement().execute("CREATE VIEW " + view2 + " AS SELECT * FROM " + view1);
        assertEquals(0, getTableSequenceNumber(phxConn, view2));
        assertEquals(2, getMaxKeySequenceNumber(phxConn, view2));
        // create an index on view2
        viewConn.createStatement().execute("CREATE INDEX " + view2Index + " ON " + view2 + " (v1) include (v2)");
        assertEquals(0, getTableSequenceNumber(phxConn, view2Index));
        assertEquals(4, getMaxKeySequenceNumber(phxConn, view2Index));
    }
    try (Connection viewConn = getTenantConnection(tenant2)) {
        // create tenant specific view for tenant2 - view3
        viewConn.createStatement().execute("CREATE VIEW " + view3 + " AS SELECT * FROM " + baseTable);
        PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class);
        assertEquals(0, getTableSequenceNumber(phxConn, view3));
        assertEquals(2, getMaxKeySequenceNumber(phxConn, view3));
        // create an index on view3
        viewConn.createStatement().execute("CREATE INDEX " + view3Index + " ON " + view3 + " (v1) include (v2)");
        assertEquals(0, getTableSequenceNumber(phxConn, view3Index));
        assertEquals(4, getMaxKeySequenceNumber(phxConn, view3Index));
    }
    // alter the base table by adding 1 non-pk and 2 pk columns
    try (Connection globalConn = DriverManager.getConnection(getUrl())) {
        globalConn.createStatement().execute("ALTER TABLE " + baseTable + " ADD v3 VARCHAR, k2 VARCHAR PRIMARY KEY, k3 VARCHAR PRIMARY KEY");
        assertEquals(4, getMaxKeySequenceNumber(globalConn.unwrap(PhoenixConnection.class), baseTable));
        // Upsert records in the base table
        String upsert = "UPSERT INTO " + baseTable + " (TENANT_ID, K1, K2, K3, V1, V2, V3) VALUES (?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = globalConn.prepareStatement(upsert);
        stmt.setString(1, tenant1);
        stmt.setString(2, "K1");
        stmt.setString(3, "K2");
        stmt.setString(4, "K3");
        stmt.setString(5, "V1");
        stmt.setString(6, "V2");
        stmt.setString(7, "V3");
        stmt.executeUpdate();
        stmt.setString(1, tenant2);
        stmt.setString(2, "K11");
        stmt.setString(3, "K22");
        stmt.setString(4, "K33");
        stmt.setString(5, "V11");
        stmt.setString(6, "V22");
        stmt.setString(7, "V33");
        stmt.executeUpdate();
        globalConn.commit();
    }
    // Also verify that the newly added pk columns show up as pk columns of data table, indexes and views.
    try (Connection viewConn = getTenantConnection(tenant1)) {
        ResultSet rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view1);
        PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class);
        assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view1));
        assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view1));
        assertEquals(1, getTableSequenceNumber(phxConn, view1));
        assertEquals(4, getMaxKeySequenceNumber(phxConn, view1));
        verifyNewColumns(rs, "K2", "K3", "V3");
        rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view2);
        assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view2));
        assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view2));
        assertEquals(1, getTableSequenceNumber(phxConn, view2));
        assertEquals(4, getMaxKeySequenceNumber(phxConn, view2));
        verifyNewColumns(rs, "K2", "K3", "V3");
        assertEquals(4, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k2"), view2Index));
        assertEquals(5, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k3"), view2Index));
        assertEquals(1, getTableSequenceNumber(phxConn, view2Index));
        assertEquals(6, getMaxKeySequenceNumber(phxConn, view2Index));
    }
    try (Connection viewConn = getTenantConnection(tenant2)) {
        ResultSet rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view3);
        PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class);
        assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view3));
        assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view3));
        assertEquals(1, getTableSequenceNumber(phxConn, view3));
        verifyNewColumns(rs, "K22", "K33", "V33");
        assertEquals(4, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k2"), view3Index));
        assertEquals(5, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k3"), view3Index));
        assertEquals(1, getTableSequenceNumber(phxConn, view3Index));
        assertEquals(6, getMaxKeySequenceNumber(phxConn, view3Index));
    }
    // Verify that the index is actually being used when using newly added pk col
    try (Connection viewConn = getTenantConnection(tenant1)) {
        String upsert = "UPSERT INTO " + view2 + " (K1, K2, K3, V1, V2, V3) VALUES ('key1', 'key2', 'key3', 'value1', 'value2', 'value3')";
        viewConn.createStatement().executeUpdate(upsert);
        viewConn.commit();
        Statement stmt = viewConn.createStatement();
        String sql = "SELECT V2 FROM " + view2 + " WHERE V1 = 'value1' AND K3 = 'key3'";
        QueryPlan plan = stmt.unwrap(PhoenixStatement.class).optimizeQuery(sql);
        assertTrue(plan.getTableRef().getTable().getName().getString().equals(SchemaUtil.normalizeIdentifier(view2Index)));
        ResultSet rs = viewConn.createStatement().executeQuery(sql);
        verifyNewColumns(rs, "value2");
    }
}