[
https://issues.apache.org/jira/browse/CALCITE-6978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17949510#comment-17949510
]
Mihai Budiu commented on CALCITE-6978:
--------------------------------------
I don't think you can define query semantics when the underlying data changes.
Even if you do no query rewrites at all, the order in which rows from different
tables are ingested is not defined.
So I assume that the correctness that Calcite promises (i.e., query semantics)
needs to assume a strong isolation. And with rewrites even that may not hold.
> 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
> 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)
