You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Lars Hofhansl (JIRA)" <ji...@apache.org> on 2018/10/17 23:17:00 UTC
[jira] [Comment Edited] (PHOENIX-4841) Filters that uses RVC with
pk columns where with DESC sort order don't work correctly
[ 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)