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)
