YQL-18303: Adjust implicit cast rules between Datetime types

Follows up 16a38d1b1de0cc97c5cbf97176331ea6691e23be
commit_hash:4d702692f7441dc95f2874f80f6618043b73c816

14 files changed, 577 insertions, 59 deletions

diff --git a/yql/essentials/core/yql_expr_type_annotation.cpp b/yql/essentials/core/yql_expr_type_annotation.cpp
index dcca732eadb..c968b6fea1c 100644
--- a/yql/essentials/core/yql_expr_type_annotation.cpp
+++ b/yql/essentials/core/yql_expr_type_annotation.cpp
@@ -88,6 +88,75 @@ TExprNode::TPtr RebuildVariant(const TExprNode::TPtr& node,
     return ret;
 }
 
+bool IsDatetimeToDatetimeCastAllowed(EDataSlot from, EDataSlot to) {
+    if (from == EDataSlot::Date && (to == EDataSlot::TzDate ||
+                                    to == EDataSlot::Date32 ||
+                                    to == EDataSlot::TzDate32 ||
+                                    to == EDataSlot::Datetime ||
+                                    to == EDataSlot::TzDatetime ||
+                                    to == EDataSlot::Datetime64 ||
+                                    to == EDataSlot::TzDatetime64 ||
+                                    to == EDataSlot::Timestamp ||
+                                    to == EDataSlot::TzTimestamp ||
+                                    to == EDataSlot::Timestamp64 ||
+                                    to == EDataSlot::TzTimestamp64)
+    ) {
+        return true;
+    } else if (from == EDataSlot::TzDate && (to == EDataSlot::TzDate32 ||
+                                             to == EDataSlot::TzDatetime ||
+                                             to == EDataSlot::TzDatetime64 ||
+                                             to == EDataSlot::TzTimestamp ||
+                                             to == EDataSlot::TzTimestamp64)
+    ) {
+        return true;
+    } else if (from == EDataSlot::Date32 && (to == EDataSlot::TzDate32 ||
+                                             to == EDataSlot::Datetime64 ||
+                                             to == EDataSlot::TzDatetime64 ||
+                                             to == EDataSlot::Timestamp64 ||
+                                             to == EDataSlot::TzTimestamp64)
+    ) {
+        return true;
+    } else if (from == EDataSlot::TzDate32 && (to == EDataSlot::TzDatetime64 ||
+                                               to == EDataSlot::TzTimestamp64)
+    ) {
+        return true;
+    } else if (from == EDataSlot::Datetime && (to == EDataSlot::TzDatetime ||
+                                               to == EDataSlot::Datetime64 ||
+                                               to == EDataSlot::TzDatetime64 ||
+                                               to == EDataSlot::Timestamp ||
+                                               to == EDataSlot::TzTimestamp ||
+                                               to == EDataSlot::Timestamp64 ||
+                                               to == EDataSlot::TzTimestamp64)
+    ) {
+        return true;
+    } else if (from == EDataSlot::TzDatetime && (to == EDataSlot::TzDatetime64 ||
+                                                 to == EDataSlot::TzTimestamp ||
+                                                 to == EDataSlot::TzTimestamp64)
+    ) {
+        return true;
+    } else if (from == EDataSlot::Datetime64 && (to == EDataSlot::TzDatetime64 ||
+                                                 to == EDataSlot::Timestamp64 ||
+                                                 to == EDataSlot::TzTimestamp64)
+    ) {
+        return true;
+    } else if (from == EDataSlot::TzDatetime64 && to == EDataSlot::TzTimestamp64) {
+        return true;
+    } else if (from == EDataSlot::Timestamp && (to == EDataSlot::TzTimestamp ||
+                                                to == EDataSlot::Timestamp64 ||
+                                                to == EDataSlot::TzTimestamp64)
+    ) {
+        return true;
+    } else if (from == EDataSlot::TzTimestamp && to == EDataSlot::TzTimestamp64) {
+        return true;
+    } else if (from == EDataSlot::Timestamp64 && to == EDataSlot::TzTimestamp64) {
+        return true;
+    } else if (from == EDataSlot::Interval && (to == EDataSlot::Interval64)) {
+        return true;
+    } else {
+        return false;
+    }
+}
+
 IGraphTransformer::TStatus TryConvertToImpl(TExprContext& ctx, TExprNode::TPtr& node,
     const TTypeAnnotationNode& sourceType, const TTypeAnnotationNode& expectedType, TConvertFlags flags, bool raiseIssues = false) {
 
@@ -332,56 +401,12 @@ IGraphTransformer::TStatus TryConvertToImpl(TExprContext& ctx, TExprNode::TPtr&
         if (IsDataTypeNumeric(from) && IsDataTypeNumeric(to)) {
             allow = GetNumericDataTypeLevel(to) >= GetNumericDataTypeLevel(from);
             isSafe = false;
-        } else if (from == EDataSlot::Date && (
-                    to == EDataSlot::Date32 ||
-                    to == EDataSlot::TzDate ||
-                    to == EDataSlot::TzDate32 ||
-                    to == EDataSlot::Datetime ||
-                    to == EDataSlot::Timestamp ||
-                    to == EDataSlot::TzDatetime ||
-                    to == EDataSlot::TzTimestamp ||
-                    to == EDataSlot::Datetime64 ||
-                    to == EDataSlot::Timestamp64 ||
-                    to == EDataSlot::TzDatetime64 ||
-                    to == EDataSlot::TzTimestamp64))
-        {
-            allow = true;
-            useCast = true;
-        } else if (from == EDataSlot::Datetime && (
-                    to == EDataSlot::Datetime64 ||
-                    to == EDataSlot::TzDatetime ||
-                    to == EDataSlot::TzDatetime64 ||
-                    to == EDataSlot::Timestamp ||
-                    to == EDataSlot::TzTimestamp ||
-                    to == EDataSlot::Timestamp64 ||
-                    to == EDataSlot::TzTimestamp64))
+        } else if (IsDataTypeDateOrTzDateOrInterval(from) &&
+                   IsDataTypeDateOrTzDateOrInterval(to) &&
+                   IsDatetimeToDatetimeCastAllowed(from, to))
         {
             allow = true;
             useCast = true;
-        } else if (from == EDataSlot::TzDate && (to == EDataSlot::TzDatetime || to == EDataSlot::TzTimestamp)) {
-            allow = true;
-            useCast = true;
-        } else if (from == EDataSlot::TzDatetime && to == EDataSlot::TzTimestamp) {
-            allow = true;
-            useCast = true;
-        } else if (from == EDataSlot::Timestamp && (to == EDataSlot::TzTimestamp || to == EDataSlot::Timestamp64)) {
-            allow = true;
-            useCast = true;
-        } else if (from == EDataSlot::Date32 && (to == EDataSlot::Datetime64 || to == EDataSlot::Timestamp64)) {
-            allow = true;
-            useCast = true;
-        } else if (from == EDataSlot::TzDate32 && (to == EDataSlot::TzDatetime64 || to == EDataSlot::TzTimestamp64)) {
-            allow = true;
-            useCast = true;
-        } else if (from == EDataSlot::Datetime64 && (to == EDataSlot::Timestamp64)) {
-            allow = true;
-            useCast = true;
-        } else if (from == EDataSlot::TzDatetime64 && to == EDataSlot::TzTimestamp64) {
-            allow = true;
-            useCast = true;
-        } else if (from == EDataSlot::Interval && (to == EDataSlot::Interval64)) {
-            allow = true;
-            useCast = true;
         } else if (from == EDataSlot::Json && to == EDataSlot::Utf8) {
             allow = true;
             useCast = true;
diff --git a/yql/essentials/public/udf/udf_data_type.cpp b/yql/essentials/public/udf/udf_data_type.cpp
index 25eae58a0af..67f31354ce3 100644
--- a/yql/essentials/public/udf/udf_data_type.cpp
+++ b/yql/essentials/public/udf/udf_data_type.cpp
@@ -49,29 +49,29 @@ static const std::array<std::array<std::optional<TCastResultOptions>, DataSlotCo
 
     {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, NO,  OK,  NO, NO, NO,  NO,  NO, NO, NO,  NO, NO, NO,  NO, NO, NO,  NO,  NO, NO, NO }}, // Uuid
 
-    {{ NO,  MF, MF, MF, OK, OK, OK, OK, OK,  OK, OK,  OK, OK,  NO, NO,  NO,  OK, OK, OK,  NO,  OK, OK, OK,  NO, NO, NO,  OK, OK, OK,  NO,  NO, NO, NO }}, // Date
-    {{ NO,  MF, MF, MF, MF, MF, OK, OK, OK,  OK, LD,  OK, OK,  NO, NO,  NO,  LD, OK, OK,  NO,  LD, OK, OK,  NO, NO, NO,  LD, OK, OK,  NO,  NO, NO, NO }}, // Datetime
-    {{ NO,  MF, MF, MF, MF, MF, MF, OK, OK,  LD, LD,  OK, OK,  NO, NO,  NO,  LD, LD, OK,  NO,  LD, LD, OK,  NO, NO, NO,  LD, LD, OK,  NO,  NO, NO, NO }}, // Timestamp
+    {{ NO,  MF, MF, MF, OK, OK, OK, OK, OK,  OK, OK,  OK, OK,  NO, NO,  NO,  OK, OK, OK,  NO,  OK, OK, OK,  NO, NO, NO,  OK, OK, OK,  NO,  OK, OK, OK }}, // Date
+    {{ NO,  MF, MF, MF, MF, MF, OK, OK, OK,  OK, LD,  OK, OK,  NO, NO,  NO,  LD, OK, OK,  NO,  LD, OK, OK,  NO, NO, NO,  LD, OK, OK,  NO,  LD, OK, OK }}, // Datetime
+    {{ NO,  MF, MF, MF, MF, MF, MF, OK, OK,  LD, LD,  OK, OK,  NO, NO,  NO,  LD, LD, OK,  NO,  LD, LD, OK,  NO, NO, NO,  LD, LD, OK,  NO,  LD, LD, OK }}, // Timestamp
 
     {{ NO,  MF, MF, MF, MF, MF, MF, OK, MF,  LD, LD,  OK, OK,  NO, NO,  NO,  NO, NO, NO,  OK,  NO, NO, NO,  NO, NO, NO,  NO, NO, NO,  OK,  NO, NO, NO }}, // Interval
 
-    {{ NO,  MF, MF, MF, OK, OK, OK, OK, OK,  OK, OK,  OK, OK,  NO, NO,  NO,  OK, OK, OK,  NO,  OK, OK, OK,  NO, NO, NO,  NO, NO, NO,  NO,  OK, OK, OK }}, // TzDate
-    {{ NO,  MF, MF, MF, MF, MF, OK, OK, OK,  OK, LD,  OK, OK,  NO, NO,  NO,  LD, OK, OK,  NO,  LD, OK, OK,  NO, NO, NO,  NO, NO, NO,  NO,  LD, OK, OK }}, // TzDatetime
-    {{ NO,  MF, MF, MF, MF, MF, MF, OK, OK,  LD, LD,  OK, OK,  NO, NO,  NO,  LD, LD, OK,  NO,  LD, LD, OK,  NO, NO, NO,  NO, NO, NO,  NO,  LD, LD, OK }}, // TzTimestamp
+    {{ NO,  MF, MF, MF, OK, OK, OK, OK, OK,  OK, OK,  OK, OK,  NO, NO,  NO,  LD, LD, LD,  NO,  OK, OK, OK,  NO, NO, NO,  LD, LD, LD,  NO,  OK, OK, OK }}, // TzDate
+    {{ NO,  MF, MF, MF, MF, MF, OK, OK, OK,  OK, LD,  OK, OK,  NO, NO,  NO,  LD, LD, LD,  NO,  LD, OK, OK,  NO, NO, NO,  LD, LD, LD,  NO,  LD, OK, OK }}, // TzDatetime
+    {{ NO,  MF, MF, MF, MF, MF, MF, OK, OK,  LD, LD,  OK, OK,  NO, NO,  NO,  LD, LD, LD,  NO,  LD, LD, OK,  NO, NO, NO,  LD, LD, LD,  NO,  LD, LD, OK }}, // TzTimestamp
 
     {{ NO,  UN, UN, UN, UN, UN, UN, UN, UN,  LD, LD,  OK, OK,  NO, NO,  NO,  NO, NO, NO,  NO,  NO, NO, NO,  UN, NO, NO,  NO, NO, NO,  NO,  NO, NO, NO }}, // Decimal
     {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, NO,  NO,  NO, NO, NO,  NO,  NO, NO, NO,  NO, OK, NO,  NO, NO, NO,  NO,  NO, NO, NO }}, // DyNumber
     {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, OK,  NO,  NO, NO, NO,  NO,  NO, NO, NO,  NO, NO, OK,  NO, NO, NO,  NO,  NO, NO, NO }}, // JsonDocument
 
