[
https://issues.apache.org/jira/browse/IGNITE-23183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17882961#comment-17882961
]
Pavel Pereslegin edited comment on IGNITE-23183 at 9/19/24 10:31 AM:
---------------------------------------------------------------------
The root cause of problem with assertion and possible fix is described in the
calcite issue comment.
{panel:title=issue description}
During validation, when registering subqueries, we wrap the sub-query in a
SCALAR_QUERY call in selectList (sqlUpdate.getSourceSelect().getSelectList()).
But after that, TypeCoersion tries to coerce types for expressions in
sourceExpressionList (see TypeCoersionImpl.coerceSourceRowType()), but it
contains SqlSelect instead of SCALAR_QUERY call.
{panel}
The possible solution - add SCALAR_QUERY to {{sourceExpressionList}} as well.
This works fine for AI3 until we look at the physical plan, the plan contains
an original SCALAR_QUERY in TableModify sourceExpressionList, and we can't
execute such a plan because we can't serialize/deserialize it properly.
Note: with deprecated SqlRelConverter.Config.withExpand(true) this
scalar-subquery will be rewritten and all works fine. But we can't enable it
because some functions stop working when this setting is enabled (I think you
can read about this in the documentation)..
*Therefore, the correct solution would be to rewrite the subquery in
TableModify.sourceExpression on the CALCITE side (in CALCITE-6570).*
Alternative ways and why they don't work:
1. Merge current patch (CALCITE-6570) to calcite and nullify
sourceExpressionList in physical TableModify node on AI3 side.
We don't use sourceExpressionList after planning, but we can't nullify this
list easily.
First parent TableModify has some checks that doesn't allow just set null to
list.
Secondly, after "nullification" plan will differ from original optimal plan
(TableModify.soruceExpressionList=[null]).
was (Author: xtern):
The root cause of problem with assertion and possible fix is described in the
calcite issue comment.
{panel:title=issue description}
During validation, when registering subqueries, we wrap the sub-query in a
SCALAR_QUERY call in selectList (sqlUpdate.getSourceSelect().getSelectList()).
But after that, TypeCoersion tries to coerce types for expressions in
sourceExpressionList (see TypeCoersionImpl.coerceSourceRowType()), but it
contains SqlSelect instead of SCALAR_QUERY call.
As a solution I added SCALAR_QUERY to sourceExpressionList as well.
{panel}
> Sql. Assertion error when validating an UPDATE query with a subquery
> expression
> -------------------------------------------------------------------------------
>
> Key: IGNITE-23183
> URL: https://issues.apache.org/jira/browse/IGNITE-23183
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Reporter: Pavel Pereslegin
> Assignee: Pavel Pereslegin
> Priority: Major
> Labels: ignite-3
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Reproducer:
> {code:java}
> sql("CREATE TABLE t0(ID INT PRIMARY KEY, VAL INT)");
> sql("UPDATE t0 set val = (select id::BIGINT from t0)");
> {code}
> fails with
> {noformat}
> Caused by: java.lang.AssertionError
> at
> org.apache.calcite.sql.validate.implicit.AbstractTypeCoercion.needToCast(AbstractTypeCoercion.java:307)
> at
> org.apache.calcite.sql.validate.implicit.AbstractTypeCoercion.needToCast(AbstractTypeCoercion.java:250)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.needToCast(IgniteTypeCoercion.java:321)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.doCoerceColumnType(IgniteTypeCoercion.java:499)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.coerceColumnType(IgniteTypeCoercion.java:406)
> at
> org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.coerceSourceRowType(TypeCoercionImpl.java:676)
> at
> org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.querySourceCoercion(TypeCoercionImpl.java:646)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.querySourceCoercion(IgniteTypeCoercion.java:278)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.checkTypeAssignment(SqlValidatorImpl.java:5272)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateUpdate(SqlValidatorImpl.java:5379)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validateUpdate(IgniteSqlValidator.java:229)
> at org.apache.calcite.sql.SqlUpdate.validate(SqlUpdate.java:190)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1101)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:807)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validate(IgniteSqlValidator.java:176)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgnitePlanner.validate(IgnitePlanner.java:200)
> at
> org.apache.ignite.internal.sql.engine.prepare.PrepareServiceImpl.lambda$prepareDml$8(PrepareServiceImpl.java:515)
> {noformat}
> Issue is caused by CALCITE-6570
> *UPDATE*
> The following case looks related also
> {code:java}
> sql("CREATE TABLE t0(ID INT PRIMARY KEY, A INT)");
> sql("INSERT INTO t0 VALUES (1, 1), (2, 2)");
> sql("UPDATE t0 SET a = a + (SELECT 1)");
> {code}
> gives the *physical* plan with *logical nodes* (look at
> TableModify.sourceExpressionList)
> {noformat}
> Project(ROWCOUNT=[CAST($0):BIGINT NOT NULL]): rowcount = 1.0, cumulative cost
> = IgniteCost [rowCount=70004.0, cpu=100004.0, memory=11.0, io=2.0,
> network=240002.0], id = 246
> ColocatedHashAggregate(group=[{}], agg#0=[$SUM0($0)]): rowcount = 1.0,
> cumulative cost = IgniteCost [rowCount=70002.0, cpu=100002.0, memory=10.0,
> io=1.0, network=240001.0], id = 245
> Exchange(distribution=[single]): rowcount = 10000.0, cumulative cost =
> IgniteCost [rowCount=60002.0, cpu=90002.0, memory=5.0, io=1.0,
> network=240001.0], id = 244
> TableModify(table=[[PUBLIC, T0]], operation=[UPDATE],
> updateColumnList=[[A]], sourceExpressionList=[[+($1, $SCALAR_QUERY({
> LogicalValues(tuples=[[{ 1 }]])
> }))]], flattened=[false], tableId=[9]): rowcount = 10000.0, cumulative cost =
> IgniteCost [rowCount=50002.0, cpu=80002.0, memory=5.0, io=1.0,
> network=200001.0], id = 243
> Project(ID=[$0], A=[$1], EXPR$2=[+($1, $2)]): rowcount = 10000.0,
> cumulative cost = IgniteCost [rowCount=50001.0, cpu=80001.0, memory=4.0,
> io=0.0, network=200000.0], id = 242
> Exchange(distribution=[affinity[tableId=9, zoneId=9][0]]): rowcount
> = 10000.0, cumulative cost = IgniteCost [rowCount=40001.0, cpu=70001.0,
> memory=4.0, io=0.0, network=200000.0], id = 241
> NestedLoopJoin(condition=[true], joinType=[left],
> variablesSet=[[]]): rowcount = 10000.0, cumulative cost = IgniteCost
> [rowCount=30001.0, cpu=60001.0, memory=4.0, io=0.0, network=80000.0], id = 240
> Exchange(distribution=[single]): rowcount = 10000.0, cumulative
> cost = IgniteCost [rowCount=20000.0, cpu=20000.0, memory=0.0, io=0.0,
> network=80000.0], id = 238
> TableScan(table=[[PUBLIC, T0]], tableId=[9],
> requiredColumns=[{0, 1}]): rowcount = 10000.0, cumulative cost = IgniteCost
> [rowCount=10000.0, cpu=10000.0, memory=0.0, io=0.0, network=0.0], id = 237
> Values(tuples=[[{ 1 }]]): rowcount = 1.0, cumulative cost =
> IgniteCost [rowCount=1.0, cpu=1.0, memory=0.0, io=0.0, network=0.0], id = 239
> {noformat}
> that cannot be deserialized
> {noformat}
> Caused by: java.lang.IllegalStateException: Unknown or unexpected operator:
> name: $SCALAR_QUERY, kind: SCALAR_QUERY, syntax: INTERNAL
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJson.toOp(RelJson.java:944)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJson.toRex(RelJson.java:818)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJson.toRexList(RelJson.java:1042)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJson.toRex(RelJson.java:784)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJsonReader$RelInputImpl.getExpressionList(RelJsonReader.java:303)
> at
> org.apache.ignite.internal.sql.engine.rel.IgniteTableModify.<init>(IgniteTableModify.java:121)
> at SC.apply(Unknown Source)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJson$RelFactory.apply(RelJson.java:129)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJsonReader.readRel(RelJsonReader.java:140)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJsonReader.readRels(RelJsonReader.java:132)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJsonReader.read(RelJsonReader.java:123)
> at
> org.apache.ignite.internal.sql.engine.externalize.RelJsonReader.fromJson(RelJsonReader.java:86)
> at
> org.apache.ignite.internal.sql.engine.exec.ExecutionServiceImpl.lambda$relationalTreeFromJsonString$8(ExecutionServiceImpl.java:362)
> at
> com.github.benmanes.caffeine.cache.BoundedLocalCache.lambda$doComputeIfAbsent$13(BoundedLocalCache.java:2457)
> at
> java.base/java.util.concurrent.ConcurrentHashMap.compute(ConcurrentHashMap.java:1908)
> at
> com.github.benmanes.caffeine.cache.BoundedLocalCache.doComputeIfAbsent(BoundedLocalCache.java:2455)
> at
> com.github.benmanes.caffeine.cache.BoundedLocalCache.computeIfAbsent(BoundedLocalCache.java:2438)
> at
> com.github.benmanes.caffeine.cache.LocalCache.computeIfAbsent(LocalCache.java:107)
> at
> org.apache.ignite.internal.sql.engine.exec.ExecutionServiceImpl.relationalTreeFromJsonString(ExecutionServiceImpl.java:360)
> at
> org.apache.ignite.internal.sql.engine.exec.ExecutionServiceImpl$DistributedQueryManager.submitFragment(ExecutionServiceImpl.java:947)
> at
> org.apache.ignite.internal.sql.engine.exec.ExecutionServiceImpl.submitFragment(ExecutionServiceImpl.java:614)
> at
> org.apache.ignite.internal.sql.engine.exec.ExecutionServiceImpl.onMessage(ExecutionServiceImpl.java:513)
> at
> org.apache.ignite.internal.sql.engine.exec.ExecutionServiceImpl.lambda$start$1(ExecutionServiceImpl.java:277)
> at
> org.apache.ignite.internal.sql.engine.message.MessageServiceImpl.onMessageInternal(MessageServiceImpl.java:151)
> at
> org.apache.ignite.internal.sql.engine.message.MessageServiceImpl.lambda$onMessage$0(MessageServiceImpl.java:115)
> ... 4 more
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
