You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by GitBox <gi...@apache.org> on 2021/03/23 09:19:22 UTC

[GitHub] [flink] wuchong commented on a change in pull request #15132: [FLINK-21698][table-planner] Disable problematic cast conversion between NUMERIC type and TIMESTAMP type

wuchong commented on a change in pull request #15132:
URL: https://github.com/apache/flink/pull/15132#discussion_r599333669



##########
File path: flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/ScalarOperatorGens.scala
##########
@@ -1203,72 +1196,111 @@ object ScalarOperatorGens {
         s"$method($operandTerm.getMillisecond(), $zone)"
       }
 
-    // Timestamp -> Decimal
-    case  (TIMESTAMP_WITHOUT_TIME_ZONE, DECIMAL) =>
-      val dt = targetType.asInstanceOf[DecimalType]
-      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm =>
-          s"""
-             |$DECIMAL_UTIL.castFrom(
-             |  ((double) ($operandTerm.getMillisecond() / 1000.0)),
-             |  ${dt.getPrecision}, ${dt.getScale})
-           """.stripMargin
-      }
-
-    // Tinyint -> Timestamp
-    // Smallint -> Timestamp
-    // Int -> Timestamp
-    // Bigint -> Timestamp
+    // Disable cast conversion between Numeric type and  Timestamp type
     case (TINYINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
          (SMALLINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
          (INTEGER, TIMESTAMP_WITHOUT_TIME_ZONE) |
-         (BIGINT, TIMESTAMP_WITHOUT_TIME_ZONE) =>
+         (BIGINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (FLOAT, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (DOUBLE, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (DECIMAL, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, TINYINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, SMALLINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, INTEGER) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, BIGINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, FLOAT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, DOUBLE) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, DECIMAL) => {
+      if (TIMESTAMP_WITHOUT_TIME_ZONE.equals(targetType.getTypeRoot)) {
+        throw new ValidationException("The cast conversion from NUMERIC type to TIMESTAMP type" +
+          " is problematic, it's recommended to use" +
+          " TO_TIMESTAMP(FROM_UNIXTIME(epochSeconds)) as an replacement.")
+      } else {
+        throw new ValidationException("The cast conversion from TIMESTAMP type to NUMERIC type" +
+          " is problematic, it's recommended to use" +
+          " UNIX_TIMESTAMP(CAST(timestampCol AS STRING)) as an replacement.")
+      }
+    }
+
+    // Tinyint -> TimestampLtz
+    // Smallint -> TimestampLtz
+    // Int -> TimestampLtz
+    // Bigint -> TimestampLtz
+    case (TINYINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (SMALLINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (INTEGER, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (BIGINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
         operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis(((long) $operandTerm) * 1000)"
       }
 
-    // Float -> Timestamp
-    // Double -> Timestamp
-    case (FLOAT, TIMESTAMP_WITHOUT_TIME_ZONE) |
-         (DOUBLE, TIMESTAMP_WITHOUT_TIME_ZONE) =>
+    // Float -> TimestampLtz
+    // Double -> TimestampLtz
+    case (FLOAT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (DOUBLE, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
+      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
+        operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis((long) ($operandTerm * 1000)," +
+          s" (int)($operandTerm * 1000_000_000 % 1000_000))"

Review comment:
       ditto.

##########
File path: flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
##########
@@ -184,82 +184,267 @@ class TemporalTypesTest extends ExpressionTestBase {
 
   @Test
   def testTimePointCasting(): Unit = {
+    // DATE -> TIMESTAMP
     testAllApis(
       'f0.cast(DataTypes.TIMESTAMP(3)),
       "CAST(f0 AS TIMESTAMP(3))",
       "1990-10-14 00:00:00.000")
 
+    // TIME -> TIMESTAMP
     testAllApis(
       'f1.cast(DataTypes.TIMESTAMP(3)),
       "CAST(f1 AS TIMESTAMP(3))",
       "1970-01-01 10:20:45.000")
 
+    // TIMESTAMP -> DATE
     testAllApis(
       'f2.cast(DataTypes.DATE),
       "CAST(f2 AS DATE)",
       "1990-10-14")
 
+    // TIMESTAMP -> TIME
     testAllApis(
       'f2.cast(DataTypes.TIME),
       "CAST(f2 AS TIME)",
       "10:20:45")
+  }
 
-    testAllApis(
-      'f2.cast(DataTypes.TIME),
-      "CAST(f2 AS TIME)",
-      "10:20:45")
+  @Test
+  def testTimestampLtzCastInUTC(): Unit = {
+    config.setLocalTimeZone(ZoneId.of("UTC"))
 
+    //DATE -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(CAST('123' as DECIMAL(5, 2)) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      "CAST(f0 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 00:00:00.000")
 
+    //TIME -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS DECIMAL(5, 2))",
-      "123.00")
+      "CAST(f1 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 10:20:45.000")
 
+    //TIMESTAMP_LTZ -> TIME
     testSqlApi(
-      "CAST(CAST('123' AS FLOAT) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIME)",
+      "01:02:03")
 
+    //TIMESTAMP_LTZ -> DATE
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS FLOAT)",
-      "123.0")
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS DATE)",
+      "2018-03-14")
+  }
+
+  @Test
+  def testTimestampLtzCastInShanghai(): Unit = {
+    config.setLocalTimeZone(ZoneId.of("Asia/Shanghai"))
 
+    // DATE -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(CAST('123' AS DOUBLE) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      "CAST(f0 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 00:00:00.000")
 
+    // TIME -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS DOUBLE)",
-      "123.0")
+      "CAST(f1 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 10:20:45.000")
 
+    // TIMESTAMP -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS TINYINT)",
+      "CAST(f2 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 10:20:45.123")
+
+    // TINYINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS TINYINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // SMALLINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS SMALLINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // INT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(100 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // BIGINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS BIGINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // FLOAT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.01 AS FLOAT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.010")
+
+    // DOUBLE -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.123 AS DOUBLE) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.123")
+
+    // DECIMAL -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.1234 as DECIMAL(38, 18)) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.123")
+
+    // TIMESTAMP_LTZ -> TIME
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIME)",
+      "01:02:03")
+
+    // TIMESTAMP_LTZ -> DATE
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS DATE)",
+      "2018-03-14")
+
+    // TIMESTAMP_LTZ -> TIMESTAMP
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIMESTAMP(3))",
+      "2018-03-14 01:02:03.000")
+
+    // TIMESTAMP_LTZ -> TINYINT
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS TINYINT)",
       "123")
 
