[
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16579061#comment-16579061
]
Daniel Wong commented on PHOENIX-4841:
--------------------------------------
I don't think handling RVC in the WHERE clause alone is enough for this issue
as there are other supported SQL where this type of failure can occur. An
example below is CASE.
{code:java}
private void initData(String fullTableName) throws SQLException {
// create base table 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();
}
}
@Test
public void testRVCWithDescAndAscPkInCase() throws Exception {
String fullTableName = generateUniqueName();
initData(fullTableName);
String query = "SELECT CASE WHEN (B, C) > ('b', '3') THEN 1 ELSE -1
END AS result FROM " + fullTableName;
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("1", rs.getString(1));
assertTrue(rs.next());
assertEquals("1", rs.getString(1));
assertTrue(rs.next());
assertEquals("-1", rs.getString(1));
assertTrue(rs.next());
assertEquals("1", rs.getString(1));
assertTrue(rs.next());
assertEquals("-1", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
{code}
We either have to handle all the current/future locations or push this further
down the evaluation stack? My thought is that there should be a decoupling
between the SQL and the row/column format issues so I'd prefer to approach in
the comparison evaluator rather than finding and adding all the SQL rewrite
locations for a conditional. Looking for some additional feedback
[~jamestaylor] [~tdsilva].
> 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)
