phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Loknath Priyatham Teja Singamsetty (JIRA)" <j...@apache.org>
Subject [jira] [Created] (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
Date Mon, 10 Apr 2017 18:16:41 GMT
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)

Mime
View raw message