+    // TIMESTAMP_LTZ -> SMALLINT
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS SMALLINT)",
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS SMALLINT)",
       "123")
 
+    // TIMESTAMP_LTZ -> INT
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS INT)",
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS INT)",
       "123")
 
+    // TIMESTAMP_LTZ -> BIGINT
     testSqlApi(
-      "CAST(f0 AS TIMESTAMP(3) WITH LOCAL TIME ZONE)",
-      "1990-10-14 00:00:00.000")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS BIGINT)",
+      "123")
 
+    // TIMESTAMP_LTZ -> FLOAT
     testSqlApi(
-      "CAST(f1 AS TIMESTAMP(3) WITH LOCAL TIME ZONE)",
-      "1970-01-01 10:20:45.000")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS FLOAT)",
+      "123.123")
 
+    // TIMESTAMP_LTZ -> DOUBLE
     testSqlApi(
-      s"CAST(${timestampTz("2018-03-14 01:02:03")} AS TIME)",
-      "01:02:03")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS DOUBLE)",
+      "123.123")
 
+    // TIMESTAMP_LTZ -> DECIMAL
     testSqlApi(
-      s"CAST(${timestampTz("2018-03-14 01:02:03")} AS DATE)",
-      "2018-03-14")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS DECIMAL(38, 3))",
+      "123.123")
+
+    // test precision when cast to TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(1.1234567 AS FLOAT) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.123456")
+    testSqlApi(
+      "CAST(CAST(1.12 AS FLOAT) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+    testSqlApi(
+      "CAST(CAST(1.1234567899 AS DOUBLE) AS TIMESTAMP_LTZ(9))",
+      "1970-01-01 08:00:01.123456789")
+    testSqlApi(
+      "CAST(CAST(1.12 AS DOUBLE) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+    testSqlApi(
+      "CAST(CAST(1.1234567899 AS DECIMAL(38, 18)) AS TIMESTAMP_LTZ(9))",
+      "1970-01-01 08:00:01.123456789")
+    testSqlApi(
+      "CAST(CAST(1.12 AS DECIMAL(38, 18)) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+
+    // test precision when cast from TIMESTAMP_LTZ
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:00:01.123456")} AS FLOAT)",
+      "1.123456")
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123456")} AS DOUBLE)",
+      "123.123456")
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123456")} AS DECIMAL(38, 6))",
+      "123.123456")
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123456")} AS DECIMAL(38, 4))",
+      "123.1235")
+
+    // test cast with null value
+    testSqlApi(
+      s"CAST(CAST(null AS BIGINT) AS TIMESTAMP_LTZ(3))",
+      "null")
+    testSqlApi(
+      s"CAST(CAST(null AS TIMESTAMP_LTZ(3)) AS BIGINT)",
+      "null")
+  }
+
+  @Test
+  def tesInvalidCastBetweenNumericAndTimestamp(): Unit = {
+    val castFromTimestampExceptionMsg = "The cast conversion from TIMESTAMP type to NUMERIC type" +
+      " is problematic, it's recommended to use UNIX_TIMESTAMP(CAST(timestampCol AS STRING))" +
+      " as an replacement."
+    val castToTimestampExceptionMsg = "The cast conversion from NUMERIC type to TIMESTAMP type" +
+      " is problematic, it's recommended to use TO_TIMESTAMP(FROM_UNIXTIME(epochSeconds)) as" +
+      " an replacement."

Review comment:
       What do you think about:
   
   The cast conversion from NUMERIC type to TIMESTAMP type is not allowed,
   it's recommended to use TO_TIMESTAMP(FROM_UNIXTIME(numeric_col)) instead, note the numeric is in seconds.

##########
File path: flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
##########
@@ -184,82 +184,267 @@ class TemporalTypesTest extends ExpressionTestBase {
 
   @Test
   def testTimePointCasting(): Unit = {
+    // DATE -> TIMESTAMP
     testAllApis(
       'f0.cast(DataTypes.TIMESTAMP(3)),
       "CAST(f0 AS TIMESTAMP(3))",
       "1990-10-14 00:00:00.000")
 
+    // TIME -> TIMESTAMP
     testAllApis(
       'f1.cast(DataTypes.TIMESTAMP(3)),
       "CAST(f1 AS TIMESTAMP(3))",
       "1970-01-01 10:20:45.000")
 
+    // TIMESTAMP -> DATE
     testAllApis(
       'f2.cast(DataTypes.DATE),
       "CAST(f2 AS DATE)",
       "1990-10-14")
 
+    // TIMESTAMP -> TIME
     testAllApis(
       'f2.cast(DataTypes.TIME),
       "CAST(f2 AS TIME)",
       "10:20:45")
+  }
 
-    testAllApis(
-      'f2.cast(DataTypes.TIME),
-      "CAST(f2 AS TIME)",
-      "10:20:45")
+  @Test
+  def testTimestampLtzCastInUTC(): Unit = {
+    config.setLocalTimeZone(ZoneId.of("UTC"))
 
+    //DATE -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(CAST('123' as DECIMAL(5, 2)) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      "CAST(f0 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 00:00:00.000")
 
+    //TIME -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS DECIMAL(5, 2))",
-      "123.00")
+      "CAST(f1 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 10:20:45.000")
 
+    //TIMESTAMP_LTZ -> TIME
     testSqlApi(
-      "CAST(CAST('123' AS FLOAT) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIME)",
+      "01:02:03")
 
+    //TIMESTAMP_LTZ -> DATE
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS FLOAT)",
-      "123.0")
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS DATE)",
+      "2018-03-14")
+  }
+
+  @Test
+  def testTimestampLtzCastInShanghai(): Unit = {
+    config.setLocalTimeZone(ZoneId.of("Asia/Shanghai"))
 
+    // DATE -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(CAST('123' AS DOUBLE) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      "CAST(f0 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 00:00:00.000")
 
+    // TIME -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS DOUBLE)",
-      "123.0")
+      "CAST(f1 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 10:20:45.000")
 
