[FLINK-10032] Rework SQL DATE_FORMAT function

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 1.5.2
• Component/s: Table SQL / API
• Labels: auto-unassigned

Environment

OS: Mac OSX.

Flink: Latest Flink 1.5.2 SQL-CLI embedded.

• Assignee: Unassigned
• Reporter: Ran Tao
• Created: 02/Aug/18 13:13
• Updated: 30/Apr/21 02:53
• Resolved: 30/Apr/21 02:48

Description

As Follows, but it works in sql program, and input string is expected result.

 

Logs:

org.apache.flink.table.client.gateway.SqlExecutionException: Invalid SQL query.
	at org.apache.flink.table.client.gateway.local.LocalExecutor.executeQueryInternal(LocalExecutor.java:396)
	at org.apache.flink.table.client.gateway.local.LocalExecutor.executeQuery(LocalExecutor.java:239)
	at org.apache.flink.table.client.cli.CliClient.callSelect(CliClient.java:376)
	at org.apache.flink.table.client.cli.CliClient.callCommand(CliClient.java:255)
	at java.util.Optional.ifPresent(Optional.java:159)
	at org.apache.flink.table.client.cli.CliClient.open(CliClient.java:184)
	at org.apache.flink.table.client.SqlClient.openCli(SqlClient.java:117)
	at org.apache.flink.table.client.SqlClient.start(SqlClient.java:101)
	at org.apache.flink.table.client.SqlClient.main(SqlClient.java:172)
Caused by: java.lang.NumberFormatException: For input string: "2018,08,01 14:00:00"
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
	at java.lang.Long.parseLong(Long.java:589)
	at java.lang.Long.valueOf(Long.java:803)
	at ExpressionReducer$15.map(Unknown Source)
	at org.apache.flink.table.codegen.ExpressionReducer.reduce(ExpressionReducer.scala:108)
	at org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressionsInternal(ReduceExpressionsRule.java:620)
	at org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:540)
	at org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:288)
	at org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:317)
	at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
	at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:415)
	at org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:252)
	at org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:127)
	at org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:211)
	at org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:198)
	at org.apache.flink.table.api.TableEnvironment.runHepPlanner(TableEnvironment.scala:258)
	at org.apache.flink.table.api.StreamTableEnvironment.optimize(StreamTableEnvironment.scala:827)
	at org.apache.flink.table.api.StreamTableEnvironment.translate(StreamTableEnvironment.scala:892)
	at org.apache.flink.table.api.StreamTableEnvironment.writeToSink(StreamTableEnvironment.scala:344)
	at org.apache.flink.table.api.Table.writeToSink(table.scala:862)
	at org.apache.flink.table.client.gateway.local.LocalExecutor.executeQueryInternal(LocalExecutor.java:389)
	... 8 more

The reason for this exception is a wrong signature definition in org.apache.flink.table.functions.sql.ScalarSqlFunctions.

While testing this 2 line fix I found an even bigger problem that makes DATE_FORMAT almost not usable. The output is time zone dependent. Although Java's SQL timestamp are timezone dependent usually we correct the offset such that the string output does not depend on a timezone.

Issue Links

• is related to: FLINK-8353 Add support for timezones
• relates to: FLINK-10143 date_format throws CodeGenException