[FLINK-15602] Blink planner does not respect the precision when casting timestamp to varchar

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.10.0
• Fix Version/s: 1.10.0
• Component/s: Table SQL / Planner
• Labels: pull-request-available
• Assignee: Zhenghua Gao
• Reporter: Dawid Wysakowicz
• Created: 15/Jan/20 13:01
• Updated: 23/Jan/20 15:32
• Resolved: 23/Jan/20 15:32

Description

According to SQL 2011 Part 2 Section 6.13 General Rules 11) d)

If SD is a datetime data type or an interval data type then let Y be the shortest character string that
conforms to the definition of <literal> in Subclause 5.3, “<literal>”, and such that the interpreted value
of Y is SV and the interpreted precision of Y is the precision of SD.

That means:

select cast(cast(TO_TIMESTAMP('2014-07-02 06:14:00', 'YYYY-MM-DD HH24:mm:SS') as TIMESTAMP(0)) as VARCHAR(256)) from ...;
// should produce
// 2014-07-02 06:14:00

select cast(cast(TO_TIMESTAMP('2014-07-02 06:14:00', 'YYYY-MM-DD HH24:mm:SS') as TIMESTAMP(3)) as VARCHAR(256)) from ...;
// should produce
// 2014-07-02 06:14:00.000

select cast(cast(TO_TIMESTAMP('2014-07-02 06:14:00', 'YYYY-MM-DD HH24:mm:SS') as TIMESTAMP(9)) as VARCHAR(256)) from ...;
// should produce
// 2014-07-02 06:14:00.000000000

One possible solution would be to propagate the precision in org.apache.flink.table.planner.codegen.calls.ScalarOperatorGens#localTimeToStringCode. If I am not mistaken this problem was introduced in FLINK-14599

Issue Links

• causes: FLINK-14505 SQL Client end-to-end test for Kafka 0.10 nightly end-to-end test failed on travis
• relates to: FLINK-14599 Support precision of TimestampType in blink planner
• links to: GitHub Pull Request #10877