Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-5698

Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.15.0, 4.14.3
    • Fix Version/s: 4.16.0
    • Component/s: None
    • Labels:

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

       

        Attachments

        1. PHOENIX-5698.patch
          39 kB
          Xinyi Yan
        2. PHOENIX-5698-4.x-HBase-1.3.patch
          39 kB
          Xinyi Yan
        3. PHOENIX-5698-4.14-HBase-1.3.patch
          29 kB
          Xinyi Yan
        4. PHOENIX-5698-master.v2.patch
          76 kB
          Xinyi Yan
        5. PHOENIX-5698-4.x.patch
          77 kB
          Xinyi Yan
        6. PHOENIX-5698-4.x.v3.patch
          83 kB
          Xinyi Yan
        7. PHOENIX-5698-4.x.v4.patch
          83 kB
          Xinyi Yan
        8. PHOENIX-5698-4.x.v5.patch
          92 kB
          Xinyi Yan
        9. PHOENIX-5698-4.x.v6.patch
          90 kB
          Xinyi Yan

          Activity

            People

            • Assignee:
              yanxinyi Xinyi Yan
              Reporter:
              dbwong Daniel Wong
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 8h
                8h