Vladimir Steshin created CALCITE-6848: -----------------------------------------
Summary: An exception with SINGLE_VALUE and correlated subquery. Key: CALCITE-6848 URL: https://issues.apache.org/jira/browse/CALCITE-6848 Project: Calcite Issue Type: Bug Affects Versions: 1.39.0 Environment: underlined text Reporter: Vladimir Steshin Hi.I'm not sure that this is not a duplicate. I found previous CALCITE-685, CALCITE-2136, CALCITE-4945. But the description is a bit different. Or could you please point to a correct and actual ticket for this case. *Reproduser:* {code:java} import static java.util.Objects.requireNonNull; /** * Tests for using Calcite via JDBC. */ public class JdbcTest { @Test void testSubq() { String sql = "select (SELECT \"salary\" FROM \"hr\".\"emps\" e order by 1 limit 2) from \"hr\".\"emps\""; CalciteAssert.hr().query(sql).returnsOrdered("salary=7000.0", "salary=8000.0"); } } {code} *Plan:* {code:java} EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1]): rowcount = 100.0, cumulative cost = {1513.125 rows, 15940.544595161893 cpu, 0.0 io}, id = 140 EnumerableNestedLoopJoin(condition=[true], joinType=[left]): rowcount = 100.0, cumulative cost = {1413.125 rows, 15640.544595161893 cpu, 0.0 io}, id = 136 EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], DUMMY=[$t5]): rowcount = 100.0, cumulative cost = {200.0 rows, 801.0 cpu, 0.0 io}, id = 142 EnumerableTableScan(table=[[hr, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 59 EnumerableAggregate(group=[{}], agg#0=[SINGLE_VALUE($3)]): rowcount = 1.0, cumulative cost = {203.125 rows, 14839.544595161893 cpu, 0.0 io}, id = 134 EnumerableLimit(fetch=[2]): rowcount = 2.0, cumulative cost = {202.0 rows, 14839.544595161893 cpu, 0.0 io}, id = 132 EnumerableSort(sort0=[$3], dir0=[ASC]): rowcount = 100.0, cumulative cost = {200.0 rows, 14837.544595161893 cpu, 0.0 io}, id = 130 EnumerableTableScan(table=[[hr, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 59 {code} *Result:* {code:java} Error while executing SQL "select (SELECT "salary" FROM "hr"."emps" e order by 1 limit 2) from "hr"."emps"": more than one value in agg SINGLE_VALUE java.sql.SQLException: Error while executing SQL "select (SELECT "salary" FROM "hr"."emps" e order by 1 limit 2) from "hr"."emps"": more than one value in agg SINGLE_VALUE ... Caused by: java.lang.IllegalStateException: more than one value in agg SINGLE_VALUE at Baz$2.apply(Unknown Source) at Baz$2.apply(Unknown Source) at Baz$2.apply(Unknown Source) at org.apache.calcite.adapter.enumerable.BasicAggregateLambdaFactory$AccumulatorAdderSeq.apply(BasicAggregateLambdaFactory.java:81) at org.apache.calcite.linq4j.EnumerableDefaults.aggregate(EnumerableDefaults.java:133) at org.apache.calcite.linq4j.DefaultEnumerable.aggregate(DefaultEnumerable.java:107) at Baz.bind(Unknown Source) at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:367) at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:335) at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:657) at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:648) at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184) at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64) at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43) at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:669) at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:717) at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677) at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157) ... 67 more {code} Looks like +SubQueryRemoveRule#rewriteScalarQuery(+) creates a +SqlStdOperatorTable.SINGLE_VALUE+. But the underlying +TableScan+ produces several values. Actual for the main/1.39. -- This message was sent by Atlassian Jira (v8.20.10#820010)