[
https://issues.apache.org/jira/browse/CALCITE-6978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17953579#comment-17953579
]
xiong duan commented on CALCITE-6978:
-------------------------------------
Perhaps we could try introducing built-in operators like "neverNull()".
1) When we convert
coalesce(a,b)--> CASE WHEN a is not null THEN a ESLE b
we improve it:
{code:java}
coalesce(a,b)--> CASE WHEN a is not null THEN neverNull(a) ESLE b{code}
{color:#172b4d}2) `neverNull` operator doesn't do anything, just tell the
validator this file is not null.{color}
{color:#172b4d}3) When we do RelToSQL, we can ignore it directly. {color}
> 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)