+    // TIMESTAMP -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS TINYINT)",
+      "CAST(f2 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 10:20:45.123")
+
+    // TINYINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS TINYINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // SMALLINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS SMALLINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // INT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(100 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // BIGINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS BIGINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // FLOAT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.01 AS FLOAT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.010")
+
+    // DOUBLE -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.123 AS DOUBLE) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.123")
+
+    // DECIMAL -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.1234 as DECIMAL(38, 18)) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.123")
+
+    // TIMESTAMP_LTZ -> TIME
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIME)",
+      "01:02:03")
+
+    // TIMESTAMP_LTZ -> DATE
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS DATE)",
+      "2018-03-14")
+
+    // TIMESTAMP_LTZ -> TIMESTAMP
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIMESTAMP(3))",
+      "2018-03-14 01:02:03.000")
+
+    // TIMESTAMP_LTZ -> TINYINT
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS TINYINT)",
       "123")
 
+    // TIMESTAMP_LTZ -> SMALLINT
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS SMALLINT)",
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS SMALLINT)",
       "123")
 
+    // TIMESTAMP_LTZ -> INT
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS INT)",
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS INT)",
       "123")
 
+    // TIMESTAMP_LTZ -> BIGINT
     testSqlApi(
-      "CAST(f0 AS TIMESTAMP(3) WITH LOCAL TIME ZONE)",
-      "1990-10-14 00:00:00.000")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS BIGINT)",
+      "123")
 
