YQL-14728 fix between and move out of sql parser

ref:74b7b5232dadb94201736f478c89da73ca7e0da7

7 files changed, 140 insertions, 29 deletions

diff --git a/ydb/library/yql/core/common_opt/yql_co_pgselect.cpp b/ydb/library/yql/core/common_opt/yql_co_pgselect.cpp
index 4147528ba17..cab0fe09c6a 100644
--- a/ydb/library/yql/core/common_opt/yql_co_pgselect.cpp
+++ b/ydb/library/yql/core/common_opt/yql_co_pgselect.cpp
@@ -1185,4 +1185,63 @@ TExprNode::TPtr ExpandPgIn(const TExprNode::TPtr& node, TExprContext& ctx, TOpti
         .Build();
 }
 
+TExprNode::TPtr ExpandPgBetween(const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& optCtx) {
+    Y_UNUSED(optCtx);
+    const bool isSym = node->IsCallable("PgBetweenSym");
+    auto input = node->ChildPtr(0);
+    auto begin = node->ChildPtr(1);
+    auto end = node->ChildPtr(2);
+    if (isSym) {
+        auto swap = ctx.Builder(node->Pos())
+            .Callable("PgOp")
+                .Atom(0, "<")
+                .Add(1, end)
+                .Add(2, begin)
+            .Seal()
+            .Build();
+
+        auto swapper = [&](auto x, auto y) {
+            return ctx.Builder(node->Pos())
+                .Callable("IfPresent")
+                    .Callable(0, "FromPg")
+                        .Add(0, swap)
+                    .Seal()
+                    .Lambda(1)
+                        .Param("unwrapped")
+                        .Callable("If")
+                            .Arg(0, "unwrapped")
+                            .Add(1, y)
+                            .Add(2, x)
+                        .Seal()
+                    .Seal()
+                    .Callable(2, "Null")
+                    .Seal()
+                .Seal()
+                .Build();
+        };
+
+        // swap: null->null, false->begin, true->end
+        auto newBegin = swapper(begin, end);
+        // swap: null->null, false->end, true->begin
+        auto newEnd = swapper(end, begin);
+        begin = newBegin;
+        end = newEnd;
+    }
+
+    return ctx.Builder(node->Pos())
+        .Callable("PgAnd")
+            .Callable(0, "PgOp")
+                .Atom(0, ">=")
+                .Add(1, input)
+                .Add(2, begin)
+            .Seal()
+            .Callable(1, "PgOp")
+                .Atom(0, "<=")
+                .Add(1, input)
+                .Add(2, end)
+            .Seal()
+        .Seal()
+        .Build();
+}
+
 } // namespace NYql
diff --git a/ydb/library/yql/core/common_opt/yql_co_pgselect.h b/ydb/library/yql/core/common_opt/yql_co_pgselect.h
index 24baabda5ba..c4afe7b0b86 100644
--- a/ydb/library/yql/core/common_opt/yql_co_pgselect.h
+++ b/ydb/library/yql/core/common_opt/yql_co_pgselect.h
@@ -13,4 +13,6 @@ TExprNode::TPtr ExpandPgLike(const TExprNode::TPtr& node, TExprContext& ctx, TOp
 
 TExprNode::TPtr ExpandPgIn(const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& optCtx);
 
+TExprNode::TPtr ExpandPgBetween(const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& optCtx);
+
 } // namespace NYql
