[ https://issues.apache.org/jira/browse/IGNITE-15605?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716219#comment-17716219 ]
Evgeny Stanilovsky commented on IGNITE-15605: --------------------------------------------- Check the plan : {noformat} LogicalSort(sort0=[$0], dir0=[ASC]) LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}]) LogicalAggregate(group=[{0}], EXPR$1=[SUM($0)]) LogicalTableScan(table=[[BLANK, INTEGERS]]) LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) LogicalProject(EXPR$0=[+($0, $1)]) LogicalAggregate(group=[{}], agg#0=[SUM($0)], agg#1=[SUM($1)]) LogicalProject(I=[$0], $f1=[$cor0.I]) LogicalTableScan(table=[[BLANK, INTEGERS]]){noformat} Correlate here is INTEGER and further scalar for aggregates representation looks like: {noformat} public void execute(org.apache.ignite.internal.sql.engine.exec.ExecutionContext ctx, Object in1, Object out) { final org.apache.ignite.internal.sql.engine.exec.RowHandler hnd = ctx.rowHandler(); final Object corr = ctx.correlatedVariable(0); final Integer input_value = (Integer) hnd.get(0, in1); final Integer corInp_value = (Integer) hnd.get(1, corr); hnd.set(0, out, input_value); hnd.set(1, out, corInp_value); }{noformat} but derived type for SUM is: IgniteTypeSystem#deriveSumType -> BIGINT (long), thus we obtain observed cast exception, changing return type to SqlTypeName.INTEGER will bring correct results and passed test. In native calcite code, this : RelDataTypeSystemImpl#deriveSumType for INTEGER derives INTEGER and no additional cast is necessary, thus no cast exception will raised. Note, for obtaining plan with correlates in calcite it`s necessary to disable tools.Programs#standard -> DecorrelateProgram usage. > Calcite. Unexpected result with aggregate inside subquery. > ---------------------------------------------------------- > > Key: IGNITE-15605 > URL: https://issues.apache.org/jira/browse/IGNITE-15605 > Project: Ignite > Issue Type: Bug > Components: sql > Reporter: Evgeny Stanilovsky > Assignee: Evgeny Stanilovsky > Priority: Major > Labels: calcite, calcite2-required, calcite3-required, ignite-3 > > {noformat} > statement ok > CREATE TABLE integers(i INTEGER) > statement ok > INSERT INTO integers VALUES (1), (2), (3), (NULL) > query R SELECT i, SUM(i), (SELECT SUM(i)+SUM(i1.i) FROM integers) FROM > integers i1 GROUP BY i ORDER BY i; > ---- > 1 1.000000 10 > 2 2.000000 14 > 3 3.000000 18 > NULL NULL NULL {noformat} > {noformat} > /subquery/scalar/test_correlated_aggregate_subquery.test_ignore > /subquery/scalar/test_varchar_correlated_subquery.test_ignore > {noformat} > > {code:java} > Caused by: java.lang.ClassCastException: class java.lang.Long cannot be cast > to class java.lang.Integer (java.lang.Long and java.lang.Integer are in > module java.base of loader 'bootstrap') > at SC.execute(Unknown Source) > at > org.apache.ignite.internal.sql.engine.exec.exp.ExpressionFactoryImpl$ProjectImpl.apply(ExpressionFactoryImpl.java:654) > at > org.apache.ignite.internal.sql.engine.exec.rel.StorageScanNode.push(StorageScanNode.java:197) > at > org.apache.ignite.internal.sql.engine.exec.rel.StorageScanNode$SubscriberImpl.lambda$onComplete$2(StorageScanNode.java:303) > at > org.apache.ignite.internal.sql.engine.exec.ExecutionContext.lambda$execute$0(ExecutionContext.java:315){code} -- This message was sent by Atlassian Jira (v8.20.10#820010)