[jira] [Created] (CALCITE-5035) Define a rule of SortProjectPullUpConstantsRule to pull up constants project under Sort

["Xurenhe (Jira)" <ji...@apache.org>]

Xurenhe created CALCITE-5035:
--------------------------------

             Summary: Define a rule of SortProjectPullUpConstantsRule to pull up constants project under Sort
                 Key: CALCITE-5035
                 URL: https://issues.apache.org/jira/browse/CALCITE-5035
             Project: Calcite
          Issue Type: Improvement
            Reporter: Xurenhe
            Assignee: Xurenhe


Define a rule to pull up constants project under Sort

As we know, sorting by constant literal is meaningless.

After the predicates' optimizing, the element of sort may be a constant literal, as below:
{code:java}
-- 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

-- rel tree
-- after executing the rule of AggregateProjectPullUpConstantsRule
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]]){code}
The field of pay_id in sort is a constant literal, it's meaningless for sort's operator.

So, we could optimize it as below:
{code:java}
-- 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]]) {code}
 

Related discussion:https://lists.apache.org/thread/bq1gn6o7279f6563njhd5ln2j5178nwm

 

 

 



--
This message was sent by Atlassian Jira
(v8.20.1#820001)