phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "chenglei (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (PHOENIX-3452) Secondary index and query using distinct: ORDER BY doesn't work correctly
Date Wed, 09 Nov 2016 14:32:58 GMT

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

chenglei edited comment on PHOENIX-3452 at 11/9/16 2:32 PM:
------------------------------------------------------------

Ok,I will try some IT tests.
Before  wrote the IT tests,  I need to make sure the meaning of "Nulls Last" I understood
is right:
In my opinion, the "Nulls Last" means the "NULL" always behind other "non-NULL" results in
the sorted results, no matter the OrderBy is ASC or DESC. 
The "Nulls First" means the "NULL" always before other "non-NULL" results in the sorted results,no
matter the OrderBy is ASC or DESC. 
So if we have a following table:
{code:borderStyle=solid} 
      CREATE TABLE IF NOT EXISTS GROUPBYDESC_TEST ( 
    	   ORGANIZATION_ID VARCHAR,
    	   CONTAINER_ID VARCHAR,
    	   ENTITY_ID VARCHAR NOT NULL,
    	   CONSTRAINT TEST_PK PRIMARY KEY ( 
    		ORGANIZATION_ID DESC,
    		CONTAINER_ID DESC,
    		ENTITY_ID));

        UPSERT INTO GROUPBYDESC_TEST VALUES ('a',null,'11');
        UPSERT INTO GROUPBYDESC_TEST VALUES (null,'2','22');
    	UPSERT INTO GROUPBYDESC_TEST VALUES ('c','3','33');
    	UPSERT INTO GROUPBYDESC_TEST VALUES (null,null,'44');
{code} 
 For the following sql:
{code:borderStyle=solid}
       select ORGANIZATION_ID, CONTAINER_ID,count(*)  from  GROUPBYDESC_TEST  
       group by ORGANIZATION_ID, CONTAINER_ID 
       order by ORGANIZATION_ID NULLS LAST, CONTAINER_ID NULLS FIRST
{code} 
the expecting result is:
{code:borderStyle=solid}
     a,  null  , 1
     c,    3   ,  1
    null, null,  1
    null,  2,     1
{code} 

For the following sql:
{code:borderStyle=solid}
     select ORGANIZATION_ID, CONTAINER_ID,count(*)  from GROUPBYDESC_TEST 
     group by ORGANIZATION_ID, CONTAINER_ID 
     order by ORGANIZATION_ID NULLS LAST, CONTAINER_ID NULLS LAST
{code} 
the expecting result is:
{code:borderStyle=solid}
        a,   null,  1
        c,     3,    1  
      null,    2,   1,
      null,   null, 1
{code} 

Am I right ?









was (Author: comnetwork):
Ok,I will try some IT tests

> Secondary index and query using distinct: ORDER BY doesn't work correctly
> -------------------------------------------------------------------------
>
>                 Key: PHOENIX-3452
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3452
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.8.0
>            Reporter: Joel Palmert
>            Assignee: chenglei
>         Attachments: PHOENIX-3452_v2.patch
>
>
> This may be related to PHOENIX-3451 but the behavior is different so filing it separately.
> Steps to repro:
> CREATE TABLE IF NOT EXISTS TEST.TEST (
>     ORGANIZATION_ID CHAR(15) NOT NULL,
>     CONTAINER_ID CHAR(15) NOT NULL,
>     ENTITY_ID CHAR(15) NOT NULL,
>     SCORE DOUBLE,
>     CONSTRAINT TEST_PK PRIMARY KEY (
>         ORGANIZATION_ID,
>         CONTAINER_ID,
>         ENTITY_ID
>     )
> ) VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=31536000;
> CREATE INDEX IF NOT EXISTS TEST_SCORE ON TEST.TEST (CONTAINER_ID, SCORE DESC, ENTITY_ID
DESC);
> UPSERT INTO test.test VALUES ('org1','container1','entityId6',1.1);
> UPSERT INTO test.test VALUES ('org1','container1','entityId5',1.2);
> UPSERT INTO test.test VALUES ('org1','container1','entityId4',1.3);
> UPSERT INTO test.test VALUES ('org1','container1','entityId3',1.4);
> UPSERT INTO test.test VALUES ('org1','container1','entityId2',1.5);
> UPSERT INTO test.test VALUES ('org1','container1','entityId1',1.6);
> SELECT DISTINCT entity_id, score
> FROM test.test
> WHERE organization_id = 'org1'
> AND container_id = 'container1'
> ORDER BY score DESC
> Notice that the returned results are not returned in descending score order. Instead
they are returned in descending entity_id order. If I remove the DISTINCT or remove the secondary
index the result is correct.



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

Mime
View raw message