Add test with expected LookupJoin

6 files changed, 70 insertions, 0 deletions

diff --git a/ydb/library/yql/tests/sql/sql2yql/canondata/result.json b/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
index a1c96c30eb..3ddced9078 100644
--- a/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
+++ b/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
@@ -7657,6 +7657,13 @@
             "uri": "https://{canondata_backend}/1937027/b5576dd03b65095c35e7cb4954821e4f66817b94/resource.tar.gz#test_sql2yql.test_join-lookupjoin_semi_subq_/sql.yql"
         }
     ],
+    "test_sql2yql.test[join-lookupjoin_take_skip]": [
+        {
+            "checksum": "0d9a5940582e119c4bf0d2c99b796fb5",
+            "size": 4430,
+            "uri": "https://{canondata_backend}/1936997/a4fb1650ba6f8c54af3ca9cd217fe3accf7ae1bc/resource.tar.gz#test_sql2yql.test_join-lookupjoin_take_skip_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[join-lookupjoin_unused_keys]": [
         {
             "checksum": "90e5e5d306d0f031da197bdbdba1a06c",
@@ -24716,6 +24723,13 @@
             "uri": "https://{canondata_backend}/1599023/4bd7e6892a9762eec433e60bda88f20cddadc74c/resource.tar.gz#test_sql_format.test_join-lookupjoin_semi_subq_/formatted.sql"
         }
     ],
+    "test_sql_format.test[join-lookupjoin_take_skip]": [
+        {
+            "checksum": "92f33c391aa47d33cf72fb866e2bcb1f",
+            "size": 512,
+            "uri": "https://{canondata_backend}/1936997/a4fb1650ba6f8c54af3ca9cd217fe3accf7ae1bc/resource.tar.gz#test_sql_format.test_join-lookupjoin_take_skip_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[join-lookupjoin_unused_keys]": [
         {
             "checksum": "dd25a30d57715fc14aeecc0c16756981",
diff --git a/ydb/library/yql/tests/sql/suites/join/input9.txt b/ydb/library/yql/tests/sql/suites/join/input9.txt
new file mode 100644
index 0000000000..b214aab0d9
--- /dev/null
+++ b/ydb/library/yql/tests/sql/suites/join/input9.txt
@@ -0,0 +1,10 @@
+{"key"="023";"subkey"="3";"value"="aaa"};
+{"key"="037";"subkey"="5";"value"="ddd"};
+{"key"="075";"subkey"="1";"value"="abc"};
+{"key"="150";"subkey"="1";"value"="aaa"};
+{"key"="150";"subkey"="3";"value"="iii"};
+{"key"="150";"subkey"="8";"value"="zzz"};
+{"key"="200";"subkey"="7";"value"="qqq"};
+{"key"="527";"subkey"="4";"value"="bbb"};
+{"key"="761";"subkey"="6";"value"="ccc"};
+{"key"="911";"subkey"="2";"value"="kkk"};
diff --git a/ydb/library/yql/tests/sql/suites/join/input9.txt.attr b/ydb/library/yql/tests/sql/suites/join/input9.txt.attr
new file mode 100644
index 0000000000..ed13e20223
--- /dev/null
+++ b/ydb/library/yql/tests/sql/suites/join/input9.txt.attr
@@ -0,0 +1,11 @@
+{"_yql_row_spec"={
+    "Type"=["StructType";[
+        ["key";["DataType";"String"]];
+        ["subkey";["DataType";"String"]];
+        ["value";["DataType";"String"]]
+    ]];
+    "SortDirections"=[1;1;];
+    "SortedBy"=["key";"subkey";];
+    "SortedByTypes"=[["DataType";"String";];["DataType";"String";];];
+    "SortMembers"=["key";"subkey";];
+}}
diff --git a/ydb/library/yql/tests/sql/suites/join/lookupjoin_take_skip.cfg b/ydb/library/yql/tests/sql/suites/join/lookupjoin_take_skip.cfg
new file mode 100644
index 0000000000..8790d17dc7
--- /dev/null
+++ b/ydb/library/yql/tests/sql/suites/join/lookupjoin_take_skip.cfg
@@ -0,0 +1,2 @@
+in Input input9.txt
+providers yt
diff --git a/ydb/library/yql/tests/sql/suites/join/lookupjoin_take_skip.sql b/ydb/library/yql/tests/sql/suites/join/lookupjoin_take_skip.sql
new file mode 100644
index 0000000000..de1195209c
--- /dev/null
+++ b/ydb/library/yql/tests/sql/suites/join/lookupjoin_take_skip.sql
@@ -0,0 +1,12 @@
+USE plato;
+PRAGMA yt.LookupJoinMaxRows="3";
+pragma yt.LookupJoinLimit = '10M';
+
+insert into @big
+select * from (select ListMap(ListFromRange(1, 100), ($x)->(Unwrap(CAST($x as String)))) as key) flatten list by key order by key;
+commit;
+
+$small = select substring(key, 0, 2) as key, subkey || '000' as subkey  from Input order by key limit 5 offset 8;
+
+select * from @big as a join $small as b using(key) order by key;
+
diff --git a/ydb/library/yql/tests/sql/yt_native_file/part9/canondata/result.json b/ydb/library/yql/tests/sql/yt_native_file/part9/canondata/result.json
index 426d5a9bc2..e08d90da97 100644
--- a/ydb/library/yql/tests/sql/yt_native_file/part9/canondata/result.json
+++ b/ydb/library/yql/tests/sql/yt_native_file/part9/canondata/result.json
@@ -2404,6 +2404,27 @@
             "uri": "https://{canondata_backend}/1871182/250608c52d287add59fe1b9e093f0d10967d8d55/resource.tar.gz#test.test_join-lookupjoin_semi_empty--Results_/results.txt"
         }
     ],
+    "test.test[join-lookupjoin_take_skip--Debug]": [
+        {
+            "checksum": "5bbc81dd43e2b3ed08c44957065628a0",
+            "size": 4607,
+            "uri": "https://{canondata_backend}/1925842/97ff5031d7caf3812b5fbe98b2a6aa20a0d2be28/resource.tar.gz#test.test_join-lookupjoin_take_skip--Debug_/opt.yql"
+        }
+    ],
+    "test.test[join-lookupjoin_take_skip--Plan]": [
+        {
+            "checksum": "e2744532509846d2ed662f28e9045eb6",
+            "size": 11185,
+            "uri": "https://{canondata_backend}/1925842/97ff5031d7caf3812b5fbe98b2a6aa20a0d2be28/resource.tar.gz#test.test_join-lookupjoin_take_skip--Plan_/plan.txt"
+        }
+    ],
+    "test.test[join-lookupjoin_take_skip--Results]": [
+        {
+            "checksum": "8e931046d429b4b6937ebc3bddc48c9d",
+            "size": 1065,
+            "uri": "https://{canondata_backend}/1925842/97ff5031d7caf3812b5fbe98b2a6aa20a0d2be28/resource.tar.gz#test.test_join-lookupjoin_take_skip--Results_/results.txt"
+        }
+    ],
     "test.test[join-mapjoin_on_tablerecord--Debug]": [
         {
             "checksum": "be7db24b4238eae64d6a61ed475deb64",