[
https://issues.apache.org/jira/browse/CALCITE-6978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17947158#comment-17947158
]
Mihai Budiu commented on CALCITE-6978:
--------------------------------------
Here is a SqlToRelConverterTest which fails:
{code:java}
@Test void testCoalesceSubquery() {
final String sql = "SELECT"
+ " deptno, "
+ " coalesce((select sum(empno) from emp "
+ " where deptno = emp.deptno limit 1), 0) as w "
+ "FROM dept";
sql(sql).ok();
}{code}
This is the plan generated in this case:
{code:java}
LogicalProject(DEPTNO=[$0], W=[CASE(IS NOT NULL($2), $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}
This is a very strange plan, which contains two joins for the exact same
subplan. I believe that $2 and $3 are always equal in the last project, but the
validator cannot prove this, and thus it cannot prove that the resulting type
is not null.
> 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
> Priority: Minor
>
> 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)
