Major Description
In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. However, this generates an incorrect scan with range ['tenant1 0CY005xx000001Sv6o'). If the order of the RVCs is changed to row key order Phoenix correctly generates a SINGLE ROW SCAN. As we provide the full PK this we expect a either tightly bounded range scan or a client side delete. Instead we get a range scan on composite leading edge TENANT_ID,KEY_PREFIX,ID1.
@Test public void testInListExpressionWithDescAgain() throws Exception { String fullTableName = generateUniqueName(); String fullViewName = generateUniqueName(); String tenantView = generateUniqueName(); // create base table and global view using global connection try (Connection conn = DriverManager.getConnection(getUrl())) { conn.setAutoCommit(true); Statement stmt = conn.createStatement(); stmt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID CHAR(15) NOT NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL,\n" + " CONSTRAINT PK PRIMARY KEY (\n" + " TENANT_ID," + " KEY_PREFIX" + ")) MULTI_TENANT=TRUE"); stmt.execute("CREATE VIEW " + fullViewName + "(\n" + " ID1 VARCHAR NOT NULL,\n" + " ID2 VARCHAR NOT NULL,\n" + " EVENT_DATE DATE NOT NULL,\n" + " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (\n" + " ID1, ID2 DESC, EVENT_DATE DESC\n" + ")) AS SELECT * FROM " + fullTableName + " WHERE KEY_PREFIX = '0CY'"); } // create and use a tenant specific view to write data try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1) ) { viewConn.setAutoCommit(true); //need autocommit for serverside deletion Statement stmt = viewConn.createStatement(); stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS SELECT * FROM " + fullViewName ); viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000300', 1532458230000)"); viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000400', 1532458240000)"); viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000500', 1532458250000)"); viewConn.commit(); ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " + tenantView ); printResultSet(rs); System.out.println("Delete Start"); rs = stmt.executeQuery("EXPLAIN DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN (('005xx000001Sv6o', 1532458240000, '000000000000400'),('005xx000001Sv6o', 1532458230000, '000000000000300'))"); printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN (('005xx000001Sv6o', 1532458240000, '000000000000400'),('005xx000001Sv6o', 1532458230000, '000000000000300'))"); viewConn.commit(); System.out.println("Delete End"); rs = stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " + tenantView ); printResultSet(rs); } } private void printResultSet(ResultSet rs) throws SQLException { StringBuilder builder = new StringBuilder(); while(rs.next()) { for(int i = 0; i < rs.getMetaData().getColumnCount(); i++) { Object col = rs.getObject(i + 1); if(col == null) { builder.append("null"); } else { if(col instanceof Date) { DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); builder.append(df.format(col)); } else { builder.append(col.toString()); } } builder.append(","); } builder.append("\n"); } System.out.println(builder.toString()); }
