I’m not sure that we’ve ever tested a user-defined function that returns java.sql.Date, java.sql.Time or java.sql.Timestamp. Can you log a bug for this?
If you could provide a test case that would be even better. See how we wrote the test case in https://issues.apache.org/jira/browse/CALCITE-986. Julian > On Dec 30, 2015, at 7:12 PM, DroopyHoo <[email protected]> wrote: > > Hi, > > I use ScalarFunction to define a UDF which return type is timestamp,and met a > problem. > > The error SQL : > > select * from table where UDF(field) in (cast('1970-01-01 00:00:00'), > cast('1997-02-01 00:00:00')); > > Error stack: > Caused by: java.lang.RuntimeException: Error while compiling generated Java > code: > ............... > .............. > at org.apache.calcite.avatica.Helper.wrap(Helper.java:37) > at > org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:108) > at > org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1044) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:293) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:188) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:669) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:570) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:539) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:173) > at > org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:504) > at > org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:477) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:109) > ... 33 more > Caused by: org.codehaus.commons.compiler.CompileException: Line 66, Column > 101: Cannot cast "long" to "java.sql.Timestamp" > at > org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:10092) > at org.codehaus.janino.UnitCompiler.compileGet2(UnitCompiler.java:3839) > at org.codehaus.janino.UnitCompiler.access$6400(UnitCompiler.java:183) > at > org.codehaus.janino.UnitCompiler$10.visitCast(UnitCompiler.java:3246) > at org.codehaus.janino.Java$Cast.accept(Java.java:3802) > > > I found the Generated Code cause Exception : > if (v && v1 == > org.apache.calcite.runtime.SqlFunctions.toLong((java.sql.Timestamp) > org.apache.calcite.avatica.util.DateTimeUtils.timestampStringToUnixDate("1986-12-18 > 00:00:00")) > > > -- > ------- > Wei Hu >
