YQL-17269: support INTERSECT and EXCEPT without PositionalUnionAll

commit_hash:632e24794e8bcf6ef0502b7e8c031e964d28d36a

10 files changed, 447 insertions, 10 deletions

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