[CALCITE-3459] AssertionError for using Timestamp/Time/Date in user defined table function

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Labels: pull-request-available
• Assignee: Wang Yanlin
• Reporter: Wang Yanlin
• Created: 30/Oct/19 03:18
• Updated: 24/Feb/22 02:12

Description

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.

@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"));
    }
  }

The stack trace of exception

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)

The implementation of the user defined table function in Smalls is quite simple

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());
  }

Issue Links

• links to: GitHub Pull Request #1545
• links to: GitHub Pull Request #2518