Lantao Jin created CALCITE-7862:
-----------------------------------
Summary: ARG_MIN and ARG_MAX fail with "Unable to implement" when
the key is an ARRAY, MAP or ROW
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
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)
