Wired behavior of DATE_FORMAT UDF in Flink SQL

[徐涛]

Hi Experts,

I found that DATE_FORMAT(timestamp,format) returns a TIMESTAMP type, it is wired, because normally format result should be a string type.

In document it says “Formats timestamp as a string using a specified format string”. But when I run it in Flink SQL, 

select 

 ALBUM_ID, 

 UNLOCK_USER_ID, 

 DATE_FORMAT(CREATE_TIME, '%Y-%m-%d') as unlock_date 

 from share_album_unlock;

The error throws out:

org.apache.flink.table.api.ValidationException: Field types of query result and registered TableSink mysqlSink do not match.

Query result schema: [ALBUM_ID: Long, unlock_date: Timestamp, score: Long]

TableSink schema:    [ALBUM_ID: Long, time_window: String, score: Long]

The flink version is 1.6.2.

Is it a program bug or something else? 

Best

Henry

[Timo Walther]

Hi Henry,

the DATE_FORMAT function is in a very bad state right now. I would recommend to implement your own custom function right now.

This issue is tracked here: https://issues.apache.org/jira/browse/FLINK-10032

Regards,
Timo


Am 31.10.18 um 07:44 schrieb 徐涛:

Hi Experts,

I found that DATE_FORMAT(timestamp,format) returns a TIMESTAMP type, it is wired, because normally format result should be a string type.

In document it says “Formats timestamp as a string using a specified format string”. But when I run it in Flink SQL, 

select 

 ALBUM_ID, 

 UNLOCK_USER_ID, 

 DATE_FORMAT(CREATE_TIME, '%Y-%m-%d') as unlock_date 

 from share_album_unlock;

The error throws out:

org.apache.flink.table.api.ValidationException: Field types of query result and registered TableSink mysqlSink do not match.

Query result schema: [ALBUM_ID: Long, unlock_date: Timestamp, score: Long]

TableSink schema:    [ALBUM_ID: Long, time_window: String, score: Long]

The flink version is 1.6.2.

Is it a program bug or something else? 

Best

Henry