add BETWEEN SYMMETRIC

canonize yt_native tests

add tests

add BETWEEN SYMMETRIC/ASYMMETRIC to parser

13 files changed, 456 insertions, 29 deletions

diff --git a/ydb/docs/ru/core/yql/reference/yql-core/syntax/_includes/expressions/between.md b/ydb/docs/ru/core/yql/reference/yql-core/syntax/_includes/expressions/between.md
index a8c1e3bc3d2..de2966da92d 100644
--- a/ydb/docs/ru/core/yql/reference/yql-core/syntax/_includes/expressions/between.md
+++ b/ydb/docs/ru/core/yql/reference/yql-core/syntax/_includes/expressions/between.md
@@ -1,6 +1,10 @@
 ## BETWEEN {#between}
 
-Проверка на вхождение значения в диапазон. Эквивалентно паре условий с `>=` и `<=`, то есть границы диапазона включаются. Может использоваться с префиксом  `NOT` для инверсии.
+Проверка на вхождение значения в диапазон. Cинтаксис: `expr [NOT] BETWEEN [ASYMMETRIC | SYMMETRIC] expr AND expr`. 
+* `BETWEEN` и `BETWEEN ASYMMETRIC` эквивалентны, `x BETWEEN a AND b` эквивалентно `a <= x AND x <= b`.
+* `BETWEEN SYMMETRIC` автоматически переставляет аргументы местами так чтобы диапазон получился непустым, 
+`x BETWEEN SYMMETRIC a AND b` эквивалентно `(x BETWEEN a AND b) OR (x BETWEEN b AND a)`.
+* `NOT` инвертирует результат проверки.
 
 **Примеры**
 
