YQL-17269: support PositionalUnionAll for INTERSECT and EXCEPT

commit_hash:4c68311d9e875b6643dd49aae1c385aace4ca978

20 files changed, 828 insertions, 31 deletions

diff --git a/yql/essentials/core/common_opt/yql_co_pgselect.cpp b/yql/essentials/core/common_opt/yql_co_pgselect.cpp
index 3142a60bbe1..b3d53dde5de 100644
--- a/yql/essentials/core/common_opt/yql_co_pgselect.cpp
+++ b/yql/essentials/core/common_opt/yql_co_pgselect.cpp
@@ -818,10 +818,10 @@ TExprNode::TPtr NormalizeColumnOrder(const TExprNode::TPtr& node, const TColumnO
         .Build();
 }
 
-TExprNode::TPtr ExpandPositionalUnionAll(const TExprNode& node, const TVector<TColumnOrder>& columnOrders,
+TExprNode::TPtr ExpandPositionalSelectOp(const TExprNode& node, const TVector<TColumnOrder>& columnOrders,
     TExprNode::TListType children, TExprContext& ctx, TOptimizeContext& optCtx)
 {
-    YQL_ENSURE(node.IsCallable({"UnionAllPositional", "UnionMergePositional"}));
+    YQL_ENSURE(node.IsCallable({"UnionAllPositional", "UnionMergePositional", "IntersectPositional", "IntersectAllPositional", "ExceptPositional", "ExceptAllPositional"}));
     auto targetColumnOrder = optCtx.Types->LookupColumnOrder(node);
     YQL_ENSURE(targetColumnOrder);
 
@@ -831,7 +831,9 @@ TExprNode::TPtr ExpandPositionalUnionAll(const TExprNode& node, const TVector<TC
         child = NormalizeColumnOrder(child, childColumnOrder, *targetColumnOrder, ctx);
     }
 
-    auto res = ctx.NewCallable(node.Pos(), node.IsCallable("UnionAllPositional") ? "UnionAll" : "UnionMerge", std::move(children));
+    TStringBuf callable = node.Content();
+    YQL_ENSURE(callable.ChopSuffix("Positional"));
+    auto res = ctx.NewCallable(node.Pos(), callable, std::move(children));
     return KeepColumnOrder(res, node, ctx, *optCtx.Types);
 }
 
diff --git a/yql/essentials/core/common_opt/yql_co_pgselect.h b/yql/essentials/core/common_opt/yql_co_pgselect.h
index 3cb84992af4..cd41f0deea8 100644
--- a/yql/essentials/core/common_opt/yql_co_pgselect.h
+++ b/yql/essentials/core/common_opt/yql_co_pgselect.h
@@ -9,7 +9,7 @@ TExprNode::TPtr ExpandPgSelect(const TExprNode::TPtr& node, TExprContext& ctx, T
 TExprNode::TPtr ExpandPgSelectSublink(const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& optCtx,
     ui32 subLinkId, const TExprNode::TListType& outerInputs, const TVector<TString>& outerInputAliases);
 
-TExprNode::TPtr ExpandPositionalUnionAll(const TExprNode& input, const TVector<TColumnOrder>& columnOrders,
+TExprNode::TPtr ExpandPositionalSelectOp(const TExprNode& input, const TVector<TColumnOrder>& columnOrders,
     TExprNode::TListType children, TExprContext& ctx, TOptimizeContext& optCtx);
 
 TExprNode::TPtr NormalizeColumnOrder(const TExprNode::TPtr& node, const TColumnOrder& sourceColumnOrder,
diff --git a/yql/essentials/core/common_opt/yql_co_simple1.cpp b/yql/essentials/core/common_opt/yql_co_simple1.cpp
index 59cd2da58f5..109fef5622a 100644
--- a/yql/essentials/core/common_opt/yql_co_simple1.cpp
+++ b/yql/essentials/core/common_opt/yql_co_simple1.cpp
@@ -6742,7 +6742,12 @@ void RegisterCoSimpleCallables1(TCallableOptimizerMap& map) {
         return node;
     };
 
-    map["UnionAllPositional"] = map["UnionMergePositional"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& optCtx) {
+    map["UnionAllPositional"] = \
+    map["UnionMergePositional"] = \
+    map["IntersectPositional"] = \
+    map["IntersectAllPositional"] = \
+    map["ExceptPositional"] = \
+    map["ExceptAllPositional"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& optCtx) {
         YQL_CLOG(DEBUG, Core) << "Expand " << node->Content();
         if (node->ChildrenSize() == 1) {
             return node->HeadPtr();
@@ -6755,7 +6760,7 @@ void RegisterCoSimpleCallables1(TCallableOptimizerMap& map) {
             columnOrders.push_back(*childColumnOrder);
         }
 
-        return ExpandPositionalUnionAll(*node, columnOrders, node->ChildrenList(), ctx, optCtx);
+        return ExpandPositionalSelectOp(*node, columnOrders, node->ChildrenList(), ctx, optCtx);
     };
 
     map["UnionPositional"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& /*optCtx*/) {
diff --git a/yql/essentials/core/type_ann/type_ann_core.cpp b/yql/essentials/core/type_ann/type_ann_core.cpp
index d74eb4996d8..33222ca9641 100644
--- a/yql/essentials/core/type_ann/type_ann_core.cpp
+++ b/yql/essentials/core/type_ann/type_ann_core.cpp
@@ -13222,9 +13222,13 @@ template <NKikimr::NUdf::EDataSlot DataSlot>
         ExtFunctions["JsonVariables"] = &JsonVariablesWrapper;
         ExtFunctions["AssumeColumnOrder"] = &AssumeColumnOrderWrapper;
         ExtFunctions["AssumeColumnOrderPartial"] = &AssumeColumnOrderWrapper;
-        ExtFunctions["UnionAllPositional"] = &UnionAllPositionalWrapper;
-        ExtFunctions["UnionPositional"] = &UnionAllPositionalWrapper;
-        ExtFunctions["UnionMergePositional"] = &UnionAllPositionalWrapper;
+        ExtFunctions["UnionAllPositional"] = &SelectOpPositionalWrapper;
+        ExtFunctions["UnionPositional"] = &SelectOpPositionalWrapper;
+        ExtFunctions["UnionMergePositional"] = &SelectOpPositionalWrapper;
+        ExtFunctions["IntersectAllPositional"] = &SelectOpPositionalWrapper;
+        ExtFunctions["IntersectPositional"] = &SelectOpPositionalWrapper;
+        ExtFunctions["ExceptAllPositional"] = &SelectOpPositionalWrapper;
+        ExtFunctions["ExceptPositional"] = &SelectOpPositionalWrapper;
         ExtFunctions["SafeCast"] = &CastWrapper<false>;
         ExtFunctions["StrictCast"] = &CastWrapper<true>;
         ExtFunctions["Version"] = &VersionWrapper;
diff --git a/yql/essentials/core/type_ann/type_ann_list.cpp b/yql/essentials/core/type_ann/type_ann_list.cpp
index 66bcedfbea1..c846f3e8abc 100644
--- a/yql/essentials/core/type_ann/type_ann_list.cpp
+++ b/yql/essentials/core/type_ann/type_ann_list.cpp
@@ -2900,7 +2900,7 @@ namespace {
         return IGraphTransformer::TStatus::Ok;
     }
 
-    IGraphTransformer::TStatus UnionAllPositionalWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TExtContext& ctx) {
+    IGraphTransformer::TStatus SelectOpPositionalWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TExtContext& ctx) {
         Y_UNUSED(output);
         if (!ctx.Types.OrderedColumns) {
             ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(input->Pos()), TStringBuilder()
diff --git a/yql/essentials/core/type_ann/type_ann_list.h b/yql/essentials/core/type_ann/type_ann_list.h
index e52a1a221be..49ff03bb5ec 100644
--- a/yql/essentials/core/type_ann/type_ann_list.h
+++ b/yql/essentials/core/type_ann/type_ann_list.h
@@ -64,7 +64,7 @@ namespace NTypeAnnImpl {
     IGraphTransformer::TStatus HasItemsWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
     IGraphTransformer::TStatus ExtendWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
     IGraphTransformer::TStatus SelectOpWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
-    IGraphTransformer::TStatus UnionAllPositionalWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TExtContext& ctx);
+    IGraphTransformer::TStatus SelectOpPositionalWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TExtContext& ctx);
     template <bool IsStrict>
     IGraphTransformer::TStatus ListExtendWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TExtContext& ctx);
     IGraphTransformer::TStatus ListUnionAllWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
diff --git a/yql/essentials/sql/v1/select.cpp b/yql/essentials/sql/v1/select.cpp
index a2207d9ef76..03a8483ce0c 100644
--- a/yql/essentials/sql/v1/select.cpp
+++ b/yql/essentials/sql/v1/select.cpp
@@ -2848,33 +2848,31 @@ public:
     }
 
     TNodePtr Build(TContext& ctx) override {
-        TPtr res;
+        TString op;
+
         if (Operator_ == "union") {
-            if (QuantifierAll_) {
-                if (ctx.EmitUnionMerge) {
-                    res = ctx.PositionalUnionAll ? Y("UnionMergePositional") : Y("UnionMerge");
-                } else {
-                    res = ctx.PositionalUnionAll ? Y("UnionAllPositional") : Y("UnionAll");
-                }
-            } else {
-                res = ctx.PositionalUnionAll ? Y("UnionPositional") : Y("Union");
-            }
+            op = "Union";
         } else if (Operator_ == "intersect") {
-            if (QuantifierAll_) {
-                res = Y("IntersectAll");
-            } else {
-                res = Y("Intersect");
-            }
+            op = "Intersect";
         } else if (Operator_ == "except") {
-            if (QuantifierAll_) {
-                res = Y("ExceptAll");
-            } else {
-                res = Y("Except");
-            }
+            op = "Except";
         } else {
             Y_ABORT("Invalid operator: %s", Operator_.c_str());
         }
 
+        if (QuantifierAll_) {
+            if (Operator_ != "union" || !ctx.EmitUnionMerge) {
+                op += "All";
+            } else {
+                op += "Merge";
+            }
+        }
+        if (ctx.PositionalUnionAll) {
+            op += "Positional";
+        }
+
+        TPtr res = Y(op);
+
         for (auto& s: Sources_) {
             auto input = s->Build(ctx);
             if (!input) {
diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h
index 223aaa52b3b..c966b731e6f 100644
--- a/yql/essentials/sql/v1/sql_ut_common.h
+++ b/yql/essentials/sql/v1/sql_ut_common.h
@@ -1835,6 +1835,28 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Intersect"]);
     }
 
+    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);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("IntersectAllPositional"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["IntersectAllPositional"]);
+    }
+
+    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);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("IntersectPositional"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["IntersectPositional"]);
+    }
+
     // EXCEPT
 
     Y_UNIT_TEST(ExceptAllTest) {
@@ -1870,6 +1892,28 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Except"]);
     }
 
+        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);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("ExceptAllPositional"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["ExceptAllPositional"]);
+    }
+
+    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);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("ExceptPositional"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["ExceptPositional"]);
+    }
+
 
     Y_UNIT_TEST(DeclareDecimalParameter) {
         NYql::TAstParseResult res = SqlToYql("declare $value as Decimal(22,9); select $value as cnt;");
diff --git a/yql/essentials/tests/sql/minirun/part0/canondata/result.json b/yql/essentials/tests/sql/minirun/part0/canondata/result.json
index faa30df1fe6..2204df8bbbd 100644
--- a/yql/essentials/tests/sql/minirun/part0/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part0/canondata/result.json
@@ -733,6 +733,20 @@
             "uri": "https://{canondata_backend}/995452/c22e7d9867fa56e2ee0c270fded39566aaa63a48/resource.tar.gz#test.test_flexible_types-group_by2-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_all_positional-default.txt-Debug]": [
+        {
+            "checksum": "03739015c1f4bef2b2c0ec1d4b990a6b",
+            "size": 2564,
+            "uri": "https://{canondata_backend}/1942415/0f21a583e42ec6120ebf4ae7adaf85924f5930c1/resource.tar.gz#test.test_intersect-intersect_all_positional-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_all_positional-default.txt-Results]": [
+        {
+            "checksum": "1737dd061908c24fa31d6dbd803743bc",
+            "size": 4456,
+            "uri": "https://{canondata_backend}/1942415/0f21a583e42ec6120ebf4ae7adaf85924f5930c1/resource.tar.gz#test.test_intersect-intersect_all_positional-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-eq_over_join_bad_rotate-default.txt-Debug]": [
         {
             "checksum": "75bc473eee49f48848cfac6902f99607",
diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
index 27aab7bf175..b195e552608 100644
--- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
@@ -440,6 +440,34 @@
             "uri": "https://{canondata_backend}/1881367/453c70881991a95154014d718c871d0afdcbf15e/resource.tar.gz#test.test_except-except_all-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_all_positional-default.txt-Debug]": [
+        {
+            "checksum": "54885f43cc523ba7a04a23168e45f8d6",
+            "size": 2554,
+            "uri": "https://{canondata_backend}/1942173/d09f4dbf08d7225751e84df7177a04a1bbd24999/resource.tar.gz#test.test_except-except_all_positional-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_all_positional-default.txt-Results]": [
+        {
+            "checksum": "3a2887548ce5198b33b138f56f787a08",
+            "size": 4897,
+            "uri": "https://{canondata_backend}/1942173/d09f4dbf08d7225751e84df7177a04a1bbd24999/resource.tar.gz#test.test_except-except_all_positional-default.txt-Results_/results.txt"
+        }
+    ],
+    "test.test[except-except_distinct_positional-default.txt-Debug]": [
+        {
+            "checksum": "3de3f71338fe7d59297e034965574df3",
+            "size": 2506,
+            "uri": "https://{canondata_backend}/1942173/d09f4dbf08d7225751e84df7177a04a1bbd24999/resource.tar.gz#test.test_except-except_distinct_positional-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_distinct_positional-default.txt-Results]": [
+        {
+            "checksum": "24f2ee28eff5c4c478d222cff5b11239",
+            "size": 4189,
+            "uri": "https://{canondata_backend}/1942173/d09f4dbf08d7225751e84df7177a04a1bbd24999/resource.tar.gz#test.test_except-except_distinct_positional-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-as_table_emptylist2-default.txt-Debug]": [
         {
             "checksum": "6424bb71484d775e2e9f4abcc3b852e6",
diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
index a62c1531662..1bd64378250 100644
--- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
@@ -718,6 +718,20 @@
             "uri": "https://{canondata_backend}/1917492/587e362e11daf3cf997a345b018213c9e8643313/resource.tar.gz#test.test_intersect-intersect_distinct_empty-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_distinct_positional-default.txt-Debug]": [
+        {
+            "checksum": "95eed1448086268db778baf93fae0ce0",
+            "size": 2670,
+            "uri": "https://{canondata_backend}/1924537/fb99eb38c73a9c3ab0458642f04a41cf634ea24f/resource.tar.gz#test.test_intersect-intersect_distinct_positional-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_distinct_positional-default.txt-Results]": [
+        {
+            "checksum": "a44871f5e2b846e64a9c2f9c3774ef5d",
+            "size": 3748,
+            "uri": "https://{canondata_backend}/1924537/fb99eb38c73a9c3ab0458642f04a41cf634ea24f/resource.tar.gz#test.test_intersect-intersect_distinct_positional-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-convert_check_key_mem-default.txt-Debug]": [
         {
             "checksum": "98813e43dffe60bd82a78af8e4a992ba",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index d533cfec5ce..32f341070c7 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -2351,6 +2351,13 @@
             "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_all_empty_/sql.yql"
         }
     ],
+    "test_sql2yql.test[except-except_all_positional]": [
+        {
+            "checksum": "322270cbab350c622fa2605923bb929c",
+            "size": 5405,
+            "uri": "https://{canondata_backend}/1871102/fab025477ce32c1aee6b4a70e141003f9f388399/resource.tar.gz#test_sql2yql.test_except-except_all_positional_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[except-except_distinct]": [
         {
             "checksum": "a73b03f897702173faee14fdea46dd85",
@@ -2365,6 +2372,13 @@
             "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_distinct_empty_/sql.yql"
         }
     ],
+    "test_sql2yql.test[except-except_distinct_positional]": [
+        {
+            "checksum": "7dfe70237345616da8a250f8b1d33939",
+            "size": 5396,
+            "uri": "https://{canondata_backend}/1871102/fab025477ce32c1aee6b4a70e141003f9f388399/resource.tar.gz#test_sql2yql.test_except-except_distinct_positional_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[expr-as_dict_dict_key]": [
         {
             "checksum": "9dd554b71081bd23a148f6d34f15d7ca",
@@ -3933,6 +3947,13 @@
             "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_all_empty_/sql.yql"
         }
     ],
+    "test_sql2yql.test[intersect-intersect_all_positional]": [
+        {
+            "checksum": "5cff5e1630c7b1e58b7d951f93ef9c84",
+            "size": 5414,
+            "uri": "https://{canondata_backend}/1942173/41ef0f591d8dd8abf2f8bbf6faf45f1ab3e54c20/resource.tar.gz#test_sql2yql.test_intersect-intersect_all_positional_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[intersect-intersect_distinct]": [
         {
             "checksum": "47b396e6add2c3a8d1e2efc3015a261f",
@@ -3947,6 +3968,13 @@
             "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_distinct_empty_/sql.yql"
         }
     ],
+    "test_sql2yql.test[intersect-intersect_distinct_positional]": [
+        {
+            "checksum": "1487d8142f572e5226a3f89b11a94073",
+            "size": 5405,
+            "uri": "https://{canondata_backend}/1942173/41ef0f591d8dd8abf2f8bbf6faf45f1ab3e54c20/resource.tar.gz#test_sql2yql.test_intersect-intersect_distinct_positional_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[join-convert_check_key_mem2]": [
         {
             "checksum": "e1f4ff23a4f2c04036a52d4a5b25529c",
@@ -9281,6 +9309,11 @@
             "uri": "file://test_sql_format.test_except-except_all_empty_/formatted.sql"
         }
     ],
+    "test_sql_format.test[except-except_all_positional]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_all_positional_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[except-except_distinct]": [
         {
             "uri": "file://test_sql_format.test_except-except_distinct_/formatted.sql"
@@ -9291,6 +9324,11 @@
             "uri": "file://test_sql_format.test_except-except_distinct_empty_/formatted.sql"
         }
     ],
+    "test_sql_format.test[except-except_distinct_positional]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_distinct_positional_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[expr-as_dict_dict_key]": [
         {
             "uri": "file://test_sql_format.test_expr-as_dict_dict_key_/formatted.sql"
@@ -10411,6 +10449,11 @@
             "uri": "file://test_sql_format.test_intersect-intersect_all_empty_/formatted.sql"
         }
     ],
+    "test_sql_format.test[intersect-intersect_all_positional]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_all_positional_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[intersect-intersect_distinct]": [
         {
             "uri": "file://test_sql_format.test_intersect-intersect_distinct_/formatted.sql"
@@ -10421,6 +10464,11 @@
             "uri": "file://test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql"
         }
     ],
+    "test_sql_format.test[intersect-intersect_distinct_positional]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_distinct_positional_/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_positional_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_positional_/formatted.sql
new file mode 100644
index 00000000000..14a0cca9c6b
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_positional_/formatted.sql
@@ -0,0 +1,80 @@
+PRAGMA PositionalUnionAll;
+
+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 (
+    w,
+    z
+);
+
+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 (
+    y,
+    x
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_positional_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_positional_/formatted.sql
new file mode 100644
index 00000000000..21b516e60c6
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_positional_/formatted.sql
@@ -0,0 +1,80 @@
+PRAGMA PositionalUnionAll;
+
+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 (
+    w,
+    z
+);
+
+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 (
+    y,
+    x
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_positional_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_positional_/formatted.sql
new file mode 100644
index 00000000000..683ef497001
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_positional_/formatted.sql
@@ -0,0 +1,80 @@
+PRAGMA PositionalUnionAll;
+
+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 (
+    w,
+    z
+);
+
+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 (
+    y,
+    x
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_positional_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_positional_/formatted.sql
new file mode 100644
index 00000000000..e5985ea3c26
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_positional_/formatted.sql
@@ -0,0 +1,80 @@
+PRAGMA PositionalUnionAll;
+
+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 (
+    w,
+    z
+);
+
+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 (
+    y,
+    x
+);
diff --git a/yql/essentials/tests/sql/suites/except/except_all_positional.sql b/yql/essentials/tests/sql/suites/except/except_all_positional.sql
new file mode 100644
index 00000000000..14a0cca9c6b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_all_positional.sql
@@ -0,0 +1,80 @@
+PRAGMA PositionalUnionAll;
+
+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 (
+    w,
+    z
+);
+
+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 (
+    y,
+    x
+);
diff --git a/yql/essentials/tests/sql/suites/except/except_distinct_positional.sql b/yql/essentials/tests/sql/suites/except/except_distinct_positional.sql
new file mode 100644
index 00000000000..21b516e60c6
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_distinct_positional.sql
@@ -0,0 +1,80 @@
+PRAGMA PositionalUnionAll;
+
+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 (
+    w,
+    z
+);
+
+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 (
+    y,
+    x
+);
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_all_positional.sql b/yql/essentials/tests/sql/suites/intersect/intersect_all_positional.sql
new file mode 100644
index 00000000000..683ef497001
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_all_positional.sql
@@ -0,0 +1,80 @@
+PRAGMA PositionalUnionAll;
+
+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 (
+    w,
+    z
+);
+
+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 (
+    y,
+    x
+);
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_distinct_positional.sql b/yql/essentials/tests/sql/suites/intersect/intersect_distinct_positional.sql
new file mode 100644
index 00000000000..9b9ca1aad99
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_distinct_positional.sql
@@ -0,0 +1,80 @@
+PRAGMA PositionalUnionAll;
+
+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 (
+    w,
+    z
+);
+
+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 (
+   y,
+   x
+);