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)