diff --git a/ydb/library/yql/core/common_opt/yql_co_simple1.cpp b/ydb/library/yql/core/common_opt/yql_co_simple1.cpp
index 97873f16c68..6bd71770535 100644
--- a/ydb/library/yql/core/common_opt/yql_co_simple1.cpp
+++ b/ydb/library/yql/core/common_opt/yql_co_simple1.cpp
@@ -6367,6 +6367,9 @@ void RegisterCoSimpleCallables1(TCallableOptimizerMap& map) {
 
     map["PgIn"] = &ExpandPgIn;
 
+    map["PgBetween"] = &ExpandPgBetween;
+    map["PgBetweenSym"] = &ExpandPgBetween;
+
     map["SqlColumnOrType"] = map["SqlPlainColumnOrType"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& /*optCtx*/) {
         YQL_CLOG(DEBUG, Core) << "Decay of never inspected " << node->Content();
         return ctx.NewCallable(node->Pos(), "Error", { ExpandType(node->Pos(), *node->GetTypeAnn(), ctx) });
diff --git a/ydb/library/yql/core/type_ann/type_ann_core.cpp b/ydb/library/yql/core/type_ann/type_ann_core.cpp
index 3a50d6b1bc9..128f1e6ef1c 100644
--- a/ydb/library/yql/core/type_ann/type_ann_core.cpp
+++ b/ydb/library/yql/core/type_ann/type_ann_core.cpp
@@ -11287,6 +11287,8 @@ template <NKikimr::NUdf::EDataSlot DataSlot>
         Functions["PgLike"] = &PgLikeWrapper;
         Functions["PgILike"] = &PgLikeWrapper;
         Functions["PgIn"] = &PgInWrapper;
+        Functions["PgBetween"] = &PgBetweenWrapper;
+        Functions["PgBetweenSym"] = &PgBetweenWrapper;
 
         Functions["AutoDemuxList"] = &AutoDemuxListWrapper;
         Functions["AggrCountInit"] = &AggrCountInitWrapper;
diff --git a/ydb/library/yql/core/type_ann/type_ann_pg.cpp b/ydb/library/yql/core/type_ann/type_ann_pg.cpp
index fb95bc8acde..f4f8965ee5c 100644
--- a/ydb/library/yql/core/type_ann/type_ann_pg.cpp
+++ b/ydb/library/yql/core/type_ann/type_ann_pg.cpp
@@ -2815,5 +2815,58 @@ IGraphTransformer::TStatus PgInWrapper(const TExprNode::TPtr& input, TExprNode::
     return IGraphTransformer::TStatus::Ok;
 }
 
+IGraphTransformer::TStatus PgBetweenWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx) {
+    Y_UNUSED(output);
+    if (!EnsureArgsCount(*input, 3, ctx.Expr)) {
+        return IGraphTransformer::TStatus::Error;
+    }
+
+    TVector<ui32> argTypes;
+    bool needRetype = false;
+    for (ui32 i = 0; i < input->ChildrenSize(); ++i) {
+        auto type = input->Child(i)->GetTypeAnn();
+        ui32 argType;
+        bool convertToPg;
+        if (!ExtractPgType(type, argType, convertToPg, input->Child(i)->Pos(), ctx.Expr)) {
+            return IGraphTransformer::TStatus::Error;
+        }
+
+        if (convertToPg) {
+            input->ChildRef(i) = ctx.Expr.NewCallable(input->Child(i)->Pos(), "ToPg", { input->ChildPtr(i) });
+            needRetype = true;
+        }
+
+        argTypes.push_back(argType);
+    }
+
+    if (needRetype) {
+        return IGraphTransformer::TStatus::Repeat;
+    }
+
+    auto elemType = argTypes.front();
+    auto elemTypeAnn = input->Child(0)->GetTypeAnn();
+    for (ui32 i = 1; i < argTypes.size(); ++i) {
+        if (elemType == 0) {
+            elemType = argTypes[i];
+            elemTypeAnn = input->Child(i)->GetTypeAnn();
+        } else if (argTypes[i] != 0 && argTypes[i] != elemType) {
+            ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(input->Pos()),
+                TStringBuilder() << "Mismatch of type of between elements: " <<
+                NPg::LookupType(elemType).Name << " and " << NPg::LookupType(argTypes[i]).Name));
+            return IGraphTransformer::TStatus::Error;
+        }
+    }
+
+    if (elemType && !elemTypeAnn->IsComparable()) {
+        ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(input->Pos()),
+            TStringBuilder() << "Cannot compare items of type: " << NPg::LookupType(elemType).Name));
+        return IGraphTransformer::TStatus::Error;
+    }
+
+    auto result = ctx.Expr.MakeType<TPgExprType>(NPg::LookupType("bool").TypeId);
+    input->SetTypeAnn(result);
+    return IGraphTransformer::TStatus::Ok;
+}
+
 } // namespace NTypeAnnImpl
 }
