[IGNITE-23183] Sql. Assertion error when validating an UPDATE query with a subquery expression

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: ignite-3
• Assignee: Pavel Pereslegin
• Reporter: Pavel Pereslegin
• Created: 10/Sep/24 16:01
• Updated: 19/Sep/24 14:45

Description

Reproducer:

    sql("CREATE TABLE t0(ID INT PRIMARY KEY, VAL INT)");
    sql("UPDATE t0 set val = (select id::BIGINT from t0)");

fails with

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)

Issue is caused by CALCITE-6570

UPDATE

The following case (looks related) gives the physical plan with logical nodes

  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)");

look at TableModify.sourceExpressionList

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

that cannot be deserialized

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

Issue Links

• blocks: IGNITE-23192 Sql. Arithmetic operations failed with "out of range" exception.
• relates to: CALCITE-6570 UPDATE with sub-query that requires type cast gives AssertionError
• split from: IGNITE-20835 Sql. Update statements incorrectly use scalar subqueries in target columns
• links to: GitHub Pull Request #4381