[ https://issues.apache.org/jira/browse/FLINK-15602?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17016512#comment-17016512 ]
Zhenghua Gao commented on FLINK-15602: -------------------------------------- Hi [~dwysakowicz] [~tiwalter] I investigated the behavior of popular DBMS and found: PostgreSQL DO NOT pad '0' and Oracle/MS SQL pad '0' (MYSQL would pad '0' for TIMESTAMP type and not pad '0' for DATETIME type). And, hive/spark would not pad '0' too. What's your opinion about the padding behavior? > Blink planner does not respect the precision when casting timestamp to varchar > ------------------------------------------------------------------------------ > > Key: FLINK-15602 > URL: https://issues.apache.org/jira/browse/FLINK-15602 > Project: Flink > Issue Type: Bug > Components: Table SQL / Planner > Affects Versions: 1.10.0 > Reporter: Dawid Wysakowicz > Priority: Blocker > Fix For: 1.10.0 > > > According to SQL 2011 Part 2 Section 6.13 General Rules 11) d) > {quote} > 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. > {quote} > That means: > {code} > 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 > {code} > 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] -- This message was sent by Atlassian Jira (v8.3.4#803005)