[
https://issues.apache.org/jira/browse/CALCITE-6978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17949509#comment-17949509
]
Dmitry Sysolyatin commented on CALCITE-6978:
--------------------------------------------
??In most databases query execution is atomic with respect to database changes??
I'm not sure if that's true for "most" — it depends on the isolation level the
user sets if database support transactions. If the database optimizes the query
into a single subquery, then maybe it works as expected. But if it doesn't, and
the isolation level allows one transaction to affect what another transaction
reads, then I doubt it.
For example, with PostgreSQL's default isolation level ({_}Read Committed{_}),
and if PostgreSQL doesn’t optimize the query, the following can happen:
1. The query starts and performs the first scan
2. Another transaction commits a write
3. The query does a second scan and now sees the committed data from the other
transaction
Another case is that we can't guarantee the whole query will run on the
database side. Sometimes, only the scan is done in the database, and the rest
is processed outside, for example by the Calcite query engine. In that case,
you need to make sure both queries run in the same transaction with an
isolation level of Repeatable Read or higher to be sure that result will be
correct.
> 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)
