[
https://issues.apache.org/jira/browse/CALCITE-6848?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Steshin closed CALCITE-6848.
-------------------------------------
Resolution: Not A Bug
[~julianhyde], oh, yes. Didn't event check. Thank you for the clarification.
> 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
> Priority: Minor
>
> 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)
