You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Mehant Baid <ba...@gmail.com> on 2014/06/10 04:05:43 UTC
Aggregate function on top of a user defined function
Hey Guys,
I was hitting the following assertion when using an aggregate
function(max) with a drill custom function (substr)
/AssertionError:[ Internal error: while converting `substr`(`A`, 1, 3) ]
< InvocationTargetException < UnsupportedOperationException:[ class
org.eigenbase.sql.SqlUnresolvedFunction: substr ]"
/Stack Trace:
at org.eigenbase.util.Util.needToImplement(Util.java:903) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql.SqlOperator.inferReturnType(SqlOperator.java:456) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.rex.RexBuilder.deriveReturnType(RexBuilder.java:260) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.rex.RexBuilder.makeCall(RexBuilder.java:219) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.StandardConvertletTable.convertFunction(StandardConvertletTable.java:597) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)(NativeMethodAccessorImpl.java) ~[na:1.7.0_45]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_45]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_45]
at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_45]
at org.eigenbase.sql2rel.ReflectiveConvertletTable$2.convertCall(ReflectiveConvertletTable.java:139) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:52) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4210) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:3611) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql.SqlCall.accept(SqlCall.java:133) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4116) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4517) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4363) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql.SqlCall.accept(SqlCall.java:133) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4473) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4363) ~[optiq-core-0.7-20140513.013236-5.jar:na]
at org.eigenbase.sql.SqlNodeList.accept(SqlNodeList.java:149) ~[optiq-core-0.7-20140513.013236-5.jar:na]
.....
Happens when we are done with the validation phase and are trying to
convert it to Rel. We hit this error when we are trying to infer the
return type of the 'substr' function, but the SqlOperator class
associated with this SqlCall is SqlUnresolvedFunction.
The problem seems to be that we are not invoking setOperator() on the
SqlCall associated with the 'substr' function, I notice that we are
calling setOperator() and replace the SqlUnresolvedFunction with the
DrillSqlOperator in the validation phase in SqlFunction.deriveType()
function (here
<https://github.com/julianhyde/optiq/blob/master/core/src/main/java/org/eigenbase/sql/SqlFunction.java#L290>).
Would appreciate any pointers on how to resolve this.
Thanks
Mehant
Re: Aggregate function on top of a user defined function
Posted by Julian Hyde <ju...@gmail.com>.
> Vladimir wrote:
>
> As far as I remember, I did try using functions with same name and
> different arguments (e.g. to_char(Department), to_char(Employee), etc) and
> it fails in beautiful ways.
Note that what Vladimir was trying to do -- overloading on object types Department, Employee -- requires support for object types that is beyond Optiq right now (pending https://github.com/julianhyde/optiq/issues/207). Overloading on scalar types may be simpler.
I added a test for UDFs overloaded on scalar type arguments, and it works fine.
https://github.com/julianhyde/optiq/commit/21106458d96c514df85531e5d795102abf0b2151
Julian
Re: Aggregate function on top of a user defined function
Posted by Mehant Baid <ba...@gmail.com>.
Since substr is overloaded the SqlUnresolvedFunction is not replaced
with the correct operator class until we try to infer the return type in
SqlValidatorImpl
<https://github.com/julianhyde/optiq/blob/master/core/src/main/java/org/eigenbase/sql/validate/SqlValidatorImpl.java#L3877>
which eventually delegates to SqlFunction
<https://github.com/julianhyde/optiq/blob/master/core/src/main/java/org/eigenbase/sql/SqlFunction.java#L290>
to replace the operator with the correct class and passes the validation
phase successfully. The problem seems to be that we aren't performing
similar replacement when we try and convert to Rel nodes.
I don't have a test case reproducible within Optiq yet, I'll try to see
if I can put together one.
Thanks
Mehant
On 6/10/14, 6:42 AM, Vladimir Sitnikov wrote:
> As far as I remember, replacement of SqlUnresolvedFunction operator with
> the correct one is performed in
> https://github.com/julianhyde/optiq/blob/master/core/src/main/java/org/eigenbase/sql/validate/SqlValidatorImpl.java#L894
>
> The code seems to lack support of user defined functions with multiple
> overloads.
> As far as I remember, I did try using functions with same name and
> different arguments (e.g. to_char(Department), to_char(Employee), etc) and
> it fails in beautiful ways.
>
> Mehant, can you please clarify if substr operator has multiple overloads?
> Can you reproduce the case using Optiq code base?
>
> --
> Vladimir Sitnikov
>
Re: Aggregate function on top of a user defined function
Posted by Vladimir Sitnikov <si...@gmail.com>.
As far as I remember, replacement of SqlUnresolvedFunction operator with
the correct one is performed in
https://github.com/julianhyde/optiq/blob/master/core/src/main/java/org/eigenbase/sql/validate/SqlValidatorImpl.java#L894
The code seems to lack support of user defined functions with multiple
overloads.
As far as I remember, I did try using functions with same name and
different arguments (e.g. to_char(Department), to_char(Employee), etc) and
it fails in beautiful ways.
Mehant, can you please clarify if substr operator has multiple overloads?
Can you reproduce the case using Optiq code base?
--
Vladimir Sitnikov
Re: Aggregate function on top of a user defined function
Posted by Julian Hyde <ju...@hydromatic.net>.
Vladimir may have fixed this issue in https://github.com/julianhyde/optiq/commit/32738eb23fadee5091351813b261c4fe48dd3407. I notice that he added a very similar test case in JdbcTest:
```java
/** Tests derived return type of user-defined function. */
@Test public void testUdfDerivedReturnType() {
final OptiqAssert.AssertThat with = withUdf();
with.query(
"select max(\"adhoc\".my_double(\"deptno\")) as p from \"adhoc\".EMPLOYEES")
.returns(
"P=40\n");
with.query(
"select max(\"adhoc\".my_str(\"name\")) as p from \"adhoc\".EMPLOYEES\n"
+ "where \"adhoc\".my_str(\"name\") is null")
.returns(
"P=null\n");
}
```
Julian