[jira] [Created] (CALCITE-4813) ANY_VALUE assumes that arguments should be comparable

["Sergey Nuyanzin (Jira)" <ji...@apache.org>]

Sergey Nuyanzin created CALCITE-4813:
----------------------------------------

             Summary: ANY_VALUE assumes that arguments should be comparable
                 Key: CALCITE-4813
                 URL: https://issues.apache.org/jira/browse/CALCITE-4813
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.27.0
            Reporter: Sergey Nuyanzin


ANY_VALUE reuses {{MinMaxImplementor}}, as a result it assumes that there are corresponding {{greater}} methods. 

In fact for non comparable input it fails, for instance every query from
{code:sql}
select any_value(r) over(), s from(select array[f, s] r, s from (select 1 as f, 2 as s) t) t;
select any_value(r) over(), s from(select map[f, s] r, s from (select 1 as f, 2 as s) t) t;
select any_value(r) over(), s from(select row(f, s) r, s from (select 1 as f, 2 as s) t) t;
{code}
will fail like
{noformat}
Caused by: java.lang.IllegalStateException: Unable to implement EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t2], S=[$t0]): rowcount = 1.0, cumulative cost = {4.0 rows, 13.0 cpu, 0.0 io}, id = 509
  EnumerableWindow(window#0=[window(aggs [ANY_VALUE($1)])]): rowcount = 1.0, cumulative cost = {3.0 rows, 8.0 cpu, 0.0 io}, id = 505
    EnumerableCalc(expr#0..1=[{inputs}], expr#2=[ARRAY($t0, $t1)], S=[$t1], $1=[$t2]): rowcount = 1.0, cumulative cost = {2.0 rows, 6.0 cpu, 0.0 io}, id = 511
      EnumerableValues(tuples=[[{ 1, 2 }]]): rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 480

	at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
	at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:113)
	at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1130)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:318)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:215)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:647)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:623)
	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
	... 8 more
	Suppressed: java.lang.RuntimeException: while resolving method 'greater[interface java.util.List, interface java.util.List]' in class class org.apache.calcite.runtime.SqlFunctions
		at org.apache.calcite.linq4j.tree.Types.lookupMethod(Types.java:318)
		at org.apache.calcite.linq4j.tree.Expressions.call(Expressions.java:448)
		at org.apache.calcite.linq4j.tree.Expressions.call(Expressions.java:460)
		at org.apache.calcite.adapter.enumerable.RexImpTable$MinMaxImplementor.implementNotNullAdd(RexImpTable.java:1113)
		at org.apache.calcite.adapter.enumerable.StrictAggImplementor.implementAdd(StrictAggImplementor.java:151)
		at org.apache.calcite.adapter.enumerable.EnumerableWindow.implementAdd(EnumerableWindow.java:880)
		at org.apache.calcite.adapter.enumerable.EnumerableWindow.implement(EnumerableWindow.java:464)
		at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
		at org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118)
		at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
		... 19 more
	Caused by: java.lang.NoSuchMethodException: org.apache.calcite.runtime.SqlFunctions.greater(java.util.List, java.util.List)
		at java.base/java.lang.Class.getMethod(Class.java:2108)
		at org.apache.calcite.linq4j.tree.Types.lookupMethod(Types.java:309)
		... 28 more

{noformat}

From one side ANY_VALUE does not guarantee being deterministic (however it looks like Calcite always picks min value), 
from another side to avoid changing the existing behavior it could be redefine for non comparable input like pick the first available value



--
This message was sent by Atlassian Jira
(v8.3.4#803005)