[ https://issues.apache.org/jira/browse/CALCITE-5035?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17503285#comment-17503285 ]
Xurenhe commented on CALCITE-5035: ---------------------------------- [~julianhyde] Thanks for your reply. I try to rename this rule, according to your suggestion. But, I find that `CoreRules#SORT_REMOVE_CONSTANT_KEYS` is what I want, and this rule has existed. I think my work may be repetitive.:( Of course, I found that original test cases does not contain all the possibilities. So, could me update this pr only for adding more tests? > Define a rule of SortProjectPullUpConstantsRule to pull up constant's project > under Sort > ---------------------------------------------------------------------------------------- > > Key: CALCITE-5035 > URL: https://issues.apache.org/jira/browse/CALCITE-5035 > Project: Calcite > Issue Type: Improvement > Reporter: Xurenhe > Assignee: Xurenhe > Priority: Major > Labels: pull-request-available > Time Spent: 1h 20m > Remaining Estimate: 0h > > 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)