[
https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
qianshen updated CALCITE-4772:
------------------------------
Priority: Blocker (was: Major)
> PushProject loses RexCall alias
> -------------------------------
>
> 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: Blocker
> 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)
