[ https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16579064#comment-16579064 ]
Thomas D'Silva commented on PHOENIX-4841: ----------------------------------------- Instead of inverting DESC columns in the WHERE expression, while constructing the {{ComparisonExpression}} if the lhs/rhs uses RVCs we could invert the columns of the RVCs that are sorted DESC. When the RVC is evaluated it should then provide the byte[] that works correctly with the comparator. [~jamestaylor] Do you think that will work? > Filters that uses RVC with pk columns where with DESC sort order don't work > correctly > ------------------------------------------------------------------------------------- > > Key: PHOENIX-4841 > URL: https://issues.apache.org/jira/browse/PHOENIX-4841 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.15.0, 5.1.0 > Reporter: Thomas D'Silva > Assignee: Daniel Wong > Priority: Major > > 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: > {code} > @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()); > } > } > {code} > The comparison expression for the above query is > {code} > (PK[-1], PK[-1]) > (TO_VARCHAR('b'), '3') > {code} > When the first row is evaluated the lhs bytes is: > {code} > [-101, -1, 49] > {code} > and rhs bytes: > {code} > [-99, -1, 51] > {code} > 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 > {code} > WHERE (B, C) > ('b', '3') > {code} > we need something like > {code} > WHERE B<~'b' OR (B=~'b' AND C>'3') > {code} > Is there a better way to handle this? -- This message was sent by Atlassian JIRA (v7.6.3#76005)