From dev-return-53603-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Wed Aug 8 21:09:05 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id 7D52F18077A for ; Wed, 8 Aug 2018 21:09:04 +0200 (CEST) Received: (qmail 97305 invoked by uid 500); 8 Aug 2018 19:09:03 -0000 Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list dev@phoenix.apache.org Received: (qmail 97294 invoked by uid 99); 8 Aug 2018 19:09:03 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 08 Aug 2018 19:09:03 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 145A8C05DC for ; Wed, 8 Aug 2018 19:09:03 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -109.501 X-Spam-Level: X-Spam-Status: No, score=-109.501 tagged_above=-999 required=6.31 tests=[ENV_AND_HDR_SPF_MATCH=-0.5, KAM_ASCII_DIVIDERS=0.8, RCVD_IN_DNSWL_MED=-2.3, SPF_PASS=-0.001, USER_IN_DEF_SPF_WL=-7.5, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id EbdBwtuuKAB3 for ; Wed, 8 Aug 2018 19:09:01 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id 487A45F385 for ; Wed, 8 Aug 2018 19:09:01 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id C5AB5E0D27 for ; Wed, 8 Aug 2018 19:09:00 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 37FF623F98 for ; Wed, 8 Aug 2018 19:09:00 +0000 (UTC) Date: Wed, 8 Aug 2018 19:09:00 +0000 (UTC) From: "Thomas D'Silva (JIRA)" To: dev@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (PHOENIX-4841) Filters that uses RVC with pk columns where with DESC sort order don't work correctly MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ 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", 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 WHERE (B, C) > ('b', '3') we need something like WHERE B<~'b' OR (B=~'b' AND C>'3'). Is there a better way to handle this? > 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)