[jira] [Commented] (CALCITE-4772) PushProjecto convert bug

["Ruben Q L (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17413972#comment-17413972 ] 

Ruben Q L commented on CALCITE-4772:
------------------------------------

[~qianshen] Please set a more specific Jira title, it should describe the bug (may I suggest something along the lines "PushProject loses RexCall alias"? Apart from that, I guess the Jira type should be moved from "New feature" to "Bug". 

> 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
>            Priority: Major
>         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)