[ https://issues.apache.org/jira/browse/PHOENIX-3777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15969741#comment-15969741 ]
James Taylor commented on PHOENIX-3777: --------------------------------------- [~singamteja] - have you been able to write a unit test for this yet so we can confirm that it's fixed by PHOENIX-3746? > 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 > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > > 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 > Assignee: Loknath Priyatham Teja Singamsetty > > 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 > WHERE id='00Dx000000091CU' > AND entity_id in ('0D5x0000006ARCN','0D5x0000006AQrO') > GROUP BY entity_id; > Current Output: > ============ > +-----------------+-----------------+-----------------+-----------------+-------+ > | ENTITY_ID | NTH1_USER_ID | NTH2_USER_ID | NTH3_USER_ID | COUNT | > +-----------------+-----------------+-----------------+-----------------+-------+ > | 0D5x0000006AQrO | 005x0000000ZSX0 | 005x0000000ZSWz| 005x0000000ZSWy | 50 > | > | 0D5x0000006ARCN | 005x0000000ZSX0 | | > > | 50 | > +-----------------+-----------------+-----------------+-----------------+-------+ > Expected Output: > ============== > +-----------------+-----------------+-----------------+-----------------+-------+ > | ENTITY_ID | NTH1_USER_ID | NTH2_USER_ID | NTH3_USER_ID | COUNT | > +-----------------+-----------------+-----------------+-----------------+-------+ > | 0D5x0000006AQrO | 005x0000000ZSX0 | 005x0000000ZSWz| 005x0000000ZSWy | 50 > | > | 0D5x0000006ARCN | 005x0000000ZSX0 | 005x0000000ZSWy| 005x0000000ZSWy | 50 > | > +-----------------+-----------------+-----------------+-----------------+-------+ > QUERY PLAN: > ============ > CLIENT 1-CHUNK 0 ROWS 0 BYTES PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER > FEEDS.FEED_ENTITY_READ ['00Dx000000091CU','0D5x0000006AQrO'] - > ['00Dx000000091CU','0D5x0000006ARCN’] > 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) > CONSTRAINT PKVIEW PRIMARY KEY ( ID, ENTITY_ID, USER_ID ) > ) VERSIONS=1,MULTI_TENANT=TRUE,REPLICATION_SCOPE=1 -- This message was sent by Atlassian JIRA (v6.3.15#6346)