qianshen created CALCITE-4772: --------------------------------- Summary: PushProjecto convert bug Key: CALCITE-4772 URL: https://issues.apache.org/jira/browse/CALCITE-4772 Project: Calcite Issue Type: New Feature Components: core Affects Versions: 1.27.0 Reporter: qianshen Attachments: image-2021-09-13-11-36-26-902.png
{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)