[
https://issues.apache.org/jira/browse/CALCITE-3459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16966993#comment-16966993
]
Julian Hyde commented on CALCITE-3459:
--------------------------------------
Has someone thought about the timezone implications here? Note that SQL's
TIMESTAMP type has NO TIMEZONE (not even UTC) whereas Java's java.sql.Timestamp
object has a timezone of UTC. Therefore when converting between the two you
have to supply a timezone (sometimes in a Calendar object).
> AssertionError for using Timestamp/Time/Date in user defined table function
> ---------------------------------------------------------------------------
>
> Key: CALCITE-3459
> URL: https://issues.apache.org/jira/browse/CALCITE-3459
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Wang Yanlin
> Assignee: Wang Yanlin
> Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Add the following test case in *TableFunctionTest* to reproduce, you need to
> add the implementation of user defined table function in *Smalls* before
> running the test.
> {code:java}
> @Test public void testTableFunctionWithTimeRelatedParameter() throws
> SQLException {
> try (Connection connection =
> DriverManager.getConnection("jdbc:calcite:")) {
> CalciteConnection calciteConnection =
> connection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = calciteConnection.getRootSchema();
> SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
> final TableFunction table1 =
> TableFunctionImpl.create(Smalls.TIMESTAMP_STRING_LENGTH);
> schema.add("TimestampStringLength", table1);
> final String sql1 = "select *\n"
> + "from table(\"s\".\"TimestampStringLength\"(TIMESTAMP '2019-10-12
> 19:00:35'))\n"
> + "as t(n, c) where n > 19";
> ResultSet resultSet1 = connection.createStatement().executeQuery(sql1);
> assertThat(CalciteAssert.toString(resultSet1),
> equalTo("N=20; C=abcdefg\n"));
> final TableFunction table2 =
> TableFunctionImpl.create(Smalls.DATE_STRING_LENGTH);
> schema.add("DateStringLength", table2);
> final String sql2 = "select *\n"
> + "from table(\"s\".\"DateStringLength\"(DATE '2019-10-12')) as
> t(n, c)\n"
> + "where n > 8";
> ResultSet resultSet2 = connection.createStatement().executeQuery(sql2);
> assertThat(CalciteAssert.toString(resultSet2),
> equalTo("N=9; C=abcdefghi\n"));
> final TableFunction table3 =
> TableFunctionImpl.create(Smalls.TIME_STRING_LENGTH);
> schema.add("TimeStringLength", table3);
> final String sql3 = "select *\n"
> + "from table(\"s\".\"TimeStringLength\"(TIME '19:00:35')) as t(n,
> c)\n"
> + "where n > 6";
> ResultSet resultSet3 = connection.createStatement().executeQuery(sql3);
> assertThat(CalciteAssert.toString(resultSet3),
> equalTo("N=7; C=abcdefg\n"));
> }
> }
> {code}
> The stack trace of exception
> {code:java}
> java.lang.AssertionError: value 2019-10-12 19:00:35 does not match type class
> java.sql.Timestamp
> at
> org.apache.calcite.linq4j.tree.ConstantExpression.<init>(ConstantExpression.java:50)
> at
> org.apache.calcite.linq4j.tree.Expressions.constant(Expressions.java:589)
> at
> org.apache.calcite.linq4j.tree.OptimizeShuttle.visit(OptimizeShuttle.java:278)
> at
> org.apache.calcite.linq4j.tree.UnaryExpression.accept(UnaryExpression.java:37)
> at
> org.apache.calcite.linq4j.tree.GotoStatement.accept(GotoStatement.java:60)
> at
> org.apache.calcite.linq4j.tree.BlockBuilder.optimize(BlockBuilder.java:437)
> at
> org.apache.calcite.linq4j.tree.BlockBuilder.toBlock(BlockBuilder.java:321)
> at
> org.apache.calcite.sql.validate.SqlUserDefinedTableMacro.coerce(SqlUserDefinedTableMacro.java:190)
> at
> org.apache.calcite.sql.validate.SqlUserDefinedTableMacro.convertArguments(SqlUserDefinedTableMacro.java:110)
> at
> org.apache.calcite.sql.validate.SqlUserDefinedTableFunction.getRowType(SqlUserDefinedTableFunction.java:70)
> at
> org.apache.calcite.sql.validate.ProcedureNamespace.validateImpl(ProcedureNamespace.java:62)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1009)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:969)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3129)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3111)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3383)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1009)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:969)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:944)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:651)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:558)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:265)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:231)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:638)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:502)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:472)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
> at
> org.apache.calcite.test.TableFunctionTest.testTableFunctionWithTimeRelatedParameter(TableFunctionTest.java:139)
> {code}
> The implementation of the user defined table function in *Smalls* is quite
> simple
> {code:java}
> public static final Method TIMESTAMP_STRING_LENGTH =
> Types.lookupMethod(Smalls.class, "timestampStringLength",
> Timestamp.class);
> public static final Method TIME_STRING_LENGTH =
> Types.lookupMethod(Smalls.class, "timeStringLength", Time.class);
> public static final Method DATE_STRING_LENGTH =
> Types.lookupMethod(Smalls.class, "dateStringLength", Date.class);
> public static QueryableTable timestampStringLength(final Timestamp timestamp)
> {
> return generateStrings(timestamp.toString().length());
> }
> public static QueryableTable timeStringLength(final Time time) {
> return generateStrings(time.toString().length());
> }
> public static QueryableTable dateStringLength(final Date date) {
> return generateStrings(date.toString().length());
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
