Loknath Priyatham Teja Singamsetty created PHOENIX-3777:
------------------------------------------------------------
Summary: 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
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)
