Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-986

User-defined function with Date or Timestamp parameters

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.3.0-incubating
    • Fix Version/s: 1.6.0
    • Component/s: core
    • Labels:
      None

      Description

      the field type is date and I want to realize the MY_FUNC(date)

      I use JavaTypeFactory.createSqlType to define the columns

      SQL : select MY_FUNC(date) from table;

      if I set my function's argument type to java.sql.Date, calcite will report Exception:

      /Caused by: org.codehaus.commons.compiler.CompileException: Line 124, Column 45: No applicable constructor/method found for actual parameters "int"; /

      if I set the function's argument type to int, calcite will report Exception:

      _/Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature MY_FUNCTION(<DATE>)/

        Issue Links

          Activity

          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/3e7efce6 .
          Hide
          Droopyhu Wei Hu added a comment -

          udf is as below:
          java.sql.Timestamp myfunc(Timestamp field)

          SQL: select * from table where myFunc(field) in (cast('1970-01-01 00:00:00'), cast('1997-02-01 00:00:00'));

          calcite's generated code throw compile exception:

          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)

          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"))

          Show
          Droopyhu Wei Hu added a comment - udf is as below: java.sql.Timestamp myfunc(Timestamp field) SQL: select * from table where myFunc(field) in (cast('1970-01-01 00:00:00'), cast('1997-02-01 00:00:00')); calcite's generated code throw compile exception: 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) 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"))
          Hide
          julianhyde Julian Hyde added a comment -

          Wei Hu, Since this case is closed, can you please add details to the new case CALCITE-1041?

          Show
          julianhyde Julian Hyde added a comment - Wei Hu , Since this case is closed, can you please add details to the new case CALCITE-1041 ?
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.6.0 (2016-01-22).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.6.0 (2016-01-22).

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              Droopyhu Wei Hu
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development