Re: Question about UDAF that returns a super type like Number

[Arun Mahadevan <ar...@apache.org>]

>What do you think should be the SQL type of a UDAF that returns java.lang.Number? I don’t think there is a clear answer.

I think the return type can be figured out only based on actual argument types. (e.g. if argument is Integer, the return type is Integer and so on). Right now the SqltypeName for a UDAF that returns Number is coming up as “OTHER”.  I couldn’t figure out a way where this can be plugged in for user defined functions so that UDAFs can return SQL types based on actual argument type.

We also have UDAFs with return types like “List<T>” and/or user defined classes. Maybe if we can plugin the SQL return type names to “ANY”, the generated code will work since ANY maps to Object. But I couldn’t figure out a way to plug this in either; somewhere in the code it looks up the java class in “JavaToSqlTypeConversionRules.rules” which seems to be hard-coded.

Thanks,
Arun

On 12/29/16, 3:08 AM, "Julian Hyde" <jh...@apache.org> wrote:

>What do you think should be the SQL type of a UDAF that returns java.lang.Number? I don’t think there is a clear answer.
>
>We can discuss what the javaRowClass method should return when we have solved the that question.
>
>Julian
>
>
>> On Dec 27, 2016, at 10:09 PM, Arun Mahadevan <ar...@apache.org> wrote:
>> 
>> Thanks Julian for the inputs.
>> 
>>> Regarding Object[]. I think that is the type of a *row* (due to the JavaRowFormat.ARRAY in [1]) not the type of a particular column.
>> 
>> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java#L75 <https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java#L75>
>> 
>> From the code it appears to add the type of the individual columns of the row into “fieldClasses” so its not clear as to why should “Object[]” be added as the type when the field type does not directly map to a SQL type. If it was Object instead of Object[], the generated code would work. If you think it makes sense, I will submit a patch.
>> 
>> Thanks,
>> Arun
>> 
>> On 12/27/16, 12:47 PM, "Julian Hyde" <jhyde@apache.org <ma...@apache.org>> wrote:
>> 
>>> The main thing it that the UD(A)F framework can deduce the SQL type of the parameters and return value of the function. If a function returns Number, should the SQL type be DECIMAL(p, s), or INTEGER, or BIGINT, or DOUBLE? None of these options is perfect. If there isn’t an obvious winner, I don’t know whether we should allow Number.
>>> 
>>> Maybe we should add an annotation, similar to Parameter[5], that allows you to specify the SQL return type of a UDF. In this case, a Java function could return, say, a SQL BIGINT even if its return type was Object, Number or String.
>>> 
>>> Regarding Object[]. I think that is the type of a *row* (due to the JavaRowFormat.ARRAY in [1]) not the type of a particular column. There have been bugs due to inconsistencies — Calcite sometimes uses an Object to represent a single-column row, and sometimes uses an Object[]. Maybe you’re running into a bug of that kind.
>>> 
>>> Julian
>>> 
>>> [5] https://calcite.apache.org/apidocs/org/apache/calcite/linq4j/function/Parameter.html <https://calcite.apache.org/apidocs/org/apache/calcite/linq4j/function/Parameter.html><https://calcite.apache.org/apidocs/org/apache/calcite/linq4j/function/Parameter.html <https://calcite.apache.org/apidocs/org/apache/calcite/linq4j/function/Parameter.html>>
>>> 
>>>> On Dec 26, 2016, at 10:03 PM, Arun Iyer <ai...@hortonworks.com> wrote:
>>>> 
>>>> We have a UDAF that returns “Number” as the result type. We use RexToLixTranslator to generate java code [1]. In the generated code, the value of the field with Number type is cast to Object[] and the compilation fails.
>>>> 
>>>> When I trace the flow, I see that in PhysTypeImpl the field classes are determined via javaRowClass method [2].  The type for the Number field turns out to be “BasicSqlType(Other)” and the javaRowClass [3] returns a “Object[]”. Somewhere down the line the field value is then cast to “Object[]” in the generate code [4].
>>>> 
>>>> Just wondering why Object[] is returned (should unknown fields be mapped to Object instead?). Also is it possible to write UDAFs that return super types like Number (so that it can work for any sub type) but the return type does not directly map to SQL types?
>>>> 
>>>> Thanks,
>>>> Arun
>>>> 
>>>> [1] https://github.com/apache/storm/blob/master/external/sql/storm-sql-core/src/jvm/org/apache/storm/sql/compiler/RexNodeToBlockStatementCompiler.java#L73
>>>> [2] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java#L75
>>>> [3] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java#L96
>>>> [4] https://gist.github.com/arunmahadevan/35809494467d5636e31c0031f81d9aa7#file-test-java-L149
>