[jira] [Created] (CALCITE-6848) An exception with SINGLE_VALUE and correlated subquery.

["Vladimir Steshin (Jira)" <ji...@apache.org>]

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)