phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "chenglei (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-3451) Secondary index and query using distinct: LIMIT doesn't return the first rows
Date Tue, 15 Nov 2016 07:39:59 GMT

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

chenglei commented on PHOENIX-3451:
-----------------------------------

[~jamestaylor].thank your for your suggestion, my considerations as follows:

1. If  GroupBy is "GROUP BY pkCol1 + 1, TRUNC(pkCol2)", the OrderBy must be "ORDER BY pkCol1
+ 1" or "ORDER BY TRUNC(pkCol2)", the OrderBy columns must match the GroupBy columns.
2. Only when all  GROUP BY/Order BY expressions are simple RowKey Columns (i.e. GROUP BY pkCol1,
pkCol2 or OrderBy BY pkCol1, pkCol2) , we have necessary to go further to  check if  the 
GROUP BY/Order BY is "isOrderPreserving". If  GROUP BY/Order BY expressions are not simple
RowKey Columns(i.e.GROUP BY pkCol1 + 1, TRUNC(pkCol2) or ORDER BY pkCol1 + 1, TRUNC(pkCol2)),
surely the  GROUP BY/Order BY should not be "isOrderPreserving".

So I think my patch is Ok, just as the following code explained,  it just need to only conside
the RowKeyColumnExpression. RowKeyColumnExpression is enough for checking if the Order BY
is "isOrderPreserving",for other type of Expression, the following visit method return null,
and the OrderPreservingTracker.isOrderPreserving method will return false,which is as  expected.

 {code:borderStyle=solid} 
        @Override
        public Info visit(RowKeyColumnExpression node) {
            if(groupBy==null || groupBy.isEmpty()) {
                return new Info(node.getPosition());
            }
            int pkPosition=node.getPosition();
            assert pkPosition < groupBy.getExpressions().size();
            Expression groupByExpression=groupBy.getExpressions().get(pkPosition);
            if(!(groupByExpression instanceof RowKeyColumnExpression)) {
                return null;
            }
            int orginalPkPosition=((RowKeyColumnExpression)groupByExpression).getPosition();
            return new Info(orginalPkPosition);
        }
 {code} 

By the way, I had already considered the modification as same as your suggestion when I made
my patch, finally I select current patch because it is more simpler ,and the modification
is just restricted in the single OrderPreservingTracker class,FYI.

> Secondary index and query using distinct: LIMIT doesn't return the first rows
> -----------------------------------------------------------------------------
>
>                 Key: PHOENIX-3451
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3451
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.8.0
>            Reporter: Joel Palmert
>            Assignee: chenglei
>         Attachments: PHOENIX-3451.diff
>
>
> This may be related to PHOENIX-3452 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 ('org2','container2','entityId6',1.1);
> UPSERT INTO test.test VALUES ('org2','container1','entityId5',1.2);
> UPSERT INTO test.test VALUES ('org2','container2','entityId4',1.3);
> UPSERT INTO test.test VALUES ('org2','container1','entityId3',1.4);
> UPSERT INTO test.test VALUES ('org2','container3','entityId7',1.35);
> UPSERT INTO test.test VALUES ('org2','container3','entityId8',1.45);
> EXPLAIN
> SELECT DISTINCT entity_id, score
> FROM test.test
> WHERE organization_id = 'org2'
> AND container_id IN ( 'container1','container2','container3' )
> ORDER BY score DESC
> LIMIT 2
> OUTPUT
> entityId5    1.2
> entityId3    1.4
> The expected out out would be
> entityId8    1.45
> entityId3    1.4
> You will get the expected output if you remove the secondary index from the table or
remove distinct from the query.
> As described in PHOENIX-3452 if you run the query without the LIMIT the ordering is not
correct. However, the 2first results in that ordering is still not the onces returned by the
limit clause, which makes me think there are multiple issues here and why I filed both separately.
The rows being returned are the ones assigned to container1. It looks like Phoenix is first
getting the rows from the first container and when it finds that to be enough it stops the
scan. What it should be doing is getting 2 results for each container and then merge then
and then limit again.



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

Mime
View raw message