[ https://issues.apache.org/jira/browse/CALCITE-5035?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17504240#comment-17504240 ]
Xurenhe commented on CALCITE-5035: ---------------------------------- I've been thinking this over and over recently Some sort elements could by removed, when the current elements is definite value There are some cases(as much as I can): 1. Constant key 2. Table's meta info, which has one row 3. `Limit 1` exist in the nether operator Welcome to let more cases. ---- Based on this cognition, we maybe define a new method in *RelMetadataQuery* to judge the field whether is only one value. Hi [~julianhyde] [~asolimando] Could you give me some directions about the above thought. I hope to received your response. Thanks a lot > 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 50m > 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)