[jira] [Commented] (CALCITE-4772) PushProjecto convert bug
[
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
> FROMuser_info
> WHERE dept_no > 20
> ) AS USER_INFO
> LEFT JOIN(
> SELECT role_id,role_name,role_no
> FROMrole_info
> WHERE role_no > 20
> ) AS role_info
> ON USER_ID = ROLE_ID
> LEFT JOIN(
> SELECT id,name,age
> FROMld_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:#FF} id + age alias was
> dt{color}. After optimization, {color:#FF}the incorrect alias + was
> used.{color}
> After querying the source code, the problem appears in
> {color:#FF}PushProjector.java#567 {color}
> !image-2021-09-13-11-36-26-902.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4772) PushProject loses RexCall alias
[
https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
qianshen updated CALCITE-4772:
--
Summary: PushProject loses RexCall alias (was: PushProjecto convert bug)
> PushProject loses RexCall alias
> ---
>
> 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
> FROMuser_info
> WHERE dept_no > 20
> ) AS USER_INFO
> LEFT JOIN(
> SELECT role_id,role_name,role_no
> FROMrole_info
> WHERE role_no > 20
> ) AS role_info
> ON USER_ID = ROLE_ID
> LEFT JOIN(
> SELECT id,name,age
> FROMld_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:#FF} id + age alias was
> dt{color}. After optimization, {color:#FF}the incorrect alias + was
> used.{color}
> After querying the source code, the problem appears in
> {color:#FF}PushProjector.java#567 {color}
> !image-2021-09-13-11-36-26-902.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4772) PushProject loses RexCall alias
[
https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
qianshen updated CALCITE-4772:
--
Issue Type: Bug (was: New Feature)
> 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: 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
> FROMuser_info
> WHERE dept_no > 20
> ) AS USER_INFO
> LEFT JOIN(
> SELECT role_id,role_name,role_no
> FROMrole_info
> WHERE role_no > 20
> ) AS role_info
> ON USER_ID = ROLE_ID
> LEFT JOIN(
> SELECT id,name,age
> FROMld_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:#FF} id + age alias was
> dt{color}. After optimization, {color:#FF}the incorrect alias + was
> used.{color}
> After querying the source code, the problem appears in
> {color:#FF}PushProjector.java#567 {color}
> !image-2021-09-13-11-36-26-902.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4772) PushProject loses RexCall alias
[
https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17413982#comment-17413982
]
qianshen commented on CALCITE-4772:
---
[~rubenql] sorry It was my fault. i will update it.
> 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: 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
> FROMuser_info
> WHERE dept_no > 20
> ) AS USER_INFO
> LEFT JOIN(
> SELECT role_id,role_name,role_no
> FROMrole_info
> WHERE role_no > 20
> ) AS role_info
> ON USER_ID = ROLE_ID
> LEFT JOIN(
> SELECT id,name,age
> FROMld_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:#FF} id + age alias was
> dt{color}. After optimization, {color:#FF}the incorrect alias + was
> used.{color}
> After querying the source code, the problem appears in
> {color:#FF}PushProjector.java#567 {color}
> !image-2021-09-13-11-36-26-902.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4772) PushProject loses RexCall alias
[
https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17413983#comment-17413983
]
qianshen commented on CALCITE-4772:
---
[~rubenql] I will provider my merge request
> 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: 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
> FROMuser_info
> WHERE dept_no > 20
> ) AS USER_INFO
> LEFT JOIN(
> SELECT role_id,role_name,role_no
> FROMrole_info
> WHERE role_no > 20
> ) AS role_info
> ON USER_ID = ROLE_ID
> LEFT JOIN(
> SELECT id,name,age
> FROMld_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:#FF} id + age alias was
> dt{color}. After optimization, {color:#FF}the incorrect alias + was
> used.{color}
> After querying the source code, the problem appears in
> {color:#FF}PushProjector.java#567 {color}
> !image-2021-09-13-11-36-26-902.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4770) Design of SearchMvService
[ https://issues.apache.org/jira/browse/CALCITE-4770?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17413995#comment-17413995 ] Alessandro Solimando commented on CALCITE-4770: --- Hi [~wojustme], I am not saying that the paper is used (or should be used) everywhere in Calcite, I am rather suggesting a comparison of the data structures and associated "compatibility checks" from the paper and those from your proposal. I see some overlapping between the paper and the proposal (they are both trying to reduce the number of MVs), so I think that understanding how your proposal extends the paper could be beneficial for both *MaterializedViewRule#perform* and ** *SubstitutionVisitor#go*.** > Design of SearchMvService > - > > Key: CALCITE-4770 > URL: https://issues.apache.org/jira/browse/CALCITE-4770 > Project: Calcite > Issue Type: New Feature >Reporter: xzh_dz >Priority: Major > > In the data system, materialized view can speed up SQL query. Materialized > view represents the pre-calculated SQL logic of the target table and contains > real data. In the project, we use materialized views to speed up SQL queries, > use the materialized view recognition technology to identify materialized > views, and enhance the ability of materialized recognition. However, with > more and more materialized views , materialized view recognition takes a lot > of time, which seriously affects the performance of SQL queries. With this > problem, we propose a design of accurate search materialized view based on > operator features. > DOC：[Design of > SearchMvService|[https://docs.google.com/document/d/1mmAsK_uW-fBs893JERP1gspMurX2lNXcVGeesP4XUqQ/edit]] > Welcome to discuss and expect more feedback,thank you. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4758) When sub-query is SqlNodeList, Calcite returns wrong result
[ https://issues.apache.org/jira/browse/CALCITE-4758?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-4758: Labels: pull-request-available (was: ) > When sub-query is SqlNodeList, Calcite returns wrong result > > > Key: CALCITE-4758 > URL: https://issues.apache.org/jira/browse/CALCITE-4758 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.27.0 >Reporter: duan xiong >Assignee: duan xiong >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > When inSubQueryThreshold is less than the SqlNodeList Size, The SqlNodeList > can't convert to OR condition. Then the result is wrong sometimes. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4756) When subquery include NULL value, Calcite should return the right result
[
https://issues.apache.org/jira/browse/CALCITE-4756?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-4756:
Labels: pull-request-available (was: )
> When subquery include NULL value, Calcite should return the right result
>
>
> Key: CALCITE-4756
> URL: https://issues.apache.org/jira/browse/CALCITE-4756
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.27.0
>Reporter: duan xiong
>Assignee: duan xiong
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> When I try to design some unit tests for CALCITE-4742. I found Calcite can't
> handle NULL very well in some subquery.
> For example:
> {code:java}
> select 1 in (values(null),(null))
> ERROR:
> java.lang.AssertionError: Conversion to relational algebra failed to
> preserve datatypes:
> validated type:
> RecordType(BOOLEAN NOT NULL EXPR$0) NOT NULL
> converted type:
> RecordType(BOOLEAN EXPR$0) NOT NULL
> rel:
> LogicalProject(EXPR$0=[IN(1, {
> LogicalValues(tuples=[[{ null }]])
> })])
> LogicalValues(tuples=[[{ 0 }]])
> select 1 > some(values(null),(null))
> ERROR:
> Suppressed: java.lang.RuntimeException: while resolving method
> 'voidValue' in class class java.lang.Void{code}
> This SQL should return NULL. But throw the runtime exception.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4702) Error when executing query with GROUP BY constant via JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414073#comment-17414073
]
Stamatis Zampetakis commented on CALCITE-4702:
--
[~julianhyde] unfortunately the solution does not work for all DBMS which seems
to necessitate the +complex+ transformation mentioned previously.
For example I just tested the following queries in Informix and none works:
{noformat}
0: jdbc:informix-sqli://localhost:9088/sysuse> select * from person group by
true;
Error: Column (true) not found in any table in the query (or SLV is undefined).
(state=IX000,code=-217)
0: jdbc:informix-sqli://localhost:9088/sysuse> select * from person group by
(select true);
Error: A syntax error has occurred. (state=42000,code=-201)
0: jdbc:informix-sqli://localhost:9088/sysuse> select * from person group by
(select 1);
Error: A syntax error has occurred. (state=42000,code=-201)
0: jdbc:informix-sqli://localhost:9088/sysuse> select * from person group by ();
Error: A syntax error has occurred. (state=42000,code=-201)
0: jdbc:informix-sqli://localhost:9088/sysuse> select * from person group by
'a';
Error: A syntax error has occurred. (state=42000,code=-201)
{noformat}
According to the JOOQ blog post the +complex+ transformation is also necessary
for the following DBMS (although I didn't test all of them):
* Access
* Informix
* Ingres
* SQL Data Warehouse
* Sybase ASE
> Error when executing query with GROUP BY constant via JDBC adapter
> --
>
> Key: CALCITE-4702
> URL: https://issues.apache.org/jira/browse/CALCITE-4702
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.27.0
>Reporter: Stamatis Zampetakis
>Assignee: Soumyakanti Das
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following functionally equivalent SQL queries are accepted by Calcite and
> produce a valid plan
> {noformat}
> select avg(salary) from employee group by true
> select avg(salary) from employee group by 'a'
> {noformat}
> but they may fail if they are executed via the JDBC adapter since not all
> DBMS allow grouping by constants expressions. Moreover, what works for one
> may not work for the other.
> +Examples+
> The {{GROUP BY TRUE}} query works in Postgres, and MySQL but fails in
> Redshift with the following exception:
> {noformat}
> com.amazon.redshift.util.RedshiftException: ERROR: non-integer constant in
> GROUP BY
> {noformat}
> The {{GROUP BY 'a'}} query works in MySQL but fails in Postgres with the
> following exception:
> {noformat}
> ERROR: non-integer constant in GROUP BY
> {noformat}
> +Edit:+
> The {{GROUP BY}} constant is similar to {{GROUP BY ()}} "nothing" but as
> shown in the discussion below they are not equivalent. There is a nice [blog
> post|https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/]
> listing some limitations of various DBMS when it comes to {{GROUP BY ()}}.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4772) PushProject loses RexCall alias
[
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
> FROMuser_info
> WHERE dept_no > 20
> ) AS USER_INFO
> LEFT JOIN(
> SELECT role_id,role_name,role_no
> FROMrole_info
> WHERE role_no > 20
> ) AS role_info
> ON USER_ID = ROLE_ID
> LEFT JOIN(
> SELECT id,name,age
> FROMld_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:#FF} id + age alias was
> dt{color}. After optimization, {color:#FF}the incorrect alias + was
> used.{color}
> After querying the source code, the problem appears in
> {color:#FF}PushProjector.java#567 {color}
> !image-2021-09-13-11-36-26-902.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row type
Ruben Q L created CALCITE-4773:
--
Summary: RelDecorrelator's RemoveSingleAggregateRule produces
result with wrong row type
Key: CALCITE-4773
URL: https://issues.apache.org/jira/browse/CALCITE-4773
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.27.0
Reporter: Ruben Q L
Assignee: Ruben Q L
Fix For: 1.28.0
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
...
{noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L updated CALCITE-4773:
---
Description:
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
...
{noformat}
The root cause seems to be in this piece of code inside
was:
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> at
> org.apache.calcite.p
[jira] [Updated] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L updated CALCITE-4773:
---
Description:
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
...
{noformat}
The root cause seems to be in this piece of code inside {{RelDecorrelator}}'s
{{RemoveSingleAggregateRule#onMatch}}:
{code}
// singleAggRel produces a nullable type, so create the new
// projection that casts proj expr to a nullable type.
final RelBuilder relBuilder = call.builder();
final RelDataType type =
relBuilder.getTypeFactory()
.createTypeWithNullability(projExprs.get(0).getType(), true);
final RexNode cast = relBuilder.getRexBuilder().makeCast(type,
projExprs.get(0));
relBuilder.push(aggregate).project(cast);
call.transformTo(relBuilder.build());
{code}
Note that the comment assumes that "singleAggRel produces a nullable type", but
in this particular case, it seems to produce a nullable type, so probably this
piece of code needs to be adapted.
was:
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUtil.verify
[jira] [Updated] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L updated CALCITE-4773:
---
Description:
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
...
{noformat}
The root cause seems to be in this piece of code inside {{RelDecorrelator}}'s
{{RemoveSingleAggregateRule#onMatch}}:
{code}
// singleAggRel produces a nullable type, so create the new
// projection that casts proj expr to a nullable type.
final RelBuilder relBuilder = call.builder();
final RelDataType type =
relBuilder.getTypeFactory()
.createTypeWithNullability(projExprs.get(0).getType(), true);
final RexNode cast = relBuilder.getRexBuilder().makeCast(type,
projExprs.get(0));
relBuilder.push(aggregate).project(cast);
call.transformTo(relBuilder.build());
{code}
Note that the comment assumes that _"singleAggRel produces a nullable type"_,
but in this particular case, it seems to produce a nullable type, so probably
this piece of code needs to be adapted.
was:
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUtil.veri
[jira] [Updated] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L updated CALCITE-4773:
---
Description:
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
...
{noformat}
The root cause seems to be in this piece of code inside {{RelDecorrelator}}'s
{{RemoveSingleAggregateRule#onMatch}}:
{code:java}
// singleAggRel produces a nullable type, so create the new
// projection that casts proj expr to a nullable type.
final RelBuilder relBuilder = call.builder();
final RelDataType type =
relBuilder.getTypeFactory()
.createTypeWithNullability(projExprs.get(0).getType(), true);
final RexNode cast = relBuilder.getRexBuilder().makeCast(type,
projExprs.get(0));
relBuilder.push(aggregate).project(cast);
call.transformTo(relBuilder.build());
{code}
Note that the comment assumes that _"singleAggRel produces a nullable type"_,
but in this particular case, it seems to produce a non-nullable type, so
probably this piece of code needs to be adapted.
was:
The problem can be reproduced with the following test (to be added to
{{unnest.iq}}):
{noformat}
SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
SELECT *
FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
SELECT COUNT(unnested_inner.val) > 0
FROM UNNEST(t.y) AS unnested_inner(val)
WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-+---+
| VAL | COUNT_VAL |
+-+---+
| 6 | 2 |
| 4 | 1 |
+-+---+
(2 rows)
!ok
{noformat}
When the test is executed, it fails with the following error:
{noformat}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is
> rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> LogicalAggregate(group=[{}], agg#0=[COUNT()])
> LogicalFilter(condition=[=($0, 4)])
> LogicalProject(VAL=[$0])
> Uncollect
> LogicalProject(Y=[$cor1.Y])
> LogicalValues(tuples=[[{ 0 }]])
249a254,325
> at
> org.apache.calcite.plan.RelOptUti
[jira] [Updated] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L updated CALCITE-4773:
---
Attachment: CP-22140_fix.patch
> RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row
> type
> ---
>
> Key: CALCITE-4773
> URL: https://issues.apache.org/jira/browse/CALCITE-4773
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.27.0
>Reporter: Ruben Q L
>Assignee: Ruben Q L
>Priority: Major
> Fix For: 1.28.0
>
>
> The problem can be reproduced with the following test (to be added to
> {{unnest.iq}}):
> {noformat}
> SELECT unnested_outer.val, COUNT(1) AS count_val
> FROM
> (
> SELECT *
> FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
> AS u(x, y)
> ) AS t, UNNEST(t.y) AS unnested_outer(val)
> WHERE
> (
> SELECT COUNT(unnested_inner.val) > 0
> FROM UNNEST(t.y) AS unnested_inner(val)
> WHERE unnested_inner.val = 4
> )
> GROUP BY unnested_outer.val
> ORDER BY count_val DESC, unnested_outer.val ASC;
> +-+---+
> | VAL | COUNT_VAL |
> +-+---+
> | 6 | 2 |
> | 4 | 1 |
> +-+---+
> (2 rows)
> !ok
> {noformat}
> When the test is executed, it fails with the following error:
> {noformat}
> > java.lang.AssertionError: Cannot add expression of different type to set:
> > set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> > expression type is RecordType(BOOLEAN $f0) NOT NULL
> > set is
> > rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> > expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> > LogicalAggregate(group=[{}], agg#0=[COUNT()])
> > LogicalFilter(condition=[=($0, 4)])
> > LogicalProject(VAL=[$0])
> > Uncollect
> > LogicalProject(Y=[$cor1.Y])
> > LogicalValues(tuples=[[{ 0 }]])
> 249a254,325
> > at
> > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> > at
> > org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> > at
> > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> > at
> > org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
> ...
> {noformat}
> The root cause seems to be in this piece of code inside {{RelDecorrelator}}'s
> {{RemoveSingleAggregateRule#onMatch}}:
> {code:java}
> // singleAggRel produces a nullable type, so create the new
> // projection that casts proj expr to a nullable type.
> final RelBuilder relBuilder = call.builder();
> final RelDataType type =
> relBuilder.getTypeFactory()
> .createTypeWithNullability(projExprs.get(0).getType(), true);
> final RexNode cast = relBuilder.getRexBuilder().makeCast(type,
> projExprs.get(0));
> relBuilder.push(aggregate).project(cast);
> call.transformTo(relBuilder.build());
> {code}
> Note that the comment assumes that _"singleAggRel produces a nullable type"_,
> but in this particular case, it seems to produce a non-nullable type, so
> probably this piece of code needs to be adapted.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L updated CALCITE-4773:
---
Attachment: (was: CP-22140_fix.patch)
> RelDecorrelator's RemoveSingleAggregateRule produces result with wrong row
> type
> ---
>
> Key: CALCITE-4773
> URL: https://issues.apache.org/jira/browse/CALCITE-4773
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.27.0
>Reporter: Ruben Q L
>Assignee: Ruben Q L
>Priority: Major
> Fix For: 1.28.0
>
>
> The problem can be reproduced with the following test (to be added to
> {{unnest.iq}}):
> {noformat}
> SELECT unnested_outer.val, COUNT(1) AS count_val
> FROM
> (
> SELECT *
> FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
> AS u(x, y)
> ) AS t, UNNEST(t.y) AS unnested_outer(val)
> WHERE
> (
> SELECT COUNT(unnested_inner.val) > 0
> FROM UNNEST(t.y) AS unnested_inner(val)
> WHERE unnested_inner.val = 4
> )
> GROUP BY unnested_outer.val
> ORDER BY count_val DESC, unnested_outer.val ASC;
> +-+---+
> | VAL | COUNT_VAL |
> +-+---+
> | 6 | 2 |
> | 4 | 1 |
> +-+---+
> (2 rows)
> !ok
> {noformat}
> When the test is executed, it fails with the following error:
> {noformat}
> > java.lang.AssertionError: Cannot add expression of different type to set:
> > set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> > expression type is RecordType(BOOLEAN $f0) NOT NULL
> > set is
> > rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> > expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> > LogicalAggregate(group=[{}], agg#0=[COUNT()])
> > LogicalFilter(condition=[=($0, 4)])
> > LogicalProject(VAL=[$0])
> > Uncollect
> > LogicalProject(Y=[$cor1.Y])
> > LogicalValues(tuples=[[{ 0 }]])
> 249a254,325
> > at
> > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> > at
> > org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> > at
> > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> > at
> > org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
> ...
> {noformat}
> The root cause seems to be in this piece of code inside {{RelDecorrelator}}'s
> {{RemoveSingleAggregateRule#onMatch}}:
> {code:java}
> // singleAggRel produces a nullable type, so create the new
> // projection that casts proj expr to a nullable type.
> final RelBuilder relBuilder = call.builder();
> final RelDataType type =
> relBuilder.getTypeFactory()
> .createTypeWithNullability(projExprs.get(0).getType(), true);
> final RexNode cast = relBuilder.getRexBuilder().makeCast(type,
> projExprs.get(0));
> relBuilder.push(aggregate).project(cast);
> call.transformTo(relBuilder.build());
> {code}
> Note that the comment assumes that _"singleAggRel produces a nullable type"_,
> but in this particular case, it seems to produce a non-nullable type, so
> probably this piece of code needs to be adapted.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule can produce result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L updated CALCITE-4773:
---
Summary: RelDecorrelator's RemoveSingleAggregateRule can produce result
with wrong row type (was: RelDecorrelator's RemoveSingleAggregateRule produces
result with wrong row type)
> RelDecorrelator's RemoveSingleAggregateRule can produce result with wrong row
> type
> --
>
> Key: CALCITE-4773
> URL: https://issues.apache.org/jira/browse/CALCITE-4773
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.27.0
>Reporter: Ruben Q L
>Assignee: Ruben Q L
>Priority: Major
> Fix For: 1.28.0
>
>
> The problem can be reproduced with the following test (to be added to
> {{unnest.iq}}):
> {noformat}
> SELECT unnested_outer.val, COUNT(1) AS count_val
> FROM
> (
> SELECT *
> FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
> AS u(x, y)
> ) AS t, UNNEST(t.y) AS unnested_outer(val)
> WHERE
> (
> SELECT COUNT(unnested_inner.val) > 0
> FROM UNNEST(t.y) AS unnested_inner(val)
> WHERE unnested_inner.val = 4
> )
> GROUP BY unnested_outer.val
> ORDER BY count_val DESC, unnested_outer.val ASC;
> +-+---+
> | VAL | COUNT_VAL |
> +-+---+
> | 6 | 2 |
> | 4 | 1 |
> +-+---+
> (2 rows)
> !ok
> {noformat}
> When the test is executed, it fails with the following error:
> {noformat}
> > java.lang.AssertionError: Cannot add expression of different type to set:
> > set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> > expression type is RecordType(BOOLEAN $f0) NOT NULL
> > set is
> > rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> > expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> > LogicalAggregate(group=[{}], agg#0=[COUNT()])
> > LogicalFilter(condition=[=($0, 4)])
> > LogicalProject(VAL=[$0])
> > Uncollect
> > LogicalProject(Y=[$cor1.Y])
> > LogicalValues(tuples=[[{ 0 }]])
> 249a254,325
> > at
> > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> > at
> > org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> > at
> > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> > at
> > org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
> ...
> {noformat}
> The root cause seems to be in this piece of code inside {{RelDecorrelator}}'s
> {{RemoveSingleAggregateRule#onMatch}}:
> {code:java}
> // singleAggRel produces a nullable type, so create the new
> // projection that casts proj expr to a nullable type.
> final RelBuilder relBuilder = call.builder();
> final RelDataType type =
> relBuilder.getTypeFactory()
> .createTypeWithNullability(projExprs.get(0).getType(), true);
> final RexNode cast = relBuilder.getRexBuilder().makeCast(type,
> projExprs.get(0));
> relBuilder.push(aggregate).project(cast);
> call.transformTo(relBuilder.build());
> {code}
> Note that the comment assumes that _"singleAggRel produces a nullable type"_,
> but in this particular case, it seems to produce a non-nullable type, so
> probably this piece of code needs to be adapted.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule can produce result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-4773:
Labels: pull-request-available (was: )
> RelDecorrelator's RemoveSingleAggregateRule can produce result with wrong row
> type
> --
>
> Key: CALCITE-4773
> URL: https://issues.apache.org/jira/browse/CALCITE-4773
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.27.0
>Reporter: Ruben Q L
>Assignee: Ruben Q L
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.28.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The problem can be reproduced with the following test (to be added to
> {{unnest.iq}}):
> {noformat}
> SELECT unnested_outer.val, COUNT(1) AS count_val
> FROM
> (
> SELECT *
> FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
> AS u(x, y)
> ) AS t, UNNEST(t.y) AS unnested_outer(val)
> WHERE
> (
> SELECT COUNT(unnested_inner.val) > 0
> FROM UNNEST(t.y) AS unnested_inner(val)
> WHERE unnested_inner.val = 4
> )
> GROUP BY unnested_outer.val
> ORDER BY count_val DESC, unnested_outer.val ASC;
> +-+---+
> | VAL | COUNT_VAL |
> +-+---+
> | 6 | 2 |
> | 4 | 1 |
> +-+---+
> (2 rows)
> !ok
> {noformat}
> When the test is executed, it fails with the following error:
> {noformat}
> > java.lang.AssertionError: Cannot add expression of different type to set:
> > set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> > expression type is RecordType(BOOLEAN $f0) NOT NULL
> > set is
> > rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> > expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> > LogicalAggregate(group=[{}], agg#0=[COUNT()])
> > LogicalFilter(condition=[=($0, 4)])
> > LogicalProject(VAL=[$0])
> > Uncollect
> > LogicalProject(Y=[$cor1.Y])
> > LogicalValues(tuples=[[{ 0 }]])
> 249a254,325
> > at
> > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> > at
> > org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> > at
> > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> > at
> > org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
> ...
> {noformat}
> The root cause seems to be in this piece of code inside {{RelDecorrelator}}'s
> {{RemoveSingleAggregateRule#onMatch}}:
> {code:java}
> // singleAggRel produces a nullable type, so create the new
> // projection that casts proj expr to a nullable type.
> final RelBuilder relBuilder = call.builder();
> final RelDataType type =
> relBuilder.getTypeFactory()
> .createTypeWithNullability(projExprs.get(0).getType(), true);
> final RexNode cast = relBuilder.getRexBuilder().makeCast(type,
> projExprs.get(0));
> relBuilder.push(aggregate).project(cast);
> call.transformTo(relBuilder.build());
> {code}
> Note that the comment assumes that _"singleAggRel produces a nullable type"_,
> but in this particular case, it seems to produce a non-nullable type, so
> probably this piece of code needs to be adapted.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4766) Remove unreachable code from SqlValidatorImpl#performUnconditionalRewrites for VALUES node
[ https://issues.apache.org/jira/browse/CALCITE-4766?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis updated CALCITE-4766: - Summary: Remove unreachable code from SqlValidatorImpl#performUnconditionalRewrites for VALUES node (was: I think this condition is always true, so we need not to check it.) > Remove unreachable code from SqlValidatorImpl#performUnconditionalRewrites > for VALUES node > -- > > Key: CALCITE-4766 > URL: https://issues.apache.org/jira/browse/CALCITE-4766 > Project: Calcite > Issue Type: Improvement >Reporter: Jianhui Dong >Priority: Minor > Labels: pull-request-available > Attachments: image-2021-09-08-15-46-09-395.png > > Time Spent: 10m > Remaining Estimate: 0h > > in SqlValidatorImpl line 1387, there is a line `if (underFrom || true)`: > !image-2021-09-08-15-46-09-395.png! > The condition is always true and I think we can remove this condition. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-4766) Remove unreachable code from SqlValidatorImpl#performUnconditionalRewrites for VALUES node
[ https://issues.apache.org/jira/browse/CALCITE-4766?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis resolved CALCITE-4766. -- Fix Version/s: 1.28.0 Resolution: Fixed Fixed in [d7e20baf3920b9e8cd1e2171b98209ef57368216|https://github.com/apache/calcite/commit/d7e20baf3920b9e8cd1e2171b98209ef57368216]. Thanks for the PR [~Lam167]! > Remove unreachable code from SqlValidatorImpl#performUnconditionalRewrites > for VALUES node > -- > > Key: CALCITE-4766 > URL: https://issues.apache.org/jira/browse/CALCITE-4766 > Project: Calcite > Issue Type: Improvement >Reporter: Jianhui Dong >Priority: Minor > Labels: pull-request-available > Fix For: 1.28.0 > > Attachments: image-2021-09-08-15-46-09-395.png > > Time Spent: 20m > Remaining Estimate: 0h > > in SqlValidatorImpl line 1387, there is a line `if (underFrom || true)`: > !image-2021-09-08-15-46-09-395.png! > The condition is always true and I think we can remove this condition. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4773) RelDecorrelator's RemoveSingleAggregateRule can produce result with wrong row type
[
https://issues.apache.org/jira/browse/CALCITE-4773?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414304#comment-17414304
]
Julian Hyde commented on CALCITE-4773:
--
bq. singleAggRel may produce a nullable type
No, it always produces a nullable type, even if the sub-query's single column
is NOT NULL. The result will be null if the sub-query produces zero rows.
Your example has a sub-query in the WHERE clause whose single column is of type
BOOLEAN NOT NULL. But the correct type of the sub-query is nullable BOOLEAN.
If WHERE needs a BOOLEAN NOT NULL the result should be wrapped in IS TRUE or
something.
> RelDecorrelator's RemoveSingleAggregateRule can produce result with wrong row
> type
> --
>
> Key: CALCITE-4773
> URL: https://issues.apache.org/jira/browse/CALCITE-4773
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.27.0
>Reporter: Ruben Q L
>Assignee: Ruben Q L
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.28.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The problem can be reproduced with the following test (to be added to
> {{unnest.iq}}):
> {noformat}
> SELECT unnested_outer.val, COUNT(1) AS count_val
> FROM
> (
> SELECT *
> FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
> AS u(x, y)
> ) AS t, UNNEST(t.y) AS unnested_outer(val)
> WHERE
> (
> SELECT COUNT(unnested_inner.val) > 0
> FROM UNNEST(t.y) AS unnested_inner(val)
> WHERE unnested_inner.val = 4
> )
> GROUP BY unnested_outer.val
> ORDER BY count_val DESC, unnested_outer.val ASC;
> +-+---+
> | VAL | COUNT_VAL |
> +-+---+
> | 6 | 2 |
> | 4 | 1 |
> +-+---+
> (2 rows)
> !ok
> {noformat}
> When the test is executed, it fails with the following error:
> {noformat}
> > java.lang.AssertionError: Cannot add expression of different type to set:
> > set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> > expression type is RecordType(BOOLEAN $f0) NOT NULL
> > set is
> > rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> > expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
> > LogicalAggregate(group=[{}], agg#0=[COUNT()])
> > LogicalFilter(condition=[=($0, 4)])
> > LogicalProject(VAL=[$0])
> > Uncollect
> > LogicalProject(Y=[$cor1.Y])
> > LogicalValues(tuples=[[{ 0 }]])
> 249a254,325
> > at
> > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> > at
> > org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> > at
> > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> > at
> > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> > at
> > org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
> ...
> {noformat}
> The root cause seems to be in this piece of code inside {{RelDecorrelator}}'s
> {{RemoveSingleAggregateRule#onMatch}}:
> {code:java}
> // singleAggRel produces a nullable type, so create the new
> // projection that casts proj expr to a nullable type.
> final RelBuilder relBuilder = call.builder();
> final RelDataType type =
> relBuilder.getTypeFactory()
> .createTypeWithNullability(projExprs.get(0).getType(), true);
> final RexNode cast = relBuilder.getRexBuilder().makeCast(type,
> projExprs.get(0));
> relBuilder.push(aggregate).project(cast);
> call.transformTo(relBuilder.build());
> {code}
> Note that the comment assumes that _"singleAggRel produces a nullable type"_,
> but in this particular case, it seems to produce a non-nullable type, so
> probably this piece of code needs to be adapted.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4766) Remove unreachable code from SqlValidatorImpl#performUnconditionalRewrites for VALUES node
[ https://issues.apache.org/jira/browse/CALCITE-4766?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414322#comment-17414322 ] Jianhui Dong commented on CALCITE-4766: --- Sorry, I did not receive the email reminder in my mailbox, so I could not see your reply in time. And Thanks for your suggestion![~zabetak] [~wojustme] > Remove unreachable code from SqlValidatorImpl#performUnconditionalRewrites > for VALUES node > -- > > Key: CALCITE-4766 > URL: https://issues.apache.org/jira/browse/CALCITE-4766 > Project: Calcite > Issue Type: Improvement >Reporter: Jianhui Dong >Priority: Minor > Labels: pull-request-available > Fix For: 1.28.0 > > Attachments: image-2021-09-08-15-46-09-395.png > > Time Spent: 20m > Remaining Estimate: 0h > > in SqlValidatorImpl line 1387, there is a line `if (underFrom || true)`: > !image-2021-09-08-15-46-09-395.png! > The condition is always true and I think we can remove this condition. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4770) Design of SearchMvService
[ https://issues.apache.org/jira/browse/CALCITE-4770?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414583#comment-17414583 ] Julian Hyde commented on CALCITE-4770: -- I don't see this as a 'service'. I'd like to see this as an API or data structure, and how it fits within the existing planning process. How does this relate to Lattice? "TableScan operator size" was confusing. I think you mean "The number of TableScan operators in the plan". To me, this looks like a Comparator or Ordering, described thus: "When comparing two materialized views, prefer the one that has fewer TableScan operators; if that is equal, prefer the one with fewer Calc operators; ...". And given that ordering, maybe you have in mind a data structure (an "index") that can quickly find the best MV. But the other important part of MV matching -- the most important part -- is that the MV needs to be able to satisfy the query. How do you plan to include that among other criteria? > Design of SearchMvService > - > > Key: CALCITE-4770 > URL: https://issues.apache.org/jira/browse/CALCITE-4770 > Project: Calcite > Issue Type: New Feature >Reporter: xzh_dz >Priority: Major > > In the data system, materialized view can speed up SQL query. Materialized > view represents the pre-calculated SQL logic of the target table and contains > real data. In the project, we use materialized views to speed up SQL queries, > use the materialized view recognition technology to identify materialized > views, and enhance the ability of materialized recognition. However, with > more and more materialized views , materialized view recognition takes a lot > of time, which seriously affects the performance of SQL queries. With this > problem, we propose a design of accurate search materialized view based on > operator features. > DOC：[Design of > SearchMvService|[https://docs.google.com/document/d/1mmAsK_uW-fBs893JERP1gspMurX2lNXcVGeesP4XUqQ/edit]] > Welcome to discuss and expect more feedback,thank you. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4702) Error when executing query with GROUP BY constant via JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414614#comment-17414614
]
Julian Hyde commented on CALCITE-4702:
--
I agree that the "complex" transformation may be needed for some databases.
Remember that in this case we are trying to implement
{code:java}
select avg(salary) from employee group by true{code}
whereas the JooQ post is trying to implement the equivalent of
{code:java}
select avg(salary) from employee group by (){code}
These queries have different semantics, so the solutions will be different.
> Error when executing query with GROUP BY constant via JDBC adapter
> --
>
> Key: CALCITE-4702
> URL: https://issues.apache.org/jira/browse/CALCITE-4702
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.27.0
>Reporter: Stamatis Zampetakis
>Assignee: Soumyakanti Das
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following functionally equivalent SQL queries are accepted by Calcite and
> produce a valid plan
> {noformat}
> select avg(salary) from employee group by true
> select avg(salary) from employee group by 'a'
> {noformat}
> but they may fail if they are executed via the JDBC adapter since not all
> DBMS allow grouping by constants expressions. Moreover, what works for one
> may not work for the other.
> +Examples+
> The {{GROUP BY TRUE}} query works in Postgres, and MySQL but fails in
> Redshift with the following exception:
> {noformat}
> com.amazon.redshift.util.RedshiftException: ERROR: non-integer constant in
> GROUP BY
> {noformat}
> The {{GROUP BY 'a'}} query works in MySQL but fails in Postgres with the
> following exception:
> {noformat}
> ERROR: non-integer constant in GROUP BY
> {noformat}
> +Edit:+
> The {{GROUP BY}} constant is similar to {{GROUP BY ()}} "nothing" but as
> shown in the discussion below they are not equivalent. There is a nice [blog
> post|https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/]
> listing some limitations of various DBMS when it comes to {{GROUP BY ()}}.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4702) Error when executing query with GROUP BY constant via JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414615#comment-17414615
]
Julian Hyde edited comment on CALCITE-4702 at 9/13/21, 10:14 PM:
-
[~zabetak], Your queries are all invalid. You need to change {{*}} to
{{count\(\*)}}.
was (Author: julianhyde):
[~zabetak], Your queries are all invalid. You need to change {{*}} to
{{count(*)}}.
> Error when executing query with GROUP BY constant via JDBC adapter
> --
>
> Key: CALCITE-4702
> URL: https://issues.apache.org/jira/browse/CALCITE-4702
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.27.0
>Reporter: Stamatis Zampetakis
>Assignee: Soumyakanti Das
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following functionally equivalent SQL queries are accepted by Calcite and
> produce a valid plan
> {noformat}
> select avg(salary) from employee group by true
> select avg(salary) from employee group by 'a'
> {noformat}
> but they may fail if they are executed via the JDBC adapter since not all
> DBMS allow grouping by constants expressions. Moreover, what works for one
> may not work for the other.
> +Examples+
> The {{GROUP BY TRUE}} query works in Postgres, and MySQL but fails in
> Redshift with the following exception:
> {noformat}
> com.amazon.redshift.util.RedshiftException: ERROR: non-integer constant in
> GROUP BY
> {noformat}
> The {{GROUP BY 'a'}} query works in MySQL but fails in Postgres with the
> following exception:
> {noformat}
> ERROR: non-integer constant in GROUP BY
> {noformat}
> +Edit:+
> The {{GROUP BY}} constant is similar to {{GROUP BY ()}} "nothing" but as
> shown in the discussion below they are not equivalent. There is a nice [blog
> post|https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/]
> listing some limitations of various DBMS when it comes to {{GROUP BY ()}}.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4702) Error when executing query with GROUP BY constant via JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414615#comment-17414615
]
Julian Hyde commented on CALCITE-4702:
--
[~zabetak], Your queries are all invalid. You need to change {{*}} to
{{count(*)}}.
> Error when executing query with GROUP BY constant via JDBC adapter
> --
>
> Key: CALCITE-4702
> URL: https://issues.apache.org/jira/browse/CALCITE-4702
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.27.0
>Reporter: Stamatis Zampetakis
>Assignee: Soumyakanti Das
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following functionally equivalent SQL queries are accepted by Calcite and
> produce a valid plan
> {noformat}
> select avg(salary) from employee group by true
> select avg(salary) from employee group by 'a'
> {noformat}
> but they may fail if they are executed via the JDBC adapter since not all
> DBMS allow grouping by constants expressions. Moreover, what works for one
> may not work for the other.
> +Examples+
> The {{GROUP BY TRUE}} query works in Postgres, and MySQL but fails in
> Redshift with the following exception:
> {noformat}
> com.amazon.redshift.util.RedshiftException: ERROR: non-integer constant in
> GROUP BY
> {noformat}
> The {{GROUP BY 'a'}} query works in MySQL but fails in Postgres with the
> following exception:
> {noformat}
> ERROR: non-integer constant in GROUP BY
> {noformat}
> +Edit:+
> The {{GROUP BY}} constant is similar to {{GROUP BY ()}} "nothing" but as
> shown in the discussion below they are not equivalent. There is a nice [blog
> post|https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/]
> listing some limitations of various DBMS when it comes to {{GROUP BY ()}}.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4770) Design of SearchMvService
[ https://issues.apache.org/jira/browse/CALCITE-4770?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414679#comment-17414679 ] xzh_dz commented on CALCITE-4770: - [~asolimando] Thanks for your review.I had added some cases in doc.It will be helpful to understand the document. As [~wojustme] said, The two materialized view recognition frameworks have their own advantages and disadvantages. * *`SubstitutionVisitor#go` :* bottom-up operator matching. * *`MaterializedViewRule#perform` :* Analyze the structure information of the operator to complete the matching. We should put the function of searching materialized view before materialized view recognition.Putting materialized view recognition conditions before recognition is very helpful for both frameworks.Materialized view recognition should take more responsibility for rewriting materialized views.The existing operator matching data structure is complex and difficult to expand, such as user-defined functions.In the project, the rules of the materialized recognition framework are difficult to extend, so I have implemented custom [materialized recognition rules|https://github.com/apache/calcite/pull/2094] and [normalization rules|https://github.com/apache/calcite/pull/2262].The new implementation is very concise and extensible.The new implementation is to extract the conditions of materialized view recognition as feature index.By the way, the ability of materialized view recognition is still insufficient, and I will continue to improve it. > Design of SearchMvService > - > > Key: CALCITE-4770 > URL: https://issues.apache.org/jira/browse/CALCITE-4770 > Project: Calcite > Issue Type: New Feature >Reporter: xzh_dz >Priority: Major > > In the data system, materialized view can speed up SQL query. Materialized > view represents the pre-calculated SQL logic of the target table and contains > real data. In the project, we use materialized views to speed up SQL queries, > use the materialized view recognition technology to identify materialized > views, and enhance the ability of materialized recognition. However, with > more and more materialized views , materialized view recognition takes a lot > of time, which seriously affects the performance of SQL queries. With this > problem, we propose a design of accurate search materialized view based on > operator features. > DOC：[Design of > SearchMvService|[https://docs.google.com/document/d/1mmAsK_uW-fBs893JERP1gspMurX2lNXcVGeesP4XUqQ/edit]] > Welcome to discuss and expect more feedback,thank you. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4772) PushProject loses RexCall alias
[
https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4772:
-
Priority: Major (was: Blocker)
> 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: 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
> FROMuser_info
> WHERE dept_no > 20
> ) AS USER_INFO
> LEFT JOIN(
> SELECT role_id,role_name,role_no
> FROMrole_info
> WHERE role_no > 20
> ) AS role_info
> ON USER_ID = ROLE_ID
> LEFT JOIN(
> SELECT id,name,age
> FROMld_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:#FF} id + age alias was
> dt{color}. After optimization, {color:#FF}the incorrect alias + was
> used.{color}
> After querying the source code, the problem appears in
> {color:#FF}PushProjector.java#567 {color}
> !image-2021-09-13-11-36-26-902.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4772) PushProjector assigns incorrect field alias when handling RexCall
[
https://issues.apache.org/jira/browse/CALCITE-4772?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4772:
-
Summary: PushProjector assigns incorrect field alias when handling RexCall
(was: PushProject loses RexCall alias)
> PushProjector assigns incorrect field alias when handling RexCall
> -
>
> 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: 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
> FROMuser_info
> WHERE dept_no > 20
> ) AS USER_INFO
> LEFT JOIN(
> SELECT role_id,role_name,role_no
> FROMrole_info
> WHERE role_no > 20
> ) AS role_info
> ON USER_ID = ROLE_ID
> LEFT JOIN(
> SELECT id,name,age
> FROMld_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:#FF} id + age alias was
> dt{color}. After optimization, {color:#FF}the incorrect alias + was
> used.{color}
> After querying the source code, the problem appears in
> {color:#FF}PushProjector.java#567 {color}
> !image-2021-09-13-11-36-26-902.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4771) change the value of the CAST function to be nullable
[
https://issues.apache.org/jira/browse/CALCITE-4771?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414687#comment-17414687
]
Julian Hyde commented on CALCITE-4771:
--
Returning {{NULL}} if the {{CAST}} fails is not compliant with the SQL
standard, as I understand it. So, the type of a {{CAST}} should be nullable if
and only if the type of the argument is nullable.
{{TRY_CAST}} is a good idea. Can you please review other major databases to see
if they have similar operators, and report back here. If there is any consensus
on the name, syntax and behavior of the operator, I would like to follow the
consensus.
> change the value of the CAST function to be nullable
> -
>
> Key: CALCITE-4771
> URL: https://issues.apache.org/jira/browse/CALCITE-4771
> Project: Calcite
> Issue Type: Improvement
>Reporter: xuyangzhong
>Priority: Major
>
> In the sql "SELECT CAST('haha' AS INT)",the value the function CAST returns
> will be parsed into NOT NULL, because when parsing, the type CAST returns is
> from the INT and the nullable attribute is from the 'haha', which doesn't
> consider the condition that parsing a string to an int could be invalid and
> return NULL values.
> I think there are two ways to improve this question:
> * One is to change the value of the CAST function to be nullable, which
> avoids the invalid parsing.
> * The other way is to introduce a function named TRY_CAST, which is used in
> SQL Server.If the parsing fails, TRY_CAST will return NULL instead of throws
> exception that a NOT NULL field will be set with our unexpected value NULL.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4771) change the value of the CAST function to be nullable
[
https://issues.apache.org/jira/browse/CALCITE-4771?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414749#comment-17414749
]
xuyangzhong commented on CALCITE-4771:
--
Hi,[~julianhyde]. I found the following databases:
* SqlServer provides TRY_CAST since version 2014.
* In Oracle we can use “cast(... DEFAULT NULL ON CONVERSION ERROR)” to return
NULL when an error happen in cast since version 12.2.
* In Google BigQuery ,there is a function named safe_cast, which will return
NULL instead of an error.
* SnowFlake provides TRY_CAST.
* T-SQL provides TRY_CAST.
* Databricks provides TRY_CAST.
* ClickHouse provides accurateCastOrNull
* ..
> change the value of the CAST function to be nullable
> -
>
> Key: CALCITE-4771
> URL: https://issues.apache.org/jira/browse/CALCITE-4771
> Project: Calcite
> Issue Type: Improvement
>Reporter: xuyangzhong
>Priority: Major
>
> In the sql "SELECT CAST('haha' AS INT)",the value the function CAST returns
> will be parsed into NOT NULL, because when parsing, the type CAST returns is
> from the INT and the nullable attribute is from the 'haha', which doesn't
> consider the condition that parsing a string to an int could be invalid and
> return NULL values.
> I think there are two ways to improve this question:
> * One is to change the value of the CAST function to be nullable, which
> avoids the invalid parsing.
> * The other way is to introduce a function named TRY_CAST, which is used in
> SQL Server.If the parsing fails, TRY_CAST will return NULL instead of throws
> exception that a NOT NULL field will be set with our unexpected value NULL.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4774) When predicate conditions are equivalent, materialized view recognition fails.
xzh_dz created CALCITE-4774:
---
Summary: When predicate conditions are equivalent, materialized
view recognition fails.
Key: CALCITE-4774
URL: https://issues.apache.org/jira/browse/CALCITE-4774
Project: Calcite
Issue Type: New Feature
Reporter: xzh_dz
{code:java}
// code placeholder
org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest
@Test void testRexCondition() {
final String mv = ""
+ "select \"name\"\n"
+ "from \"emps\"\n"
+ "where \"deptno\" > 100 and \"deptno\" > 50\n"
+ "group by \"name\"";
final String query = ""
+ "select \"name\"\n"
+ "from \"emps\"\n"
+ "where \"deptno\" > 100"
+ "group by \"name\"";
sql(mv, query).withChecker(
resultContains(""
+ "EnumerableTableScan(table=[[hr, MV0]])")).ok();
}
{code}
Materialized view failed to be matched by optimized results:Materialized view
failed to be matched by optimized results: java.lang.AssertionError:
Materialized view failed to be matched by optimized results: at
org.apache.calcite.test.AbstractMaterializedViewTest.checkMaterialize(AbstractMaterializedViewTest.java:116)
at
org.apache.calcite.test.AbstractMaterializedViewTest.access$000(AbstractMaterializedViewTest.java:67)
at
org.apache.calcite.test.AbstractMaterializedViewTest$Sql.ok(AbstractMaterializedViewTest.java:229)
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
