YQL-17269: support INTERSECT and EXCEPT without PositionalUnionAll

commit_hash:632e24794e8bcf6ef0502b7e8c031e964d28d36a

1 files changed, 73 insertions, 0 deletions

diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h
index 8b33aa8be0d..223aaa52b3b 100644
--- a/yql/essentials/sql/v1/sql_ut_common.h
+++ b/yql/essentials/sql/v1/sql_ut_common.h
@@ -1724,6 +1724,8 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:1:6: Error: BATCH UPDATE is unsupported with ON\n");
     }
 
+    // UNION
+
     Y_UNIT_TEST(UnionAllTest) {
         NYql::TAstParseResult res = SqlToYql("PRAGMA DisableEmitUnionMerge; SELECT key FROM plato.Input UNION ALL select subkey FROM plato.Input;");
         UNIT_ASSERT(res.Root);
@@ -1798,6 +1800,77 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_VALUES_EQUAL(3, elementStat["Union"]);
     }
 
+    // INTERSECT
+
+    Y_UNIT_TEST(IntersectAllTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT ALL select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("IntersectAll"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["IntersectAll"]);
+    }
+
+    Y_UNIT_TEST(IntersectTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("Intersect"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Intersect"]);
+    }
+
+    Y_UNIT_TEST(IntersectDistinctTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT DISTINCT select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("Intersect"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Intersect"]);
+    }
+
+    // EXCEPT
+
+    Y_UNIT_TEST(ExceptAllTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT ALL select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("ExceptAll"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["ExceptAll"]);
+    }
+
+    Y_UNIT_TEST(ExceptTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("Except"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Except"]);
+    }
+
+    Y_UNIT_TEST(ExceptDistinctTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT DISTINCT select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("Except"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Except"]);
+    }
+
+
     Y_UNIT_TEST(DeclareDecimalParameter) {
         NYql::TAstParseResult res = SqlToYql("declare $value as Decimal(22,9); select $value as cnt;");
         UNIT_ASSERT(res.Root);