YQL-17269: support INTERSECT and EXCEPT without PositionalUnionAll

commit_hash:632e24794e8bcf6ef0502b7e8c031e964d28d36a

26 files changed, 885 insertions, 10 deletions

diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
index 021ebed645a..27aab7bf175 100644
--- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
@@ -426,6 +426,20 @@
             "uri": "https://{canondata_backend}/1903885/f19a3c78c5968d4ae7992179e15806b818abce10/resource.tar.gz#test.test_datetime-date_convert-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_all-default.txt-Debug]": [
+        {
+            "checksum": "1bdaf8e6cdeeba7ccdbec393da4facda",
+            "size": 2108,
+            "uri": "https://{canondata_backend}/1881367/453c70881991a95154014d718c871d0afdcbf15e/resource.tar.gz#test.test_except-except_all-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_all-default.txt-Results]": [
+        {
+            "checksum": "1c2de5c252530ae81b0c46bd53f8ef65",
+            "size": 3437,
+            "uri": "https://{canondata_backend}/1881367/453c70881991a95154014d718c871d0afdcbf15e/resource.tar.gz#test.test_except-except_all-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-as_table_emptylist2-default.txt-Debug]": [
         {
             "checksum": "6424bb71484d775e2e9f4abcc3b852e6",
diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/result.json b/yql/essentials/tests/sql/minirun/part2/canondata/result.json
index 9ab45a645fc..28b1ecec99c 100644
--- a/yql/essentials/tests/sql/minirun/part2/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part2/canondata/result.json
@@ -461,6 +461,20 @@
             "uri": "https://{canondata_backend}/1917492/e1446481f72d95d231bd730cb8c596af631640ea/resource.tar.gz#test.test_datetime-date_types-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_all_empty-default.txt-Debug]": [
+        {
+            "checksum": "e46b9bf056a427b8de96acf38e6fdc56",
+            "size": 348,
+            "uri": "https://{canondata_backend}/1942278/736d75cf62b2e931435e1042d99607804c1fe8a7/resource.tar.gz#test.test_except-except_all_empty-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_all_empty-default.txt-Results]": [
+        {
+            "checksum": "d2e2d092c3bb9360e458b8d8e66baa1d",
+            "size": 1474,
+            "uri": "https://{canondata_backend}/1942278/736d75cf62b2e931435e1042d99607804c1fe8a7/resource.tar.gz#test.test_except-except_all_empty-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-cast_dynumber-default.txt-Debug]": [
         {
             "checksum": "114987035ac6e54e07a57e3019c0e192",
diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
index baf4cd34051..a62c1531662 100644
--- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
@@ -704,6 +704,20 @@
             "uri": "https://{canondata_backend}/1936273/60c880bb0a569b92424a6d5d2554b715fd342146/resource.tar.gz#test.test_in-in_with_tuple_simple-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_distinct_empty-default.txt-Debug]": [
+        {
+            "checksum": "de3495bdc43ec7707722e1b9585237d0",
+            "size": 298,
+            "uri": "https://{canondata_backend}/1917492/587e362e11daf3cf997a345b018213c9e8643313/resource.tar.gz#test.test_intersect-intersect_distinct_empty-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_distinct_empty-default.txt-Results]": [
+        {
+            "checksum": "36e87fce680f9d5c30c1e656e6be934b",
+            "size": 749,
+            "uri": "https://{canondata_backend}/1917492/587e362e11daf3cf997a345b018213c9e8643313/resource.tar.gz#test.test_intersect-intersect_distinct_empty-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-convert_check_key_mem-default.txt-Debug]": [
         {
             "checksum": "98813e43dffe60bd82a78af8e4a992ba",
diff --git a/yql/essentials/tests/sql/minirun/part4/canondata/result.json b/yql/essentials/tests/sql/minirun/part4/canondata/result.json
index 214ceb66734..653bc9a7089 100644
--- a/yql/essentials/tests/sql/minirun/part4/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part4/canondata/result.json
@@ -814,6 +814,20 @@
             "uri": "https://{canondata_backend}/1847551/2d49108793659545d13c6e0b843e2c017302ad43/resource.tar.gz#test.test_in-in_with_nulls_and_optionals_extra-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_all_empty-default.txt-Debug]": [
+        {
+            "checksum": "de3495bdc43ec7707722e1b9585237d0",
+            "size": 298,
+            "uri": "https://{canondata_backend}/1937150/92cf5fc8848ad59407c2744be1538c731bcb8de4/resource.tar.gz#test.test_intersect-intersect_all_empty-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_all_empty-default.txt-Results]": [
+        {
+            "checksum": "36e87fce680f9d5c30c1e656e6be934b",
+            "size": 749,
+            "uri": "https://{canondata_backend}/1937150/92cf5fc8848ad59407c2744be1538c731bcb8de4/resource.tar.gz#test.test_intersect-intersect_all_empty-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-cross_join_with_lazy_list-default.txt-Debug]": [
         {
             "checksum": "8687ef86fa2f673b9760d9595f475ece",
diff --git a/yql/essentials/tests/sql/minirun/part6/canondata/result.json b/yql/essentials/tests/sql/minirun/part6/canondata/result.json
index b31a0804b64..c5d1ec58cb3 100644
--- a/yql/essentials/tests/sql/minirun/part6/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part6/canondata/result.json
@@ -558,6 +558,20 @@
             "uri": "https://{canondata_backend}/1775059/8f98a745728bc42ccc40bc5c65b23d499853793a/resource.tar.gz#test.test_distinct-distinct_star_inmem-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_distinct_empty-default.txt-Debug]": [
+        {
+            "checksum": "e46b9bf056a427b8de96acf38e6fdc56",
+            "size": 348,
+            "uri": "https://{canondata_backend}/1942278/fc503167a0e9dd2e6465bc29c2b2299043a83a6c/resource.tar.gz#test.test_except-except_distinct_empty-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_distinct_empty-default.txt-Results]": [
+        {
+            "checksum": "d2e2d092c3bb9360e458b8d8e66baa1d",
+            "size": 1474,
+            "uri": "https://{canondata_backend}/1942278/fc503167a0e9dd2e6465bc29c2b2299043a83a6c/resource.tar.gz#test.test_except-except_distinct_empty-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-as_table-default.txt-Debug]": [
         {
             "checksum": "8799d51aa9290bc664b44e0945b559da",
@@ -824,6 +838,20 @@
             "uri": "https://{canondata_backend}/1031349/7264f379f5ed6029cd16e136c979509c14985388/resource.tar.gz#test.test_in-large_in_YQL-19183-ansi-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_distinct-default.txt-Debug]": [
+        {
+            "checksum": "1528f6b2213ece6a1f4a8f32465e8dea",
+            "size": 2226,
+            "uri": "https://{canondata_backend}/1937424/51d6348179b23fc8977fc8de7b08323f6c5603f5/resource.tar.gz#test.test_intersect-intersect_distinct-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_distinct-default.txt-Results]": [
+        {
+            "checksum": "3f4b92b2d6a3a7a9f18273430118c7b2",
+            "size": 2617,
+            "uri": "https://{canondata_backend}/1937424/51d6348179b23fc8977fc8de7b08323f6c5603f5/resource.tar.gz#test.test_intersect-intersect_distinct-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[json-combination/nested-default.txt-Debug]": [
         {
             "checksum": "31d64ac4278d43e44b6d9c979b636ce6",
diff --git a/yql/essentials/tests/sql/minirun/part8/canondata/result.json b/yql/essentials/tests/sql/minirun/part8/canondata/result.json
index b47658a25bd..45f139b3e50 100644
--- a/yql/essentials/tests/sql/minirun/part8/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part8/canondata/result.json
@@ -639,6 +639,20 @@
             "uri": "https://{canondata_backend}/1903885/a240e9eb95f33f5b5688d6770bbb18aa5d19778a/resource.tar.gz#test.test_datetime-date_tz_scale-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_distinct-default.txt-Debug]": [
+        {
+            "checksum": "f0505afaff2f21cf0f9bdab5842fd369",
+            "size": 2112,
+            "uri": "https://{canondata_backend}/1942278/3c1745dc69d236a46dcb2ea6952e67da86aa3e9d/resource.tar.gz#test.test_except-except_distinct-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_distinct-default.txt-Results]": [
+        {
+            "checksum": "35ad547f6b9d1506b0e4a5ec10f52154",
+            "size": 2929,
+            "uri": "https://{canondata_backend}/1942278/3c1745dc69d236a46dcb2ea6952e67da86aa3e9d/resource.tar.gz#test.test_except-except_distinct-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-cast_longint-default.txt-Debug]": [
         {
             "checksum": "852e514a4bf927cd6c59acf27e3cc716",
@@ -964,6 +978,20 @@
             "uri": "https://{canondata_backend}/1871182/8488be1009a783ec149801679b1d381d33cbeb2f/resource.tar.gz#test.test_in-YQL-18950-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_all-default.txt-Debug]": [
+        {
+            "checksum": "4d081baeeaa6382eb5b8cde859c96164",
+            "size": 2068,
+            "uri": "https://{canondata_backend}/1775059/2b5e7aa24664d04b90cf055001d40e858a3f4532/resource.tar.gz#test.test_intersect-intersect_all-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_all-default.txt-Results]": [
+        {
+            "checksum": "cbcec9c2bbeaa07a3789ddff3f12ad54",
+            "size": 3125,
+            "uri": "https://{canondata_backend}/1775059/2b5e7aa24664d04b90cf055001d40e858a3f4532/resource.tar.gz#test.test_intersect-intersect_all-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-left_join_predicate_push_parent_cross_join-default.txt-Debug]": [
         {
             "checksum": "23ca906191920493283354f7d5513c7a",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 2ddd8201e80..d533cfec5ce 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -953,9 +953,9 @@
     ],
     "test_sql2yql.test[aggregate-group_by_expr_after_where_ver]": [
         {
-            "checksum": "d7d81aab522ef18bb07e619c426594c5",
+            "checksum": "4799645fef77850f5f5f07de2d1b8bc2",
             "size": 1762,
-            "uri": "https://{canondata_backend}/1597364/99b2cf59a9975dbc2994ead01aa9dcbd784b5279/resource.tar.gz#test_sql2yql.test_aggregate-group_by_expr_after_where_ver_/sql.yql"
+            "uri": "https://{canondata_backend}/1937027/20da4ae4ed8ef86446356b94288d2f22b76637b5/resource.tar.gz#test_sql2yql.test_aggregate-group_by_expr_after_where_ver_/sql.yql"
         }
     ],
     "test_sql2yql.test[aggregate-group_by_rollup_rename]": [
@@ -2337,6 +2337,34 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_distinct-distinct_star_inmem_/sql.yql"
         }
     ],
+    "test_sql2yql.test[except-except_all]": [
+        {
+            "checksum": "432071f0d21d01d9bd0b792c77db417e",
+            "size": 3566,
+            "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_all_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[except-except_all_empty]": [
+        {
+            "checksum": "40a32c1c8fb5a979dea406ffe7963eeb",
+            "size": 4163,
+            "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_all_empty_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[except-except_distinct]": [
+        {
+            "checksum": "a73b03f897702173faee14fdea46dd85",
+            "size": 3560,
+            "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_distinct_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[except-except_distinct_empty]": [
+        {
+            "checksum": "601b8022980473e8eafdf9365597cf1e",
+            "size": 4154,
+            "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_distinct_empty_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[expr-as_dict_dict_key]": [
         {
             "checksum": "9dd554b71081bd23a148f6d34f15d7ca",
@@ -3891,6 +3919,34 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_in-small_in_YQL-19183_/sql.yql"
         }
     ],
+    "test_sql2yql.test[intersect-intersect_all]": [
+        {
+            "checksum": "24cbce9982a475e5a7cb746228ef18f7",
+            "size": 3572,
+            "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_all_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[intersect-intersect_all_empty]": [
+        {
+            "checksum": "6ea841d5596fd7d558edf69fe1107a25",
+            "size": 4172,
+            "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_all_empty_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[intersect-intersect_distinct]": [
+        {
+            "checksum": "47b396e6add2c3a8d1e2efc3015a261f",
+            "size": 3566,
+            "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_distinct_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[intersect-intersect_distinct_empty]": [
+        {
+            "checksum": "8d722656304be05a941f2adfd4f32405",
+            "size": 4163,
+            "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_distinct_empty_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[join-convert_check_key_mem2]": [
         {
             "checksum": "e1f4ff23a4f2c04036a52d4a5b25529c",
@@ -4010,13 +4066,6 @@
             "uri": "https://{canondata_backend}/1942525/94a477066ea16f69d4848bbe524485fc029978b8/resource.tar.gz#test_sql2yql.test_join-prune_keys_YQL-19979_/sql.yql"
         }
     ],
-    "test_sql2yql.test[join-prune_keys_YQL-19979]": [
-        {
-            "checksum": "0dad5d395f90148805e893a30f0b4963",
-            "size": 3845,
-            "uri": "https://{canondata_backend}/1942525/94a477066ea16f69d4848bbe524485fc029978b8/resource.tar.gz#test_sql2yql.test_join-prune_keys_YQL-19979_/sql.yql"
-        }
-    ],
     "test_sql2yql.test[join-yql-19192]": [
         {
             "checksum": "fffdf1cbb40643da9daf9bdf3edec121",
@@ -9222,6 +9271,26 @@
             "uri": "file://test_sql_format.test_distinct-distinct_star_inmem_/formatted.sql"
         }
     ],
+    "test_sql_format.test[except-except_all]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_all_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[except-except_all_empty]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_all_empty_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[except-except_distinct]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_distinct_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[except-except_distinct_empty]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_distinct_empty_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[expr-as_dict_dict_key]": [
         {
             "uri": "file://test_sql_format.test_expr-as_dict_dict_key_/formatted.sql"
@@ -10332,6 +10401,26 @@
             "uri": "file://test_sql_format.test_in-small_in_YQL-19183_/formatted.sql"
         }
     ],
+    "test_sql_format.test[intersect-intersect_all]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_all_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[intersect-intersect_all_empty]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_all_empty_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[intersect-intersect_distinct]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_distinct_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[intersect-intersect_distinct_empty]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[join-convert_check_key_mem2]": [
         {
             "uri": "file://test_sql_format.test_join-convert_check_key_mem2_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql
new file mode 100644
index 00000000000..917e3d4a593
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+EXCEPT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+EXCEPT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql
new file mode 100644
index 00000000000..3a516c473a8
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT ALL
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+EXCEPT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql
new file mode 100644
index 00000000000..f1aacddbb1c
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+EXCEPT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+EXCEPT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql
new file mode 100644
index 00000000000..d5ffcca0bcf
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT DISTINCT
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+EXCEPT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql
new file mode 100644
index 00000000000..a76f042e494
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+INTERSECT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+INTERSECT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql
new file mode 100644
index 00000000000..c89a93a9243
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT ALL
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+INTERSECT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql
new file mode 100644
index 00000000000..5b7d85a1f46
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+INTERSECT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+INTERSECT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql
new file mode 100644
index 00000000000..16623be2f3f
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT DISTINCT
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+INTERSECT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/sql2yql/test_sql2yql.py b/yql/essentials/tests/sql/sql2yql/test_sql2yql.py
index 924d5f4181d..c8f5bbcd845 100644
--- a/yql/essentials/tests/sql/sql2yql/test_sql2yql.py
+++ b/yql/essentials/tests/sql/sql2yql/test_sql2yql.py
@@ -2,8 +2,11 @@ import os
 
 import yatest.common
 
-from test_utils import pytest_generate_tests_by_template, SQLRUN_PATH, SQL_FLAGS
+from test_utils import get_config, pytest_generate_tests_by_template, SQLRUN_PATH, SQL_FLAGS
 
+from yql_utils import get_langver
+
+DEFAULT_LANG_VER = '2025.01'
 DATA_PATH = yatest.common.source_path('yql/essentials/tests/sql/suites')
 
 
@@ -11,6 +14,25 @@ def pytest_generate_tests(metafunc):
     return pytest_generate_tests_by_template('.sql', metafunc, data_path=DATA_PATH)
 
 
+def _get_cfg_path(suite, case, data_path):
+    cfg_path = os.path.join(data_path, suite, case)
+    if os.path.exists(cfg_path + '.cfg'):
+        return ""
+    else:
+        return "default.txt"
+
+
+def _get_langver(suite, case, data_path):
+    cfg_path = _get_cfg_path(suite, case, data_path)
+    config = get_config(suite, case, cfg_path, data_path=DATA_PATH)
+
+    langver = get_langver(config)
+    if langver is None:
+        langver = DEFAULT_LANG_VER
+
+    return langver
+
+
 def get_sql2yql_cmd(suite, case, case_file, out_dir, ansi_lexer, test_format, test_double_format):
     cmd = [
         SQLRUN_PATH,
@@ -50,6 +72,8 @@ def get_sql2yql_cmd(suite, case, case_file, out_dir, ansi_lexer, test_format, te
     if SQL_FLAGS:
         cmd.append('--flags=%s' % ','.join(SQL_FLAGS))
 
+    cmd.append('--langver=%s' % _get_langver(suite, case, data_path=DATA_PATH))
+
     return cmd
 
 
diff --git a/yql/essentials/tests/sql/suites/except/default.cfg b/yql/essentials/tests/sql/suites/except/default.cfg
new file mode 100644
index 00000000000..cd964fef9e8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/default.cfg
@@ -0,0 +1 @@
+langver 2025.03
diff --git a/yql/essentials/tests/sql/suites/except/except_all.sql b/yql/essentials/tests/sql/suites/except/except_all.sql
new file mode 100644
index 00000000000..917e3d4a593
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_all.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+EXCEPT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+EXCEPT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/suites/except/except_all_empty.sql b/yql/essentials/tests/sql/suites/except/except_all_empty.sql
new file mode 100644
index 00000000000..3a516c473a8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_all_empty.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT ALL
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+EXCEPT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/suites/except/except_distinct.sql b/yql/essentials/tests/sql/suites/except/except_distinct.sql
new file mode 100644
index 00000000000..f1aacddbb1c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_distinct.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+EXCEPT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+EXCEPT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql b/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql
new file mode 100644
index 00000000000..d5ffcca0bcf
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT DISTINCT
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+EXCEPT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/suites/intersect/default.cfg b/yql/essentials/tests/sql/suites/intersect/default.cfg
new file mode 100644
index 00000000000..cd964fef9e8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/default.cfg
@@ -0,0 +1 @@
+langver 2025.03
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_all.sql b/yql/essentials/tests/sql/suites/intersect/intersect_all.sql
new file mode 100644
index 00000000000..a76f042e494
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_all.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+INTERSECT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+INTERSECT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql b/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql
new file mode 100644
index 00000000000..c89a93a9243
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT ALL
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+INTERSECT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql b/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql
new file mode 100644
index 00000000000..5b7d85a1f46
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+INTERSECT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+INTERSECT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql b/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql
new file mode 100644
index 00000000000..16623be2f3f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT DISTINCT
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+INTERSECT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;