phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (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 Sun, 16 Apr 2017 23:32:41 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-3777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15970552#comment-15970552
] 

James Taylor commented on PHOENIX-3777:
---------------------------------------

+1 on the patch.

> 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 
>             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) 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