[jira] [Created] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

["Daniel Wong (Jira)" <ji...@apache.org>]

Daniel Wong created PHOENIX-5698:
------------------------------------

             Summary: Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks
                 Key: PHOENIX-5698
                 URL: https://issues.apache.org/jira/browse/PHOENIX-5698
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 4.14.3, 4.15.0
            Reporter: Daniel Wong


n 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.


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



--
This message was sent by Atlassian Jira
(v8.3.4#803005)