[
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16654356#comment-16654356
]
Lars Hofhansl edited comment on PHOENIX-4841 at 10/17/18 11:16 PM:
-------------------------------------------------------------------
Tested this with some scenarios. Works all fine. I really like the comments (I
wish everybody would do that).
There are two things I saw that may or may not be a problem:
# INVERT makes no difference. I.e. (A, B, C) > (1, 2, 3) behaves identical to
(A, INVERT(B), C) > (1, 2, 3), it also behaves identical to (A, B, C) > (1,
INVERT(2), 3). Perhaps that's by design.
# The plans looks a funky sometimes, just a nit maybe (see below):
{{create table test(k1 integer not null, k2 integer not null, k3 integer not
null, v integer, constraint pk primary key (k1, k2 desc, k3));}}
{code}
explain select * from test where (k1, k2, k3) > (1,2,1) order by k1, k2 desc,
k3;
+------------------------------------------------------------------------------------+-----------------+----------------+--------------+
| PLAN
| EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+------------------------------------------------------------------------------------+-----------------+----------------+--------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER TEST [1] - [*]
|
| SERVER FILTER BY (K1, TO_INTEGER(K2), K3) > (1,
TO_INTEGER(TO_INTEGER(2)), 1) |
+------------------------------------------------------------------------------------+-----------------+----------------+--------------+
{code}
(Note the multiple wrapping of {{TO_INTEGER}}.
was (Author: lhofhansl):
Tested this with some scenarios. Works all fine. I really like the comments (I
wish everybody would do that).
There are two things I saw that may or may not be a problem:
# INVERT makes no difference. I.e. (A, B, C) > (1, 2, 3) behaves identical to
(A, INVERT(B), C) > (1, 2, 3), it behaves identical to (A, B, C) > (1,
INVERT(2), 3). Perhaps that's by design.
# The plans looks a funky sometimes, just a nit maybe (see below):
{{create table test(k1 integer not null, k2 integer not null, k3 integer not
null, v integer, constraint pk primary key (k1, k2 desc, k3));}}
{code}
+------------------------------------------------------------------------------------+-----------------+----------------+--------------+
| PLAN
| EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+------------------------------------------------------------------------------------+-----------------+----------------+--------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER TEST [1] - [*]
|
| SERVER FILTER BY (K1, TO_INTEGER(K2), K3) > (1,
TO_INTEGER(TO_INTEGER(2)), 1) |
+------------------------------------------------------------------------------------+-----------------+----------------+--------------+
{code}
(Note the multiple wrapping of {{TO_INTEGER}}.
> 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
> Labels: DESC
> Attachments: PHOENIX-4841-v2.patch, PHOENIX-4841-v3.patch,
> PHOENIX-4841-v4.patch, PHOENIX-4841.patch
>
>
> 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)
