[jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly

2018-11-12 Thread Daniel Wong (JIRA) 


 [ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Daniel Wong updated PHOENIX-4841:
-
Attachment: PHOENIX-4841.4.x-HBase-1.4.patch

> 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-v5.patch, PHOENIX-4841-v6.patch, 
> PHOENIX-4841.4.x-HBase-1.4.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)

[jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly

2018-11-02 Thread Daniel Wong (JIRA) 


 [ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Daniel Wong updated PHOENIX-4841:
-
Attachment: PHOENIX-4841-v6.patch

> 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-v5.patch, PHOENIX-4841-v6.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)

[jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly

2018-10-31 Thread Daniel Wong (JIRA) 


 [ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Daniel Wong updated PHOENIX-4841:
-
Attachment: PHOENIX-4841-v5.patch

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

[jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly

2018-10-10 Thread Daniel Wong (JIRA) 


 [ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Daniel Wong updated PHOENIX-4841:
-
Attachment: PHOENIX-4841-v4.patch

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

[jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly

2018-10-09 Thread Daniel Wong (JIRA) 


 [ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Daniel Wong updated PHOENIX-4841:
-
Attachment: PHOENIX-4841-v3.patch

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

[jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly

2018-10-08 Thread Daniel Wong (JIRA) 


 [ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Daniel Wong updated PHOENIX-4841:
-
Attachment: PHOENIX-4841-v2.patch

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

[jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly

2018-08-29 Thread Daniel Wong (JIRA) 


 [ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Daniel Wong updated PHOENIX-4841:
-
Labels: DESC  (was: )

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

[jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly

2018-08-08 Thread Thomas D'Silva (JIRA) 


 [ 
https://issues.apache.org/jira/browse/PHOENIX-4841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Thomas D'Silva updated PHOENIX-4841:

Description: 
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?



  was:
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",