YQL-19123 More SQL features for Variant type

More SQL features for Variant type
commit_hash:51c15343e2d24190ec59085888dfa3fd008cafcc

7 files changed, 244 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 553fd35c78..ee6df683bf 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -6908,6 +6908,13 @@
             "uri": "https://{canondata_backend}/1937429/434276f26b2857be3c5ad3fdbbf877d2bf775ac5/resource.tar.gz#test_sql2yql.test_expr-uuid_order_/sql.yql"
         }
     ],
+    "test_sql2yql.test[expr-variant_builtins_opt]": [
+        {
+            "checksum": "00f7f7ba82bd2474f7b1bfd7771323f9",
+            "size": 3847,
+            "uri": "https://{canondata_backend}/1936842/4ad1682e9f564ef555f5e0180b972a1adad2fc43/resource.tar.gz#test_sql2yql.test_expr-variant_builtins_opt_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[expr-variant_list_sort]": [
         {
             "checksum": "abd93bfb65a1cb17235be843d4cbd2c3",
@@ -6915,6 +6922,13 @@
             "uri": "https://{canondata_backend}/1936273/4a1b39013e1bae40e722cff8ccef8829784964e2/resource.tar.gz#test_sql2yql.test_expr-variant_list_sort_/sql.yql"
         }
     ],
+    "test_sql2yql.test[expr-variant_struct_builtins]": [
+        {
+            "checksum": "cb90540556b74bdbb9a95959c62a3cbf",
+            "size": 5780,
+            "uri": "https://{canondata_backend}/1936842/4ad1682e9f564ef555f5e0180b972a1adad2fc43/resource.tar.gz#test_sql2yql.test_expr-variant_struct_builtins_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[expr-variant_struct_comp]": [
         {
             "checksum": "3f85119441280a8729ef912910ac7745",
@@ -6922,6 +6936,13 @@
             "uri": "https://{canondata_backend}/1936273/4a1b39013e1bae40e722cff8ccef8829784964e2/resource.tar.gz#test_sql2yql.test_expr-variant_struct_comp_/sql.yql"
         }
     ],
+    "test_sql2yql.test[expr-variant_tuple_builtins]": [
+        {
+            "checksum": "41d01afdf58dcca513a44186a279efcb",
+            "size": 5682,
+            "uri": "https://{canondata_backend}/1936842/4ad1682e9f564ef555f5e0180b972a1adad2fc43/resource.tar.gz#test_sql2yql.test_expr-variant_tuple_builtins_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[expr-variant_tuple_comp]": [
         {
             "checksum": "62f3d0d5c99c037db94a4f8f3c3c9d1d",
@@ -24821,16 +24842,31 @@
             "uri": "file://test_sql_format.test_expr-uuid_order_/formatted.sql"
         }
     ],
+    "test_sql_format.test[expr-variant_builtins_opt]": [
+        {
+            "uri": "file://test_sql_format.test_expr-variant_builtins_opt_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[expr-variant_list_sort]": [
         {
             "uri": "file://test_sql_format.test_expr-variant_list_sort_/formatted.sql"
         }
     ],
+    "test_sql_format.test[expr-variant_struct_builtins]": [
+        {
+            "uri": "file://test_sql_format.test_expr-variant_struct_builtins_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[expr-variant_struct_comp]": [
         {
             "uri": "file://test_sql_format.test_expr-variant_struct_comp_/formatted.sql"
         }
     ],
+    "test_sql_format.test[expr-variant_tuple_builtins]": [
+        {
+            "uri": "file://test_sql_format.test_expr-variant_tuple_builtins_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[expr-variant_tuple_comp]": [
         {
             "uri": "file://test_sql_format.test_expr-variant_tuple_comp_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_builtins_opt_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_builtins_opt_/formatted.sql
new file mode 100644
index 0000000000..3023dcf5c2
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_builtins_opt_/formatted.sql
@@ -0,0 +1,30 @@
+$vartype = Variant<a: Optional<String>, b: Optional<String>>;
+
+$handle_a = ($x) -> {
+    RETURN CAST(($x || "1") AS Uint32);
+};
+
+$handle_b = ($x) -> {
+    RETURN CAST(($x || "2") AS Uint32);
+};
+
+$var_a = VARIANT ("5", "a", $vartype);
+$var_b = VARIANT ("6", "b", $vartype);
+
+SELECT
+    Visit(Just($var_a), $handle_a AS a, $handle_b AS b),
+    Visit(Just($var_b), $handle_a AS a, $handle_b AS b),
+    VisitOrDefault(Just($var_b), Just(777u), $handle_a AS a),
+    VariantItem(Just($var_b))
+;
+
+$vartype_t = Variant<Optional<String>, Optional<String>>;
+$var_1 = VARIANT ("7", "0", $vartype_t);
+$var_2 = VARIANT ("8", "1", $vartype_t);
+
+SELECT
+    Visit(Just($var_1), $handle_a, $handle_b),
+    Visit(Just($var_2), $handle_a, $handle_b),
+    VisitOrDefault(Just($var_2), $handle_a, Just(777u)),
+    VariantItem(Just($var_b))
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_struct_builtins_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_struct_builtins_/formatted.sql
new file mode 100644
index 0000000000..1192a90d9b
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_struct_builtins_/formatted.sql
@@ -0,0 +1,46 @@
+$vartype = Variant<num: Int32, flag: Bool, str: String>;
+
+$handle_num = ($x) -> {
+    RETURN 2 * $x;
+};
+
+$handle_flag = ($x) -> {
+    RETURN If($x, 200, 10);
+};
+
+$handle_str = ($x) -> {
+    RETURN Unwrap(CAST(LENGTH($x) AS Int32));
+};
+
+$visitor = ($var) -> {
+    RETURN Visit($var, $handle_num AS num, $handle_flag AS flag, $handle_str AS str);
+};
+
+SELECT
+    $visitor(VARIANT (5, "num", $vartype)),
+    $visitor(Just(VARIANT (TRUE, "flag", $vartype))),
+    $visitor(Just(VARIANT ("somestr", "str", $vartype))),
+    $visitor(Nothing(OptionalType($vartype))),
+    $visitor(NULL)
+;
+
+$visitor_def = ($var) -> {
+    RETURN VisitOrDefault($var, 999, $handle_num AS num, $handle_flag AS flag);
+};
+
+SELECT
+    $visitor_def(VARIANT (5, "num", $vartype)),
+    $visitor_def(Just(VARIANT (TRUE, "flag", $vartype))),
+    $visitor_def(Just(VARIANT ("somestr", "str", $vartype))),
+    $visitor_def(Nothing(OptionalType($vartype))),
+    $visitor_def(NULL)
+;
+
+$vartype1 = Variant<num1: Int32, num2: Int32, num3: Int32>;
+
+SELECT
+    VariantItem(VARIANT (7, "num2", $vartype1)),
+    VariantItem(Just(VARIANT (5, "num1", $vartype1))),
+    VariantItem(Nothing(OptionalType($vartype1))),
+    VariantItem(NULL)
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_tuple_builtins_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_tuple_builtins_/formatted.sql
new file mode 100644
index 0000000000..b1e21cda10
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_expr-variant_tuple_builtins_/formatted.sql
@@ -0,0 +1,46 @@
+$vartype = Variant<Int32, Bool, String>;
+
+$handle_num = ($x) -> {
+    RETURN 2 * $x;
+};
+
+$handle_flag = ($x) -> {
+    RETURN If($x, 200, 10);
+};
+
+$handle_str = ($x) -> {
+    RETURN Unwrap(CAST(LENGTH($x) AS Int32));
+};
+
+$visitor = ($var) -> {
+    RETURN Visit($var, $handle_num, $handle_flag, $handle_str);
+};
+
+SELECT
+    $visitor(VARIANT (5, "0", $vartype)),
+    $visitor(Just(VARIANT (TRUE, "1", $vartype))),
+    $visitor(Just(VARIANT ("somestr", "2", $vartype))),
+    $visitor(Nothing(OptionalType($vartype))),
+    $visitor(NULL)
+;
+
+$visitor_def = ($var) -> {
+    RETURN VisitOrDefault($var, $handle_num, $handle_flag, 999);
+};
+
+SELECT
+    $visitor_def(VARIANT (5, "0", $vartype)),
+    $visitor_def(Just(VARIANT (TRUE, "1", $vartype))),
+    $visitor_def(Just(VARIANT ("somestr", "2", $vartype))),
+    $visitor_def(Nothing(OptionalType($vartype))),
+    $visitor_def(NULL)
+;
+
+$vartype1 = Variant<Int32, Int32, Int32>;
+
+SELECT
+    VariantItem(VARIANT (7, "1", $vartype1)),
+    VariantItem(Just(VARIANT (5, "0", $vartype1))),
+    VariantItem(Nothing(OptionalType($vartype1))),
+    VariantItem(NULL)
+;
diff --git a/yql/essentials/tests/sql/suites/expr/variant_builtins_opt.sql b/yql/essentials/tests/sql/suites/expr/variant_builtins_opt.sql
new file mode 100644
index 0000000000..443802fe56
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/expr/variant_builtins_opt.sql
@@ -0,0 +1,26 @@
+$vartype = Variant<a: Optional<String>, b: Optional<String>>;
+
+$handle_a = ($x) -> { return CAST(($x || "1") AS Uint32); };
+$handle_b = ($x) -> { return CAST(($x || "2") AS Uint32); };
+
+$var_a = Variant("5", "a", $vartype);
+$var_b = Variant("6", "b", $vartype);
+
+SELECT
+    Visit(Just($var_a), $handle_a AS a, $handle_b AS b),
+    Visit(Just($var_b), $handle_a AS a, $handle_b AS b),
+    VisitOrDefault(Just($var_b), Just(777u), $handle_a AS a),
+    VariantItem(Just($var_b))
+;
+
+$vartype_t = Variant<Optional<String>, Optional<String>>;
+
+$var_1 = Variant("7", "0", $vartype_t);
+$var_2 = Variant("8", "1", $vartype_t);
+
+SELECT
+    Visit(Just($var_1), $handle_a, $handle_b),
+    Visit(Just($var_2), $handle_a, $handle_b),
+    VisitOrDefault(Just($var_2), $handle_a, Just(777u)),
+    VariantItem(Just($var_b))
+;
diff --git a/yql/essentials/tests/sql/suites/expr/variant_struct_builtins.sql b/yql/essentials/tests/sql/suites/expr/variant_struct_builtins.sql
new file mode 100644
index 0000000000..71a09810da
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/expr/variant_struct_builtins.sql
@@ -0,0 +1,30 @@
+$vartype = Variant<num: Int32, flag: Bool, str: String>;
+$handle_num = ($x) -> { return 2 * $x; };
+$handle_flag = ($x) -> { return If($x, 200, 10); };
+$handle_str = ($x) -> { return Unwrap(CAST(LENGTH($x) AS Int32)); };
+
+$visitor = ($var) -> { return Visit($var, $handle_num AS num, $handle_flag AS flag, $handle_str AS str); };
+SELECT
+    $visitor(Variant(5, "num", $vartype)),
+    $visitor(Just(Variant(True, "flag", $vartype))),
+    $visitor(Just(Variant("somestr", "str", $vartype))),
+    $visitor(Nothing(OptionalType($vartype))),
+    $visitor(NULL)
+;
+
+$visitor_def = ($var) -> { return VisitOrDefault($var, 999, $handle_num AS num, $handle_flag AS flag); };
+SELECT
+    $visitor_def(Variant(5, "num", $vartype)),
+    $visitor_def(Just(Variant(True, "flag", $vartype))),
+    $visitor_def(Just(Variant("somestr", "str", $vartype))),
+    $visitor_def(Nothing(OptionalType($vartype))),
+    $visitor_def(NULL)
+;
+
+$vartype1 = Variant<num1: Int32, num2: Int32, num3: Int32>;
+SELECT
+    VariantItem(Variant(7, "num2", $vartype1)),
+    VariantItem(Just(Variant(5, "num1", $vartype1))),
+    VariantItem(Nothing(OptionalType($vartype1))),
+    VariantItem(NULL)
+;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/expr/variant_tuple_builtins.sql b/yql/essentials/tests/sql/suites/expr/variant_tuple_builtins.sql
new file mode 100644
index 0000000000..35d1775708
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/expr/variant_tuple_builtins.sql
@@ -0,0 +1,30 @@
+$vartype = Variant<Int32, Bool, String>;
+$handle_num = ($x) -> { return 2 * $x; };
+$handle_flag = ($x) -> { return If($x, 200, 10); };
+$handle_str = ($x) -> { return Unwrap(CAST(LENGTH($x) AS Int32)); };
+
+$visitor = ($var) -> { return Visit($var, $handle_num, $handle_flag, $handle_str); };
+SELECT
+    $visitor(Variant(5, "0", $vartype)),
+    $visitor(Just(Variant(True, "1", $vartype))),
+    $visitor(Just(Variant("somestr", "2", $vartype))),
+    $visitor(Nothing(OptionalType($vartype))),
+    $visitor(NULL)
+;
+
+$visitor_def = ($var) -> { return VisitOrDefault($var, $handle_num, $handle_flag, 999); };
+SELECT
+    $visitor_def(Variant(5, "0", $vartype)),
+    $visitor_def(Just(Variant(True, "1", $vartype))),
+    $visitor_def(Just(Variant("somestr", "2", $vartype))),
+    $visitor_def(Nothing(OptionalType($vartype))),
+    $visitor_def(NULL)
+;
+
+$vartype1 = Variant<Int32, Int32, Int32>;
+SELECT
+    VariantItem(Variant(7, "1", $vartype1)),
+    VariantItem(Just(Variant(5, "0", $vartype1))),
+    VariantItem(Nothing(OptionalType($vartype1))),
+    VariantItem(NULL)
+;
\ No newline at end of file