diff --git a/ydb/library/yql/core/type_ann/type_ann_pg.h b/ydb/library/yql/core/type_ann/type_ann_pg.h
index 08fdd3296ea..20644e369e1 100644
--- a/ydb/library/yql/core/type_ann/type_ann_pg.h
+++ b/ydb/library/yql/core/type_ann/type_ann_pg.h
@@ -34,6 +34,7 @@ IGraphTransformer::TStatus PgArrayWrapper(const TExprNode::TPtr& input, TExprNod
 IGraphTransformer::TStatus PgTypeModWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
 IGraphTransformer::TStatus PgLikeWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
 IGraphTransformer::TStatus PgInWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
+IGraphTransformer::TStatus PgBetweenWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
 
 } // namespace NTypeAnnImpl
 } // namespace NYql
diff --git a/ydb/library/yql/sql/pg/pg_sql.cpp b/ydb/library/yql/sql/pg/pg_sql.cpp
index c79b5cac3fc..da7d9833476 100644
--- a/ydb/library/yql/sql/pg/pg_sql.cpp
+++ b/ydb/library/yql/sql/pg/pg_sql.cpp
@@ -2041,41 +2041,32 @@ public:
             return nullptr;
         }
 
-        if (IsIn({ AEXPR_BETWEEN_SYM, AEXPR_NOT_BETWEEN_SYM }, value->kind)) {
-            auto minmax = L(A("If"),
-                L(A("Coalesce"),
-                    L(A("FromPg"),
-                        L(A("PgOp"), QA("<"), rbhs, rehs)
-                    ),
-                    L(A("Bool"), QA("false"))
-                ), QL(rbhs, rehs), QL(rehs, rbhs)
-            );
-
-            rbhs = L(A("Nth"), minmax, QA("0"));
-            rehs = L(A("Nth"), minmax, QA("1"));
-        }
-
-        switch (value->kind) {
+        A_Expr_Kind kind = value->kind;
+        bool inverse = false;
+        if (kind == AEXPR_NOT_BETWEEN) {
+            inverse = true;
+            kind = AEXPR_BETWEEN;
+        } else if (kind == AEXPR_NOT_BETWEEN_SYM) {
+            inverse = true;
+            kind = AEXPR_BETWEEN_SYM;
+        }
+
+        TAstNode* ret;
+        switch (kind) {
         case AEXPR_BETWEEN:
-            [[fallthrough]];
         case AEXPR_BETWEEN_SYM:
-        {
-            auto gte = L(A("PgOp"), QA(">="), lhs, rbhs);
-            auto lte = L(A("PgOp"), QA("<="), lhs, rehs);
-            return L(A("PgAnd"), gte, lte);
-        }
-        case AEXPR_NOT_BETWEEN:
-            [[fallthrough]];
-        case AEXPR_NOT_BETWEEN_SYM:
-        {
-            auto lt = L(A("PgOp"), QA("<"), lhs, rbhs);
-            auto gt = L(A("PgOp"), QA(">"), lhs, rehs);
-            return L(A("PgOr"), lt, gt);
-        }
+            ret = L(A(kind == AEXPR_BETWEEN ? "PgBetween" : "PgBetweenSym"), lhs, rbhs, rehs);
+            break;
         default:
             AddError(TStringBuilder() << "BETWEEN kind unsupported value: " << (int)value->kind);
             return nullptr;
         }
+
+        if (inverse) {
+            ret = L(A("PgNot"), ret);
+        }
+
+        return ret;
     }
 
     TAstNode* ParseAExpr(const A_Expr* value, const TExprSettings& settings) {