From dev-return-60201-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Mon Mar 2 23:07:02 2020 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 [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id 2D9EF18062C for ; Tue, 3 Mar 2020 00:07:02 +0100 (CET) Received: (qmail 82671 invoked by uid 500); 2 Mar 2020 23:07:01 -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 82644 invoked by uid 99); 2 Mar 2020 23:07:01 -0000 Received: from mailrelay1-us-west.apache.org (HELO mailrelay1-us-west.apache.org) (209.188.14.139) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 02 Mar 2020 23:07:01 +0000 Received: from jira-he-de.apache.org (static.172.67.40.188.clients.your-server.de [188.40.67.172]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id A50CAE02AA for ; Mon, 2 Mar 2020 23:07:00 +0000 (UTC) Received: from jira-he-de.apache.org (localhost.localdomain [127.0.0.1]) by jira-he-de.apache.org (ASF Mail Server at jira-he-de.apache.org) with ESMTP id 22A7978007C for ; Mon, 2 Mar 2020 23:07:00 +0000 (UTC) Date: Mon, 2 Mar 2020 23:07:00 +0000 (UTC) From: "Xinyi Yan (Jira)" To: dev@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/PHOENIX-5698?page=3Dcom.atlass= ian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xinyi Yan updated PHOENIX-5698: ------------------------------- Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch) > Phoenix Query with RVC IN list expression generates wrong scan with non-p= k ordered pks > -------------------------------------------------------------------------= ------------- > > Key: PHOENIX-5698 > URL: https://issues.apache.org/jira/browse/PHOENIX-5698 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.15.0, 4.14.3 > Reporter: Daniel Wong > Assignee: Xinyi Yan > Priority: Major > Labels: DESC > Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, PHOENIX-5698-4.x-= HBase-1.3.patch, PHOENIX-5698.patch > > Time Spent: 4h 40m > Remaining Estimate: 0h > > In the code below ideally we'd expect a SINGLE ROW DELETE plan client sid= e. However, this generates an incorrect scan with range ['tenant1=C2=A0=C2= =A0=C2=A0=C2=A00CY005xx000001Sv6o'). If the order of the RVCs is changed to= row key order Phoenix correctly generates a SINGLE ROW SCAN.=C2=A0 As we p= rovide the full PK this we expect a either tightly bounded range scan or a = client side delete.=C2=A0 Instead we get a range scan on composite leading = edge TENANT_ID,KEY_PREFIX,ID1. > =C2=A0 > {code:java} > @Test > public void testInListExpressionWithDescAgain() throws Exception { > String fullTableName =3D generateUniqueName(); > String fullViewName =3D generateUniqueName(); > String tenantView =3D generateUniqueName(); > // create base table and global view using global connection > try (Connection conn =3D DriverManager.getConnection(getUrl())) > { conn.setAutoCommit(true); Statement stmt =3D conn.createStatement(); st= mt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID CHAR(15) N= OT NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL,\n" + " CONSTRAINT PK PRIMARY K= EY (\n" + " TENANT_ID," + " KEY_PREFIX" + ")) MULTI_TENANT=3DTRUE"); stmt.e= xecute("CREATE VIEW " + fullViewName + "(\n" + " ID1 VARCHAR NOT NULL,\n" += " ID2 VARCHAR NOT NULL,\n" + " EVENT_DATE DATE NOT NULL,\n" + " CONSTRAINT= PKVIEW PRIMARY KEY\n" + " (\n" + " ID1, ID2 DESC, EVENT_DATE DESC\n" + "))= AS SELECT * FROM " + fullTableName + " WHERE KEY_PREFIX =3D '0CY'"); } > // create and use a tenant specific view to write data > try (Connection viewConn =3D DriverManager.getConnection(TENANT_SPECIFIC= _URL1) ) { > viewConn.setAutoCommit(true); //need autocommit for serverside deletion > Statement stmt =3D viewConn.createStatement(); > stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS SELECT * F= ROM " + fullViewName ); > viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, = ID2, EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000300', 153245823000= 0)"); > viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, = ID2, EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000400', 153245824000= 0)"); > viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, = ID2, EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000500', 153245825000= 0)"); > viewConn.commit(); > ResultSet rs =3D stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " + = tenantView ); > printResultSet(rs); > System.out.println("Delete Start"); > rs =3D stmt.executeQuery("EXPLAIN DELETE FROM " + tenantView + " WHERE (I= D1, EVENT_DATE, ID2) IN (('005xx000001Sv6o', 1532458240000, '00000000000040= 0'),('005xx000001Sv6o', 1532458230000, '000000000000300'))"); > printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN > stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2)= IN (('005xx000001Sv6o', 1532458240000, '000000000000400'),('005xx000001Sv6= o', 1532458230000, '000000000000300'))"); > viewConn.commit(); > System.out.println("Delete End"); > rs =3D stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " + tenantView= ); > printResultSet(rs); > } > } > private void printResultSet(ResultSet rs) throws SQLException { > StringBuilder builder =3D new StringBuilder(); > while(rs.next()) { > for(int i =3D 0; i < rs.getMetaData().getColumnCount(); i++) { > Object col =3D rs.getObject(i + 1); > if(col =3D=3D null) > { builder.append("null"); } > else { > if(col instanceof Date) > { DateFormat df =3D new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); builder.= append(df.format(col)); } > else { > builder.append(col.toString()); > } > } > builder.append(","); > } > builder.append("\n"); > } > System.out.println(builder.toString()); > } > {code} > =C2=A0 -- This message was sent by Atlassian Jira (v8.3.4#803005)