Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 22815200C58 for ; Sun, 16 Apr 2017 23:19:48 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 192B2160B9A; Sun, 16 Apr 2017 21:19:48 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 38958160B80 for ; Sun, 16 Apr 2017 23:19:47 +0200 (CEST) Received: (qmail 48660 invoked by uid 500); 16 Apr 2017 21:19:46 -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 48647 invoked by uid 99); 16 Apr 2017 21:19:46 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 16 Apr 2017 21:19:46 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id EF1BD18009B for ; Sun, 16 Apr 2017 21:19:45 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -99.202 X-Spam-Level: X-Spam-Status: No, score=-99.202 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, RP_MATCHES_RCVD=-0.001, SPF_PASS=-0.001, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id Xhe6JIUOtFPc for ; Sun, 16 Apr 2017 21:19:44 +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 ED52B5F20C for ; Sun, 16 Apr 2017 21:19:43 +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 504F5E05B1 for ; Sun, 16 Apr 2017 21:19:43 +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 EAF7920D54 for ; Sun, 16 Apr 2017 21:19:41 +0000 (UTC) Date: Sun, 16 Apr 2017 21:19:41 +0000 (UTC) From: "Loknath Priyatham Teja Singamsetty (JIRA)" To: dev@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (PHOENIX-3777) NTH_VALUE() function with multiple where clause filters on primary key components with GROUP BY is returning results for first grouped set and not for all grouped sets MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Sun, 16 Apr 2017 21:19:48 -0000 [ https://issues.apache.org/jira/browse/PHOENIX-3777?page=3Dcom.atlass= ian.jira.plugin.system.issuetabpanels:all-tabpanel ] Loknath Priyatham Teja Singamsetty updated PHOENIX-3777: --------------------------------------------------------- Fix Version/s: 4.10.1 4.11.0 Description:=20 Here is the reproducible case. The following query is failing: SELECT entity_id, NTH_VALUE(user_id,1) WITHIN GROUP (ORDER BY last_read_date DESC) as = nth1_user_id, NTH_VALUE(user_id,2) WITHIN GROUP (ORDER BY last_read_date DESC) as = nth2_user_id, NTH_VALUE(user_id,3) WITHIN GROUP (ORDER BY last_read_date DESC) as = nth3_user_id, count(*) FROM TEST.TEST=20 WHERE id=3D'00Dx000000091CU' AND entity_id in ('0D5x0000006ARCN','0D5x0000006AQrO') GROUP BY entity_id; Current Output: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D +-----------------+-----------------+-----------------+-----------------+--= -----+ | ENTITY_ID | NTH1_USER_ID | NTH2_USER_ID | NTH3_USER_ID | COUNT | +-----------------+-----------------+-----------------+-----------------+--= -----+ | 0D5x0000006AQrO | 005x0000000ZSX0 | 005x0000000ZSWz| 005x0000000ZSWy | 50= | | 0D5x0000006ARCN | 005x0000000ZSX0 | | = = | 50 | +-----------------+-----------------+-----------------+-----------------+--= -----+ Expected Output: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D +-----------------+-----------------+-----------------+-----------------+--= -----+ | ENTITY_ID | NTH1_USER_ID | NTH2_USER_ID | NTH3_USER_ID | COUNT | +-----------------+-----------------+-----------------+-----------------+--= -----+ | 0D5x0000006AQrO | 005x0000000ZSX0 | 005x0000000ZSWz| 005x0000000ZSWy | 50= | | 0D5x0000006ARCN | 005x0000000ZSX0 | 005x0000000ZSWy| 005x0000000ZSWy | 50= | +-----------------+-----------------+-----------------+-----------------+--= -----+ QUERY PLAN: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CLIENT 1-CHUNK 0 ROWS 0 BYTES PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER FEEDS= .FEED_ENTITY_READ ['00Dx000000091CU','0D5x0000006AQrO'] - ['00Dx000000091CU= ','0D5x0000006ARCN=E2=80=99] SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [FEED_ENTITY_ID] Schema: CREATE TABLE IF NOT EXISTS TEST.TEST ( ID CHAR(15) NOT NULL, ENTITY_ID CHAR(15) NOT NULL, USER_ID CHAR(15) NOT NULL, LAST_READ_DATE TIMESTAMP NULL, ENTITY_READ_ID CHAR(15)=20 CONSTRAINT PKVIEW PRIMARY KEY ( ID, ENTITY_ID, USER_ID ) ) VERSIONS=3D1,MULTI_TENANT=3DTRUE,REPLICATION_SCOPE=3D1 was: Here is the reproducible case. The following query is failing: SELECT entity_id, NTH_VALUE(user_id,1) WITHIN GROUP (ORDER BY last_read_date DESC) as = nth1_user_id, NTH_VALUE(user_id,2) WITHIN GROUP (ORDER BY last_read_date DESC) as = nth2_user_id, NTH_VALUE(user_id,3) WITHIN GROUP (ORDER BY last_read_date DESC) as = nth3_user_id, count(*) FROM TEST.TEST=20 WHERE id=3D'00Dx000000091CU' AND entity_id in ('0D5x0000006ARCN','0D5x0000006AQrO') GROUP BY entity_id; Current Output: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D +-----------------+-----------------+-----------------+-----------------+--= -----+ | ENTITY_ID | NTH1_USER_ID | NTH2_USER_ID | NTH3_USER_ID | COUNT | +-----------------+-----------------+-----------------+-----------------+--= -----+ | 0D5x0000006AQrO | 005x0000000ZSX0 | 005x0000000ZSWz| 005x0000000ZSWy | 50= | | 0D5x0000006ARCN | 005x0000000ZSX0 | | = = | 50 | +-----------------+-----------------+-----------------+-----------------+--= -----+ Expected Output: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D +-----------------+-----------------+-----------------+-----------------+--= -----+ | ENTITY_ID | NTH1_USER_ID | NTH2_USER_ID | NTH3_USER_ID | COUNT | +-----------------+-----------------+-----------------+-----------------+--= -----+ | 0D5x0000006AQrO | 005x0000000ZSX0 | 005x0000000ZSWz| 005x0000000ZSWy | 50= | | 0D5x0000006ARCN | 005x0000000ZSX0 | 005x0000000ZSWy| 005x0000000ZSWy | 50= | +-----------------+-----------------+-----------------+-----------------+--= -----+ QUERY PLAN: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CLIENT 1-CHUNK 0 ROWS 0 BYTES PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER FEEDS= .FEED_ENTITY_READ ['00Dx000000091CU','0D5x0000006AQrO'] - ['00Dx000000091CU= ','0D5x0000006ARCN=E2=80=99] SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [FEED_ENTITY_ID] Schema: CREATE TABLE IF NOT EXISTS TEST.TEST ( ID CHAR(15) NOT NULL, ENTITY_ID CHAR(15) NOT NULL, USER_ID CHAR(15) NOT NULL, LAST_READ_DATE TIMESTAMP NULL, ENTITY_READ_ID CHAR(15)=20 CONSTRAINT PKVIEW PRIMARY KEY ( ID, ENTITY_ID, USER_ID ) ) VERSIONS=3D1,MULTI_TENANT=3DTRUE,REPLICATION_SCOPE=3D1 > NTH_VALUE() function with multiple where clause filters on primary key co= mponents with GROUP BY is returning results for first grouped set and not f= or all grouped sets=20 > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------- > > Key: PHOENIX-3777 > URL: https://issues.apache.org/jira/browse/PHOENIX-3777 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.10.0 > Reporter: Loknath Priyatham Teja Singamsetty=20 > Assignee: Loknath Priyatham Teja Singamsetty=20 > Fix For: 4.11.0, 4.10.1 > > Attachments: PHOENIX-3777.patch > > > Here is the reproducible case. The following query is failing: > SELECT entity_id, > NTH_VALUE(user_id,1) WITHIN GROUP (ORDER BY last_read_date DESC) a= s nth1_user_id, > NTH_VALUE(user_id,2) WITHIN GROUP (ORDER BY last_read_date DESC) a= s nth2_user_id, > NTH_VALUE(user_id,3) WITHIN GROUP (ORDER BY last_read_date DESC) a= s nth3_user_id, > count(*) > FROM TEST.TEST=20 > WHERE id=3D'00Dx000000091CU' > AND entity_id in ('0D5x0000006ARCN','0D5x0000006AQrO') > GROUP BY entity_id; > Current Output: > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > +-----------------+-----------------+-----------------+-----------------+= -------+ > | ENTITY_ID | NTH1_USER_ID | NTH2_USER_ID | NTH3_USER_ID | COUNT | > +-----------------+-----------------+-----------------+-----------------+= -------+ > | 0D5x0000006AQrO | 005x0000000ZSX0 | 005x0000000ZSWz| 005x0000000ZSWy | = 50 | > | 0D5x0000006ARCN | 005x0000000ZSX0 | | = = | 50 | > +-----------------+-----------------+-----------------+-----------------+= -------+ > Expected Output: > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > +-----------------+-----------------+-----------------+-----------------+= -------+ > | ENTITY_ID | NTH1_USER_ID | NTH2_USER_ID | NTH3_USER_ID | COUNT | > +-----------------+-----------------+-----------------+-----------------+= -------+ > | 0D5x0000006AQrO | 005x0000000ZSX0 | 005x0000000ZSWz| 005x0000000ZSWy | = 50 | > | 0D5x0000006ARCN | 005x0000000ZSX0 | 005x0000000ZSWy| 005x0000000ZSWy | = 50 | > +-----------------+-----------------+-----------------+-----------------+= -------+ > QUERY PLAN: > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > CLIENT 1-CHUNK 0 ROWS 0 BYTES PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER FEE= DS.FEED_ENTITY_READ ['00Dx000000091CU','0D5x0000006AQrO'] - ['00Dx000000091= CU','0D5x0000006ARCN=E2=80=99] > SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [FEED_ENTITY_ID] > Schema: > CREATE TABLE IF NOT EXISTS TEST.TEST > ( ID CHAR(15) NOT NULL, > ENTITY_ID CHAR(15) NOT NULL, > USER_ID CHAR(15) NOT NULL, > LAST_READ_DATE TIMESTAMP NULL, > ENTITY_READ_ID CHAR(15)=20 > CONSTRAINT PKVIEW PRIMARY KEY ( ID, ENTITY_ID, USER_ID ) > ) VERSIONS=3D1,MULTI_TENANT=3DTRUE,REPLICATION_SCOPE=3D1 -- This message was sent by Atlassian JIRA (v6.3.15#6346)