+    // TIMESTAMP_LTZ -> FLOAT
     testSqlApi(
-      "CAST(f1 AS TIMESTAMP(3) WITH LOCAL TIME ZONE)",
-      "1970-01-01 10:20:45.000")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS FLOAT)",
+      "123.123")
 
+    // TIMESTAMP_LTZ -> DOUBLE
     testSqlApi(
-      s"CAST(${timestampTz("2018-03-14 01:02:03")} AS TIME)",
-      "01:02:03")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS DOUBLE)",
+      "123.123")
 
+    // TIMESTAMP_LTZ -> DECIMAL
     testSqlApi(
-      s"CAST(${timestampTz("2018-03-14 01:02:03")} AS DATE)",
-      "2018-03-14")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS DECIMAL(38, 3))",
+      "123.123")
+
+    // test precision when cast to TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(1.1234567 AS FLOAT) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.123456")
+    testSqlApi(
+      "CAST(CAST(1.12 AS FLOAT) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+    testSqlApi(
+      "CAST(CAST(1.1234567899 AS DOUBLE) AS TIMESTAMP_LTZ(9))",
+      "1970-01-01 08:00:01.123456789")
+    testSqlApi(
+      "CAST(CAST(1.12 AS DOUBLE) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+    testSqlApi(
+      "CAST(CAST(1.1234567899 AS DECIMAL(38, 18)) AS TIMESTAMP_LTZ(9))",
+      "1970-01-01 08:00:01.123456789")
+    testSqlApi(
+      "CAST(CAST(1.12 AS DECIMAL(38, 18)) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+
+    // test precision when cast from TIMESTAMP_LTZ
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:00:01.123456")} AS FLOAT)",
+      "1.123456")
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123456")} AS DOUBLE)",
+      "123.123456")
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123456")} AS DECIMAL(38, 6))",
+      "123.123456")
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123456")} AS DECIMAL(38, 4))",
+      "123.1235")
+
+    // test cast with null value
+    testSqlApi(
+      s"CAST(CAST(null AS BIGINT) AS TIMESTAMP_LTZ(3))",
+      "null")
+    testSqlApi(
+      s"CAST(CAST(null AS TIMESTAMP_LTZ(3)) AS BIGINT)",
+      "null")
+  }
+
+  @Test
+  def tesInvalidCastBetweenNumericAndTimestamp(): Unit = {
+    val castFromTimestampExceptionMsg = "The cast conversion from TIMESTAMP type to NUMERIC type" +
+      " is problematic, it's recommended to use UNIX_TIMESTAMP(CAST(timestampCol AS STRING))" +
+      " as an replacement."

Review comment:
       What do you think about:
   
   The cast conversion from TIMESTAMP type to NUMERIC type is not allowed, 
   it's recommended to use UNIX_TIMESTAMP(CAST(timestamp_col AS STRING)) instead. 

##########
File path: flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/ScalarOperatorGens.scala
##########
@@ -1203,72 +1196,111 @@ object ScalarOperatorGens {
         s"$method($operandTerm.getMillisecond(), $zone)"
       }
 
-    // Timestamp -> Decimal
-    case  (TIMESTAMP_WITHOUT_TIME_ZONE, DECIMAL) =>
-      val dt = targetType.asInstanceOf[DecimalType]
-      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm =>
-          s"""
-             |$DECIMAL_UTIL.castFrom(
-             |  ((double) ($operandTerm.getMillisecond() / 1000.0)),
-             |  ${dt.getPrecision}, ${dt.getScale})
-           """.stripMargin
-      }
-
-    // Tinyint -> Timestamp
-    // Smallint -> Timestamp
-    // Int -> Timestamp
-    // Bigint -> Timestamp
+    // Disable cast conversion between Numeric type and  Timestamp type
     case (TINYINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
          (SMALLINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
          (INTEGER, TIMESTAMP_WITHOUT_TIME_ZONE) |
-         (BIGINT, TIMESTAMP_WITHOUT_TIME_ZONE) =>
+         (BIGINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (FLOAT, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (DOUBLE, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (DECIMAL, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, TINYINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, SMALLINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, INTEGER) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, BIGINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, FLOAT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, DOUBLE) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, DECIMAL) => {
+      if (TIMESTAMP_WITHOUT_TIME_ZONE.equals(targetType.getTypeRoot)) {
+        throw new ValidationException("The cast conversion from NUMERIC type to TIMESTAMP type" +
+          " is problematic, it's recommended to use" +
+          " TO_TIMESTAMP(FROM_UNIXTIME(epochSeconds)) as an replacement.")
+      } else {
+        throw new ValidationException("The cast conversion from TIMESTAMP type to NUMERIC type" +
+          " is problematic, it's recommended to use" +
+          " UNIX_TIMESTAMP(CAST(timestampCol AS STRING)) as an replacement.")
+      }
+    }
+
+    // Tinyint -> TimestampLtz
+    // Smallint -> TimestampLtz
+    // Int -> TimestampLtz
+    // Bigint -> TimestampLtz
+    case (TINYINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (SMALLINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (INTEGER, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (BIGINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
         operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis(((long) $operandTerm) * 1000)"
       }
 
-    // Float -> Timestamp
-    // Double -> Timestamp
-    case (FLOAT, TIMESTAMP_WITHOUT_TIME_ZONE) |
-         (DOUBLE, TIMESTAMP_WITHOUT_TIME_ZONE) =>
+    // Float -> TimestampLtz
+    // Double -> TimestampLtz
+    case (FLOAT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (DOUBLE, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
+      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
+        operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis((long) ($operandTerm * 1000)," +
+          s" (int)($operandTerm * 1000_000_000 % 1000_000))"
+      }
+
+    // DECIMAL -> TimestampLtz
+    case (DECIMAL, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis((long) ($operandTerm * 1000))"
+        operandTerm =>
+          s"$TIMESTAMP_DATA.fromEpochMillis(" +
+            s"(long) ($DECIMAL_UTIL.doubleValue($operandTerm) * 1000), " +
+            s"(int)($DECIMAL_UTIL.doubleValue($operandTerm) * 1000_000_000 % 1000_000) )"

Review comment:
       Please do not convert into double to get fractional and integer part, because double is not accurate and `double * 1000_000_000` may overflow. 

##########
File path: flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/ScalarOperatorGens.scala
##########
@@ -1203,72 +1196,111 @@ object ScalarOperatorGens {
         s"$method($operandTerm.getMillisecond(), $zone)"
       }
 
-    // Timestamp -> Decimal
-    case  (TIMESTAMP_WITHOUT_TIME_ZONE, DECIMAL) =>
-      val dt = targetType.asInstanceOf[DecimalType]
-      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm =>
-          s"""
-             |$DECIMAL_UTIL.castFrom(
-             |  ((double) ($operandTerm.getMillisecond() / 1000.0)),
-             |  ${dt.getPrecision}, ${dt.getScale})
-           """.stripMargin
-      }
-
-    // Tinyint -> Timestamp
-    // Smallint -> Timestamp
-    // Int -> Timestamp
-    // Bigint -> Timestamp
+    // Disable cast conversion between Numeric type and  Timestamp type
     case (TINYINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
          (SMALLINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
          (INTEGER, TIMESTAMP_WITHOUT_TIME_ZONE) |
-         (BIGINT, TIMESTAMP_WITHOUT_TIME_ZONE) =>
+         (BIGINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (FLOAT, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (DOUBLE, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (DECIMAL, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, TINYINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, SMALLINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, INTEGER) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, BIGINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, FLOAT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, DOUBLE) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, DECIMAL) => {
+      if (TIMESTAMP_WITHOUT_TIME_ZONE.equals(targetType.getTypeRoot)) {
+        throw new ValidationException("The cast conversion from NUMERIC type to TIMESTAMP type" +
+          " is problematic, it's recommended to use" +
+          " TO_TIMESTAMP(FROM_UNIXTIME(epochSeconds)) as an replacement.")
+      } else {
+        throw new ValidationException("The cast conversion from TIMESTAMP type to NUMERIC type" +
+          " is problematic, it's recommended to use" +
+          " UNIX_TIMESTAMP(CAST(timestampCol AS STRING)) as an replacement.")
+      }
+    }
+
+    // Tinyint -> TimestampLtz
+    // Smallint -> TimestampLtz
+    // Int -> TimestampLtz
+    // Bigint -> TimestampLtz
+    case (TINYINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (SMALLINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (INTEGER, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (BIGINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
         operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis(((long) $operandTerm) * 1000)"
       }
 
-    // Float -> Timestamp
-    // Double -> Timestamp
-    case (FLOAT, TIMESTAMP_WITHOUT_TIME_ZONE) |
-         (DOUBLE, TIMESTAMP_WITHOUT_TIME_ZONE) =>
+    // Float -> TimestampLtz
+    // Double -> TimestampLtz
+    case (FLOAT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (DOUBLE, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
+      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
+        operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis((long) ($operandTerm * 1000)," +
+          s" (int)($operandTerm * 1000_000_000 % 1000_000))"
+      }
+
+    // DECIMAL -> TimestampLtz
+    case (DECIMAL, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis((long) ($operandTerm * 1000))"
+        operandTerm =>
+          s"$TIMESTAMP_DATA.fromEpochMillis(" +
+            s"(long) ($DECIMAL_UTIL.doubleValue($operandTerm) * 1000), " +
+            s"(int)($DECIMAL_UTIL.doubleValue($operandTerm) * 1000_000_000 % 1000_000) )"
       }
 
-    // Timestamp -> Tinyint
-    case (TIMESTAMP_WITHOUT_TIME_ZONE, TINYINT) =>
+    // TimestampLtz -> Tinyint
+    case (TIMESTAMP_WITH_LOCAL_TIME_ZONE, TINYINT) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
         operandTerm => s"((byte) ($operandTerm.getMillisecond() / 1000))"
       }
 
-    // Timestamp -> Smallint
-    case (TIMESTAMP_WITHOUT_TIME_ZONE, SMALLINT) =>
+    // TimestampLtz -> Smallint
+    case (TIMESTAMP_WITH_LOCAL_TIME_ZONE, SMALLINT) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
         operandTerm => s"((short) ($operandTerm.getMillisecond() / 1000))"
       }
 
-    // Timestamp -> Int
-    case (TIMESTAMP_WITHOUT_TIME_ZONE, INTEGER) =>
+    // TimestampLtz -> Int
+    case (TIMESTAMP_WITH_LOCAL_TIME_ZONE, INTEGER) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
         operandTerm => s"((int) ($operandTerm.getMillisecond() / 1000))"
       }
 
-    // Timestamp -> BigInt
-    case (TIMESTAMP_WITHOUT_TIME_ZONE, BIGINT) =>
+    // TimestampLtz -> BigInt
+    case (TIMESTAMP_WITH_LOCAL_TIME_ZONE, BIGINT) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
         operandTerm => s"((long) ($operandTerm.getMillisecond() / 1000))"
       }
 
-    // Timestamp -> Float
-    case (TIMESTAMP_WITHOUT_TIME_ZONE, FLOAT) =>
+    // TimestampLtz -> Float
+    case (TIMESTAMP_WITH_LOCAL_TIME_ZONE, FLOAT) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm => s"((float) ($operandTerm.getMillisecond() / 1000.0))"
+        operandTerm => s"((float) ($operandTerm.getMillisecond() / 1000.0" +
+          s" + $operandTerm.getNanoOfMillisecond() / 1000_000_000.0))"
       }
 
-    // Timestamp -> Double
-    case (TIMESTAMP_WITHOUT_TIME_ZONE, DOUBLE) =>
+    // TimestampLtz -> Double
+    case (TIMESTAMP_WITH_LOCAL_TIME_ZONE, DOUBLE) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm => s"((double) ($operandTerm.getMillisecond() / 1000.0))"
+        operandTerm => s"((double) ($operandTerm.getMillisecond() / 1000.0" +
+          s" + $operandTerm.getNanoOfMillisecond() / 1000_000_000.0))"
+      }
+
+    // TimestampLtz -> Decimal
+    case  (TIMESTAMP_WITH_LOCAL_TIME_ZONE, DECIMAL) =>
+      val dt = targetType.asInstanceOf[DecimalType]
+      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
+        operandTerm =>
+          s"""
+             |$DECIMAL_UTIL.castFrom(
+             |  ((double) ($operandTerm.getMillisecond() / 1000.0
+             |   + $operandTerm.getNanoOfMillisecond() / 1000_000_000.0)),

Review comment:
       Do not use double to generate decimal, because double is not accurate. For example, new `BigDecimal(12334934.000000011)` will get `12334934.0000000111758708953857421875`.
   
   We can use `DecimalData#fromBigDecimal(new BigDecimal(String.valueOf("123.4456")))` which is accurate. 

##########
File path: flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
##########
@@ -184,82 +184,267 @@ class TemporalTypesTest extends ExpressionTestBase {
 
   @Test
   def testTimePointCasting(): Unit = {
+    // DATE -> TIMESTAMP
     testAllApis(
       'f0.cast(DataTypes.TIMESTAMP(3)),
       "CAST(f0 AS TIMESTAMP(3))",
       "1990-10-14 00:00:00.000")
 
+    // TIME -> TIMESTAMP
     testAllApis(
       'f1.cast(DataTypes.TIMESTAMP(3)),
       "CAST(f1 AS TIMESTAMP(3))",
       "1970-01-01 10:20:45.000")
 
+    // TIMESTAMP -> DATE
     testAllApis(
       'f2.cast(DataTypes.DATE),
       "CAST(f2 AS DATE)",
       "1990-10-14")
 
+    // TIMESTAMP -> TIME
     testAllApis(
       'f2.cast(DataTypes.TIME),
       "CAST(f2 AS TIME)",
       "10:20:45")
+  }
 
-    testAllApis(
-      'f2.cast(DataTypes.TIME),
-      "CAST(f2 AS TIME)",
-      "10:20:45")
+  @Test
+  def testTimestampLtzCastInUTC(): Unit = {
+    config.setLocalTimeZone(ZoneId.of("UTC"))
 
+    //DATE -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(CAST('123' as DECIMAL(5, 2)) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      "CAST(f0 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 00:00:00.000")
 
+    //TIME -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS DECIMAL(5, 2))",
-      "123.00")
+      "CAST(f1 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 10:20:45.000")
 
+    //TIMESTAMP_LTZ -> TIME
     testSqlApi(
-      "CAST(CAST('123' AS FLOAT) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIME)",
+      "01:02:03")
 
+    //TIMESTAMP_LTZ -> DATE
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS FLOAT)",
-      "123.0")
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS DATE)",
+      "2018-03-14")
+  }
+
+  @Test
+  def testTimestampLtzCastInShanghai(): Unit = {
+    config.setLocalTimeZone(ZoneId.of("Asia/Shanghai"))
 
+    // DATE -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(CAST('123' AS DOUBLE) AS TIMESTAMP)",
-      "1970-01-01 00:02:03.000000")
+      "CAST(f0 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 00:00:00.000")
 
+    // TIME -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS DOUBLE)",
-      "123.0")
+      "CAST(f1 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 10:20:45.000")
 
+    // TIMESTAMP -> TIMESTAMP_LTZ
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS TINYINT)",
+      "CAST(f2 AS TIMESTAMP_LTZ(3))",
+      "1990-10-14 10:20:45.123")
+
+    // TINYINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS TINYINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // SMALLINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS SMALLINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // INT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(100 AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // BIGINT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100 AS BIGINT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.000")
+
+    // FLOAT -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.01 AS FLOAT) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.010")
+
+    // DOUBLE -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.123 AS DOUBLE) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.123")
+
+    // DECIMAL -> TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(100.1234 as DECIMAL(38, 18)) AS TIMESTAMP_LTZ(3))",
+      "1970-01-01 08:01:40.123")
+
+    // TIMESTAMP_LTZ -> TIME
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIME)",
+      "01:02:03")
+
+    // TIMESTAMP_LTZ -> DATE
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS DATE)",
+      "2018-03-14")
+
+    // TIMESTAMP_LTZ -> TIMESTAMP
+    testSqlApi(
+      s"CAST(${timestampLtz("2018-03-14 01:02:03")} AS TIMESTAMP(3))",
+      "2018-03-14 01:02:03.000")
+
+    // TIMESTAMP_LTZ -> TINYINT
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS TINYINT)",
       "123")
 
+    // TIMESTAMP_LTZ -> SMALLINT
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS SMALLINT)",
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS SMALLINT)",
       "123")
 
+    // TIMESTAMP_LTZ -> INT
     testSqlApi(
-      "CAST(TIMESTAMP '1970-01-01 00:02:03' AS INT)",
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS INT)",
       "123")
 
+    // TIMESTAMP_LTZ -> BIGINT
     testSqlApi(
-      "CAST(f0 AS TIMESTAMP(3) WITH LOCAL TIME ZONE)",
-      "1990-10-14 00:00:00.000")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS BIGINT)",
+      "123")
 
+    // TIMESTAMP_LTZ -> FLOAT
     testSqlApi(
-      "CAST(f1 AS TIMESTAMP(3) WITH LOCAL TIME ZONE)",
-      "1970-01-01 10:20:45.000")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS FLOAT)",
+      "123.123")
 
+    // TIMESTAMP_LTZ -> DOUBLE
     testSqlApi(
-      s"CAST(${timestampTz("2018-03-14 01:02:03")} AS TIME)",
-      "01:02:03")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS DOUBLE)",
+      "123.123")
 
+    // TIMESTAMP_LTZ -> DECIMAL
     testSqlApi(
-      s"CAST(${timestampTz("2018-03-14 01:02:03")} AS DATE)",
-      "2018-03-14")
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123")} AS DECIMAL(38, 3))",
+      "123.123")
+
+    // test precision when cast to TIMESTAMP_LTZ
+    testSqlApi(
+      "CAST(CAST(1.1234567 AS FLOAT) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.123456")
+    testSqlApi(
+      "CAST(CAST(1.12 AS FLOAT) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+    testSqlApi(
+      "CAST(CAST(1.1234567899 AS DOUBLE) AS TIMESTAMP_LTZ(9))",
+      "1970-01-01 08:00:01.123456789")
+    testSqlApi(
+      "CAST(CAST(1.12 AS DOUBLE) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+    testSqlApi(
+      "CAST(CAST(1.1234567899 AS DECIMAL(38, 18)) AS TIMESTAMP_LTZ(9))",
+      "1970-01-01 08:00:01.123456789")
+    testSqlApi(
+      "CAST(CAST(1.12 AS DECIMAL(38, 18)) AS TIMESTAMP_LTZ(6))",
+      "1970-01-01 08:00:01.120000")
+
+    // test precision when cast from TIMESTAMP_LTZ
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:00:01.123456")} AS FLOAT)",
+      "1.123456")
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123456")} AS DOUBLE)",
+      "123.123456")
+    testSqlApi(
+      s"CAST(${timestampLtz("1970-01-01 08:02:03.123456")} AS DECIMAL(38, 6))",

Review comment:
       please  add timestamp 9 test

##########
File path: flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/ScalarOperatorGens.scala
##########
@@ -1203,72 +1196,111 @@ object ScalarOperatorGens {
         s"$method($operandTerm.getMillisecond(), $zone)"
       }
 
-    // Timestamp -> Decimal
-    case  (TIMESTAMP_WITHOUT_TIME_ZONE, DECIMAL) =>
-      val dt = targetType.asInstanceOf[DecimalType]
-      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm =>
-          s"""
-             |$DECIMAL_UTIL.castFrom(
-             |  ((double) ($operandTerm.getMillisecond() / 1000.0)),
-             |  ${dt.getPrecision}, ${dt.getScale})
-           """.stripMargin
-      }
-
-    // Tinyint -> Timestamp
-    // Smallint -> Timestamp
-    // Int -> Timestamp
-    // Bigint -> Timestamp
+    // Disable cast conversion between Numeric type and  Timestamp type
     case (TINYINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
          (SMALLINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
          (INTEGER, TIMESTAMP_WITHOUT_TIME_ZONE) |
-         (BIGINT, TIMESTAMP_WITHOUT_TIME_ZONE) =>
+         (BIGINT, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (FLOAT, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (DOUBLE, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (DECIMAL, TIMESTAMP_WITHOUT_TIME_ZONE) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, TINYINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, SMALLINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, INTEGER) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, BIGINT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, FLOAT) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, DOUBLE) |
+         (TIMESTAMP_WITHOUT_TIME_ZONE, DECIMAL) => {
+      if (TIMESTAMP_WITHOUT_TIME_ZONE.equals(targetType.getTypeRoot)) {
+        throw new ValidationException("The cast conversion from NUMERIC type to TIMESTAMP type" +
+          " is problematic, it's recommended to use" +
+          " TO_TIMESTAMP(FROM_UNIXTIME(epochSeconds)) as an replacement.")
+      } else {
+        throw new ValidationException("The cast conversion from TIMESTAMP type to NUMERIC type" +
+          " is problematic, it's recommended to use" +
+          " UNIX_TIMESTAMP(CAST(timestampCol AS STRING)) as an replacement.")
+      }
+    }
+
+    // Tinyint -> TimestampLtz
+    // Smallint -> TimestampLtz
+    // Int -> TimestampLtz
+    // Bigint -> TimestampLtz
+    case (TINYINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (SMALLINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (INTEGER, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (BIGINT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
         operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis(((long) $operandTerm) * 1000)"
       }
 
-    // Float -> Timestamp
-    // Double -> Timestamp
-    case (FLOAT, TIMESTAMP_WITHOUT_TIME_ZONE) |
-         (DOUBLE, TIMESTAMP_WITHOUT_TIME_ZONE) =>
+    // Float -> TimestampLtz
+    // Double -> TimestampLtz
+    case (FLOAT, TIMESTAMP_WITH_LOCAL_TIME_ZONE) |
+         (DOUBLE, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
+      generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
+        operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis((long) ($operandTerm * 1000)," +
+          s" (int)($operandTerm * 1000_000_000 % 1000_000))"
+      }
+
+    // DECIMAL -> TimestampLtz
+    case (DECIMAL, TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
       generateUnaryOperatorIfNotNull(ctx, targetType, operand) {
-        operandTerm => s"$TIMESTAMP_DATA.fromEpochMillis((long) ($operandTerm * 1000))"
+        operandTerm =>
+          s"$TIMESTAMP_DATA.fromEpochMillis(" +
+            s"(long) ($DECIMAL_UTIL.doubleValue($operandTerm) * 1000), " +
+            s"(int)($DECIMAL_UTIL.doubleValue($operandTerm) * 1000_000_000 % 1000_000) )"

Review comment:
       Could you add test `CAST(CAST(1616490480.123 AS DECIMAL(38, 18)) AS TIMESTAMP_LTZ(9))`, the output should be `2021-03-23 17:08:00.123000000` , however get `2021-03-23 17:08:00.123999808`.
   
   You can see this about how to get fractional part of a BigDecimal: https://stackoverflow.com/questions/10038749/java-extract-just-the-fractional-part-of-a-bigdecimal




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org