[ https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17415889#comment-17415889 ]
xzh_dz commented on CALCITE-4772: --------------------------------- After the SQL optimized, the alias of SQL should be the same, we should fix it. > PushProjector assigns incorrect field alias when handling RexCall > ----------------------------------------------------------------- > > Key: CALCITE-4772 > URL: https://issues.apache.org/jira/browse/CALCITE-4772 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.27.0 > Reporter: qianshen > Priority: Major > Labels: pull-request-available > Attachments: image-2021-09-13-11-36-26-902.png > > Time Spent: 10m > Remaining Estimate: 0h > > > {code:java} > // source sql > SELECT USER_ID > ,USER_NAME > ,DEPT_NO > ,ROLE_ID > ,ROLE_NAME > ,ROLE_NO > ,ID > ,NAME > ,id + age as dt > > FROM ( > SELECT user_id,user_name,dept_no > FROM user_info > WHERE dept_no > 20 > ) AS USER_INFO > LEFT JOIN ( > SELECT role_id,role_name,role_no > FROM role_info > WHERE role_no > 20 > ) AS role_info > ON USER_ID = ROLE_ID > LEFT JOIN ( > SELECT id,name,age > FROM ld_ant_test.dim > WHERE age >22 and (name='a' or name ='b') > ) dim > ON dim.id = USER_INFO.USER_ID where user_name='a' > //execute plan > LogicalProject(USER_ID=[$0], USER_NAME=[$1], DEPT_NO=[$2], ROLE_ID=[$3], > ROLE_NAME=[$4], ROLE_NO=[$5], ID=[$6], NAME=[$7], dt=[+($6, $8)]) > LogicalFilter(condition=[=($1, 'a')]) > LogicalJoin(condition=[=($6, $0)], joinType=[left]) > LogicalJoin(condition=[=($0, $3)], joinType=[left]) > LogicalProject(user_id=[$0], user_name=[$1], dept_no=[$2]) > LogicalFilter(condition=[>($2, 20)]) > LogicalTableScan(table=[[LD_ANT_TEST, USER_INFO]]) > LogicalProject(role_id=[$0], role_name=[$1], role_no=[$2]) > LogicalFilter(condition=[>($2, 20)]) > LogicalTableScan(table=[[LD_ANT_TEST, ROLE_INFO]]) > LogicalProject(id=[$0], name=[$1], age=[$2]) > LogicalFilter(condition=[AND(>($2, 22), OR(=($1, 'a'), =($1, 'b')))]) > LogicalTableScan(table=[[LD_ANT_TEST, DIM]]) > {code} > use HepPlanner RBO rules optimize. > * CoreRules.FILTER_INTO_JOIN > * CoreRules.PROJECT_JOIN_TRANSPOSE > > After optimize > > {code:java} > //代码占位符 > SELECT * > FROM (SELECT * > FROM (SELECT * > FROM (SELECT `USER_ID` AS `user_id`, `USER_NAME` AS `user_name`, `DEPT_NO` AS > `dept_no` > FROM `LD_ANT_TEST`.`USER_INFO` > WHERE `DEPT_NO` > 20) AS `t0` > WHERE `user_name` = 'a') AS `t1` > LEFT JOIN (SELECT `ROLE_ID` AS `role_id`, `ROLE_NAME` AS `role_name`, > `ROLE_NO` AS `role_no` > FROM `LD_ANT_TEST`.`ROLE_INFO` > WHERE `ROLE_NO` > 20) AS `t3` ON `t1`.`user_id` = `t3`.`role_id`) AS `t4` > LEFT JOIN (SELECT `ID` AS `id`, `NAME` AS `name`, `ID` + `AGE` AS `+` > FROM `LD_ANT_TEST`.`DIM` > WHERE `AGE` > 22 AND (`NAME` = 'a' OR `NAME` = 'b')) AS `t7` ON > `t4`.`user_id` = `t7`.`id` > LogicalJoin(condition=[=($6, $0)], joinType=[left]) > LogicalProject(user_id=[$0], user_name=[$1], dept_no=[$2], role_id=[$3], > role_name=[$4], role_no=[$5]) > LogicalJoin(condition=[=($0, $3)], joinType=[left]) > LogicalFilter(condition=[=($1, 'a')]) > LogicalProject(user_id=[$0], user_name=[$1], dept_no=[$2]) > LogicalFilter(condition=[>($2, 20)]) > LogicalTableScan(table=[[LD_ANT_TEST, USER_INFO]]) > LogicalProject(role_id=[$0], role_name=[$1], role_no=[$2]) > LogicalFilter(condition=[>($2, 20)]) > LogicalTableScan(table=[[LD_ANT_TEST, ROLE_INFO]]) > LogicalProject(id=[$0], name=[$1], +=[+($0, $2)]) > LogicalProject(id=[$0], name=[$1], age=[$2]) > LogicalFilter(condition=[AND(>($2, 22), OR(=($1, 'a'), =($1, 'b')))]) > LogicalTableScan(table=[[LD_ANT_TEST, DIM]]) > {code} > > In the original SQL, the query column{color:#FF0000} id + age alias was > dt{color}. After optimization, {color:#FF0000}the incorrect alias + was > used.{color} > After querying the source code, the problem appears in > {color:#FF0000}PushProjector.java#567 {color} > !image-2021-09-13-11-36-26-902.png! > -- This message was sent by Atlassian Jira (v8.3.4#803005)