phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "chenglei (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-5148) Improve OrderPreservingTracker to optimize OrderBy/GroupBy for ClientScanPlan and ClientAggregatePlan
Date Thu, 21 Feb 2019 01:59:00 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-5148?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

chenglei updated PHOENIX-5148:
------------------------------
    Description: 
Given a table
{code:java}
  create table test ( 
       pk1 varchar not null , 
       pk2 varchar not null, 
       pk3 varchar not null,
        v1 varchar, 
        v2 varchar, 
        CONSTRAINT TEST_PK PRIMARY KEY ( 
              pk1,
              pk2,
              pk3 ))
{code}
Consider following three cases:

*1. OrderBy of ClientScanPlan*

for sql:
{code:java}
select v1 from (select v1,v2,pk3 from test t where pk1 = '6' order by t.v2,t.pk3,t.v1 limit
10) a order by v2,pk3
{code}
Obviously, the outer {{OrderBy}} "order by v2,pk3" should be compiled out because it matchs
the inner query {{OrderBy}} "order by t.v2,t.pk3,t.v1" , but unfortunately it is not compiled
out.

*2. GroupBy of ClientAggregatePlan*

for sql :
{code:java}
select v1 from (select v1,pk2,pk1 from test t where pk1 = '6' order by t.pk2,t.v1,t.pk1 limit
10) a group by pk2,v1
{code}
Obviously, the outer {{GroupBy}} "group by pk2,v1" should be orderPreserving because it matchs
the inner query {{OrderBy}} "order by t.pk2,t.v1,t.pk1" , but unfortunately the {{isOrderPreserving()}}
of outer {{GroupBy}} return false.

 

 

3. SortMergeJoinPlan(from PHOENIX-4618)

 

  was:
Given a table
{code:java}
  create table test ( 
       pk1 varchar not null , 
       pk2 varchar not null, 
       pk3 varchar not null,
        v1 varchar, 
        v2 varchar, 
        CONSTRAINT TEST_PK PRIMARY KEY ( 
              pk1,
              pk2,
              pk3 ))
{code}

Consider following three cases:


*1. OrderBy of ClientScanPlan*

for sql:
{code}
select v1 from (select v1,v2,pk3 from test t where pk1 = '6' order by t.v2,t.pk3,t.v1 limit
10) a order by v2,pk3
{code}

Obviously, the outer {{OrderBy}} "order by v2,pk3" should be compiled out because it matchs
the inner query {{OrderBy}} "order by t.v2,t.pk3,t.v1"  , but unfortunately  it is not compiled
out.


*2. GroupBy of ClientAggregatePlan*

for sql :
{code}
select v1 from (select v1,pk2,pk1 from test t where pk1 = '6' order by t.pk2,t.v1,t.pk1 limit
10) a group by pk2,v1
{code}

Obviously, the outer {{GroupBy}} "group by pk2,v1" should be orderPreserving because it matchs
the inner query {{OrderBy}} "order by t.pk2,t.v1,t.pk1" , but unfortunately the {{isOrderPreserving()}}
of outer {{GroupBy}} return false.





> Improve OrderPreservingTracker to optimize OrderBy/GroupBy for ClientScanPlan and ClientAggregatePlan
> -----------------------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-5148
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-5148
>             Project: Phoenix
>          Issue Type: Improvement
>    Affects Versions: 4.14.1
>            Reporter: chenglei
>            Priority: Major
>
> Given a table
> {code:java}
>   create table test ( 
>        pk1 varchar not null , 
>        pk2 varchar not null, 
>        pk3 varchar not null,
>         v1 varchar, 
>         v2 varchar, 
>         CONSTRAINT TEST_PK PRIMARY KEY ( 
>               pk1,
>               pk2,
>               pk3 ))
> {code}
> Consider following three cases:
> *1. OrderBy of ClientScanPlan*
> for sql:
> {code:java}
> select v1 from (select v1,v2,pk3 from test t where pk1 = '6' order by t.v2,t.pk3,t.v1
limit 10) a order by v2,pk3
> {code}
> Obviously, the outer {{OrderBy}} "order by v2,pk3" should be compiled out because it
matchs the inner query {{OrderBy}} "order by t.v2,t.pk3,t.v1" , but unfortunately it is not
compiled out.
> *2. GroupBy of ClientAggregatePlan*
> for sql :
> {code:java}
> select v1 from (select v1,pk2,pk1 from test t where pk1 = '6' order by t.pk2,t.v1,t.pk1
limit 10) a group by pk2,v1
> {code}
> Obviously, the outer {{GroupBy}} "group by pk2,v1" should be orderPreserving because
it matchs the inner query {{OrderBy}} "order by t.pk2,t.v1,t.pk1" , but unfortunately the
{{isOrderPreserving()}} of outer {{GroupBy}} return false.
>  
>  
> 3. SortMergeJoinPlan(from PHOENIX-4618)
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message