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/order_by | |
| 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/order_by')
139 files changed, 957 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/order_by/SortByOneField.cfg b/yql/essentials/tests/sql/suites/order_by/SortByOneField.cfg new file mode 100644 index 0000000000..2dc97b5e95 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/SortByOneField.cfg @@ -0,0 +1 @@ +in Input columns.txt diff --git a/yql/essentials/tests/sql/suites/order_by/SortByOneField.sql b/yql/essentials/tests/sql/suites/order_by/SortByOneField.sql new file mode 100644 index 0000000000..192b522df4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/SortByOneField.sql @@ -0,0 +1 @@ +select a from plato.Input order by a; diff --git a/yql/essentials/tests/sql/suites/order_by/SortByOneFieldDesc.cfg b/yql/essentials/tests/sql/suites/order_by/SortByOneFieldDesc.cfg new file mode 100644 index 0000000000..2dc97b5e95 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/SortByOneFieldDesc.cfg @@ -0,0 +1 @@ +in Input columns.txt diff --git a/yql/essentials/tests/sql/suites/order_by/SortByOneFieldDesc.sql b/yql/essentials/tests/sql/suites/order_by/SortByOneFieldDesc.sql new file mode 100644 index 0000000000..e269f50243 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/SortByOneFieldDesc.sql @@ -0,0 +1 @@ +select b from plato.Input order by b desc; diff --git a/yql/essentials/tests/sql/suites/order_by/SortByTwoFields.cfg b/yql/essentials/tests/sql/suites/order_by/SortByTwoFields.cfg new file mode 100644 index 0000000000..2dc97b5e95 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/SortByTwoFields.cfg @@ -0,0 +1 @@ +in Input columns.txt diff --git a/yql/essentials/tests/sql/suites/order_by/SortByTwoFields.sql b/yql/essentials/tests/sql/suites/order_by/SortByTwoFields.sql new file mode 100644 index 0000000000..0a1a137b35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/SortByTwoFields.sql @@ -0,0 +1 @@ +select a, b from plato.Input order by a, b; diff --git a/yql/essentials/tests/sql/suites/order_by/SortByTwoFieldsDesc.cfg b/yql/essentials/tests/sql/suites/order_by/SortByTwoFieldsDesc.cfg new file mode 100644 index 0000000000..2dc97b5e95 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/SortByTwoFieldsDesc.cfg @@ -0,0 +1 @@ +in Input columns.txt diff --git a/yql/essentials/tests/sql/suites/order_by/SortByTwoFieldsDesc.sql b/yql/essentials/tests/sql/suites/order_by/SortByTwoFieldsDesc.sql new file mode 100644 index 0000000000..adc58f1da7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/SortByTwoFieldsDesc.sql @@ -0,0 +1 @@ +select a, b from plato.Input order by a desc, b desc; diff --git a/yql/essentials/tests/sql/suites/order_by/assume_cut_prefix.cfg b/yql/essentials/tests/sql/suites/order_by/assume_cut_prefix.cfg new file mode 100644 index 0000000000..768acac67b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_cut_prefix.cfg @@ -0,0 +1,2 @@ +in Input input_sorted.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/assume_cut_prefix.sql b/yql/essentials/tests/sql/suites/order_by/assume_cut_prefix.sql new file mode 100644 index 0000000000..8e81372d57 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_cut_prefix.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +USE plato; + +insert into Output +select * from Input +assume order by key; diff --git a/yql/essentials/tests/sql/suites/order_by/assume_over_input.cfg b/yql/essentials/tests/sql/suites/order_by/assume_over_input.cfg new file mode 100644 index 0000000000..4ef8c714be --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_over_input.cfg @@ -0,0 +1,2 @@ +in Input input2.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/assume_over_input.sql b/yql/essentials/tests/sql/suites/order_by/assume_over_input.sql new file mode 100644 index 0000000000..f2a2611de7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_over_input.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +USE plato; + +INSERT INTO Output +SELECT * FROM Input ASSUME ORDER BY key, subkey, value; diff --git a/yql/essentials/tests/sql/suites/order_by/assume_over_input_desc.cfg b/yql/essentials/tests/sql/suites/order_by/assume_over_input_desc.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_over_input_desc.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/assume_over_input_desc.sql b/yql/essentials/tests/sql/suites/order_by/assume_over_input_desc.sql new file mode 100644 index 0000000000..252bd2ae91 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_over_input_desc.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +/* hybridfile can not YQL-17743 */ +USE plato; + +insert into Output +select * from concat(`Input[#3]`, `Input[#0]`, `Input[#2]`) +assume order by key desc; diff --git a/yql/essentials/tests/sql/suites/order_by/assume_with_filter.cfg b/yql/essentials/tests/sql/suites/order_by/assume_with_filter.cfg new file mode 100644 index 0000000000..4ef8c714be --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_with_filter.cfg @@ -0,0 +1,2 @@ +in Input input2.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/assume_with_filter.sql b/yql/essentials/tests/sql/suites/order_by/assume_with_filter.sql new file mode 100644 index 0000000000..45463f1953 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_with_filter.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +USE plato; + +INSERT INTO Output +SELECT + "3" || key as key, + subkey, + value +FROM Input +WHERE key >= "0" +ASSUME ORDER BY key, subkey; diff --git a/yql/essentials/tests/sql/suites/order_by/assume_with_transform_desc.cfg b/yql/essentials/tests/sql/suites/order_by/assume_with_transform_desc.cfg new file mode 100644 index 0000000000..4ef8c714be --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_with_transform_desc.cfg @@ -0,0 +1,2 @@ +in Input input2.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/assume_with_transform_desc.sql b/yql/essentials/tests/sql/suites/order_by/assume_with_transform_desc.sql new file mode 100644 index 0000000000..0c25b703e1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/assume_with_transform_desc.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +/* hybridfile can not YQL-17743 */ +/* multirun can not */ +/* syntax version 1 */ +USE plato; + +INSERT INTO Output +SELECT + -(CAST(key as Int32) ?? 0) as key, + subkey, + value +FROM Input +ASSUME ORDER BY key DESC; diff --git a/yql/essentials/tests/sql/suites/order_by/avg_and_sum_by_value.txt b/yql/essentials/tests/sql/suites/order_by/avg_and_sum_by_value.txt new file mode 100644 index 0000000000..cebd64e139 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/avg_and_sum_by_value.txt @@ -0,0 +1,4 @@ +{"key"="1";"subkey"="10";"value"="FOO"}; +{"key"="2";"subkey"="20";"value"="BAR"}; +{"key"="3";"subkey"="30";"value"="BAR"}; +{"key"="WAT";"subkey"="WAT";"value"="FOO"}; diff --git a/yql/essentials/tests/sql/suites/order_by/changed_sort_with_limit.cfg b/yql/essentials/tests/sql/suites/order_by/changed_sort_with_limit.cfg new file mode 100644 index 0000000000..f9159f698b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/changed_sort_with_limit.cfg @@ -0,0 +1,3 @@ +in Input input_sorted.txt +out Output output.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/changed_sort_with_limit.sql b/yql/essentials/tests/sql/suites/order_by/changed_sort_with_limit.sql new file mode 100644 index 0000000000..e69117f930 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/changed_sort_with_limit.sql @@ -0,0 +1,11 @@ +USE plato; + +INSERT INTO Output +SELECT + key, + '1' as subkey, + value || "a" as value +FROM Input +WHERE key < "100" +ORDER BY value +limit 3; diff --git a/yql/essentials/tests/sql/suites/order_by/columns.txt b/yql/essentials/tests/sql/suites/order_by/columns.txt new file mode 100644 index 0000000000..24785da79e --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/columns.txt @@ -0,0 +1,8 @@ +{"a"="x"; "b"=1u; "c"=133; "d"=-5 }; +{"a"="y"; "b"=1u; "c"=90; "d"=12 }; +{"a"="y"; "b"=0u; "c"=5; "d"=999}; +{"a"="y"; "b"=0u; "c"=111; "d"=42 }; +{"a"="y"; "b"=1u; "c"=-8; "d"=6 }; +{"a"="x"; "b"=1u; "c"=256; "d"=-77}; +{"a"="x"; "b"=1u; "c"=2; "d"=-47}; +{"a"="y"; "b"=1u; "c"=88; "d"=3 }; diff --git a/yql/essentials/tests/sql/suites/order_by/columns.txt.attr b/yql/essentials/tests/sql/suites/order_by/columns.txt.attr new file mode 100644 index 0000000000..70da6cab1d --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/columns.txt.attr @@ -0,0 +1,3 @@ +{ + "_read_schema"=[{"type"="string";"name"="a"};{"type"="uint64";"name"="b"};{"type"="int64";"name"="c"};{"type"="int64";"name"="d"}] +} diff --git a/yql/essentials/tests/sql/suites/order_by/complex_type.txt b/yql/essentials/tests/sql/suites/order_by/complex_type.txt new file mode 100644 index 0000000000..1dd5ba0166 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/complex_type.txt @@ -0,0 +1,2 @@ +{"value"=["v1";"v2"];"key"="k1"}; +{"value"=["v2";"v3"];"key"="k2"}; diff --git a/yql/essentials/tests/sql/suites/order_by/complex_type.txt.attr b/yql/essentials/tests/sql/suites/order_by/complex_type.txt.attr new file mode 100644 index 0000000000..42cec863fb --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/complex_type.txt.attr @@ -0,0 +1,13 @@ +{ + "_yql_row_spec" = { + "StrictSchema"=%true; + "Type"=["StructType";[ + ["value";["ListType";["DataType";"String"]]]; + ["key";["OptionalType";["DataType";"String"]]]; + ]]; + }; + "schema" = [ + {"required"=%false; "type"="any"; "name"="value"}; + {"required"=%false; "type"="string"; "name"="key"}; + ]; +} diff --git a/yql/essentials/tests/sql/suites/order_by/decimals.txt b/yql/essentials/tests/sql/suites/order_by/decimals.txt new file mode 100644 index 0000000000..5c1ede16e0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/decimals.txt @@ -0,0 +1,8 @@ +{"a"="x"; "b"=1u; "c"="133.34"; "d"="-inf" }; +{"a"="y"; "b"=1u; "c"="90.5"; "d"="12.7" }; +{"a"="y"; "b"=0u; "c"="5.01"; "d"="inf"}; +{"a"="y"; "b"=0u; "c"="111"; "d"="nan" }; +{"a"="y"; "b"=1u; "c"="-8.99"; "d"="6.6" }; +{"a"="x"; "b"=1u; "c"="256.333"; "d"="-77.5"}; +{"a"="x"; "b"=1u; "c"="2.0"; "d"="-47.0"}; +{"a"="y"; "b"=1u; "c"="88.88"; "d"="3.3" }; diff --git a/yql/essentials/tests/sql/suites/order_by/decimals.txt.attr b/yql/essentials/tests/sql/suites/order_by/decimals.txt.attr new file mode 100644 index 0000000000..1d13a3e179 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/decimals.txt.attr @@ -0,0 +1,8 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["a";["DataType";"String"]]; + ["b";["DataType";"Uint8"]]; + ["c";["DataType";"String"]]; + ["d";["DataType";"String"]]; + ]]; +}} diff --git a/yql/essentials/tests/sql/suites/order_by/default.cfg b/yql/essentials/tests/sql/suites/order_by/default.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/default.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/order_by/descending.txt b/yql/essentials/tests/sql/suites/order_by/descending.txt new file mode 100644 index 0000000000..8e63b46e56 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/descending.txt @@ -0,0 +1,2 @@ +{"key"="075";"subkey"="2";"value"="abc"}; +{"key"="020";"subkey"="1";"value"="q"}; diff --git a/yql/essentials/tests/sql/suites/order_by/descending.txt.attr b/yql/essentials/tests/sql/suites/order_by/descending.txt.attr new file mode 100644 index 0000000000..7024a9fc9c --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/descending.txt.attr @@ -0,0 +1,21 @@ +{ + "schema"=< + "strict" = %true; + "unique_keys" = %false + >[ + { + "name" = "key"; + "type" = "string"; + "sort_order" = "descending"; + }; + { + "name" = "subkey"; + "type" = "string"; + "sort_order" = "descending"; + }; + { + "name" = "value"; + "type" = "string"; + }; + ] +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/extract_members_over_sort_desc.cfg b/yql/essentials/tests/sql/suites/order_by/extract_members_over_sort_desc.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/extract_members_over_sort_desc.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/extract_members_over_sort_desc.sql b/yql/essentials/tests/sql/suites/order_by/extract_members_over_sort_desc.sql new file mode 100644 index 0000000000..6e5b264216 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/extract_members_over_sort_desc.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +/* multirun can not */ +USE plato; + +$top = (select * from Input order by value desc limit 100); + +insert into Output +select key, value +from $top +order by value desc; + +select * from $top;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/input.txt b/yql/essentials/tests/sql/suites/order_by/input.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/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/order_by/input2.txt b/yql/essentials/tests/sql/suites/order_by/input2.txt new file mode 100644 index 0000000000..65f33616b2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/input2.txt @@ -0,0 +1,9 @@ +{"key"="0";"subkey"="10";"value"="FOO"}; +{"key"="0";"subkey"="40";"value"="FOO"}; +{"key"="1";"subkey"="10";"value"="FOO"}; +{"key"="1";"subkey"="20";"value"="BAR"}; +{"key"="1";"subkey"="40";"value"="BAR"}; +{"key"="1";"subkey"="50";"value"="WAT"}; +{"key"="2";"subkey"="40";"value"="WAT"}; +{"key"="2";"subkey"="50";"value"="FOO"}; +{"key"="2";"subkey"="60";"value"="BAR"}; diff --git a/yql/essentials/tests/sql/suites/order_by/input_expr.txt b/yql/essentials/tests/sql/suites/order_by/input_expr.txt new file mode 100644 index 0000000000..621e7eaead --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/input_expr.txt @@ -0,0 +1,10 @@ +{"key"="192";"subkey"="5";"value"="ddd"}; +{"key"="075";"subkey"="15";"value"="abc"}; +{"key"="911";"subkey"="1";"value"="kkk"}; +{"key"="023";"subkey"="15";"value"="aaa"}; +{"key"="527";"subkey"="1";"value"="bbb"}; +{"key"="037";"subkey"="15";"value"="ddd"}; +{"key"="761";"subkey"="1";"value"="ccc"}; +{"key"="200";"subkey"="5";"value"="qqq"}; +{"key"="150";"subkey"="5";"value"="zzz"}; +{"key"="042";"subkey"="15";"value"="kkk"}; diff --git a/yql/essentials/tests/sql/suites/order_by/input_sorted.txt b/yql/essentials/tests/sql/suites/order_by/input_sorted.txt new file mode 100644 index 0000000000..004ddc583b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/input_sorted.txt @@ -0,0 +1,4 @@ +{"key"="020";"subkey"="3";"value"="q"}; +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="150";"subkey"="4";"value"="qzz"}; +{"key"="800";"subkey"="2";"value"="ddd"}; diff --git a/yql/essentials/tests/sql/suites/order_by/input_sorted.txt.attr b/yql/essentials/tests/sql/suites/order_by/input_sorted.txt.attr new file mode 100644 index 0000000000..aa00b0ede5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/input_sorted.txt.attr @@ -0,0 +1,53 @@ +{ + "_yql_row_spec" = { + "SortMembers" = [ + "key"; + "subkey"; + ]; + "SortDirections" = [ + 1; + 1; + ]; + "UniqueKeys" = %false; + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ]; + "SortedByTypes" = [ + [ + "DataType"; + "String" + ]; + [ + "DataType"; + "String" + ]; + ]; + "SortedBy" = [ + "key"; + "subkey"; + ] + } +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/limit.cfg b/yql/essentials/tests/sql/suites/order_by/limit.cfg new file mode 100644 index 0000000000..86c772ae1c --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/limit.cfg @@ -0,0 +1 @@ +in Input read_schema.txt diff --git a/yql/essentials/tests/sql/suites/order_by/limit.sql b/yql/essentials/tests/sql/suites/order_by/limit.sql new file mode 100644 index 0000000000..eb87ba6968 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/limit.sql @@ -0,0 +1 @@ +select * from plato.Input order by a desc limit 3; diff --git a/yql/essentials/tests/sql/suites/order_by/literal.cfg b/yql/essentials/tests/sql/suites/order_by/literal.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/literal.sql b/yql/essentials/tests/sql/suites/order_by/literal.sql new file mode 100644 index 0000000000..aac08c3fb8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +use plato; + +insert into Output +select x +from (select ListFromRange(0,100) as x) +flatten by x +order by x; diff --git a/yql/essentials/tests/sql/suites/order_by/literal_complex.cfg b/yql/essentials/tests/sql/suites/order_by/literal_complex.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_complex.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/literal_complex.sql b/yql/essentials/tests/sql/suites/order_by/literal_complex.sql new file mode 100644 index 0000000000..84bee87970 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_complex.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* hybridfile can not YQL-17743 */ +use plato; + +$list = AsList(AsStruct(1 as a, "2" as b, "3" as c), AsStruct(4 as a, "5" as b, "6" as c)); + +insert into Output +select * from as_table($list) +order by a desc, b, c desc; diff --git a/yql/essentials/tests/sql/suites/order_by/literal_desc.cfg b/yql/essentials/tests/sql/suites/order_by/literal_desc.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_desc.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/literal_desc.sql b/yql/essentials/tests/sql/suites/order_by/literal_desc.sql new file mode 100644 index 0000000000..3aec7f5aa7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_desc.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* hybridfile can not YQL-17743 */ +use plato; + +insert into Output +select x +from (select ListFromRange(0,100) as x) +flatten by x +order by x desc; diff --git a/yql/essentials/tests/sql/suites/order_by/literal_empty_list_sort.cfg b/yql/essentials/tests/sql/suites/order_by/literal_empty_list_sort.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_empty_list_sort.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/literal_empty_list_sort.sql b/yql/essentials/tests/sql/suites/order_by/literal_empty_list_sort.sql new file mode 100644 index 0000000000..ed51a6a4c2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_empty_list_sort.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +use plato; + +$list = ListCreate(Struct<key:String, subkey:String, value:String>); + +insert into Output +select * from as_table($list) order by key; diff --git a/yql/essentials/tests/sql/suites/order_by/literal_single_item_sort.cfg b/yql/essentials/tests/sql/suites/order_by/literal_single_item_sort.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_single_item_sort.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/literal_single_item_sort.sql b/yql/essentials/tests/sql/suites/order_by/literal_single_item_sort.sql new file mode 100644 index 0000000000..a928a89fd1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_single_item_sort.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +use plato; + +$t = AsList( + AsStruct(1 as key, 101 as value) +); + +insert into Output +select * from as_table($t) order by key; diff --git a/yql/essentials/tests/sql/suites/order_by/literal_take_zero_sort.cfg b/yql/essentials/tests/sql/suites/order_by/literal_take_zero_sort.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_take_zero_sort.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/literal_take_zero_sort.sql b/yql/essentials/tests/sql/suites/order_by/literal_take_zero_sort.sql new file mode 100644 index 0000000000..18288547fc --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_take_zero_sort.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +use plato; + +$t = AsList( + AsStruct(1 as key, 101 as value), + AsStruct(2 as key, 34 as value), + AsStruct(4 as key, 22 as value), + AsStruct(6 as key, 256 as value), + AsStruct(7 as key, 111 as value) +); + +insert into Output +select * from as_table($t) order by key limit 0; diff --git a/yql/essentials/tests/sql/suites/order_by/literal_with_assume.cfg b/yql/essentials/tests/sql/suites/order_by/literal_with_assume.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_with_assume.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/literal_with_assume.sql b/yql/essentials/tests/sql/suites/order_by/literal_with_assume.sql new file mode 100644 index 0000000000..5324313491 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_with_assume.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +use plato; + +$t = AsList( + AsStruct(1 as key, 101 as value), + AsStruct(2 as key, 34 as value), + AsStruct(4 as key, 22 as value), + AsStruct(6 as key, 256 as value), + AsStruct(7 as key, 111 as value) +); + +insert into Output +select * from as_table($t) assume order by key; diff --git a/yql/essentials/tests/sql/suites/order_by/literal_with_assume_desc.cfg b/yql/essentials/tests/sql/suites/order_by/literal_with_assume_desc.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_with_assume_desc.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/literal_with_assume_desc.sql b/yql/essentials/tests/sql/suites/order_by/literal_with_assume_desc.sql new file mode 100644 index 0000000000..dd67c120a1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/literal_with_assume_desc.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +/* multirun can not */ +/* hybridfile can not YQL-17743 */ +/* syntax version 1 */ +use plato; + +insert into Output +select x +from (select ListFromRange(10,0,-1) as x) +flatten by x +assume order by x desc; diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_assume_with_transform.cfg b/yql/essentials/tests/sql/suites/order_by/native_desc_assume_with_transform.cfg new file mode 100644 index 0000000000..e40a99f9ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_assume_with_transform.cfg @@ -0,0 +1,3 @@ +in Input input2.txt +out Output output.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_assume_with_transform.sql b/yql/essentials/tests/sql/suites/order_by/native_desc_assume_with_transform.sql new file mode 100644 index 0000000000..7f7ea25d54 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_assume_with_transform.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +USE plato; +pragma yt.UseNativeDescSort; + +INSERT INTO Output +SELECT + -(CAST(key as Int32) ?? 0) as key, + subkey, + value +FROM Input +ASSUME ORDER BY key DESC; diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_publish.cfg b/yql/essentials/tests/sql/suites/order_by/native_desc_publish.cfg new file mode 100644 index 0000000000..fccca71d35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_publish.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output output.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_publish.sql b/yql/essentials/tests/sql/suites/order_by/native_desc_publish.sql new file mode 100644 index 0000000000..18bbffc0d6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_publish.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +use plato; +pragma yt.UseNativeDescSort; + +insert into Output +select * from Input +order by key desc; + +insert into Output +select * from Input +order by key desc; diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_sort-over_sorted.cfg b/yql/essentials/tests/sql/suites/order_by/native_desc_sort-over_sorted.cfg new file mode 100644 index 0000000000..1a2a7d1afc --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_sort-over_sorted.cfg @@ -0,0 +1,3 @@ +in Input descending.txt +out Output output.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_sort.cfg b/yql/essentials/tests/sql/suites/order_by/native_desc_sort.cfg new file mode 100644 index 0000000000..fccca71d35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_sort.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output output.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_sort.sql b/yql/essentials/tests/sql/suites/order_by/native_desc_sort.sql new file mode 100644 index 0000000000..5f8292ae08 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_sort.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +use plato; +pragma yt.UseNativeDescSort; + +insert into Output +select * from Input +order by key desc; diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_sort_calc.cfg b/yql/essentials/tests/sql/suites/order_by/native_desc_sort_calc.cfg new file mode 100644 index 0000000000..fccca71d35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_sort_calc.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output output.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_sort_calc.sql b/yql/essentials/tests/sql/suites/order_by/native_desc_sort_calc.sql new file mode 100644 index 0000000000..3501a154d5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_sort_calc.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +use plato; +pragma yt.UseNativeDescSort; + +insert into Output +select * from Input +order by key || subkey desc, key desc; diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_sort_with_limit.cfg b/yql/essentials/tests/sql/suites/order_by/native_desc_sort_with_limit.cfg new file mode 100644 index 0000000000..fccca71d35 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_sort_with_limit.cfg @@ -0,0 +1,3 @@ +in Input input.txt +out Output output.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/native_desc_sort_with_limit.sql b/yql/essentials/tests/sql/suites/order_by/native_desc_sort_with_limit.sql new file mode 100644 index 0000000000..6d4be0774b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/native_desc_sort_with_limit.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +use plato; +pragma yt.UseNativeDescSort; + +insert into Output +select * from Input +order by key, subkey desc limit 3; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_dot_column.sql b/yql/essentials/tests/sql/suites/order_by/order_by_dot_column.sql new file mode 100644 index 0000000000..b844b33900 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_dot_column.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +PRAGMA DisableSimpleColumns; +SELECT 100500 as magic, t.* FROM plato.Input as t ORDER BY `t.subkey` DESC diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_dynum.sql b/yql/essentials/tests/sql/suites/order_by/order_by_dynum.sql new file mode 100644 index 0000000000..75b083db2b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_dynum.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* syntax version 1 */ +SELECT * FROM plato.Input ORDER BY CAST(key AS DyNumber); diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_dynum_desc.sql b/yql/essentials/tests/sql/suites/order_by/order_by_dynum_desc.sql new file mode 100644 index 0000000000..4229b9ccf3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_dynum_desc.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +/* syntax version 1 */ +SELECT * FROM plato.Input ORDER BY CAST(key AS DyNumber) DESC; + diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_expr.cfg new file mode 100644 index 0000000000..c7e99df4d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr.cfg @@ -0,0 +1 @@ +in Input input_expr.txt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr.sql b/yql/essentials/tests/sql/suites/order_by/order_by_expr.sql new file mode 100644 index 0000000000..ec5b1999e3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select * from plato.Input as i order by cast(i.key as uint32) * cast(subkey as uint32) LIMIT 3; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr_mul_cols.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_expr_mul_cols.cfg new file mode 100644 index 0000000000..c7e99df4d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr_mul_cols.cfg @@ -0,0 +1 @@ +in Input input_expr.txt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr_mul_cols.sql b/yql/essentials/tests/sql/suites/order_by/order_by_expr_mul_cols.sql new file mode 100644 index 0000000000..221cd5b9ee --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr_mul_cols.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select i.key, i.subkey from plato.Input as i order by cast(subkey as uint32), cast(i.key as uint32) * cast(i.subkey as uint32) desc LIMIT 3 OFFSET 4; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr_over_sorted_table.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_expr_over_sorted_table.cfg new file mode 100644 index 0000000000..fb5b1cca95 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr_over_sorted_table.cfg @@ -0,0 +1,2 @@ +in Input sorted_table1.txt +udf string_udf diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr_over_sorted_table.sql b/yql/essentials/tests/sql/suites/order_by/order_by_expr_over_sorted_table.sql new file mode 100644 index 0000000000..34d9290737 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr_over_sorted_table.sql @@ -0,0 +1,2 @@ +select key, value from plato.Input +order by key, String::SplitToList(value, "$", 2 as Limit)[0] diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr_simple.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_expr_simple.cfg new file mode 100644 index 0000000000..c7e99df4d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr_simple.cfg @@ -0,0 +1 @@ +in Input input_expr.txt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr_simple.sql b/yql/essentials/tests/sql/suites/order_by/order_by_expr_simple.sql new file mode 100644 index 0000000000..a4a7d6ba92 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr_simple.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select * from plato.Input as i order by (cast(i.key as uint32) / 10) % 10 desc, subkey; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_expr_with_deps.sql b/yql/essentials/tests/sql/suites/order_by/order_by_expr_with_deps.sql new file mode 100644 index 0000000000..581b50d142 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_expr_with_deps.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +/* syntax version 1 */ +USE plato; + +$list = select ListSort(aggregate_list(key)) from Input; + +SELECT * FROM Input +ORDER BY ListIndexOf($list ?? [], key); diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_list_of_strings.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_list_of_strings.cfg new file mode 100644 index 0000000000..14e04a5467 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_list_of_strings.cfg @@ -0,0 +1 @@ +in Input complex_type.txt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_list_of_strings.sql b/yql/essentials/tests/sql/suites/order_by/order_by_list_of_strings.sql new file mode 100644 index 0000000000..26d888f98a --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_list_of_strings.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +use plato; +select key,value from Input order by value limit 1 diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column.sql b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column.sql new file mode 100644 index 0000000000..56b49f972f --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column.sql @@ -0,0 +1,11 @@ +use plato; + +select subkey from Input order by key, value; +select subkey from Input order by "x" || key, value; +select subkey from Input order by key || "x" limit 3; + +select subkey from Input as a order by "x" || key, a.value limit 3; +select subkey from Input as a order by a.key, value limit 1; +select subkey from Input as a order by key, value limit 2; + +select subkey, key from Input order by key, value; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_as_table.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_as_table.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_as_table.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_as_table.sql b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_as_table.sql new file mode 100644 index 0000000000..d014aa41c1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_as_table.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ +/* dq can not */ +/* dqfile can not */ +/* yt can not */ + +$src = [ + <|a:4, b:4, date:4|>, + <|a:3, b:3, date:3|>, + <|a:2, b:2, date:2|>, + <|a:1, b:1, date:1|>, +]; + +select a from as_table($src) order by date; +select x.a from as_table($src) as x order by date; +select x.a from as_table($src) as x order by x.date; + diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_ignored.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_ignored.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_ignored.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_ignored.sql b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_ignored.sql new file mode 100644 index 0000000000..0a8301ea7b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_ignored.sql @@ -0,0 +1,20 @@ +/* syntax version 1 */ +/* postgres can not */ +/* dq can not */ +/* dqfile can not */ +/* yt can not */ +pragma warning("disable", "4504"); + +$t = [<|k:1, v:2|>]; + +$src = select k FROM as_table($t) order by x; +select * from $src; + +$src = select a.k as key from as_table($t) as a join as_table($t) as b on a.k=b.k order by b.u; +select * from $src; + +$src = select a.k as key from as_table($t) as a join as_table($t) as b on a.k=b.k order by v; +select * from $src; + +$src = select a.k as key from as_table($t) as a join as_table($t) as b on a.k=b.k order by z; +select * from $src; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join.sql b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join.sql new file mode 100644 index 0000000000..1573b34b06 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join.sql @@ -0,0 +1,47 @@ +/* syntax version 1 */ +/* postgres can not */ +/* dq can not */ +/* dqfile can not */ +/* yt can not */ + +$src = [ +<|a:5, b:50, zz:500|>, +<|a:4, b:40, zz:400|>, +<|a:3, b:30, zz:300|>, +<|a:2, b:20, zz:200|>, +<|a:1, b:10, zz:100|>, +]; + +$src1 = [ +<|e:5, f:50|>, +<|e:4, f:40|>, +<|e:3, f:30|>, +<|e:2, f:20|>, +<|e:1, f:10|>, +]; + + +$src = select * from as_table($src); +$src1 = select * from as_table($src1); + +select a, b from $src order by zz + 1; +select x.a, b from $src as x order by x.zz + 1; + +select * without b, a from $src order by zz + 1; +select * without b, a, zz from $src order by zz + 1; + +select * without x.b, x.a from $src as x order by zz + 1; +select * without x.b, x.a, zz from $src as x order by zz + 1; + +select a, b, x.* without b, a from $src as x order by zz + 1; +select a, b, x.* without b, a, x.zz from $src as x order by zz + 1; +select a, b, x.* without b, a, x.zz from $src as x order by x.zz + 1; + +select y.e, y.f from $src as x join $src1 as y on x.a = y.e order by x.zz; +select * without x.a, x.b, from $src as x join $src1 as y on x.a = y.e order by zz; +select x.* without x.zz from $src as x join $src1 as y on x.a = y.e order by x.zz; + +select x.*, unwrap(x.zz) as zz, without x.a, x.zz from $src as x order by zz; +select x.*, unwrap(x.zz) as zz, without x.a, x.zz from $src as x join $src1 as y on x.a = y.e order by x.zz; +select x.*, unwrap(x.zz) as zz, without x.a, x.zz from $src as x join $src1 as y on x.a = y.e order by zz; + diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join2.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join2.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join2.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join2.sql b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join2.sql new file mode 100644 index 0000000000..fa283b1894 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join2.sql @@ -0,0 +1,27 @@ +/* syntax version 1 */ +/* postgres can not */ +/* dq can not */ +/* dqfile can not */ +/* yt can not */ + +$src = [ +<|a:5, b:50, zz:500|>, +<|a:4, b:40, zz:400|>, +<|a:3, b:30, zz:300|>, +<|a:2, b:20, zz:200|>, +<|a:1, b:10, zz:100|>, +]; + +$src1 = [ +<|e:5, f:50|>, +<|e:4, f:40|>, +<|e:3, f:30|>, +<|e:2, f:20|>, +<|e:1, f:10|>, +]; + + +$src = select * from as_table($src); +$src1 = select * from as_table($src1); + +select x.zz, x.b + y.f as col1 from $src as x cross join $src1 as y where x.a = y.e order by zz, col1; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join_types.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join_types.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join_types.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join_types.sql b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join_types.sql new file mode 100644 index 0000000000..0107a069d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_missing_project_column_join_types.sql @@ -0,0 +1,47 @@ +/* syntax version 1 */ +/* postgres can not */ +/* dq can not */ +/* dqfile can not */ +/* yt can not */ + +$src = [ +<|a:5, b:50, date:500|>, +<|a:4, b:40, date:400|>, +<|a:3, b:30, date:300|>, +<|a:2, b:20, date:200|>, +<|a:1, b:10, date:100|>, +]; + +$src1 = [ +<|e:5, f:50|>, +<|e:4, f:40|>, +<|e:3, f:30|>, +<|e:2, f:20|>, +<|e:1, f:10|>, +]; + + +$src = select * from as_table($src); +$src1 = select * from as_table($src1); + +select a, b from $src order by date + 1; +select x.a, b from $src as x order by x.date + 1; + +select * without b, a from $src order by date + 1; +select * without b, a, date from $src order by date + 1; + +select * without x.b, x.a from $src as x order by date + 1; +select * without x.b, x.a, date from $src as x order by date + 1; + +select a, b, x.* without b, a from $src as x order by date + 1; +select a, b, x.* without b, a, x.date from $src as x order by date + 1; +select a, b, x.* without b, a, x.date from $src as x order by x.date + 1; + +select y.e, y.f from $src as x join $src1 as y on x.a = y.e order by x.date; +select * without x.a, x.b, from $src as x join $src1 as y on x.a = y.e order by date; +select x.* without x.date from $src as x join $src1 as y on x.a = y.e order by x.date; + +select x.*, unwrap(x.date) as date, without x.a, x.date from $src as x order by date; +select x.*, unwrap(x.date) as date, without x.a, x.date from $src as x join $src1 as y on x.a = y.e order by x.date; +select x.*, unwrap(x.date) as date, without x.a, x.date from $src as x join $src1 as y on x.a = y.e order by date; + diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_mul_columns.sql b/yql/essentials/tests/sql/suites/order_by/order_by_mul_columns.sql new file mode 100644 index 0000000000..223e3f50fe --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_mul_columns.sql @@ -0,0 +1 @@ +select key, subkey, value from plato.Input order by value, subkey;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey.cfg new file mode 100644 index 0000000000..96f3ef0db7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey.cfg @@ -0,0 +1 @@ +in Input avg_and_sum_by_value.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey.sql b/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey.sql new file mode 100644 index 0000000000..6a28c09401 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select cast(key as int) as key, subkey, value from plato.Input order by key, subkey;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey_desc.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey_desc.cfg new file mode 100644 index 0000000000..96f3ef0db7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey_desc.cfg @@ -0,0 +1 @@ +in Input avg_and_sum_by_value.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey_desc.sql b/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey_desc.sql new file mode 100644 index 0000000000..0d49cf05d7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_num_key_and_subkey_desc.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select coalesce(cast(key as int), 0) as key, subkey, value from plato.Input order by key desc, subkey;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_subquery.sqlx b/yql/essentials/tests/sql/suites/order_by/order_by_subquery.sqlx new file mode 100644 index 0000000000..e7abfd65ed --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_subquery.sqlx @@ -0,0 +1,25 @@ +/* postgres can not */ +use plato; +pragma warning("error", "4504"); + +$x = (select * from Input order by value); +select * from $x; + +select * from (select * from Input order by value); + +select * from (select * from Input as a join Input as b using(key) order by a.value); + +select * from ( +select * from Input +union all +select * from Input order by key +); + +select * from ( +SELECT + key, prefix, + COUNT(*) AS cnt, + grouping(key, prefix) as agrouping +FROM Input +GROUP BY ROLLUP (key as key, Substring(value, 1, 1) as prefix) +ORDER BY key); diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tablepath_column.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_tablepath_column.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tablepath_column.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tablepath_column.sql b/yql/essentials/tests/sql/suites/order_by/order_by_tablepath_column.sql new file mode 100644 index 0000000000..cc9ffeba99 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tablepath_column.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +use plato; +select * +from Input +order by TablePath(), key; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tablerecord_column.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_tablerecord_column.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tablerecord_column.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tablerecord_column.sql b/yql/essentials/tests/sql/suites/order_by/order_by_tablerecord_column.sql new file mode 100644 index 0000000000..834527a065 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tablerecord_column.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; +select * +from Input +order by TableRecordIndex(); diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tablerow_column.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_tablerow_column.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tablerow_column.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tablerow_column.sql b/yql/essentials/tests/sql/suites/order_by/order_by_tablerow_column.sql new file mode 100644 index 0000000000..950374674c --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tablerow_column.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +use plato; +select key +from Input +order by TableRow().key; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tuple.sql b/yql/essentials/tests/sql/suites/order_by/order_by_tuple.sql new file mode 100644 index 0000000000..8e33db8a2c --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tuple.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +USE plato; + +select * from ( + select key, AsTuple(key, subkey) as tpl from Input +) order by tpl; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tuple_and_member.sql b/yql/essentials/tests/sql/suites/order_by/order_by_tuple_and_member.sql new file mode 100644 index 0000000000..fecbcc7de3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tuple_and_member.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +USE plato; + +select * from ( + select key, AsTuple(key, subkey) as tpl from Input +) order by key, tpl; diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_tuple_expr.sql b/yql/essentials/tests/sql/suites/order_by/order_by_tuple_expr.sql new file mode 100644 index 0000000000..cb2b0b538b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_tuple_expr.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +USE plato; + +$keys = ($key) -> { + return AsTuple($key, $key); +}; + +select * from Input order by $keys(value); diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_udf.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_udf.cfg new file mode 100644 index 0000000000..677bf9308b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_udf.cfg @@ -0,0 +1,2 @@ +in Input input2.txt +udf math_udf diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_udf.sql b/yql/essentials/tests/sql/suites/order_by/order_by_udf.sql new file mode 100644 index 0000000000..998abf210b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_udf.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +SELECT * +FROM plato.Input +ORDER BY Math::Pow(cast(subkey as double), 2); diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_udf_duo.cfg b/yql/essentials/tests/sql/suites/order_by/order_by_udf_duo.cfg new file mode 100644 index 0000000000..677bf9308b --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_udf_duo.cfg @@ -0,0 +1,2 @@ +in Input input2.txt +udf math_udf diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_udf_duo.sql b/yql/essentials/tests/sql/suites/order_by/order_by_udf_duo.sql new file mode 100644 index 0000000000..17032c0605 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_udf_duo.sql @@ -0,0 +1,5 @@ +/* postgres can not */ + +SELECT * +FROM plato.Input +ORDER BY Math::Pow(cast(subkey as double), 2), Math::Pow(cast(key as double), 2); diff --git a/yql/essentials/tests/sql/suites/order_by/order_by_value_desc.sql b/yql/essentials/tests/sql/suites/order_by/order_by_value_desc.sql new file mode 100644 index 0000000000..df3e4d9be0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_by_value_desc.sql @@ -0,0 +1 @@ +select key, subkey, value from plato.Input order by value desc;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/order_with_null.sql b/yql/essentials/tests/sql/suites/order_by/order_with_null.sql new file mode 100644 index 0000000000..27171419c4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/order_with_null.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +use plato; + +$input = ( +select null as key, "0" as subkey, "kkk" as value +union all +select * from Input +); + +select * from $input order by key asc; +select * from $input order by key desc; diff --git a/yql/essentials/tests/sql/suites/order_by/ordered_fill.cfg b/yql/essentials/tests/sql/suites/order_by/ordered_fill.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/ordered_fill.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/ordered_fill.sql b/yql/essentials/tests/sql/suites/order_by/ordered_fill.sql new file mode 100644 index 0000000000..97ce0503f3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/ordered_fill.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +/* multirun can not */ +use plato; + +insert into Output +select x from (select ListFromRange(0,100) as x) flatten by x order by x; + +commit; + +select * from Output where x=50;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/presort_mem.sql b/yql/essentials/tests/sql/suites/order_by/presort_mem.sql new file mode 100644 index 0000000000..1c6bf964ae --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/presort_mem.sql @@ -0,0 +1,76 @@ +/* postgres can not */ +select Yql::Sort( + AsList( + AsTuple(3,1), + AsTuple(1,2), + AsTuple(1,3), + ), + true, + ($x)->{return $x;} +); + +select Yql::Sort( + AsList( + AsTuple(3,1), + AsTuple(1,1), + AsTuple(1,3), + ), + AsTuple(true, false), + ($x)->{return $x;} +); + +select Yql::Sort( + AsList( + AsList(3,1), + AsList(1,2), + AsList(2,3), + AsList(1,2,3) + ), + true, + ($x)->{return $x;} +); + +select Yql::Sort( + AsList( + AsTuple(1, AsList(3,1)), + AsTuple(1, AsList(1,2)), + AsTuple(1, AsList(2,3)), + AsTuple(1, AsList(1,2,3)), + AsTuple(2, AsList(3,1)), + AsTuple(2, AsList(1,2)), + AsTuple(2, AsList(2,3)), + AsTuple(2, AsList(1,2,3)) + ), + AsTuple(true, false), + ($x)->{return $x;} +); + +select Yql::Sort( + AsList( + AsTuple(1, AsList(3,1)), + AsTuple(1, AsList(1,2)), + AsTuple(1, AsList(2,3)), + AsTuple(1, AsList(1,2,3)), + AsTuple(2, AsList(3,1)), + AsTuple(2, AsList(1,2)), + AsTuple(2, AsList(2,3)), + AsTuple(2, AsList(1,2,3)) + ), + AsTuple(true, true), + ($x)->{return $x;} +); + +select Yql::Sort( + AsList( + AsTuple(1, AsList(3,1)), + AsTuple(1, AsList(1,2)), + AsTuple(1, AsList(2,3)), + AsTuple(1, AsList(1,2,3)), + AsTuple(2, AsList(3,1)), + AsTuple(2, AsList(1,2)), + AsTuple(2, AsList(2,3)), + AsTuple(2, AsList(1,2,3)) + ), + false, + ($x)->{return $x;} +); diff --git a/yql/essentials/tests/sql/suites/order_by/presort_order_by_table.sql b/yql/essentials/tests/sql/suites/order_by/presort_order_by_table.sql new file mode 100644 index 0000000000..9929a31017 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/presort_order_by_table.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +use plato; + +$list = AsList( + AsList(3,1), + AsList(1,1), + AsList(1), + ); + +insert into @foo +select x from (select $list as x) +flatten by x; +commit; +select * from @foo +order by x asc diff --git a/yql/essentials/tests/sql/suites/order_by/read_schema.txt b/yql/essentials/tests/sql/suites/order_by/read_schema.txt new file mode 100644 index 0000000000..851703e9a0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/read_schema.txt @@ -0,0 +1,5 @@ +{"a"=1;"b"=1u;"c"=0.1;"d"="x";"e"=%false;"f"={"a"="b";"c"="d";"e"=[0;1;2]}}; +{"a"=2;"b"=2u;"c"=2.5;"d"="xx";"e"=%true;"f"=0.123}; +{"a"=-5;"b"=8u;"c"=10.;"d"="xxx";"f"="xyz"}; +{"b"=5u;"d"="skdjfnsdf";"e"=%false}; +{}; diff --git a/yql/essentials/tests/sql/suites/order_by/read_schema.txt.attr b/yql/essentials/tests/sql/suites/order_by/read_schema.txt.attr new file mode 100644 index 0000000000..3176be619e --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/read_schema.txt.attr @@ -0,0 +1,3 @@ +{ + "_read_schema"=[{"type"="int64";"name"="a"};{"type"="uint64";"name"="b"};{"type"="double";"name"="c"};{"type"="string";"name"="d"};{"type"="boolean";name="e"};{"type"="any";"name"="f"}] +} diff --git a/yql/essentials/tests/sql/suites/order_by/single_item_tuple.sql b/yql/essentials/tests/sql/suites/order_by/single_item_tuple.sql new file mode 100644 index 0000000000..d04c7f9040 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/single_item_tuple.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +SELECT (42,) AS s ASSUME ORDER BY s; diff --git a/yql/essentials/tests/sql/suites/order_by/singular.sql b/yql/essentials/tests/sql/suites/order_by/singular.sql new file mode 100644 index 0000000000..8c22ab69f6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/singular.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +/* syntax version 1 */ +/* hybridfile can not YQL-17743 */ +use plato; + +insert into @foo +select void() as x,null as y,[] as z,{} as w +order by x,y,z,w; + +commit; + +select * from @foo; diff --git a/yql/essentials/tests/sql/suites/order_by/sort.cfg b/yql/essentials/tests/sql/suites/order_by/sort.cfg new file mode 100644 index 0000000000..c3d8fbb039 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort.cfg @@ -0,0 +1 @@ +in Input unsorted.txt diff --git a/yql/essentials/tests/sql/suites/order_by/sort.sql b/yql/essentials/tests/sql/suites/order_by/sort.sql new file mode 100644 index 0000000000..37ceaff6de --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort.sql @@ -0,0 +1 @@ +select * from plato.Input order by d, a, b, c; diff --git a/yql/essentials/tests/sql/suites/order_by/sort_decimals.cfg b/yql/essentials/tests/sql/suites/order_by/sort_decimals.cfg new file mode 100644 index 0000000000..1d2a5c3bce --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort_decimals.cfg @@ -0,0 +1 @@ +in Input decimals.txt diff --git a/yql/essentials/tests/sql/suites/order_by/sort_decimals.sql b/yql/essentials/tests/sql/suites/order_by/sort_decimals.sql new file mode 100644 index 0000000000..5c250e1658 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort_decimals.sql @@ -0,0 +1,4 @@ +select * from plato.Input order by Cast(c as Decimal(13,3)); +select * from plato.Input order by Cast(c as Decimal(13,3)) desc; +select * from plato.Input order by Cast(d as Decimal(5,1)); +select * from plato.Input order by Cast(d as Decimal(5,1)) desc; diff --git a/yql/essentials/tests/sql/suites/order_by/sort_simple.cfg b/yql/essentials/tests/sql/suites/order_by/sort_simple.cfg new file mode 100644 index 0000000000..c3d8fbb039 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort_simple.cfg @@ -0,0 +1 @@ +in Input unsorted.txt diff --git a/yql/essentials/tests/sql/suites/order_by/sort_simple.sql b/yql/essentials/tests/sql/suites/order_by/sort_simple.sql new file mode 100644 index 0000000000..8db149f8f4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort_simple.sql @@ -0,0 +1,6 @@ +select a from plato.Input order by a; +select b from plato.Input order by b; +select c from plato.Input order by c; +select d from plato.Input order by d; +select e from plato.Input order by e; + diff --git a/yql/essentials/tests/sql/suites/order_by/sort_with_take.cfg b/yql/essentials/tests/sql/suites/order_by/sort_with_take.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort_with_take.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/sort_with_take.sql b/yql/essentials/tests/sql/suites/order_by/sort_with_take.sql new file mode 100644 index 0000000000..f728aecb00 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort_with_take.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +USE plato; + +insert into Output with truncate +select * from (select * from Input limit 3) +order by key; diff --git a/yql/essentials/tests/sql/suites/order_by/sort_with_take_limit.cfg b/yql/essentials/tests/sql/suites/order_by/sort_with_take_limit.cfg new file mode 100644 index 0000000000..ad52c79527 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort_with_take_limit.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/order_by/sort_with_take_limit.sql b/yql/essentials/tests/sql/suites/order_by/sort_with_take_limit.sql new file mode 100644 index 0000000000..a7b4e757e7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sort_with_take_limit.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +USE plato; + +insert into Output with truncate +select * from (select * from Input limit 3) +order by key limit 2; diff --git a/yql/essentials/tests/sql/suites/order_by/sorted_table1.txt b/yql/essentials/tests/sql/suites/order_by/sorted_table1.txt new file mode 100644 index 0000000000..73eb5e6653 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sorted_table1.txt @@ -0,0 +1,4 @@ +{"key"="1";"subkey"="1";"value"="z$c"}; +{"key"="1";"subkey"="3";"value"="y$b"}; +{"key"="1";"subkey"="5";"value"="x$a"}; + diff --git a/yql/essentials/tests/sql/suites/order_by/sorted_table1.txt.attr b/yql/essentials/tests/sql/suites/order_by/sorted_table1.txt.attr new file mode 100644 index 0000000000..ba8118899c --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/sorted_table1.txt.attr @@ -0,0 +1,12 @@ +{"_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/yql/essentials/tests/sql/suites/order_by/tuple01.sql b/yql/essentials/tests/sql/suites/order_by/tuple01.sql new file mode 100644 index 0000000000..da079265d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/tuple01.sql @@ -0,0 +1,32 @@ +/* postgres can not */ + +select Yql::Sort( +AsList(3,2,1), +AsTuple(), +($_x)->{return AsTuple()} +); + + +select Yql::Sort( +AsList(3,2,1), +AsTuple(true), +($x)->{return $x} +); + +select Yql::Sort( +AsList(3,2,1), +true, +($x)->{return $x} +); + +select Yql::Sort( +AsList(3,2,1), +AsTuple(true), +($x)->{return AsTuple($x)} +); + +select Yql::Sort( +AsList(3,2,1), +AsTuple(true,true), +($x)->{return AsTuple($x,$x)} +); diff --git a/yql/essentials/tests/sql/suites/order_by/union_all.cfg b/yql/essentials/tests/sql/suites/order_by/union_all.cfg new file mode 100644 index 0000000000..de341b36d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/union_all.cfg @@ -0,0 +1,2 @@ +in Input input.txt +out Output output.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/union_all.sql b/yql/essentials/tests/sql/suites/order_by/union_all.sql new file mode 100644 index 0000000000..fce8866ddf --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/union_all.sql @@ -0,0 +1,21 @@ +USE plato; + +pragma DqEngine = "disable"; + +DEFINE SUBQUERY $sample($product_type) AS + + SELECT * + FROM Input + WHERE subkey = $product_type + ORDER BY key + LIMIT 10; + +END DEFINE; + +$list = ["a", "b"]; +$s = SubqueryUnionAllFor($list, $sample); +$concated = PROCESS $s(); + +INSERT INTO Output +SELECT * +FROM $concated
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/order_by/unsorted.txt b/yql/essentials/tests/sql/suites/order_by/unsorted.txt new file mode 100644 index 0000000000..30aeaac5b3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/unsorted.txt @@ -0,0 +1,8 @@ +{"a"=1; "b"=1u; "c"=0.1; "d"="x"; "e"=%false; "f"={"a"="b";"c"="d";"e"=[0;1;2]}}; +{"a"=2; "b"=2u; "c"=2.5; "d"="xx"; "e"=%true; "f"=0.123}; +{"a"=-5;"b"=8u; "c"=10.; "d"="xxx"; "f"="xyz"}; +{ "b"=5u; "d"="skdjfnsdf";"e"=%false}; +{}; +{"a"=-5;"b"=8u; "c"=12.; "d"="xxx"; "f"="xyz"}; +{"a"=-5;"b"=8u; "c"=10.01; "d"=""; "f"=%false}; +{} diff --git a/yql/essentials/tests/sql/suites/order_by/unsorted.txt.attr b/yql/essentials/tests/sql/suites/order_by/unsorted.txt.attr new file mode 100644 index 0000000000..3176be619e --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/unsorted.txt.attr @@ -0,0 +1,3 @@ +{ + "_read_schema"=[{"type"="int64";"name"="a"};{"type"="uint64";"name"="b"};{"type"="double";"name"="c"};{"type"="string";"name"="d"};{"type"="boolean";name="e"};{"type"="any";"name"="f"}] +} diff --git a/yql/essentials/tests/sql/suites/order_by/warn_offset_wo_sort.cfg b/yql/essentials/tests/sql/suites/order_by/warn_offset_wo_sort.cfg new file mode 100644 index 0000000000..8300ccd161 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/warn_offset_wo_sort.cfg @@ -0,0 +1 @@ +in Input input_sorted.txt diff --git a/yql/essentials/tests/sql/suites/order_by/warn_offset_wo_sort.sql b/yql/essentials/tests/sql/suites/order_by/warn_offset_wo_sort.sql new file mode 100644 index 0000000000..7e7f146553 --- /dev/null +++ b/yql/essentials/tests/sql/suites/order_by/warn_offset_wo_sort.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +USE plato; + +select * from Input +limit 5 offset 10; |