@@ -9,3 +13,7 @@ SELECT * FROM my_table
 WHERE key BETWEEN 10 AND 20;
 ```
 
+``` yql
+SELECT * FROM my_table
+WHERE key NOT BETWEEN SYMMETRIC 20 AND 10;
+```
diff --git a/ydb/library/yql/sql/v1/SQLv1.g.in b/ydb/library/yql/sql/v1/SQLv1.g.in
index d23bf0f090c..294f1b57251 100644
--- a/ydb/library/yql/sql/v1/SQLv1.g.in
+++ b/ydb/library/yql/sql/v1/SQLv1.g.in
@@ -76,7 +76,7 @@ cond_expr:
     NOT? match_op eq_subexpr (ESCAPE eq_subexpr)?
   | NOT? IN COMPACT? in_expr
   | (ISNULL | NOTNULL | IS NULL | (IS)? NOT NULL)
-  | NOT? BETWEEN eq_subexpr AND eq_subexpr
+  | NOT? BETWEEN (SYMMETRIC | ASYMMETRIC)? eq_subexpr AND eq_subexpr
   | ((EQUALS | EQUALS2 | NOT_EQUALS | NOT_EQUALS2 | distinct_from_op) eq_subexpr)+ /* order of the eq subexpressions is reversed! */
 ;
 
@@ -1036,7 +1036,8 @@ keyword:
 ;
 
 keyword_expr_uncompat:
-    BETWEEN
+    ASYMMETRIC
+  | BETWEEN
   | BITCAST
   | CASE
   | CAST
@@ -1060,6 +1061,7 @@ keyword_expr_uncompat:
   | RETURN
   | ROLLUP
   | SELECT
+  | SYMMETRIC
   | UNBOUNDED
   | WHEN
   | WHERE
@@ -1472,6 +1474,7 @@ ARRAY: A R R A Y;
 AS: A S;
 ASC: A S C;
 ASSUME: A S S U M E;
+ASYMMETRIC: A S Y M M E T R I C;
 ASYNC: A S Y N C;
 ATTACH: A T T A C H;
 ATTRIBUTES: A T T R I B U T E S;
@@ -1689,6 +1692,7 @@ STRUCT: S T R U C T;
 SUBQUERY: S U B Q U E R Y;
 SUBSET: S U B S E T;
 SYMBOLS: S Y M B O L S;
+SYMMETRIC: S Y M M E T R I C;
 SYNC: S Y N C;
 SYSTEM: S Y S T E M;
 TABLE: T A B L E;
diff --git a/ydb/library/yql/sql/v1/node.cpp b/ydb/library/yql/sql/v1/node.cpp
index 175d2aadc32..e8fe845cd6e 100644
--- a/ydb/library/yql/sql/v1/node.cpp
+++ b/ydb/library/yql/sql/v1/node.cpp
@@ -2614,7 +2614,7 @@ TNodePtr BuildBinaryOp(TContext& ctx, TPosition pos, const TString& opName, TNod
         const bool bothArgNull = a->IsNull() && b->IsNull();
         const bool oneArgNull  = a->IsNull() || b->IsNull();
 
-        if (bothArgNull || (opName != "Or" && oneArgNull)) {
+        if (bothArgNull || (oneArgNull && opName != "Or" && opName != "And")) {
             ctx.Warning(pos, TIssuesIds::YQL_OPERATION_WILL_RETURN_NULL) << "Binary operation "
             << opName.substr(0, opName.Size() - 7 * opName.EndsWith("MayWarn"))
             << " will return NULL here";
@@ -2624,6 +2624,14 @@ TNodePtr BuildBinaryOp(TContext& ctx, TPosition pos, const TString& opName, TNod
     return new TBinaryOpNode(pos, opName, a, b);
 }
 
+TNodePtr BuildBinaryOpRaw(TPosition pos, const TString& opName, TNodePtr a, TNodePtr b) {
+    if (!a || !b) {
+        return nullptr;
+    }
+
+    return new TBinaryOpNode(pos, opName, a, b);
+}
+
 class TCalcOverWindow final: public INode {
 public:
     TCalcOverWindow(TPosition pos, const TString& windowName, TNodePtr node)
diff --git a/ydb/library/yql/sql/v1/node.h b/ydb/library/yql/sql/v1/node.h
index 1b92496274e..865ecd231d8 100644
--- a/ydb/library/yql/sql/v1/node.h
+++ b/ydb/library/yql/sql/v1/node.h
@@ -1176,6 +1176,7 @@ namespace NSQLTranslationV1 {
     TNodePtr BuildSimpleType(TContext& ctx, TPosition pos, const TString& typeName, bool dataOnly);
     TNodePtr BuildIsNullOp(TPosition pos, TNodePtr a);
     TNodePtr BuildBinaryOp(TContext& ctx, TPosition pos, const TString& opName, TNodePtr a, TNodePtr b);
+    TNodePtr BuildBinaryOpRaw(TPosition pos, const TString& opName, TNodePtr a, TNodePtr b);
 
     TNodePtr BuildCalcOverWindow(TPosition pos, const TString& windowName, TNodePtr call);
     TNodePtr BuildYsonOptionsNode(TPosition pos, bool autoConvert, bool strict, bool fastYson);
diff --git a/ydb/library/yql/sql/v1/sql_expression.cpp b/ydb/library/yql/sql/v1/sql_expression.cpp
index be0ee43c816..74a7a72a18b 100644
--- a/ydb/library/yql/sql/v1/sql_expression.cpp
+++ b/ydb/library/yql/sql/v1/sql_expression.cpp
@@ -1905,24 +1905,51 @@ TNodePtr TSqlExpression::SubExpr(const TRule_xor_subexpr& node, const TTrailingQ
             }
             case TRule_cond_expr::kAltCondExpr4: {
                 auto alt = cond.GetAlt_cond_expr4();
-                if (alt.HasBlock1()) {
-                    Ctx.IncrementMonCounter("sql_features", "NotBetween");
-                    return BuildBinaryOp(
-                        Ctx,
-                        pos,
-                        "Or",
-                        BuildBinaryOp(Ctx, pos, "<", res, SubExpr(alt.GetRule_eq_subexpr3(), {})),
-                        BuildBinaryOp(Ctx, pos, ">", res, SubExpr(alt.GetRule_eq_subexpr5(), tail))
+                const bool symmetric = alt.HasBlock3() && alt.GetBlock3().GetToken1().GetId() == SQLv1LexerTokens::TOKEN_SYMMETRIC;
+                const bool negation = alt.HasBlock1();
+                TNodePtr left = SubExpr(alt.GetRule_eq_subexpr4(), {});
+                TNodePtr right = SubExpr(alt.GetRule_eq_subexpr6(), tail);
+                if (!left || !right) {
+                    return {};
+                }
+
+                const bool bothArgNull = left->IsNull() && right->IsNull();
+                const bool oneArgNull  = left->IsNull() || right->IsNull();
+
+                if (res->IsNull() || bothArgNull || (symmetric && oneArgNull)) {
+                    Ctx.Warning(pos, TIssuesIds::YQL_OPERATION_WILL_RETURN_NULL)
+                    << "BETWEEN operation will return NULL here";
+                }
+
+                auto buildSubexpr = [&](const TNodePtr& left, const TNodePtr& right) {
+                    if (negation) {
+                        return BuildBinaryOpRaw(
+                            pos,
+                            "Or",
+                            BuildBinaryOpRaw(pos, "<", res, left),
+                            BuildBinaryOpRaw(pos, ">", res, right)
+                        );
+                    } else {
+                        return BuildBinaryOpRaw(
+                            pos,
+                            "And",
+                            BuildBinaryOpRaw(pos, ">=", res, left),
+                            BuildBinaryOpRaw(pos, "<=", res, right)
+                        );
+                    }
+                };
+
+                if (symmetric) {
+                    Ctx.IncrementMonCounter("sql_features", negation? "NotBetweenSymmetric" : "BetweenSymmetric");
+                    return BuildBinaryOpRaw(
+                        pos, 
+                        negation? "And" : "Or", 
+                        buildSubexpr(left, right), 
+                        buildSubexpr(right, left)
                     );
                 } else {
-                    Ctx.IncrementMonCounter("sql_features", "Between");
-                    return BuildBinaryOp(
-                        Ctx,
-                        pos,
-                        "And",
-                        BuildBinaryOp(Ctx, pos, ">=", res, SubExpr(alt.GetRule_eq_subexpr3(), {})),
-                        BuildBinaryOp(Ctx, pos, "<=", res, SubExpr(alt.GetRule_eq_subexpr5(), tail))
-                    );
+                    Ctx.IncrementMonCounter("sql_features", negation? "NotBetween" : "Between");
+                    return buildSubexpr(left, right);
                 }
             }
             case TRule_cond_expr::kAltCondExpr5: {
diff --git a/ydb/library/yql/sql/v1/sql_ut.cpp b/ydb/library/yql/sql/v1/sql_ut.cpp
index a775cedb9c0..b244d2c3c0a 100644
--- a/ydb/library/yql/sql/v1/sql_ut.cpp
+++ b/ydb/library/yql/sql/v1/sql_ut.cpp
@@ -39,13 +39,13 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
 
     Y_UNIT_TEST(TokensAsColumnName) { //id_expr
         auto failed = ValidateTokens({
-                "ALL", "ANY", "AS", "ASSUME", "AUTOMAP", "BETWEEN", "BITCAST",
+                "ALL", "ANY", "AS", "ASSUME", "ASYMMETRIC", "AUTOMAP", "BETWEEN", "BITCAST",
                 "CALLABLE", "CASE", "CAST", "CUBE", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP",
                 "DICT", "DISTINCT", "ENUM", "ERASE", "EXCEPT", "EXISTS", "FLOW", "FROM", "FULL", "GLOBAL",
                 "HAVING", "HOP", "INTERSECT", "JSON_EXISTS", "JSON_QUERY", "JSON_VALUE", "LIMIT", "LIST", "LOCAL",
                 "NOT", "OPTIONAL", "PROCESS", "REDUCE", "REPEATABLE", "RESOURCE", "RETURN", "ROLLUP",
-                "SELECT", "SET", "STREAM", "STRUCT", "TAGGED", "TUPLE", "UNBOUNDED", "UNION", "VARIANT",
-                "WHEN", "WHERE", "WINDOW", "WITHOUT"
+                "SELECT", "SET", "STREAM", "STRUCT", "SYMMETRIC", "TAGGED", "TUPLE", "UNBOUNDED",
+                "UNION", "VARIANT", "WHEN", "WHERE", "WINDOW", "WITHOUT"
             },
             [](const TString& token){
                 TStringBuilder req;
@@ -58,13 +58,13 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
 
     Y_UNIT_TEST(TokensAsWithoutColumnName) { //id_without
         auto failed = ValidateTokens({
-                "ALL", "AS", "ASSUME", "AUTOMAP", "BETWEEN", "BITCAST",
+                "ALL", "AS", "ASSUME", "ASYMMETRIC", "AUTOMAP", "BETWEEN", "BITCAST",
                 "CALLABLE", "CASE", "CAST", "CUBE", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP",
                 "DICT", "DISTINCT", "EMPTY_ACTION", "ENUM", "EXCEPT", "EXISTS", "FALSE", "FLOW", "FROM", "FULL", "GLOBAL",
                 "HAVING", "HOP", "INTERSECT", "JSON_EXISTS", "JSON_QUERY", "JSON_VALUE", "LIMIT", "LIST", "LOCAL",
                 "NOT", "NULL", "OPTIONAL", "PROCESS", "REDUCE", "REPEATABLE", "RESOURCE", "RETURN", "ROLLUP",
-                "SELECT", "SET", "STRUCT", "TAGGED", "TRUE", "TUPLE", "UNBOUNDED", "UNION", "VARIANT",
-                "WHEN", "WHERE", "WINDOW", "WITHOUT"
+                "SELECT", "SET", "STRUCT", "SYMMETRIC", "TAGGED", "TRUE", "TUPLE", "UNBOUNDED",
+                "UNION", "VARIANT", "WHEN", "WHERE", "WINDOW", "WITHOUT"
              },
              [](const TString& token){
                  TStringBuilder req;
@@ -164,13 +164,13 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
 
     Y_UNIT_TEST(TokensAsIdExprIn) { //id_expr_in
         auto failed = ValidateTokens({
-                "ALL", "ANY", "AS", "ASSUME", "AUTOMAP", "BETWEEN", "BITCAST",
+                "ALL", "ANY", "AS", "ASSUME", "ASYMMETRIC", "AUTOMAP", "BETWEEN", "BITCAST",
                 "CALLABLE", "CASE", "CAST", "COMPACT", "CUBE", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP",
                 "DICT", "DISTINCT", "ENUM", "ERASE", "EXCEPT", "EXISTS", "FLOW", "FROM", "FULL", "GLOBAL",
                 "HAVING", "HOP", "INTERSECT", "JSON_EXISTS", "JSON_QUERY", "JSON_VALUE", "LIMIT", "LIST", "LOCAL",
                 "NOT", "OPTIONAL", "PROCESS", "REDUCE", "REPEATABLE", "RESOURCE", "RETURN", "ROLLUP",
-                "SELECT", "SET", "STREAM", "STRUCT", "TAGGED", "TUPLE", "UNBOUNDED", "UNION", "VARIANT",
-                "WHEN", "WHERE", "WINDOW", "WITHOUT"
+                "SELECT", "SET", "STREAM", "STRUCT", "SYMMETRIC", "TAGGED", "TUPLE", "UNBOUNDED",
+                "UNION", "VARIANT", "WHEN", "WHERE", "WINDOW", "WITHOUT"
             },
             [](const TString& token){
                 TStringBuilder req;
@@ -2485,6 +2485,13 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT(SqlToYql("USE plato; CREATE TABLE tableName (Key Uint32, PRIMARY KEY (Key),);").IsOk());
         UNIT_ASSERT(SqlToYql("USE plato; CREATE TABLE tableName (Key Uint32,);").IsOk());
     }
+
+    Y_UNIT_TEST(BetweenSymmetric) {
+        UNIT_ASSERT(SqlToYql("select 3 between symmetric 5 and 4;").IsOk());
+        UNIT_ASSERT(SqlToYql("select 3 between asymmetric 5 and 4;").IsOk());
+        UNIT_ASSERT(SqlToYql("use plato; select key between symmetric and and and from Input;").IsOk());
+        UNIT_ASSERT(SqlToYql("use plato; select key between and and and from Input;").IsOk());
+    }
 }
 
 Y_UNIT_TEST_SUITE(ExternalFunction) {
diff --git a/ydb/library/yql/tests/sql/dq_file/part5/canondata/result.json b/ydb/library/yql/tests/sql/dq_file/part5/canondata/result.json
index b099532a2f9..40ba37c08db 100644
--- a/ydb/library/yql/tests/sql/dq_file/part5/canondata/result.json
+++ b/ydb/library/yql/tests/sql/dq_file/part5/canondata/result.json
@@ -1690,6 +1690,35 @@
         }
     ],
     "test.test[expr-as_variant_enum-default.txt-Results]": [],
+    "test.test[expr-between-default.txt-Analyze]": [
+        {
+            "checksum": "b2a2eb5d6b0a138ee924c128fc7738ef",
+            "size": 1331,
+            "uri": "https://{canondata_backend}/1600758/6d0d27fad1cf46a244c609129a6009834bc45a9a/resource.tar.gz#test.test_expr-between-default.txt-Analyze_/plan.txt"
+        },
+        {
+            "uri": "file://test.test_expr-between-default.txt-Analyze_/extracted"
+        }
+    ],
+    "test.test[expr-between-default.txt-Debug]": [
+        {
+            "checksum": "0fa3e7823acebf6997b1104de6d5ee73",
+            "size": 1771,
+            "uri": "https://{canondata_backend}/1900335/169589e252b4bc7ea01c1412bbd59cfc555b685b/resource.tar.gz#test.test_expr-between-default.txt-Debug_/opt.yql_patched"
+        }
+    ],
+    "test.test[expr-between-default.txt-Plan]": [
+        {
+            "checksum": "b2a2eb5d6b0a138ee924c128fc7738ef",
+            "size": 1331,
+            "uri": "https://{canondata_backend}/1600758/6d0d27fad1cf46a244c609129a6009834bc45a9a/resource.tar.gz#test.test_expr-between-default.txt-Plan_/plan.txt"
+        }
+    ],
+    "test.test[expr-between-default.txt-Results]": [
+        {
+            "uri": "file://test.test_expr-between-default.txt-Results_/extracted"
+        }
+    ],
     "test.test[expr-cast_variant-default.txt-Analyze]": [
         {
             "checksum": "a3b64a2cf9903b3868a2dd88a18fc46e",
diff --git a/ydb/library/yql/tests/sql/dq_file/part5/canondata/test.test_expr-between-default.txt-Analyze_/extracted b/ydb/library/yql/tests/sql/dq_file/part5/canondata/test.test_expr-between-default.txt-Analyze_/extracted
new file mode 100644
index 00000000000..92e97cc83da
--- /dev/null
+++ b/ydb/library/yql/tests/sql/dq_file/part5/canondata/test.test_expr-between-default.txt-Analyze_/extracted
@@ -0,0 +1,114 @@
+<tmp_path>/program.sql:<main>: Warning: Parse Sql
+
+    <tmp_path>/program.sql:<main>:7:5: Warning: BETWEEN operation will return NULL here
+    	    null between 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:11:5: Warning: BETWEEN operation will return NULL here
+    	    1 between null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:12:5: Warning: BETWEEN operation will return NULL here
+    	    null between null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:20:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:24:5: Warning: BETWEEN operation will return NULL here
+    	    1 between asymmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:25:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:34:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:35:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:36:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 4 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:37:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 0 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:38:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:39:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric null and null;
+	    ^
+<tmp_path>/program.sql:<main>: Warning: Parse Sql
+
+    <tmp_path>/program.sql:<main>:7:5: Warning: BETWEEN operation will return NULL here
+    	    null between 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:11:5: Warning: BETWEEN operation will return NULL here
+    	    1 between null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:12:5: Warning: BETWEEN operation will return NULL here
+    	    null between null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:20:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:24:5: Warning: BETWEEN operation will return NULL here
+    	    1 between asymmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:25:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:34:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:35:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:36:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 4 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:37:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 0 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:38:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:39:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric null and null;
+	    ^
+<tmp_path>/program.sql:<main>: Warning: Parse Sql
+
+    <tmp_path>/program.sql:<main>:7:5: Warning: BETWEEN operation will return NULL here
+    	    null between 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:11:5: Warning: BETWEEN operation will return NULL here
+    	    1 between null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:12:5: Warning: BETWEEN operation will return NULL here
+    	    null between null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:20:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:24:5: Warning: BETWEEN operation will return NULL here
+    	    1 between asymmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:25:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:34:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:35:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:36:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 4 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:37:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 0 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:38:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:39:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric null and null;
+	    ^
\ No newline at end of file
diff --git a/ydb/library/yql/tests/sql/dq_file/part5/canondata/test.test_expr-between-default.txt-Results_/extracted b/ydb/library/yql/tests/sql/dq_file/part5/canondata/test.test_expr-between-default.txt-Results_/extracted
new file mode 100644
index 00000000000..92e97cc83da
--- /dev/null
+++ b/ydb/library/yql/tests/sql/dq_file/part5/canondata/test.test_expr-between-default.txt-Results_/extracted
@@ -0,0 +1,114 @@
+<tmp_path>/program.sql:<main>: Warning: Parse Sql
+
+    <tmp_path>/program.sql:<main>:7:5: Warning: BETWEEN operation will return NULL here
+    	    null between 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:11:5: Warning: BETWEEN operation will return NULL here
+    	    1 between null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:12:5: Warning: BETWEEN operation will return NULL here
+    	    null between null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:20:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:24:5: Warning: BETWEEN operation will return NULL here
+    	    1 between asymmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:25:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:34:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:35:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:36:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 4 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:37:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 0 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:38:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:39:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric null and null;
+	    ^
+<tmp_path>/program.sql:<main>: Warning: Parse Sql
+
+    <tmp_path>/program.sql:<main>:7:5: Warning: BETWEEN operation will return NULL here
+    	    null between 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:11:5: Warning: BETWEEN operation will return NULL here
+    	    1 between null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:12:5: Warning: BETWEEN operation will return NULL here
+    	    null between null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:20:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:24:5: Warning: BETWEEN operation will return NULL here
+    	    1 between asymmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:25:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:34:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:35:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:36:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 4 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:37:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 0 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:38:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:39:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric null and null;
+	    ^
+<tmp_path>/program.sql:<main>: Warning: Parse Sql
+
+    <tmp_path>/program.sql:<main>:7:5: Warning: BETWEEN operation will return NULL here
+    	    null between 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:11:5: Warning: BETWEEN operation will return NULL here
+    	    1 between null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:12:5: Warning: BETWEEN operation will return NULL here
+    	    null between null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:20:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:24:5: Warning: BETWEEN operation will return NULL here
+    	    1 between asymmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:25:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:34:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:35:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:36:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 4 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:37:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 0 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:38:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:39:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric null and null;
+	    ^
\ No newline at end of file
diff --git a/ydb/library/yql/tests/sql/sql2yql/canondata/result.json b/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
index 34bb2e50070..70bc89cf7d4 100644
--- a/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
+++ b/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
@@ -4780,6 +4780,13 @@
             "uri": "https://{canondata_backend}/1936947/659b615f15086142a8960946dabd06b519d43335/resource.tar.gz#test_sql2yql.test_expr-backtick_escape_/sql.yql"
         }
     ],
+    "test_sql2yql.test[expr-between]": [
+        {
+            "checksum": "8910752a7d37369a588f6407008ad083",
+            "size": 9940,
+            "uri": "https://{canondata_backend}/1931696/18b8a06dae10699fe886d31fe0f2dbf29f41dc3d/resource.tar.gz#test_sql2yql.test_expr-between_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[expr-callable]": [
         {
             "checksum": "bdb8aae366f819657f25b74d51a295f5",
@@ -21881,6 +21888,13 @@
             "uri": "https://{canondata_backend}/1880306/64654158d6bfb1289c66c626a8162239289559d0/resource.tar.gz#test_sql_format.test_expr-backtick_escape_/formatted.sql"
         }
     ],
+    "test_sql_format.test[expr-between]": [
+        {
+            "checksum": "235df44ddffb81d65cf099a9b6f39ede",
+            "size": 1127,
+            "uri": "https://{canondata_backend}/1931696/18b8a06dae10699fe886d31fe0f2dbf29f41dc3d/resource.tar.gz#test_sql_format.test_expr-between_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[expr-callable]": [
         {
             "checksum": "a7509ab7ee850bba787543f4494e8d3a",
diff --git a/ydb/library/yql/tests/sql/suites/expr/between.sql b/ydb/library/yql/tests/sql/suites/expr/between.sql
new file mode 100644
index 00000000000..df6af1dc4ac
--- /dev/null
+++ b/ydb/library/yql/tests/sql/suites/expr/between.sql
@@ -0,0 +1,39 @@
+select
+    1 between 2 and 4,
+    3 between 2 and 4,
+    5 between 2 and 4,
+    3 between 4 and 2,
+    5 between 4 and 2,
+    null between 2 and 4,
+    1 between null and 4,
+    1 between 4 and null,
+    1 between 0 and null,
+    1 between null and null,
+    null between null and null;
+
+select
+    1 between asymmetric 2 and 4,
+    3 between asymmetric 2 and 4,
+    5 between asymmetric 2 and 4,
+    3 between asymmetric 4 and 2,
+    5 between asymmetric 4 and 2,
+    null between asymmetric 2 and 4,
+    1 between asymmetric null and 4,
+    1 between asymmetric 4 and null,
+    1 between asymmetric 0 and null,
+    1 between asymmetric null and null,
+    null between asymmetric null and null;
+
+select
+    1 between symmetric 2 and 4,
+    3 between symmetric 2 and 4,
+    5 between symmetric 2 and 4,
+    1 between symmetric 4 and 2,
+    3 between symmetric 4 and 2,
+    5 between symmetric 4 and 2,
+    null between symmetric 2 and 4,
+    1 between symmetric null and 4,
+    1 between symmetric 4 and null,
+    1 between symmetric 0 and null,
+    1 between symmetric null and null,
+    null between symmetric null and null;
diff --git a/ydb/library/yql/tests/sql/yt_native_file/part5/canondata/result.json b/ydb/library/yql/tests/sql/yt_native_file/part5/canondata/result.json
index fe50e052cc5..3179c094c3f 100644
--- a/ydb/library/yql/tests/sql/yt_native_file/part5/canondata/result.json
+++ b/ydb/library/yql/tests/sql/yt_native_file/part5/canondata/result.json
@@ -1747,6 +1747,30 @@
             "uri": "https://{canondata_backend}/1937150/b466c661905bdae484e68f0651bb2615aefc377c/resource.tar.gz#test.test_expr-as_variant_enum-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[expr-between-default.txt-Debug]": [
+        {
+            "checksum": "63e9d81eff7ff8fe7f407034ae0744e1",
+            "size": 1693,
+            "uri": "https://{canondata_backend}/212715/5c974a3f27c541a0710fb1bf257fbcb9cec1b1cf/resource.tar.gz#test.test_expr-between-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[expr-between-default.txt-Plan]": [
+        {
+            "checksum": "b2a2eb5d6b0a138ee924c128fc7738ef",
+            "size": 1331,
+            "uri": "https://{canondata_backend}/1937367/ecf86647077fc0b49f1a68ff664ab2354166a1a5/resource.tar.gz#test.test_expr-between-default.txt-Plan_/plan.txt"
+        }
+    ],
+    "test.test[expr-between-default.txt-Results]": [
+        {
+            "checksum": "b3fd1fe8b3e5ade5671eed54814542b8",
+            "size": 13622,
+            "uri": "https://{canondata_backend}/212715/5c974a3f27c541a0710fb1bf257fbcb9cec1b1cf/resource.tar.gz#test.test_expr-between-default.txt-Results_/results.txt"
+        },
+        {
+            "uri": "file://test.test_expr-between-default.txt-Results_/extracted"
+        }
+    ],
     "test.test[expr-cast_variant-default.txt-Debug]": [
         {
             "checksum": "9cbbb84e3d34494142f31e0f9d8efbbf",
diff --git a/ydb/library/yql/tests/sql/yt_native_file/part5/canondata/test.test_expr-between-default.txt-Results_/extracted b/ydb/library/yql/tests/sql/yt_native_file/part5/canondata/test.test_expr-between-default.txt-Results_/extracted
new file mode 100644
index 00000000000..8815bd8fdb8
--- /dev/null
+++ b/ydb/library/yql/tests/sql/yt_native_file/part5/canondata/test.test_expr-between-default.txt-Results_/extracted
@@ -0,0 +1,38 @@
+<tmp_path>/program.sql:<main>: Warning: Parse Sql
+
+    <tmp_path>/program.sql:<main>:7:5: Warning: BETWEEN operation will return NULL here
+    	    null between 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:11:5: Warning: BETWEEN operation will return NULL here
+    	    1 between null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:12:5: Warning: BETWEEN operation will return NULL here
+    	    null between null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:20:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:24:5: Warning: BETWEEN operation will return NULL here
+    	    1 between asymmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:25:5: Warning: BETWEEN operation will return NULL here
+    	    null between asymmetric null and null;
+	    ^
+    <tmp_path>/program.sql:<main>:34:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric 2 and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:35:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and 4,
+	    ^
+    <tmp_path>/program.sql:<main>:36:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 4 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:37:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric 0 and null,
+	    ^
+    <tmp_path>/program.sql:<main>:38:5: Warning: BETWEEN operation will return NULL here
+    	    1 between symmetric null and null,
+	    ^
+    <tmp_path>/program.sql:<main>:39:5: Warning: BETWEEN operation will return NULL here
+    	    null between symmetric null and null;
+	    ^
\ No newline at end of file