[CALCITE-4772] PushProjector should retain alias when handling RexCall

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.27.0
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Flags: Important
• Assignee: Unassigned
• Reporter: qianshen
• Created: 13/Sep/21 03:36
• Updated: 20/Oct/21 05:24
• Resolved: 23/Sep/21 09:34

Description

 

// 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]])

use HepPlanner RBO rules  optimize.

• CoreRules.FILTER_INTO_JOIN
• CoreRules.PROJECT_JOIN_TRANSPOSE

 

After optimize 

 

//代码占位符
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]])

 

In the original SQL, the query column id + age alias was dt. After optimization, the incorrect alias + was used.

After querying the source code, the problem appears in PushProjector.java#567 

 

Issue Links

• is related to: CALCITE-4733 Column names in materialization do not match
• links to: GitHub Pull Request #2516