-    {{ NO,  MF, MF, MF, MF, OK, MF, OK, MF,  LD, OK,  OK, OK,  NO, NO,  NO,  MF, MF, MF,  NO,  NO, NO, NO,  NO, NO, NO,  OK, OK, OK,  NO,  NO, NO, NO }}, // Date32
-    {{ NO,  MF, MF, MF, MF, MF, MF, OK, MF,  LD, LD,  OK, OK,  NO, NO,  NO,  FL, MF, MF,  NO,  NO, NO, NO,  NO, NO, NO,  LD, OK, OK,  NO,  NO, NO, NO }}, // Datetime64
-    {{ NO,  MF, MF, MF, MF, MF, MF, OK, MF,  LD, LD,  OK, OK,  NO, NO,  NO,  FL, FL, MF,  NO,  NO, NO, NO,  NO, NO, NO,  LD, LD, OK,  NO,  NO, NO, NO }}, // Timestamp64
+    {{ NO,  MF, MF, MF, MF, OK, MF, OK, MF,  LD, OK,  OK, OK,  NO, NO,  NO,  MF, MF, MF,  NO,  MF, MF, MF,  NO, NO, NO,  OK, OK, OK,  NO,  OK, OK, OK }}, // Date32
+    {{ NO,  MF, MF, MF, MF, MF, MF, OK, MF,  LD, LD,  OK, OK,  NO, NO,  NO,  FL, MF, MF,  NO,  FL, MF, MF,  NO, NO, NO,  LD, OK, OK,  NO,  LD, OK, OK }}, // Datetime64
+    {{ NO,  MF, MF, MF, MF, MF, MF, OK, MF,  LD, LD,  OK, OK,  NO, NO,  NO,  FL, FL, MF,  NO,  FL, FL, MF,  NO, NO, NO,  LD, LD, OK,  NO,  LD, LD, OK }}, // Timestamp64
 
     {{ NO,  MF, MF, MF, MF, MF, MF, OK, MF,  LD, LD,  OK, OK,  NO, NO,  NO,  NO, NO, NO,  MF,  NO, NO, NO,  NO, NO, NO,  NO, NO, NO,  OK,  NO, NO, NO }}, // Interval64
 
