YQL-17269: restrict multiple INTERSECT and EXCEPT

commit_hash:635c8def3bba6890d2d80b418fca9bec6cbd4b73

2 files changed, 69 insertions, 23 deletions

diff --git a/yql/essentials/sql/v1/sql_select.cpp b/yql/essentials/sql/v1/sql_select.cpp
index b9be35f76f1..51894a2bda8 100644
--- a/yql/essentials/sql/v1/sql_select.cpp
+++ b/yql/essentials/sql/v1/sql_select.cpp
@@ -1359,7 +1359,7 @@ TSourcePtr TSqlSelect::Build(const TRule& node, TPosition pos, TSelectKindResult
 
     auto blocks = node.GetBlock2();
 
-    TPosition unionPos = pos; // Position of first select
+    TPosition opPos = pos; // Position of first select
     TVector<TSortSpecificationPtr> orderBy;
     bool assumeOrderBy = false;
     TNodePtr skipTake;
@@ -1372,7 +1372,7 @@ TSourcePtr TSqlSelect::Build(const TRule& node, TPosition pos, TSelectKindResult
 
     for (int i = 0; i < blocks.size(); ++i) {
         auto& b = blocks[i];
-        const bool second = (i == 0);
+        const bool first = (i == 0);
         const bool last = (i + 1 == blocks.size());
         TSelectKindPlacement placement;
         placement.IsLastInSelectOp = last;
@@ -1395,7 +1395,7 @@ TSourcePtr TSqlSelect::Build(const TRule& node, TPosition pos, TSelectKindResult
             // nothing
         } else if (selectOperator == "intersect" || selectOperator == "except") {
             if (!IsBackwardCompatibleFeatureAvailable(Ctx_.Settings.LangVer, MakeLangVersion(2025, 3), Ctx_.Settings.BackportMode)) {
-                Ctx_.Error() << "INTERSECT and EXCEPT are available starting from 2025.03 version";
+                Ctx_.Error() << "INTERSECT and EXCEPT are available starting from 2025.03 version.";
                 return nullptr;
             }
         } else {
@@ -1414,11 +1414,23 @@ TSourcePtr TSqlSelect::Build(const TRule& node, TPosition pos, TSelectKindResult
             }
         }
 
-        // currentSelectOperator == "" <=> second == true
-        if (!second && (currentSelectOperator != selectOperator || quantifier != currentQuantifier)) {
-            auto source = BuildSelectOp(pos, std::move(sources), currentSelectOperator, currentQuantifier, {});
-            sources.clear();
-            sources.emplace_back(std::move(source));
+        // currentSelectOperator == "" <=> first == true
+        if (!first) {
+            if (currentSelectOperator == selectOperator) {
+                if (currentSelectOperator == "union") {
+                    if (currentQuantifier != quantifier) {
+                        auto source = BuildSelectOp(pos, std::move(sources), currentSelectOperator, currentQuantifier, {});
+                        sources.clear();
+                        sources.emplace_back(std::move(source));
+                    }
+                } else {
+                    Ctx_.Error() << "Multiple usage of INTERSECT and EXCEPT is not implemented yet.";
+                    return nullptr;
+                }
+            } else {
+                Ctx_.Error() << "Simultaneous usage of UNION, INTERSECT, and EXCEPT is not implemented yet.";
+                return nullptr;
+            }
         }
 
         sources.emplace_back(std::move(next.Source));
@@ -1442,15 +1454,15 @@ TSourcePtr TSqlSelect::Build(const TRule& node, TPosition pos, TSelectKindResult
         bool stream = false;
 
         TVector<TNodePtr> terms;
-        terms.push_back(BuildColumn(unionPos, "*", ""));
+        terms.push_back(BuildColumn(opPos, "*", ""));
 
-        result = BuildSelectCore(Ctx_, unionPos, std::move(result), groupByExpr, groupBy, compactGroupBy, groupBySuffix,
+        result = BuildSelectCore(Ctx_, opPos, std::move(result), groupByExpr, groupBy, compactGroupBy, groupBySuffix,
             assumeOrderBy, orderBy, having, std::move(winSpecs), legacyHoppingWindowSpec, std::move(terms),
             distinct, std::move(without), forceWithout, stream, outermostSettings, {}, {});
 
-        result = BuildSelect(unionPos, std::move(result), skipTake);
+        result = BuildSelect(opPos, std::move(result), skipTake);
     } else if (skipTake) {
-        result = BuildSelect(unionPos, std::move(result), skipTake);
+        result = BuildSelect(opPos, std::move(result), skipTake);
     }
 
     return result;
diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h
index c966b731e6f..431fa39d6cb 100644
--- a/yql/essentials/sql/v1/sql_ut_common.h
+++ b/yql/essentials/sql/v1/sql_ut_common.h
@@ -1805,7 +1805,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     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);
+        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}};
@@ -1816,7 +1816,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     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);
+        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}};
@@ -1827,7 +1827,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     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);
+        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}};
@@ -1838,7 +1838,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     Y_UNIT_TEST(IntersectAllPositionalTest) {
         NSQLTranslation::TTranslationSettings settings;
         settings.LangVer = 202503;
-        NYql::TAstParseResult res = SqlToYqlWithSettings("PRAGMA PositionalUnionAll; SELECT key FROM plato.Input INTERSECT ALL select subkey FROM plato.Input;", settings);
+        NYql::TAstParseResult res = SqlToYqlWithSettings("PRAGMA PositionalUnionAll; SELECT key FROM plato.Input INTERSECT ALL SELECT subkey FROM plato.Input;", settings);
         UNIT_ASSERT(res.Root);
 
         TWordCountHive elementStat = {{TString("IntersectAllPositional"), 0}};
@@ -1849,7 +1849,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     Y_UNIT_TEST(IntersectDistinctPositionalTest) {
         NSQLTranslation::TTranslationSettings settings;
         settings.LangVer = 202503;
-        NYql::TAstParseResult res = SqlToYqlWithSettings("PRAGMA PositionalUnionAll; SELECT key FROM plato.Input INTERSECT DISTINCT select subkey FROM plato.Input;", settings);
+        NYql::TAstParseResult res = SqlToYqlWithSettings("PRAGMA PositionalUnionAll; SELECT key FROM plato.Input INTERSECT DISTINCT SELECT subkey FROM plato.Input;", settings);
         UNIT_ASSERT(res.Root);
 
         TWordCountHive elementStat = {{TString("IntersectPositional"), 0}};
@@ -1857,12 +1857,29 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_VALUES_EQUAL(1, elementStat["IntersectPositional"]);
     }
 
+    Y_UNIT_TEST(MultipleIntersectTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT SELECT subkey FROM plato.Input INTERSECT SELECT subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(!res.Root);
+        UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:1:70: Error: Multiple usage of INTERSECT and EXCEPT is not implemented yet.\n");
+
+        res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT SELECT subkey FROM plato.Input INTERSECT ALL SELECT subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(!res.Root);
+        UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:1:80: Error: Multiple usage of INTERSECT and EXCEPT is not implemented yet.\n");
+
+        res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT SELECT subkey FROM plato.Input UNION SELECT subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(!res.Root);
+        UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:1:70: Error: Simultaneous usage of UNION, INTERSECT, and EXCEPT is not implemented yet.\n");
+    }
+
     // 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);
+        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}};
@@ -1873,7 +1890,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     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);
+        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}};
@@ -1884,7 +1901,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     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);
+        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}};
@@ -1892,10 +1909,10 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Except"]);
     }
 
