[
https://issues.apache.org/jira/browse/CALCITE-2641?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16667830#comment-16667830
]
Vladimir Sitnikov commented on CALCITE-2641:
--------------------------------------------
[~ptrbojko], PR896 looks weird to me.
I think the proper way to approach this is to teach RexToLixTranslator to
translate Date/Time/Timestamp/TimestampWithTimeZoneString into proper java
literals.
RexToLixTranslator generates "Java" code for enumerable convention, and it
looks like RexToLixTranslator keeps TimestampString.
In other words, it is not just TimestampString that need to be converted
somehow to Timestamp. Expressions like {{current_timestamp + interval '1'
hour}} should work as well (you can't add {{ToTypeConvertible}} there).
On top of that, you probably want {{OffsetDateTime}}, {{ZonedDateTime}},
{{java.time.Instant}} and friends to be supported for call arguments as well.
> DATE and TIMESTAMP arguments to user-defined table function
> -----------------------------------------------------------
>
> Key: CALCITE-2641
> URL: https://issues.apache.org/jira/browse/CALCITE-2641
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.17.0, 1.18.0
> Reporter: Piotr Bojko
> Assignee: Julian Hyde
> Priority: Major
>
> I am trying to create UDF with java.sql.Timestamp as a parameter:
> {code:java}
> public static QueryableTable getTable(
> @Parameter(name = "Projection") String projection,
> @Parameter(name = "Query") String query,
> @Parameter(name = "Test Timestamp") Timestamp
> timestamp)
> {code}
> But when using following UDF like this:
> {code:java}
> SELECT * FROM TABLE(MYUDF('ID','', TIMESTAMP '2013-01-01 01:01:01'))
> {code}
> I've got following exception and wondering whether I am missing something or
> having date/time as a parameter is not fully supported yet?
> {noformat}
> [INFO] [talledLocalContainer] java.lang.AssertionError: value 2013-01-01
> 01:01:01 does not match type class java.sql.Timestamp
> [INFO] [talledLocalContainer] at
> org.apache.calcite.linq4j.tree.ConstantExpression.<init>(ConstantExpression.java:49)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.linq4j.tree.Expressions.constant(Expressions.java:588)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.linq4j.tree.OptimizeShuttle.visit(OptimizeShuttle.java:278)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.linq4j.tree.UnaryExpression.accept(UnaryExpression.java:37)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.linq4j.tree.GotoStatement.accept(GotoStatement.java:60)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.linq4j.tree.BlockBuilder.optimize(BlockBuilder.java:438)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.linq4j.tree.BlockBuilder.toBlock(BlockBuilder.java:322)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlUserDefinedTableMacro.coerce(SqlUserDefinedTableMacro.java:190)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlUserDefinedTableMacro.convertArguments(SqlUserDefinedTableMacro.java:110)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlUserDefinedTableFunction.getRowType(SqlUserDefinedTableFunction.java:70)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.ProcedureNamespace.validateImpl(ProcedureNamespace.java:62)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:971)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:950)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3036)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3018)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3288)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:971)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:950)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:925)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:629)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:553)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:264)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:230)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:774)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:638)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:608)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:238)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:220)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:209)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:96)
> [INFO] [talledLocalContainer] at
> org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
> {noformat}
> When breakpointed on ConstantExpression.java:49, the debugged value was
> TimestampString with toString = 2013-01-01 01:01:01
> Suggestion from [~vladimirsitnikov]:
> {quote}Then it might be a bug
> in org/apache/calcite/linq4j/tree/OptimizeShuttle.java:277
> Have you tried to comment that branch out for test purposes?{quote}
> Suggestion from [~julianhyde]:
> {quote}
> Two existing JIRA cases that relate to this are
> https://issues.apache.org/jira/browse/CALCITE-1041
> <https://issues.apache.org/jira/browse/CALCITE-1041> and
> https://issues.apache.org/jira/browse/CALCITE-986
> <https://issues.apache.org/jira/browse/CALCITE-986>.
> This case seems different because it is a user-defined TABLE function. I
> don’t think that has been tested before, therefore I wouldn’t consider it
> “done". The first step is to a bug “DATE and TIMESTAMP arguments to
> user-defined table function”, and the second step is to write a test in
> TableFunctionTest.
> {quote}
> I will attach a test case in a moment,
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
