[jira] [Commented] (CALCITE-7862) ARG_MIN and ARG_MAX should support ARRAY, MAP and ROW keys

["Zhen Chen (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-7862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18128617#comment-18128617 ] 

Zhen Chen commented on CALCITE-7862:
------------------------------------

Fix in [{{7b1650e}}|https://github.com/apache/calcite/commit/7b1650e31ca1770d2d3e049f32f9044687012595]

Thanks for the contribution [~cltlfcjin] 

Thanks for the review [~mbudiu] 

> ARG_MIN and ARG_MAX should support ARRAY, MAP and ROW keys
> ----------------------------------------------------------
>
>                 Key: CALCITE-7862
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7862
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.42.0
>            Reporter: Lantao Jin
>            Priority: Major
>              Labels: pull-request-available
>
> h2. Problem
> {{ARG_MIN(value, key)}} and {{ARG_MAX(value, key)}}, and their aliases {{MIN_BY}} and {{MAX_BY}}, pass validation when the key is an {{ARRAY}}, {{MAP}} or {{ROW}}, but the Enumerable convention cannot implement them. {{MIN}}, {{MAX}} and {{ORDER BY}} on the same values work.
> Related: CALCITE-5283 (added the functions), CALCITE-4813 (made MIN and MAX work on these types), CALCITE-7858
> h2. How to reproduce
> {code:sql}
> select arg_min(x, v)
> from (values (1, array[3.0, 1.0]), (2, array[1.0, 5.0]), (3, array[1.0, 4.0])) as t(x, v);
> {code}
> The expected result is 3, the row with the smallest key {{[1.0, 4.0]}}. The query fails instead:
> {noformat}
> java.lang.IllegalStateException: Unable to implement EnumerableAggregate(group=[{}], A=[ARG_MIN($0, $1)]): ...
>   Suppressed: java.lang.RuntimeException: while resolving method 'ltNullable[interface java.util.List, interface java.util.List]' in class class org.apache.calcite.runtime.SqlFunctions
>   Caused by: java.lang.NoSuchMethodException: org.apache.calcite.runtime.SqlFunctions.ltNullable(java.util.List,java.util.List)
> {noformat}
> On the same data, {{select min(v)}} returns {{[1.0, 4.0]}} and {{select x ... order by v}} returns 3, 2, 1. {{ARG_MAX}} fails the same way, and so does a {{MAP}} key (no {{ltNullable(Map, Map)}}) or a {{ROW}} key (no {{ltNullable(Object[], Object[])}}). An {{ARRAY}} as the value argument, {{arg_min(v, x)}}, works.
> h2. Cause
> {{RexImpTable.ArgMinMaxImplementor}} compares a non-primitive key with {{SqlFunctions.ltNullable}} or {{gtNullable}}, which are only declared for {{T extends Comparable<T>}}. At run time {{ARRAY}}, {{MAP}} and {{ROW}} values are {{List}}, {{Map}} and {{Object[]}}, which are not {{Comparable}}, so the method lookup fails during code generation. CALCITE-4813 added {{List}}, {{Map}} and {{Object[]}} overloads of {{lesser}} and {{greater}}, which {{MIN}} and {{MAX}} use, but not of {{ltNullable}} and {{gtNullable}}. The implementor is unchanged since CALCITE-5283, so every release since 1.33.0 is probably affected; I reproduced it on {{main}}.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)