[jira] [Created] (PHOENIX-3212) Column is null which should contains value after joining

["dzhu (JIRA)" <ji...@apache.org>]

dzhu created PHOENIX-3212:
-----------------------------

             Summary: Column is null which should contains value after joining
                 Key: PHOENIX-3212
                 URL: https://issues.apache.org/jira/browse/PHOENIX-3212
             Project: Phoenix
          Issue Type: Bug
         Environment: Phoenix 4.8.0
            Reporter: dzhu


Hi, I intend to retrieve top n values in each group in Phoenix SQL, the sample schema and data is as follows:

```
CREATE TABLE IF NOT EXISTS COMMUNITIES.processor_recommendation (
    ORGANIZATION_ID     CHAR(15) NOT NULL,
    NETWORK_ID             CHAR(15) NOT NULL,
    USER_ID             CHAR(15) NOT NULL,
    ACTION                 SMALLINT NOT NULL,
    PROCESSOR             INTEGER NOT NULL,
    ENTITY_ID            CHAR(15) NOT NULL,
    EXPLANATION         INTEGER,
    SCORE                 DOUBLE,
    EXPLANATION_DATA    VARCHAR(3999)
    CONSTRAINT processor_recommendation_PK PRIMARY KEY (
        ORGANIZATION_ID,
        NETWORK_ID,
        USER_ID,
        ACTION,
        PROCESSOR,
        ENTITY_ID
    )
) VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=86400

UPSERT INTO COMMUNITIES.processor_recommendation (ORGANIZATION_ID,NETWORK_ID,USER_ID,ACTION,PROCESSOR,ENTITY_ID,EXPLANATION,SCORE,EXPLANATION_DATA) VALUES ('00Dxx0000001gMu','000000000000000','005xx000001T0RL',0,0,'005xx000001T0RN',24,1.63299316186,null);
UPSERT INTO COMMUNITIES.processor_recommendation (ORGANIZATION_ID,NETWORK_ID,USER_ID,ACTION,PROCESSOR,ENTITY_ID,EXPLANATION,SCORE,EXPLANATION_DATA) VALUES ('00Dxx0000001gMu','000000000000000','005xx000001T0RL',0,0,'005xx000001T0RO',24,1.63299316186,null);
UPSERT INTO COMMUNITIES.processor_recommendation (ORGANIZATION_ID,NETWORK_ID,USER_ID,ACTION,PROCESSOR,ENTITY_ID,EXPLANATION,SCORE,EXPLANATION_DATA) VALUES ('00Dxx0000001gMu','000000000000000','005xx000001T0RL',0,0,'0TOxx00000000bT',32,1.63299316186,null);
UPSERT INTO COMMUNITIES.processor_recommendation (ORGANIZATION_ID,NETWORK_ID,USER_ID,ACTION,PROCESSOR,ENTITY_ID,EXPLANATION,SCORE,EXPLANATION_DATA) VALUES ('00Dxx0000001gMu','000000000000000','005xx000001T0RL',0,0,'0TOxx00000000bU',32,1.63299316186,null);

```

Firstly, I apply `nth_value` to retrieve the nth value in each group, calling the subtable `t`:

```
select * from
(
    select explanation, (nth_value(entity_id, 2) within group (order by entity_id asc)) as boundary from COMMUNITIES.processor_recommendation group by explanation
) t

Result:
24    005xx000001T0RO
32    0TOxx00000000bU

```

And after I join the table `t` with the original table, the `boundary` column is null, which should expectedly contain the above value. Could anyone help me out? Great thanks!

```
select pr.entity_id, t.explanation, t.boundary from COMMUNITIES.processor_recommendation pr
join
(
    select explanation, nth_value(entity_id, 2) within group (order by entity_id asc) as boundary from COMMUNITIES.processor_recommendation group by explanation
) t
on pr.explanation = t.explanation

Result:
005xx000001T0RN    24    <null>
005xx000001T0RO    24    <null>
0TOxx00000000bT    32    <null>
0TOxx00000000bU    32    <null>

``` 




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)