[
https://issues.apache.org/jira/browse/CALCITE-7862?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen resolved CALCITE-7862.
--------------------------------
Fix Version/s: 1.43.0
Assignee: Lantao Jin
Resolution: Fixed
> 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
> Assignee: Lantao Jin
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> 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)