-        Y_UNIT_TEST(ExceptAllPositionalTest) {
+    Y_UNIT_TEST(ExceptAllPositionalTest) {
         NSQLTranslation::TTranslationSettings settings;
         settings.LangVer = 202503;
-        NYql::TAstParseResult res = SqlToYqlWithSettings("PRAGMA PositionalUnionAll; SELECT key FROM plato.Input EXCEPT ALL select subkey FROM plato.Input;", settings);
+        NYql::TAstParseResult res = SqlToYqlWithSettings("PRAGMA PositionalUnionAll; SELECT key FROM plato.Input EXCEPT ALL SELECT subkey FROM plato.Input;", settings);
         UNIT_ASSERT(res.Root);
 
         TWordCountHive elementStat = {{TString("ExceptAllPositional"), 0}};
@@ -1906,7 +1923,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
     Y_UNIT_TEST(ExceptDistinctPositionalTest) {
         NSQLTranslation::TTranslationSettings settings;
         settings.LangVer = 202503;
-        NYql::TAstParseResult res = SqlToYqlWithSettings("PRAGMA PositionalUnionAll; SELECT key FROM plato.Input EXCEPT DISTINCT select subkey FROM plato.Input;", settings);
+        NYql::TAstParseResult res = SqlToYqlWithSettings("PRAGMA PositionalUnionAll; SELECT key FROM plato.Input EXCEPT DISTINCT SELECT subkey FROM plato.Input;", settings);
         UNIT_ASSERT(res.Root);
 
         TWordCountHive elementStat = {{TString("ExceptPositional"), 0}};
@@ -1914,6 +1931,23 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_VALUES_EQUAL(1, elementStat["ExceptPositional"]);
     }
 
+    Y_UNIT_TEST(MultipleExceptTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT SELECT subkey FROM plato.Input EXCEPT SELECT subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(!res.Root);
+        UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:1:67: Error: Multiple usage of INTERSECT and EXCEPT is not implemented yet.\n");
+
+        res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT SELECT subkey FROM plato.Input EXCEPT ALL SELECT subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(!res.Root);
+        UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:1:74: Error: Multiple usage of INTERSECT and EXCEPT is not implemented yet.\n");
+
+        res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT SELECT subkey FROM plato.Input UNION SELECT subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(!res.Root);
+        UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:1:67: Error: Simultaneous usage of UNION, INTERSECT, and EXCEPT is not implemented yet.\n");
+    }
+
 
     Y_UNIT_TEST(DeclareDecimalParameter) {
         NYql::TAstParseResult res = SqlToYql("declare $value as Decimal(22,9); select $value as cnt;");