[PHOENIX-4841] Filters that uses RVC with pk columns where with DESC sort order don't work correctly

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.15.0, 5.1.0
• Fix Version/s: 4.15.0, 5.1.0
• Labels: DESC
• Assignee: Daniel Wong
• Reporter: Thomas D'Silva
• Created: 08/Aug/18 19:07
• Updated: 29/Feb/20 05:40
• Resolved: 15/Nov/18 22:24

Description

If we filter on pk columns where one of the columns is DESC, we don't get the expected results. If the PK columns are of sorted by ASC we get the correct results. For eg. the following test fails:

@Test
    public void testRVCWithDescAndAscPK() throws Exception {
        String fullTableName = generateUniqueName();
        // create base table and global view using global connection
        try (Connection conn = DriverManager.getConnection(getUrl())) {
            Statement stmt = conn.createStatement();
            stmt.execute("CREATE TABLE " + fullTableName + "(\n" +
                    "    A VARCHAR NOT NULL,\n" + 
                    "    B VARCHAR NOT NULL,\n" + 
                    "    C VARCHAR NOT NULL,\n" + 
                    "    CONSTRAINT PK PRIMARY KEY (A, B DESC, C))");
            
            conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('x', 'd', '1')");
            conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('x', 'c', '2')");
            conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('x', 'b', '3')");
            conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('x', 'b', '4')");
            conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('x', 'a', '4')");
            conn.commit();
        }

        // validate that running query using global view gives same results
        try (Connection conn = DriverManager.getConnection(getUrl())) {
            ResultSet rs =
                    conn.createStatement().executeQuery(
                        "SELECT B, C FROM " + fullTableName + " WHERE (B, C) > ('b', '3')");
            assertTrue(rs.next());
            assertEquals("d", rs.getString(1));
            assertEquals("1", rs.getString(2));
            assertTrue(rs.next());
            assertEquals("c", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertTrue(rs.next());
            assertEquals("b", rs.getString(1));
            assertEquals("4", rs.getString(2));
            assertFalse(rs.next());
        }
    }

The comparison expression for the above query is

(PK[-1], PK[-1]) > (TO_VARCHAR('b'), '3')

When the first row is evaluated the lhs bytes is:

[-101, -1, 49]

and rhs bytes:

[-99, -1, 51]

We invert the bytes of the B column but since the greater than comparison operator usedthe row is filtered out (even though it should be returned).

jamestaylor
When a column is DESC order do we need to rewrite the comparison expression?
Instead of

WHERE (B, C) > ('b', '3') 

we need something like

WHERE B<~'b' OR (B=~'b' AND C>'3')

Is there a better way to handle this?

Issue Links

• duplicates: PHOENIX-4976 Comparisson on RVC are still incorrect
• is required by: PHOENIX-4846 WhereOptimizer.pushKeyExpressionsToScan() does not work correctly if the sort order of pk columns being filtered on changes
• relates to: PHOENIX-3301 Row Value Constructors Against Indexes Don't Work Correctly
• relates to: PHOENIX-5753 Fix erroneous query result when RVC is clipped with desc column
• links to: GitHub Pull Request #362