-    {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, NO,  NO,  MF, MF, MF,  NO,  MF, MF, MF,  NO, NO, NO,  OK, OK, OK,  NO,  OK, OK, OK }}, // TzDate32
-    {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, NO,  NO,  MF, MF, MF,  NO,  MF, MF, MF,  NO, NO, NO,  LD, OK, OK,  NO,  LD, OK, OK }}, // TzDatetime64
-    {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, NO,  NO,  MF, MF, MF,  NO,  MF, MF, MF,  NO, NO, NO,  LD, LD, OK,  NO,  LD, LD, OK }}, // TzTimestamp64
+    {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, NO,  NO,  FL, FL, FL,  NO,  MF, MF, MF,  NO, NO, NO,  LD, LD, LD,  NO,  OK, OK, OK }}, // TzDate32
+    {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, NO,  NO,  FL, FL, FL,  NO,  FL, MF, MF,  NO, NO, NO,  LD, LD, LD,  NO,  LD, OK, OK }}, // TzDatetime64
+    {{ NO,  NO, NO, NO, NO, NO, NO, NO, NO,  NO, NO,  OK, OK,  NO, NO,  NO,  FL, FL, FL,  NO,  FL, FL, MF,  NO, NO, NO,  LD, LD, LD,  NO,  LD, LD, OK }}, // TzTimestamp64
 }};
 
 }
diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
index 164e13258b3..baec7526562 100644
--- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
@@ -181,6 +181,20 @@
             "uri": "https://{canondata_backend}/1920236/fd3dfd41a8e63d7fa9cb2e8c3d8e6d8d5f51f2cb/resource.tar.gz#test.test_bigdate-common_type-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[bigdate-date_implicit_casts-default.txt-Debug]": [
+        {
+            "checksum": "789f10569635819d64aa27c444319745",
+            "size": 2504,
+            "uri": "https://{canondata_backend}/1784117/f78d2455106ce42ce9bf47234459b8ee215bd2a8/resource.tar.gz#test.test_bigdate-date_implicit_casts-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[bigdate-date_implicit_casts-default.txt-Results]": [
+        {
+            "checksum": "ccd1abdc388d2fb9efc4ad32e92e494b",
+            "size": 8256,
+            "uri": "https://{canondata_backend}/1881367/3d3cb0f3b636c2874422b42dbe0654ce256c590a/resource.tar.gz#test.test_bigdate-date_implicit_casts-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[bigdate-int_literals-default.txt-Debug]": [
         {
             "checksum": "7e213ec4e7df7188baa32a632462cf2c",
diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
index 9dc8932e187..0771058e6e6 100644
--- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
@@ -195,6 +195,20 @@
             "uri": "https://{canondata_backend}/1881367/948e93b5cacfa4a732bc9d920d6546ab3f25aba9/resource.tar.gz#test.test_bigdate-const_interval64-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[bigdate-date_tz_implicit_casts-default.txt-Debug]": [
+        {
+            "checksum": "af8f65aa083f5cde0a4bb4f078e10bc9",
+            "size": 1306,
+            "uri": "https://{canondata_backend}/1936947/d9fc1c1289e163d90287579127b30a378edfb34d/resource.tar.gz#test.test_bigdate-date_tz_implicit_casts-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[bigdate-date_tz_implicit_casts-default.txt-Results]": [
+        {
+            "checksum": "619f9a0210499a1f34d5209cd7d1d77a",
+            "size": 3137,
+            "uri": "https://{canondata_backend}/1942278/584acca05dd7d7e8eec1b57cf050b7123a65c1ca/resource.tar.gz#test.test_bigdate-date_tz_implicit_casts-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[bigdate-output_interval64-default.txt-Debug]": [
         {
             "checksum": "298089b9382a0a77c9e268c9f0e95b3a",
@@ -418,6 +432,20 @@
             "uri": "https://{canondata_backend}/1946324/15d61515f724d2d343da00780ff2c6ac72278fb3/resource.tar.gz#test.test_datetime-date_diff_sub-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[datetime-date_tz_implicit_casts-default.txt-Debug]": [
+        {
+            "checksum": "a12a2924c35fe2b445a4dcd5754c3a3e",
+            "size": 2810,
+            "uri": "https://{canondata_backend}/1936947/d9fc1c1289e163d90287579127b30a378edfb34d/resource.tar.gz#test.test_datetime-date_tz_implicit_casts-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[datetime-date_tz_implicit_casts-default.txt-Results]": [
+        {
+            "checksum": "69877e6b31258394712a217a32aa3104",
+            "size": 9381,
+            "uri": "https://{canondata_backend}/1942278/584acca05dd7d7e8eec1b57cf050b7123a65c1ca/resource.tar.gz#test.test_datetime-date_tz_implicit_casts-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-between-default.txt-Debug]": [
         {
             "checksum": "c82b19a5180d2a2ed8230c2cf45ab2cc",
diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/result.json b/yql/essentials/tests/sql/minirun/part7/canondata/result.json
index 1ed6dbeb712..af489ad7c1c 100644
--- a/yql/essentials/tests/sql/minirun/part7/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part7/canondata/result.json
@@ -300,6 +300,20 @@
             "uri": "https://{canondata_backend}/1942525/7665133ac15a3f92aef975c99a4dfe3023179a69/resource.tar.gz#test.test_compute_range-huge_in-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[datetime-date_implicit_casts-default.txt-Debug]": [
+        {
+            "checksum": "2cdfd9b9555629a03ccbe417b073bd0d",
+            "size": 5074,
+            "uri": "https://{canondata_backend}/1784117/d599636e2d967126fd75f502ca3a5d02b64bf51b/resource.tar.gz#test.test_datetime-date_implicit_casts-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[datetime-date_implicit_casts-default.txt-Results]": [
+        {
+            "checksum": "43ef32eed9cca4f5bbae8c64716bd496",
+            "size": 19531,
+            "uri": "https://{canondata_backend}/1942278/99edd2df721ae9dc3716d646ed769087f6a44079/resource.tar.gz#test.test_datetime-date_implicit_casts-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[datetime-date_out-default.txt-Debug]": [
         {
             "checksum": "6e830b11f1faaaa8fba8fca98ab9337c",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index d4a9e5b804f..6580d9eca77 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -1098,6 +1098,13 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_bigdate-const_timestamp64_/sql.yql"
         }
     ],
+    "test_sql2yql.test[bigdate-date_implicit_casts]": [
+        {
+            "checksum": "a83a400d2d37cac5440715cb909f67e6",
+            "size": 3333,
+            "uri": "https://{canondata_backend}/1871102/c2e9767e39c8f9e50c0016d8377ff12660471aaf/resource.tar.gz#test_sql2yql.test_bigdate-date_implicit_casts_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[bigdate-date_tz_bounds]": [
         {
             "checksum": "7e6319138c451a9af0fab81161b22c28",
@@ -1112,6 +1119,13 @@
             "uri": "https://{canondata_backend}/1880306/7ab74c806ebfa825c90d696b230a4fba9939dddd/resource.tar.gz#test_sql2yql.test_bigdate-date_tz_bounds_scale_/sql.yql"
         }
     ],
+    "test_sql2yql.test[bigdate-date_tz_implicit_casts]": [
+        {
+            "checksum": "38f28381b9bdd5cc1e856fafcba7c1ea",
+            "size": 3243,
+            "uri": "https://{canondata_backend}/1871102/c2e9767e39c8f9e50c0016d8377ff12660471aaf/resource.tar.gz#test_sql2yql.test_bigdate-date_tz_implicit_casts_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[bigdate-date_tz_impossible_cast]": [
         {
             "checksum": "96261bbea3e8e2d39aa2f6a88862e07a",
@@ -2127,6 +2141,13 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_datetime-date_diff_sub_/sql.yql"
         }
     ],
+    "test_sql2yql.test[datetime-date_implicit_casts]": [
+        {
+            "checksum": "de20b1127905d96a5d384d1547419733",
+            "size": 3589,
+            "uri": "https://{canondata_backend}/1871102/c2e9767e39c8f9e50c0016d8377ff12660471aaf/resource.tar.gz#test_sql2yql.test_datetime-date_implicit_casts_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[datetime-date_in]": [
         {
             "checksum": "f042635815e0ece3c9aa69a5cc35c44f",
@@ -2232,6 +2253,13 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_datetime-date_tz_expand_gmt_/sql.yql"
         }
     ],
+    "test_sql2yql.test[datetime-date_tz_implicit_casts]": [
+        {
+            "checksum": "c56d5f5cf37c97f0ef2a7ca3000a84bc",
+            "size": 3373,
+            "uri": "https://{canondata_backend}/1871102/c2e9767e39c8f9e50c0016d8377ff12660471aaf/resource.tar.gz#test_sql2yql.test_datetime-date_tz_implicit_casts_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[datetime-date_tz_impossible_cast]": [
         {
             "checksum": "5199dc2297404c9343dc321e299c0fb4",
@@ -8162,6 +8190,11 @@
             "uri": "file://test_sql_format.test_bigdate-const_timestamp64_/formatted.sql"
         }
     ],
+    "test_sql_format.test[bigdate-date_implicit_casts]": [
+        {
+            "uri": "file://test_sql_format.test_bigdate-date_implicit_casts_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[bigdate-date_tz_bounds]": [
         {
             "uri": "file://test_sql_format.test_bigdate-date_tz_bounds_/formatted.sql"
@@ -8172,6 +8205,11 @@
             "uri": "file://test_sql_format.test_bigdate-date_tz_bounds_scale_/formatted.sql"
         }
     ],
+    "test_sql_format.test[bigdate-date_tz_implicit_casts]": [
+        {
+            "uri": "file://test_sql_format.test_bigdate-date_tz_implicit_casts_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[bigdate-date_tz_impossible_cast]": [
         {
             "uri": "file://test_sql_format.test_bigdate-date_tz_impossible_cast_/formatted.sql"
@@ -8897,6 +8935,11 @@
             "uri": "file://test_sql_format.test_datetime-date_diff_sub_/formatted.sql"
         }
     ],
+    "test_sql_format.test[datetime-date_implicit_casts]": [
+        {
+            "uri": "file://test_sql_format.test_datetime-date_implicit_casts_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[datetime-date_in]": [
         {
             "uri": "file://test_sql_format.test_datetime-date_in_/formatted.sql"
@@ -8972,6 +9015,11 @@
             "uri": "file://test_sql_format.test_datetime-date_tz_expand_gmt_/formatted.sql"
         }
     ],
+    "test_sql_format.test[datetime-date_tz_implicit_casts]": [
+        {
+            "uri": "file://test_sql_format.test_datetime-date_tz_implicit_casts_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[datetime-date_tz_impossible_cast]": [
         {
             "uri": "file://test_sql_format.test_datetime-date_tz_impossible_cast_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_bigdate-date_implicit_casts_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_bigdate-date_implicit_casts_/formatted.sql
new file mode 100644
index 00000000000..e9cd73c0ff7
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_bigdate-date_implicit_casts_/formatted.sql
@@ -0,0 +1,50 @@
+PRAGMA warning('disable', '4528');
+
+$fromTypes = {
+    'Date32': '2025-03-19',
+    'Datetime64': '2025-03-19T01:02:03Z',
+    'Timestamp64': '2025-03-19T01:02:03.456789Z',
+};
+
+$toTypes = [
+    'Date', 'Datetime', 'Timestamp',
+    'TzDate', 'TzDatetime', 'TzTimestamp',
+    'Date32', 'Datetime64', 'Timestamp64',
+    'TzDate32', 'TzDatetime64', 'TzTimestamp64',
+];
+
+$allowed = {
+    'Date32': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */'DateTime64', 'Timestamp64',
+        'TzDate32', 'TzDatetime64', 'TzTimestamp64',
+    },
+    'Datetime64': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */'Timestamp64',
+
+        /* - */ 'TzDatetime64', 'TzTimestamp64',
+    },
+    'Timestamp64': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */'TzTimestamp64',
+    },
+};
+
+EVALUATE FOR $from IN DictItems($fromTypes) DO BEGIN
+    EVALUATE FOR $to IN $toTypes DO BEGIN
+        EVALUATE IF DictContains($allowed[$from.0], $to) DO BEGIN
+            $callable = Callable(CallableType(0, String, DataType($to)), ($x) -> (CAST($x AS String)));
+            $srcType = DataType($from.0);
+
+            SELECT
+                $from.0 || ' => ' || $to,
+                $callable(Unwrap(CAST($from.1 AS $srcType)))
+            ;
+        END DO;
+    END DO;
+END DO;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_bigdate-date_tz_implicit_casts_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_bigdate-date_tz_implicit_casts_/formatted.sql
new file mode 100644
index 00000000000..d21f4bbc7dc
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_bigdate-date_tz_implicit_casts_/formatted.sql
@@ -0,0 +1,49 @@
+PRAGMA warning('disable', '4528');
+
+$fromTypes = {
+    'TzDate32': '2025-03-19,Europe/Moscow',
+    'TzDatetime64': '2025-03-19T01:02:03,Europe/Moscow',
+    'TzTimestamp64': '2025-03-19T01:02:03.456789,Europe/Moscow',
+};
+
+$toTypes = [
+    'Date', 'Datetime', 'Timestamp',
+    'TzDate', 'TzDatetime', 'TzTimestamp',
+    'Date32', 'Datetime64', 'Timestamp64',
+    'TzDate32', 'TzDatetime64', 'TzTimestamp64',
+];
+
+$allowed = {
+    'TzDate32': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */'TzDatetime64', 'TzTimestamp64',
+    },
+    'TzDatetime64': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */'TzTimestamp64',
+    },
+    'TzTimestamp64': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+    },
+};
+
+EVALUATE FOR $from IN DictItems($fromTypes) DO BEGIN
+    EVALUATE FOR $to IN $toTypes DO BEGIN
+        EVALUATE IF DictContains($allowed[$from.0], $to) DO BEGIN
+            $callable = Callable(CallableType(0, String, DataType($to)), ($x) -> (CAST($x AS String)));
+            $srcType = DataType($from.0);
+
+            SELECT
+                $from.0 || ' => ' || $to,
+                $callable(Unwrap(CAST($from.1 AS $srcType)))
+            ;
+        END DO;
+    END DO;
+END DO;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_datetime-date_implicit_casts_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_datetime-date_implicit_casts_/formatted.sql
new file mode 100644
index 00000000000..685eb7ad13e
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_datetime-date_implicit_casts_/formatted.sql
@@ -0,0 +1,54 @@
+PRAGMA warning('disable', '4528');
+
+$fromTypes = {
+    'Date': '2025-03-19',
+    'Datetime': '2025-03-19T01:02:03Z',
+    'Timestamp': '2025-03-19T01:02:03.456789Z',
+};
+
+$toTypes = [
+    'Date', 'Datetime', 'Timestamp',
+    'TzDate', 'TzDatetime', 'TzTimestamp',
+    'Date32', 'Datetime64', 'Timestamp64',
+    'TzDate32', 'TzDatetime64', 'TzTimestamp64',
+];
+
+$allowed = {
+    'Date': {
+        /* - */'Datetime', 'Timestamp',
+        'TzDate', 'TzDatetime', 'TzTimestamp',
+        'Date32', 'DateTime64', 'Timestamp64',
+        'TzDate32', 'TzDatetime64', 'TzTimestamp64',
+    },
+    'Datetime': {
+        /* - */ /* - */'Timestamp',
+
+        /* - */ 'TzDatetime', 'TzTimestamp',
+
+        /* - */ 'DateTime64', 'Timestamp64',
+
+        /* - */ 'TzDatetime64', 'TzTimestamp64',
+    },
+    'Timestamp': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */'TzTimestamp',
+
+        /* - */ /* - */ 'Timestamp64',
+
+        /* - */ /* - */ 'TzTimestamp64',
+    },
+};
+
+EVALUATE FOR $from IN DictItems($fromTypes) DO BEGIN
+    EVALUATE FOR $to IN $toTypes DO BEGIN
+        EVALUATE IF DictContains($allowed[$from.0], $to) DO BEGIN
+            $callable = Callable(CallableType(0, String, DataType($to)), ($x) -> (CAST($x AS String)));
+            $srcType = DataType($from.0);
+
+            SELECT
+                $from.0 || ' => ' || $to,
+                $callable(Unwrap(CAST($from.1 AS $srcType)))
+            ;
+        END DO;
+    END DO;
+END DO;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_datetime-date_tz_implicit_casts_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_datetime-date_tz_implicit_casts_/formatted.sql
new file mode 100644
index 00000000000..ea6079a723f
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_datetime-date_tz_implicit_casts_/formatted.sql
@@ -0,0 +1,51 @@
+PRAGMA warning('disable', '4528');
+
+$fromTypes = {
+    'TzDate': '2025-03-19,Europe/Moscow',
+    'TzDatetime': '2025-03-19T01:02:03,Europe/Moscow',
+    'TzTimestamp': '2025-03-19T01:02:03.456789,Europe/Moscow',
+};
+
+$toTypes = [
+    'Date', 'Datetime', 'Timestamp',
+    'TzDate', 'TzDatetime', 'TzTimestamp',
+    'Date32', 'Datetime64', 'Timestamp64',
+    'TzDate32', 'TzDatetime64', 'TzTimestamp64',
+];
+
+$allowed = {
+    'TzDate': {
+        /* - */ /* - */ /* - */
+        /* - */'TzDatetime', 'TzTimestamp',
+
+        /* - */ /* - */ /* - */
+        'TzDate32', 'TzDatetime64', 'TzTimestamp64',
+    },
+    'TzDatetime': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */'TzTimestamp',
+
+        /* - */ /* - */ /* - */
+        /* - */ 'TzDatetime64', 'TzTimestamp64',
+    },
+    'TzTimestamp': {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */'TzTimestamp64',
+    },
+};
+
+EVALUATE FOR $from IN DictItems($fromTypes) DO BEGIN
+    EVALUATE FOR $to IN $toTypes DO BEGIN
+        EVALUATE IF DictContains($allowed[$from.0], $to) DO BEGIN
+            $callable = Callable(CallableType(0, String, DataType($to)), ($x) -> (CAST($x AS String)));
+            $srcType = DataType($from.0);
+
+            SELECT
+                $from.0 || ' => ' || $to,
+                $callable(Unwrap(CAST($from.1 AS $srcType)))
+            ;
+        END DO;
+    END DO;
+END DO;
diff --git a/yql/essentials/tests/sql/suites/bigdate/date_implicit_casts.sql b/yql/essentials/tests/sql/suites/bigdate/date_implicit_casts.sql
new file mode 100644
index 00000000000..735893aeddb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/bigdate/date_implicit_casts.sql
@@ -0,0 +1,47 @@
+PRAGMA warning('disable', '4528');
+
+$fromTypes = {
+    "Date32": "2025-03-19",
+    "Datetime64": "2025-03-19T01:02:03Z",
+    "Timestamp64": "2025-03-19T01:02:03.456789Z",
+};
+
+$toTypes = [
+    "Date",     "Datetime",     "Timestamp",
+    "TzDate",   "TzDatetime",   "TzTimestamp",
+    "Date32",   "Datetime64",   "Timestamp64",
+    "TzDate32", "TzDatetime64", "TzTimestamp64",
+];
+
+$allowed = {
+    "Date32": {
+        /* - */     /* - */         /* - */
+        /* - */     /* - */         /* - */
+        /* - */     "DateTime64",   "Timestamp64",
+        "TzDate32", "TzDatetime64", "TzTimestamp64",
+    },
+    "Datetime64": {
+        /* - */ /* - */         /* - */
+        /* - */ /* - */         /* - */
+        /* - */ /* - */         "Timestamp64",
+        /* - */ "TzDatetime64", "TzTimestamp64",
+    },
+    "Timestamp64": {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ "TzTimestamp64",
+    },
+};
+
+EVALUATE FOR $from IN DictItems($fromTypes) DO BEGIN
+    EVALUATE FOR $to IN $toTypes DO BEGIN
+        EVALUATE IF DictContains($allowed[$from.0], $to) DO BEGIN
+            $callable = Callable(CallableType(0, String, DataType($to)), ($x)->(cast($x as String)));
+            $srcType = DataType($from.0);
+            SELECT $from.0 || " => " || $to,
+                 $callable(Unwrap(CAST($from.1 as $srcType)))
+        END DO;
+    END DO;
+END DO;
+
diff --git a/yql/essentials/tests/sql/suites/bigdate/date_tz_implicit_casts.sql b/yql/essentials/tests/sql/suites/bigdate/date_tz_implicit_casts.sql
new file mode 100644
index 00000000000..d7ff05196b0
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/bigdate/date_tz_implicit_casts.sql
@@ -0,0 +1,46 @@
+PRAGMA warning('disable', '4528');
+
+$fromTypes = {
+    "TzDate32": "2025-03-19,Europe/Moscow",
+    "TzDatetime64": "2025-03-19T01:02:03,Europe/Moscow",
+    "TzTimestamp64": "2025-03-19T01:02:03.456789,Europe/Moscow",
+};
+
+$toTypes = [
+    "Date",     "Datetime",     "Timestamp",
+    "TzDate",   "TzDatetime",   "TzTimestamp",
+    "Date32",   "Datetime64",   "Timestamp64",
+    "TzDate32", "TzDatetime64", "TzTimestamp64",
+];
+
+$allowed = {
+    "TzDate32": {
+        /* - */ /* - */         /* - */
+        /* - */ /* - */         /* - */
+        /* - */ /* - */         /* - */
+        /* - */ "TzDatetime64", "TzTimestamp64",
+    },
+    "TzDatetime64": {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ "TzTimestamp64",
+    },
+    "TzTimestamp64": {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+    },
+};
+
+EVALUATE FOR $from IN DictItems($fromTypes) DO BEGIN
+    EVALUATE FOR $to IN $toTypes DO BEGIN
+        EVALUATE IF DictContains($allowed[$from.0], $to) DO BEGIN
+            $callable = Callable(CallableType(0, String, DataType($to)), ($x)->(cast($x as String)));
+            $srcType = DataType($from.0);
+            SELECT $from.0 || " => " || $to,
+                 $callable(Unwrap(CAST($from.1 as $srcType)))
+        END DO;
+    END DO;
+END DO;
diff --git a/yql/essentials/tests/sql/suites/datetime/date_implicit_casts.sql b/yql/essentials/tests/sql/suites/datetime/date_implicit_casts.sql
new file mode 100644
index 00000000000..0030b0811dd
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/datetime/date_implicit_casts.sql
@@ -0,0 +1,46 @@
+PRAGMA warning('disable', '4528');
+
+$fromTypes = {
+    "Date": "2025-03-19",
+    "Datetime": "2025-03-19T01:02:03Z",
+    "Timestamp": "2025-03-19T01:02:03.456789Z",
+};
+
+$toTypes = [
+    "Date",     "Datetime",     "Timestamp",
+    "TzDate",   "TzDatetime",   "TzTimestamp",
+    "Date32",   "Datetime64",   "Timestamp64",
+    "TzDate32", "TzDatetime64", "TzTimestamp64",
+];
+
+$allowed = {
+    "Date": {
+        /* - */     "Datetime",     "Timestamp",
+        "TzDate",   "TzDatetime",   "TzTimestamp",
+        "Date32",   "DateTime64",   "Timestamp64",
+        "TzDate32", "TzDatetime64", "TzTimestamp64",
+    },
+    "Datetime": {
+        /* - */ /* - */         "Timestamp",
+        /* - */ "TzDatetime",   "TzTimestamp",
+        /* - */ "DateTime64",   "Timestamp64",
+        /* - */ "TzDatetime64", "TzTimestamp64",
+    },
+    "Timestamp": {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ "TzTimestamp",
+        /* - */ /* - */ "Timestamp64",
+        /* - */ /* - */ "TzTimestamp64",
+    },
+};
+
+EVALUATE FOR $from IN DictItems($fromTypes) DO BEGIN
+    EVALUATE FOR $to IN $toTypes DO BEGIN
+        EVALUATE IF DictContains($allowed[$from.0], $to) DO BEGIN
+            $callable = Callable(CallableType(0, String, DataType($to)), ($x)->(cast($x as String)));
+            $srcType = DataType($from.0);
+            SELECT $from.0 || " => " || $to,
+                 $callable(Unwrap(CAST($from.1 as $srcType)))
+        END DO;
+    END DO;
+END DO;
diff --git a/yql/essentials/tests/sql/suites/datetime/date_tz_implicit_casts.sql b/yql/essentials/tests/sql/suites/datetime/date_tz_implicit_casts.sql
new file mode 100644
index 00000000000..07c646c6541
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/datetime/date_tz_implicit_casts.sql
@@ -0,0 +1,46 @@
+PRAGMA warning('disable', '4528');
+
+$fromTypes = {
+    "TzDate": "2025-03-19,Europe/Moscow",
+    "TzDatetime": "2025-03-19T01:02:03,Europe/Moscow",
+    "TzTimestamp": "2025-03-19T01:02:03.456789,Europe/Moscow",
+};
+
+$toTypes = [
+    "Date",     "Datetime",     "Timestamp",
+    "TzDate",   "TzDatetime",   "TzTimestamp",
+    "Date32",   "Datetime64",   "Timestamp64",
+    "TzDate32", "TzDatetime64", "TzTimestamp64",
+];
+
+$allowed = {
+    "TzDate": {
+        /* - */     /* - */         /* - */
+        /* - */     "TzDatetime",   "TzTimestamp",
+        /* - */     /* - */         /* - */
+        "TzDate32", "TzDatetime64", "TzTimestamp64",
+    },
+    "TzDatetime": {
+        /* - */ /* - */         /* - */
+        /* - */ /* - */         "TzTimestamp",
+        /* - */ /* - */         /* - */
+        /* - */ "TzDatetime64", "TzTimestamp64",
+    },
+    "TzTimestamp": {
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ /* - */
+        /* - */ /* - */ "TzTimestamp64",
+    },
+};
+
+EVALUATE FOR $from IN DictItems($fromTypes) DO BEGIN
+    EVALUATE FOR $to IN $toTypes DO BEGIN
+        EVALUATE IF DictContains($allowed[$from.0], $to) DO BEGIN
+            $callable = Callable(CallableType(0, String, DataType($to)), ($x)->(cast($x as String)));
+            $srcType = DataType($from.0);
+            SELECT $from.0 || " => " || $to,
+                 $callable(Unwrap(CAST($from.1 as $srcType)))
+        END DO;
+    END DO;
+END DO;