[ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16577333#comment-16577333
 ] 

Thomas D'Silva commented on PHOENIX-4841:
-----------------------------------------

I don't think we can push the filter into the start/stop of the scan because 
the filter does not contain the leading PK column. 

{code}
filter
(PK[-1], PK[-1]) > (TO_VARCHAR('b'), '3')
lhs
[-101, -1, 49]
rhs
[-99, -1, 51]
{code}

While building the expression tree if we have a 
{{RowValueConstructorComparisonExpression}} that is used when comparing RVCs we 
can take into account the sort order of the column in the RVCs. For the above 
example since b is sorted DESC it would compare [-99] and [-101] with the 
comparison operator flipped (so using < instead of >). If the result is not 
equal it would return , if it  is equal it would move on the remaining bytes 
comparing [51] and [49] with the > comparison operator. 


> 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
>            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)

Reply via email to