[
https://issues.apache.org/jira/browse/CALCITE-6978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17953960#comment-17953960
]
xiong duan commented on CALCITE-6978:
-------------------------------------
I try to add NOT NULL operator, then the generate plan will be:
{code:java}
LogicalProject(DEPTNO=[$0], W=[CASE(IS NOT NULL($2), NOT NULL($3), 0)])
LogicalJoin(condition=[true], joinType=[left])
LogicalJoin(condition=[true], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalSort(fetch=[1])
LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[=($7, $7)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalSort(fetch=[1])
LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[=($7, $7)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
It does not throw an exception.
> Translation to relational algebra of correlated query with COALESCE causes an
> assertion failure
> -----------------------------------------------------------------------------------------------
>
> Key: CALCITE-6978
> URL: https://issues.apache.org/jira/browse/CALCITE-6978
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.39.0
> Reporter: Mihai Budiu
> Assignee: Mihai Budiu
> Priority: Minor
> Labels: pull-request-available
>
> The following program fails during conversion to Rel:
> {code:sql}
> CREATE TABLE T(x DECIMAL(6, 2), z INT);
> CREATE TABLE S(y INT);
> CREATE VIEW V AS SELECT
> y,
> coalesce((select sum(X) from T
> where y = T.z limit 1), 0) as w
> FROM S;
> {code}
> The error is:
> {code}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(INTEGER y, DECIMAL(28, 2) NOT NULL w) NOT NULL
> converted type:
> RecordType(INTEGER y, DECIMAL(28, 2) w) NOT NULL
> rel:
> LogicalProject(y=[$0], w=[CASE(IS NOT NULL($1), $2, 0.00:DECIMAL(28, 2))])
> LogicalCorrelate(correlation=[$cor1], joinType=[left],
> requiredColumns=[{0}])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}])
> LogicalTableScan(table=[[schema, s]])
> LogicalSort(fetch=[1])
> LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
> LogicalProject(x=[$0])
> LogicalFilter(condition=[=($cor0.y, $1)])
> LogicalTableScan(table=[[schema, t]])
> LogicalSort(fetch=[1])
> LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
> LogicalProject(x=[$0])
> LogicalFilter(condition=[=($cor1.y, $1)])
> LogicalTableScan(table=[[schema, t]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
