Could calcite support SortProjectPullUpConstantsRule?

[徐仁和 <xu...@gmail.com>]

Hi community:

Could calcite support the rule to pull up constant project from sort, just
like `AggregateProjectPullUpConstantsRule`?

-- sql
select pay_amount, pay_id, user_id
from pay_tbl
where pay_id = 1234
group by pay_amount, pay_id, user_id
order by pay_amount, pay_id, user_id

-- origin rel tree
LogicalSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC],
dir2=[ASC])
  LogicalProject(pay_amount=[$0], pay_id=[1234], user_id=[$1])
    LogicalAggregate(group=[{0, 1}])
      LogicalProject(pay_amount=[$1], user_id=[$3])
        LogicalFilter(condition=[=($0, 1234)])
          LogicalTableScan(table=[[default, pay_tbl]])

-- optimized rel tree
LogicalProject(pay_amount=[$0], pay_id=[1234], user_id=[$1])
  LogicalSort(sort0=[$0], sort2=[$1], dir0=[ASC], dir2=[ASC])
    LogicalProject(pay_amount=[$0], user_id=[$1])
      LogicalAggregate(group=[{0, 1}])
        LogicalProject(pay_amount=[$1], user_id=[$3])
          LogicalFilter(condition=[=($0, 1234)])
            LogicalTableScan(table=[[default, pay_tbl]])

Re: Could calcite support SortProjectPullUpConstantsRule?

[Julian Hyde <jh...@gmail.com>]

Sounds reasonable. Please log a jira. 

Consider implementing this rule by inspecting predicates or unique keys, rather than by matching a Project or Filter. If you can ascertain that a sort column has only one value (counting NULL as a value for these purposes) then you can remove it from the sort. 

I find that using metadata in this way is very effective. You get the desired effect without moving relational operators around. 

Julian

> On Mar 7, 2022, at 1:01 AM, 徐仁和 <xu...@gmail.com> wrote:
> 
> Hi community:
> 
> Could calcite support the rule to pull up constant project from sort, just
> like `AggregateProjectPullUpConstantsRule`?
> 
> -- sql
> select pay_amount, pay_id, user_id
> from pay_tbl
> where pay_id = 1234
> group by pay_amount, pay_id, user_id
> order by pay_amount, pay_id, user_id
> 
> -- origin rel tree
> LogicalSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC],
> dir2=[ASC])
>  LogicalProject(pay_amount=[$0], pay_id=[1234], user_id=[$1])
>    LogicalAggregate(group=[{0, 1}])
>      LogicalProject(pay_amount=[$1], user_id=[$3])
>        LogicalFilter(condition=[=($0, 1234)])
>          LogicalTableScan(table=[[default, pay_tbl]])
> 
> -- optimized rel tree
> LogicalProject(pay_amount=[$0], pay_id=[1234], user_id=[$1])
>  LogicalSort(sort0=[$0], sort2=[$1], dir0=[ASC], dir2=[ASC])
>    LogicalProject(pay_amount=[$0], user_id=[$1])
>      LogicalAggregate(group=[{0, 1}])
>        LogicalProject(pay_amount=[$1], user_id=[$3])
>          LogicalFilter(condition=[=($0, 1234)])
>            LogicalTableScan(table=[[default, pay_tbl]])