diff options
| author | Maxim Yurchuk <maxim-yurchuk@ydb.tech> | 2024-11-20 17:37:57 +0000 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-11-20 17:37:57 +0000 |
| commit | f76323e9b295c15751e51e3443aa47a36bee8023 (patch) | |
| tree | 4113c8cad473a33e0f746966e0cf087252fa1d7a /yql/essentials/tests/sql/suites/limit | |
| parent | 753ecb8d410a4cb459c26f3a0082fb2d1724fe63 (diff) | |
| parent | a7b9a6afea2a9d7a7bfac4c5eb4c1a8e60adb9e6 (diff) | |
| download | ydb-f76323e9b295c15751e51e3443aa47a36bee8023.tar.gz | |
Merge pull request #11788 from ydb-platform/mergelibs-241120-1113
Library import 241120-1113
Diffstat (limited to 'yql/essentials/tests/sql/suites/limit')
35 files changed, 318 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/limit/default.cfg b/yql/essentials/tests/sql/suites/limit/default.cfg new file mode 100644 index 0000000000..09cf05d98c --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/default.cfg @@ -0,0 +1,2 @@ +in Input input.txt +res result.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/limit/dynamic.txt b/yql/essentials/tests/sql/suites/limit/dynamic.txt new file mode 100644 index 0000000000..c2f900fd7c --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/dynamic.txt @@ -0,0 +1,7 @@ +{"key"="020";"subkey"="1";"value"="q"}; +{"key"="075";"subkey"="2";"value"="abc"}; +{"key"="075";"subkey"="3";"value"="abc"}; +{"key"="075";"subkey"="4";"value"="abc"}; +{"key"="075";"subkey"="5";"value"="abc"}; +{"key"="150";"subkey"="6";"value"="qzz"}; +{"key"="800";"subkey"="7";"value"="ddd"}; diff --git a/yql/essentials/tests/sql/suites/limit/dynamic.txt.attr b/yql/essentials/tests/sql/suites/limit/dynamic.txt.attr new file mode 100644 index 0000000000..9e3c33c4cb --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/dynamic.txt.attr @@ -0,0 +1,33 @@ +{ + "_yql_row_spec"={ + "Type"=["StructType";[ + ["key"; ["DataType"; "String"]]; + ["subkey"; ["DataType"; "String"]]; + ["value"; ["DataType"; "String"]]; + ]]; + "SortMembers"=["key"; "subkey"]; + "SortedBy"=["key"; "subkey"]; + "SortDirections"=[1; 1]; + "SortedByTypes"=[["DataType";"String"]; ["DataType";"String"]] + }; + "_yql_dynamic"=%true; + "schema"=< + "strict" = %true; + "unique_keys" = %true + >[ + { + "name" = "key"; + "type" = "string"; + "sort_order" = "ascending"; + }; + { + "name" = "subkey"; + "type" = "string"; + "sort_order" = "ascending"; + }; + { + "name" = "value"; + "type" = "string"; + }; + ] +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/limit/dynamic_limit.cfg b/yql/essentials/tests/sql/suites/limit/dynamic_limit.cfg new file mode 100644 index 0000000000..eb1992232a --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/dynamic_limit.cfg @@ -0,0 +1,4 @@ +in Input input.txt +res result.txt +providers yt + diff --git a/yql/essentials/tests/sql/suites/limit/dynamic_limit.sql b/yql/essentials/tests/sql/suites/limit/dynamic_limit.sql new file mode 100644 index 0000000000..b4eac0329a --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/dynamic_limit.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +use plato; + +$avg = (SELECT AVG(Length(key)) FROM Input); + +SELECT key FROM Input LIMIT Cast($avg as Uint64) ?? 0; diff --git a/yql/essentials/tests/sql/suites/limit/dynamic_sort_limit.cfg b/yql/essentials/tests/sql/suites/limit/dynamic_sort_limit.cfg new file mode 100644 index 0000000000..1bfc4d45f1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/dynamic_sort_limit.cfg @@ -0,0 +1,4 @@ +in Input input.txt +res result.txt +udf python2_udf +providers yt diff --git a/yql/essentials/tests/sql/suites/limit/dynamic_sort_limit.sql b/yql/essentials/tests/sql/suites/limit/dynamic_sort_limit.sql new file mode 100644 index 0000000000..96e033df95 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/dynamic_sort_limit.sql @@ -0,0 +1,19 @@ +/* postgres can not */ +/* syntax version 1 */ +USE plato; + +$script = @@ +def f(s): + return int(s) +@@; + +$callable = Python::f(Callable<(String)->Uint64?>,$script); + +$i = unwrap($callable("2")); + +SELECT + key, + SOME(value) as value +FROM Input +GROUP BY key +ORDER BY key LIMIT $i; diff --git a/yql/essentials/tests/sql/suites/limit/empty_input_after_limit.sql b/yql/essentials/tests/sql/suites/limit/empty_input_after_limit.sql new file mode 100644 index 0000000000..3a15ab796f --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/empty_input_after_limit.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +$in = ( + select * from plato.Input where key = "150" + union all + select * from plato.Input where key = "075" +); + +select * from $in order by key limit 100 offset 90; diff --git a/yql/essentials/tests/sql/suites/limit/empty_read_after_limit.sql b/yql/essentials/tests/sql/suites/limit/empty_read_after_limit.sql new file mode 100644 index 0000000000..bc0dfad64e --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/empty_read_after_limit.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +pragma direct_read; +select * from plato.Input order by key limit 100 offset 90; diff --git a/yql/essentials/tests/sql/suites/limit/empty_sort_after_limit.sql b/yql/essentials/tests/sql/suites/limit/empty_sort_after_limit.sql new file mode 100644 index 0000000000..11ed40c1ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/empty_sort_after_limit.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select * from plato.Input order by key limit 100 offset 90; diff --git a/yql/essentials/tests/sql/suites/limit/empty_sort_calc_after_limit.sql b/yql/essentials/tests/sql/suites/limit/empty_sort_calc_after_limit.sql new file mode 100644 index 0000000000..08033a5fef --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/empty_sort_calc_after_limit.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select * from plato.Input order by key || subkey limit 100 offset 90; diff --git a/yql/essentials/tests/sql/suites/limit/empty_sort_desc_after_limit.sql b/yql/essentials/tests/sql/suites/limit/empty_sort_desc_after_limit.sql new file mode 100644 index 0000000000..8bf56e6f2d --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/empty_sort_desc_after_limit.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select * from plato.Input order by key desc limit 100 offset 90; diff --git a/yql/essentials/tests/sql/suites/limit/input.txt b/yql/essentials/tests/sql/suites/limit/input.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/input.txt @@ -0,0 +1,4 @@ +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="800";"subkey"="2";"value"="ddd"}; +{"key"="020";"subkey"="3";"value"="q"}; +{"key"="150";"subkey"="4";"value"="qzz"}; diff --git a/yql/essentials/tests/sql/suites/limit/insert_with_limit-dynamic.cfg b/yql/essentials/tests/sql/suites/limit/insert_with_limit-dynamic.cfg new file mode 100644 index 0000000000..b4ffc75e3c --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/insert_with_limit-dynamic.cfg @@ -0,0 +1,2 @@ +in Input dynamic.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/limit/insert_with_limit.cfg b/yql/essentials/tests/sql/suites/limit/insert_with_limit.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/insert_with_limit.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/limit/insert_with_limit.sql b/yql/essentials/tests/sql/suites/limit/insert_with_limit.sql new file mode 100644 index 0000000000..f51506c8af --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/insert_with_limit.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +insert into plato.Output with truncate +select * from plato.Input limit 2; diff --git a/yql/essentials/tests/sql/suites/limit/limit-dynamic.cfg b/yql/essentials/tests/sql/suites/limit/limit-dynamic.cfg new file mode 100644 index 0000000000..80e8546f07 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/limit-dynamic.cfg @@ -0,0 +1 @@ +in Input dynamic.txt diff --git a/yql/essentials/tests/sql/suites/limit/limit.cfg b/yql/essentials/tests/sql/suites/limit/limit.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/limit.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/limit/limit.sql b/yql/essentials/tests/sql/suites/limit/limit.sql new file mode 100644 index 0000000000..8b2d036f97 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/limit.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select * from plato.Input limit 1000;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/limit/limit_offset.sql b/yql/essentials/tests/sql/suites/limit/limit_offset.sql new file mode 100644 index 0000000000..99ac57e551 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/limit_offset.sql @@ -0,0 +1 @@ +select value, subkey, key from (select * from plato.Input) as x order by key, subkey limit 1 offset 1; diff --git a/yql/essentials/tests/sql/suites/limit/limit_over_sort_desc_in_subquery.cfg b/yql/essentials/tests/sql/suites/limit/limit_over_sort_desc_in_subquery.cfg new file mode 100644 index 0000000000..c85115a8e4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/limit_over_sort_desc_in_subquery.cfg @@ -0,0 +1,2 @@ +in Input sorted_desc.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/limit/limit_over_sort_desc_in_subquery.sql b/yql/essentials/tests/sql/suites/limit/limit_over_sort_desc_in_subquery.sql new file mode 100644 index 0000000000..9567209c22 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/limit_over_sort_desc_in_subquery.sql @@ -0,0 +1,19 @@ +/* postgres can not */ +/* hybridfile can not YQL-17743 */ +/* syntax version 1 */ +USE plato; + +$in = ( + select + * + from Input + where subkey > '1' + order by + key desc + limit 15000 +); + +select + * +from $in +where value like "a%"; diff --git a/yql/essentials/tests/sql/suites/limit/limit_skip_take.sql b/yql/essentials/tests/sql/suites/limit/limit_skip_take.sql new file mode 100644 index 0000000000..d6c63e4d82 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/limit_skip_take.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select value, subkey, key from (select * from plato.Input) order by key, subkey limit 1,2; diff --git a/yql/essentials/tests/sql/suites/limit/many_top_sorts.sql b/yql/essentials/tests/sql/suites/limit/many_top_sorts.sql new file mode 100644 index 0000000000..ee81465874 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/many_top_sorts.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +/* ignore runonopt ast diff */ +/* ignore runonopt plan diff */ +use plato; + +$i = (select * from Input where key > "100"); + +select * from $i order by key limit 1; + +select * from $i order by subkey limit 2;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/limit/sort_calc_limit.cfg b/yql/essentials/tests/sql/suites/limit/sort_calc_limit.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/sort_calc_limit.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/limit/sort_calc_limit.sql b/yql/essentials/tests/sql/suites/limit/sort_calc_limit.sql new file mode 100644 index 0000000000..62bb39d249 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/sort_calc_limit.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +USE plato; + +insert into Output with truncate +select * +from Input +order by key || subkey +limit 2;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/limit/sorted_desc.txt b/yql/essentials/tests/sql/suites/limit/sorted_desc.txt new file mode 100644 index 0000000000..235fc7cd49 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/sorted_desc.txt @@ -0,0 +1,10 @@ +{"_yql_column_0"="\xE0\xC6\xCE\xCE\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="911";"subkey"="2";"value"="kkk"}; +{"_yql_column_0"="\xE0\xC8\xC9\xCE\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="761";"subkey"="6";"value"="ccc"}; +{"_yql_column_0"="\xE0\xCA\xCD\xC8\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="527";"subkey"="4";"value"="bbb"}; +{"_yql_column_0"="\xE0\xCD\xCF\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="200";"subkey"="7";"value"="qqq"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="1";"value"="aaa"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="3";"value"="iii"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="8";"value"="zzz"}; +{"_yql_column_0"="\xE0\xCF\xC8\xCA\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="075";"subkey"="1";"value"="abc"}; +{"_yql_column_0"="\xE0\xCF\xCC\xC8\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="037";"subkey"="5";"value"="ddd"}; +{"_yql_column_0"="\xE0\xCF\xCD\xCC\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="023";"subkey"="3";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/limit/sorted_desc.txt.attr b/yql/essentials/tests/sql/suites/limit/sorted_desc.txt.attr new file mode 100644 index 0000000000..5a6ca5761e --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/sorted_desc.txt.attr @@ -0,0 +1,47 @@ +{ + "_yql_row_spec" = { + "SortMembers" = [ + "key" + ]; + "SortDirections" = [ + 0 + ]; + "UniqueKeys" = %false; + "SortedByTypes" = [ + [ + "DataType"; + "String" + ] + ]; + "StrictSchema" = %true; + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ]; + "SortedBy" = [ + "_yql_column_0" + ] + } +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/limit/yql-7900_empty_sorted_without_keys.sql b/yql/essentials/tests/sql/suites/limit/yql-7900_empty_sorted_without_keys.sql new file mode 100644 index 0000000000..d61f7e9183 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/yql-7900_empty_sorted_without_keys.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +USE plato; + +$cnt = (select count(*) from Input); +$offset = ($cnt + 10) ?? 0; + +$data_limited = (select * from Input order by key || value limit 1 offset $offset); + +$result_top = (SELECT subkey, Length(key) as l, key FROM $data_limited); + +SELECT * FROM $result_top; diff --git a/yql/essentials/tests/sql/suites/limit/yql-8046_empty_sorted_desc.cfg b/yql/essentials/tests/sql/suites/limit/yql-8046_empty_sorted_desc.cfg new file mode 100644 index 0000000000..2e3eaf9682 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/yql-8046_empty_sorted_desc.cfg @@ -0,0 +1,4 @@ +in Input input.txt +out Output output.txt +providers yt + diff --git a/yql/essentials/tests/sql/suites/limit/yql-8046_empty_sorted_desc.sql b/yql/essentials/tests/sql/suites/limit/yql-8046_empty_sorted_desc.sql new file mode 100644 index 0000000000..6647df9e90 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/yql-8046_empty_sorted_desc.sql @@ -0,0 +1,45 @@ +/* postgres can not */ +USE plato; + +$visitors = ( +SELECT + key, + subkey, + value +FROM Input +WHERE subkey != "" +); + +$over_threshold = ( +SELECT + key, + subkey, + value +FROM $visitors +WHERE key > "070" +); + +$clean = (SELECT COUNT(*) FROM $over_threshold); + +$tail = ( +SELECT + key, + subkey, + value +FROM $visitors +ORDER BY key DESC +LIMIT IF($clean ?? 0 < 2, 2 - $clean ?? 0, 0) +); + +INSERT INTO Output WITH TRUNCATE +SELECT + key, + subkey, + value +FROM $over_threshold +UNION ALL +SELECT + key, + subkey, + value +FROM $tail; diff --git a/yql/essentials/tests/sql/suites/limit/yql-8611_calc_peephole.cfg b/yql/essentials/tests/sql/suites/limit/yql-8611_calc_peephole.cfg new file mode 100644 index 0000000000..b3080c2484 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/yql-8611_calc_peephole.cfg @@ -0,0 +1,6 @@ +in Input input.txt +out Output0 output0.txt +out Output1 output1.txt +out Output2 output2.txt +out Output3 output3.txt +udf libyson_udf diff --git a/yql/essentials/tests/sql/suites/limit/yql-8611_calc_peephole.sql b/yql/essentials/tests/sql/suites/limit/yql-8611_calc_peephole.sql new file mode 100644 index 0000000000..aa1546401e --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/yql-8611_calc_peephole.sql @@ -0,0 +1,31 @@ +USE plato; + +$tableList = AsList( + AsStruct(Yson('{"row_count"=9}') as Attributes, "Input1" as Path, "table" as Type), + AsStruct(Yson('{"row_count"=19}') as Attributes, "Input2" as Path, "table" as Type) +); + +-- $bucket_size = 1000000; +$buckets = ASLIST(0, 1, 2, 3); + +$row_count = ( + SELECT Yson::LookupInt64(Attributes, "row_count") + FROM AS_TABLE($tableList) + WHERE + Type = "table" +); + +$bucket_size = unwrap(CAST($row_count / ListLength($buckets) AS Uint64)); + +DEFINE ACTION $make_bucket($bucket_number) AS + $offset = unwrap(CAST($bucket_number AS UInt8)) * $bucket_size; + $dst = "Output" || $bucket_number; + + INSERT INTO $dst ( + SELECT * FROM Input + ORDER BY key + LIMIT $bucket_size OFFSET $offset); +END DEFINE; + +EVALUATE FOR $bucket_number IN $buckets + DO $make_bucket(CAST($bucket_number AS String)); diff --git a/yql/essentials/tests/sql/suites/limit/yql-9617_empty_lambda.sql b/yql/essentials/tests/sql/suites/limit/yql-9617_empty_lambda.sql new file mode 100644 index 0000000000..8d438c5122 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/yql-9617_empty_lambda.sql @@ -0,0 +1,7 @@ +use plato; + +select key, some(value) as value from Input +where key > "999" +group by key +order by key +limit 10;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/limit/zero_limit.sql b/yql/essentials/tests/sql/suites/limit/zero_limit.sql new file mode 100644 index 0000000000..6532fb3737 --- /dev/null +++ b/yql/essentials/tests/sql/suites/limit/zero_limit.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +use plato; + +$x = (select * from Input order by value limit 10); + +select * from $x where key > "000" limit coalesce(cast(0.1 * 0 as Uint64), 0); |