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/aggregate | |
| 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/aggregate')
301 files changed, 2368 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/aggregate/GroupByOneField.cfg b/yql/essentials/tests/sql/suites/aggregate/GroupByOneField.cfg new file mode 100644 index 0000000000..2dc97b5e95 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/GroupByOneField.cfg @@ -0,0 +1 @@ +in Input columns.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/GroupByOneField.sql b/yql/essentials/tests/sql/suites/aggregate/GroupByOneField.sql new file mode 100644 index 0000000000..fcb528e1e9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/GroupByOneField.sql @@ -0,0 +1,2 @@ +/* syntax version 1 */ +select sum(c) as sumc, max(d) as maxd from plato.Input group by a order by sumc, maxd; diff --git a/yql/essentials/tests/sql/suites/aggregate/GroupByTwoFields.cfg b/yql/essentials/tests/sql/suites/aggregate/GroupByTwoFields.cfg new file mode 100644 index 0000000000..2dc97b5e95 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/GroupByTwoFields.cfg @@ -0,0 +1 @@ +in Input columns.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/GroupByTwoFields.sql b/yql/essentials/tests/sql/suites/aggregate/GroupByTwoFields.sql new file mode 100644 index 0000000000..94ea550019 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/GroupByTwoFields.sql @@ -0,0 +1,2 @@ +/* syntax version 1 */ +select sum(c) as sum_c, max(d) as max_d from plato.Input group by a, b order by sum_c, max_d; diff --git a/yql/essentials/tests/sql/suites/aggregate/agg_filter_pushdown.cfg b/yql/essentials/tests/sql/suites/aggregate/agg_filter_pushdown.cfg new file mode 100644 index 0000000000..73dfbac8bf --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/agg_filter_pushdown.cfg @@ -0,0 +1 @@ +in Input dedup_state_keys.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/agg_filter_pushdown.sql b/yql/essentials/tests/sql/suites/aggregate/agg_filter_pushdown.sql new file mode 100644 index 0000000000..a728c4895c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/agg_filter_pushdown.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +USE plato; + +select * from ( + select key, subkey, max(value) from Input group by key, subkey + having count(*) < 100 and subkey > "0" +) +where key > "1" and Likely(subkey < "4") +order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/agg_full_table_list.sql b/yql/essentials/tests/sql/suites/aggregate/agg_full_table_list.sql new file mode 100644 index 0000000000..5f95cf878b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/agg_full_table_list.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ +select agglist(x) from ( +select 1 as x +); + + +select agglist(x) from ( +select 1 as x +limit 0 +); diff --git a/yql/essentials/tests/sql/suites/aggregate/agg_phases_table1.sql b/yql/essentials/tests/sql/suites/aggregate/agg_phases_table1.sql new file mode 100644 index 0000000000..9b657bd938 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/agg_phases_table1.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; +pragma EmitAggApply; + +pragma yt.UseAggPhases = "1"; + +SELECT + key, + count(value) +FROM Input +GROUP BY key +ORDER BY key diff --git a/yql/essentials/tests/sql/suites/aggregate/agg_phases_table2.sql b/yql/essentials/tests/sql/suites/aggregate/agg_phases_table2.sql new file mode 100644 index 0000000000..d434180941 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/agg_phases_table2.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; +pragma EmitAggApply; + +pragma yt.UseAggPhases = "1"; + +SELECT + key, + count(distinct value) +FROM Input +GROUP BY key +ORDER BY key diff --git a/yql/essentials/tests/sql/suites/aggregate/agg_phases_table3.sql b/yql/essentials/tests/sql/suites/aggregate/agg_phases_table3.sql new file mode 100644 index 0000000000..a39958bac3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/agg_phases_table3.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; +pragma EmitAggApply; + +pragma yt.UseAggPhases = "1"; + +SELECT + key, + count(value), + count(distinct value) +FROM Input +GROUP BY key +ORDER BY key diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_by_column_lookup_in_const_dict.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_by_column_lookup_in_const_dict.sql new file mode 100644 index 0000000000..e38a70598d --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_by_column_lookup_in_const_dict.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +USE plato; + +$dict = AsDict(("800", "foo")); + +SELECT + lookup_result +FROM Input +GROUP BY $dict[key] ?? "bar" AS lookup_result +ORDER BY lookup_result; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_by_one_column.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_by_one_column.sql new file mode 100644 index 0000000000..d2127859b7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_by_one_column.sql @@ -0,0 +1,2 @@ +/* syntax version 1 */ +select key, "WAT" as subkey, Max(value) as value from plato.Input group by key order by key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr.sql new file mode 100644 index 0000000000..46f2a248c7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + + +select + sum(distinct cast(Unicode::ToLower(CAST(subkey AS Utf8)) as Int32)) + sum(distinct cast(Unicode::ToUpper(CAST(subkey AS Utf8)) as Uint64)) as sks, + ListSort(aggregate_list(distinct key || "_")) as kl +from Input3; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_groupby_expr.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_groupby_expr.sql new file mode 100644 index 0000000000..ffc79038d5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_groupby_expr.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + + +select key, + sum(distinct cast(Unicode::ToLower(CAST(subkey AS Utf8)) as Int32)) + sum(distinct cast(Unicode::ToUpper(CAST(subkey AS Utf8)) as Uint64)) as sks, + aggregate_list(distinct key || "") as kl +from Input3 +group by key || "foo" as key +order by key; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_udf.cfg b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_udf.cfg new file mode 100644 index 0000000000..6a677c3b7f --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_udf.cfg @@ -0,0 +1,2 @@ +in Input2 input2.txt +udf math_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_udf.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_udf.sql new file mode 100644 index 0000000000..456f3a9ab8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_expr_with_udf.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +select + Math::Round(count(distinct Math::Round(cast(key as Int32)))/100.0, -2) +from Input2; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_in_access_node_exprs.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_in_access_node_exprs.sql new file mode 100644 index 0000000000..0f0c3b68b3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_in_access_node_exprs.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +SELECT + key, + AGGREGATE_LIST(DISTINCT cast(subkey as Int32))[COUNT(DISTINCT cast(subkey as Uint64)) - 1] as foo +FROM + AS_TABLE([<|key:1, subkey:"1"|>, + <|key:2, subkey:"2"|>, + <|key:1, subkey:"1"|>, + <|key:2, subkey:"2"|>]) +GROUP BY key +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_list.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_list.sql new file mode 100644 index 0000000000..ee6f70e39d --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_list.sql @@ -0,0 +1,38 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +insert into @foo +select AsList(1,2) as x +union all +select AsList(1,3) as x +union all +select AsList(1,2) as x; + +commit; + +select listlength(aggregate_list(distinct x)) as c +from @foo; + +select count(distinct x) as c +from @foo; + +insert into @bar +select AsList(1,2) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,2) as x,AsList(5) as y +union all +select AsList(1,2) as x,AsList(5) as y; + +commit; + +select x,count(distinct y) as c +from @bar +group by x +order by c; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_struct_access.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_struct_access.sql new file mode 100644 index 0000000000..e9d312c63b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_distinct_struct_access.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$withStruct = select subkey, value, AsStruct(key as key) as s from Input3; + +select count(distinct s.key) as cnt from $withStruct; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_inmem_distinct_list.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_inmem_distinct_list.sql new file mode 100644 index 0000000000..5ea0b2283e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_inmem_distinct_list.sql @@ -0,0 +1,32 @@ +/* syntax version 1 */ +/* postgres can not */ +select listlength(aggregate_list(distinct x)) as c from ( +select AsList(1,2) as x +union all +select AsList(1,3) as x +union all +select AsList(1,2) as x +); + +select count(distinct x) as c from ( +select AsList(1,2) as x +union all +select AsList(1,3) as x +union all +select AsList(1,2) as x +); + +select x,count(distinct y) as c from ( +select AsList(1,2) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,2) as x,AsList(5) as y +union all +select AsList(1,2) as x,AsList(5) as y +) group by x +order by c; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_inmem_list_in_key.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_inmem_list_in_key.sql new file mode 100644 index 0000000000..4158e0e80f --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_inmem_list_in_key.sql @@ -0,0 +1,26 @@ +/* syntax version 1 */ +/* postgres can not */ +select x,count(*) as c from ( +select AsList(1,2) as x +union all +select AsList(1,3) as x +union all +select AsList(1,2) as x +) +group by x +order by c; + +select x,y,count(*) as c from ( +select AsList(1,2) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,3) as x,AsList(4) as y +union all +select AsList(1,2) as x,AsList(5) as y +union all +select AsList(1,2) as x,AsList(5) as y +) group by x, y +order by c; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_key_column.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_key_column.sql new file mode 100644 index 0000000000..cd95e5be56 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_key_column.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +/* postgres can not */ +SELECT + Some(key) as some_key, +FROM + plato.Input +GROUP BY + key +ORDER BY some_key; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_list_in_key.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_list_in_key.sql new file mode 100644 index 0000000000..9aa0bd88fa --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_list_in_key.sql @@ -0,0 +1,62 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; +insert into @foo +select AsList(1,2) as x,1 as y +union all +select AsList(1,3) as x,2 as y +union all +select AsList(1,2) as x,3 as y; +commit; + +select x,count(*) as c +from @foo +group by x +order by c; + +insert into @bar +select AsList(1,2) as x,AsList(4) as y, 1 as z +union all +select AsList(1,3) as x,AsList(4) as y, 2 as z +union all +select AsList(1,3) as x,AsList(4) as y, 3 as z +union all +select AsList(1,3) as x,AsList(4) as y, 4 as z +union all +select AsList(1,2) as x,AsList(5) as y, 5 as z +union all +select AsList(1,2) as x,AsList(5) as y, 6 as z; +commit; + +select x,y,count(*) as c +from @bar +group by x, y +order by c; + +select x,y,count(distinct z) as c +from @bar +group by x,y +order by c; + +select x,y, min(z) as m, count(distinct z) as c +from @bar +group by x,y +order by c; + +select x +from @bar as t +group by x +order by t.x[1]; + +select x,y +from @bar as t +group by x, y +order by t.x[1],t.y[0]; + +select distinct x +from @bar as t +order by t.x[1] desc; + +select distinct x,y +from @bar as t +order by t.x[1] desc,t.y[0] desc; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_subquery_yql_15869.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_subquery_yql_15869.sql new file mode 100644 index 0000000000..f3fc92b09e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_subquery_yql_15869.sql @@ -0,0 +1,7 @@ +use plato; + +$a = select CurrentUtcDate() as _date, Just(1.0) as parsed_lag from Input; + +SELECT + SUM(parsed_lag) +FROM $a; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_udf_nested.cfg b/yql/essentials/tests/sql/suites/aggregate/aggregate_udf_nested.cfg new file mode 100644 index 0000000000..060e6c9057 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_udf_nested.cfg @@ -0,0 +1,5 @@ +in Input input.txt +in Input2 input2.txt +in Input3 input3.txt +in Input4 input4.txt +udf string_udf
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_udf_nested.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_udf_nested.sql new file mode 100644 index 0000000000..a65c584f1a --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_udf_nested.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +SELECT + String::HexText(String::HexText(value)) as value +FROM plato.Input4 +GROUP BY value +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_with_const_yson_options.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_const_yson_options.sql new file mode 100644 index 0000000000..c940ba32cd --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_const_yson_options.sql @@ -0,0 +1,8 @@ +USE plato; + +SELECT + key, + Yson::SerializeJson(Yson::From(AGGREGATE_LIST(value), Yson::Options(true AS Strict))) as value +FROM Input +GROUP BY key +ORDER BY key diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_with_deep_aggregated_column.cfg b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_deep_aggregated_column.cfg new file mode 100644 index 0000000000..582479bcde --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_deep_aggregated_column.cfg @@ -0,0 +1 @@ +in Input input_tutorial_users.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_with_deep_aggregated_column.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_deep_aggregated_column.sql new file mode 100644 index 0000000000..c251ae8c4f --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_deep_aggregated_column.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +--insert into Output +select + region, + max(case when age % 10u between 1u and region % 10u then age else 0u end) as max_age_at_range_intersect +from $data +group by region +order by region +; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_with_default_yson_options.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_default_yson_options.sql new file mode 100644 index 0000000000..ef6a64cf42 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_default_yson_options.sql @@ -0,0 +1,8 @@ +USE plato; + +SELECT + key, + Yson::SerializeJson(Yson::From(AGGREGATE_LIST(value))) as value +FROM Input +GROUP BY key +ORDER BY key diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda.cfg b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda.sql new file mode 100644 index 0000000000..4a10cda985 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$empty = ($list) -> { + RETURN ListCreate(TypeOf($list[0])); +}; + +SELECT + $empty(AGGREGATE_LIST(key)) +FROM Input +GROUP BY value; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda_inside_avg.cfg b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda_inside_avg.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda_inside_avg.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda_inside_avg.sql b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda_inside_avg.sql new file mode 100644 index 0000000000..7672cec89e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregate_with_lambda_inside_avg.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$cast_to_double = ($column) -> { + RETURN CAST($column as Double); +}; +$column_name = 'key'; +SELECT AVG($cast_to_double($column_name)) +FROM Input; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregation_and_order.sql b/yql/essentials/tests/sql/suites/aggregate/aggregation_and_order.sql new file mode 100644 index 0000000000..940c38c928 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregation_and_order.sql @@ -0,0 +1,2 @@ +/* syntax version 1 */ +select key, Min(subkey) as subkey, Max(value) as value from plato.Input group by key order by key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregation_by_udf.cfg b/yql/essentials/tests/sql/suites/aggregate/aggregation_by_udf.cfg new file mode 100644 index 0000000000..55c2f97264 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregation_by_udf.cfg @@ -0,0 +1,5 @@ +in Input input.txt +in Input2 input2.txt +in Input3 input3.txt +in Input4 input4.txt +udf math_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregation_by_udf.sql b/yql/essentials/tests/sql/suites/aggregate/aggregation_by_udf.sql new file mode 100644 index 0000000000..105378efc1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregation_by_udf.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +SELECT sum(Math::Pow(cast(subkey as double), 2)) +FROM plato.Input4; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregation_with_named_node.cfg b/yql/essentials/tests/sql/suites/aggregate/aggregation_with_named_node.cfg new file mode 100644 index 0000000000..a8a884001d --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregation_with_named_node.cfg @@ -0,0 +1,5 @@ +in Input input.txt +in Input2 input2.txt +in Input3 input3.txt +in Input4 input4.txt +udf stat_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/aggregation_with_named_node.sql b/yql/essentials/tests/sql/suites/aggregate/aggregation_with_named_node.sql new file mode 100644 index 0000000000..33757e759c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggregation_with_named_node.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +$data = (SELECT cast(key as Uint32) ?? 0 as key, value FROM plato.Input); + +$quant = 0.1; +SELECT + $quant * 100 as quantile, + PERCENTILE(key, $quant) as key_q, + COUNT(*) as count +FROM $data; diff --git a/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping.cfg b/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping.cfg new file mode 100644 index 0000000000..272cf740b2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping.cfg @@ -0,0 +1,3 @@ +in Input input_sorted.txt +udf stat_udf +udf math_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping.sql b/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping.sql new file mode 100644 index 0000000000..b03b5283be --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping.sql @@ -0,0 +1,43 @@ +/* syntax version 1 */ +/* postgres can not */ +select + count(key) as keyCount, + count(sub) as subCount, + count(val) as valCount, + countIf(sub % 2 == 0) as evenCount, + countIf(sub % 2 == 1) as oddCount, + every(sub % 2 == 0) as every, + boolOr(sub % 2 == 0) as boolOr, + avg(key) as keyAvg, + avg(sub) as subAvg, + min(key) as keyMin, + min(sub) as subMin, + min(val) as valMin, + max(key) as keyMax, + max(sub) as subMax, + max(val) as valMax, + some(key) as keySome, + some(sub) as subSome, + some(val) as valSome, + bitAnd(cast(key AS Uint64)) as keyBitAnd, + bitOr(cast(key AS Uint64)) as keyBitOr, + bitXor(cast(key AS Uint64)) as keyBitXor, + bitAnd(cast(sub AS Uint64)) as subBitAnd, + bitOr(cast(sub AS Uint64)) as subBitOr, + bitXor(cast(sub AS Uint64)) as subBitXor, + median(key) as keyMedian, + median(sub) as subMedian, + stdDev(key) as keyStdDev, + stdDev(sub) as subStdDev, + stdDev(empty) as emptyStdDev, + variance(key) as keyVariance, + variance(sub) as subVariance, + stdDevPop(key) as keyPopStdDev, + stdDevPop(sub) as subPopStdDev, + varPop(key) as keyPopVariance, + varPop(sub) as subPopVariance, + correlation(key, sub) AS corr, + covariance(key, sub) AS covar, + covarpop(key, sub) AS covarpop +from + (select cast(key as int) as key, Unwrap(cast(subkey as int)) as sub, value as val, cast(value AS int) AS empty from plato.Input); diff --git a/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping_via_map.sql b/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping_via_map.sql new file mode 100644 index 0000000000..cb1105b769 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping_via_map.sql @@ -0,0 +1,44 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma yt.PartitionByConstantKeysViaMap; +select + count(key) as keyCount, + count(sub) as subCount, + count(val) as valCount, + countIf(sub % 2 == 0) as evenCount, + countIf(sub % 2 == 1) as oddCount, + every(sub % 2 == 0) as every, + boolOr(sub % 2 == 0) as boolOr, + avg(key) as keyAvg, + avg(sub) as subAvg, + min(key) as keyMin, + min(sub) as subMin, + min(val) as valMin, + max(key) as keyMax, + max(sub) as subMax, + max(val) as valMax, + some(key) as keySome, + some(sub) as subSome, + some(val) as valSome, + bitAnd(cast(key AS Uint64)) as keyBitAnd, + bitOr(cast(key AS Uint64)) as keyBitOr, + bitXor(cast(key AS Uint64)) as keyBitXor, + bitAnd(cast(sub AS Uint64)) as subBitAnd, + bitOr(cast(sub AS Uint64)) as subBitOr, + bitXor(cast(sub AS Uint64)) as subBitXor, + median(key) as keyMedian, + median(sub) as subMedian, + stdDev(key) as keyStdDev, + stdDev(sub) as subStdDev, + stdDev(empty) as emptyStdDev, + variance(key) as keyVariance, + variance(sub) as subVariance, + stdDevPop(key) as keyPopStdDev, + stdDevPop(sub) as subPopStdDev, + varPop(key) as keyPopVariance, + varPop(sub) as subPopVariance, + correlation(key, sub) AS corr, + covariance(key, sub) AS covar, + covarpop(key, sub) AS covarpop +from + (select cast(key as int) as key, Unwrap(cast(subkey as int)) as sub, value as val, cast(value AS int) AS empty from plato.Input); diff --git a/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping_via_map_compact.sql b/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping_via_map_compact.sql new file mode 100644 index 0000000000..acb0e79e4e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/aggrs_no_grouping_via_map_compact.sql @@ -0,0 +1,45 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma yt.PartitionByConstantKeysViaMap; +select + count(key) as keyCount, + count(sub) as subCount, + count(val) as valCount, + countIf(sub % 2 == 0) as evenCount, + countIf(sub % 2 == 1) as oddCount, + every(sub % 2 == 0) as every, + boolOr(sub % 2 == 0) as boolOr, + avg(key) as keyAvg, + avg(sub) as subAvg, + min(key) as keyMin, + min(sub) as subMin, + min(val) as valMin, + max(key) as keyMax, + max(sub) as subMax, + max(val) as valMax, + some(key) as keySome, + some(sub) as subSome, + some(val) as valSome, + bitAnd(cast(key AS Uint64)) as keyBitAnd, + bitOr(cast(key AS Uint64)) as keyBitOr, + bitXor(cast(key AS Uint64)) as keyBitXor, + bitAnd(cast(sub AS Uint64)) as subBitAnd, + bitOr(cast(sub AS Uint64)) as subBitOr, + bitXor(cast(sub AS Uint64)) as subBitXor, + median(key) as keyMedian, + median(sub) as subMedian, + stdDev(key) as keyStdDev, + stdDev(sub) as subStdDev, + stdDev(empty) as emptyStdDev, + variance(key) as keyVariance, + variance(sub) as subVariance, + stdDevPop(key) as keyPopStdDev, + stdDevPop(sub) as subPopStdDev, + varPop(key) as keyPopVariance, + varPop(sub) as subPopVariance, + correlation(key, sub) AS corr, + covariance(key, sub) AS covar, + covarpop(key, sub) AS covarpop +from + (select cast(key as int) as key, Unwrap(cast(subkey as int)) as sub, value as val, cast(value AS int) AS empty from plato.Input) +group compact by (); diff --git a/yql/essentials/tests/sql/suites/aggregate/avg_and_sum.sql b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum.sql new file mode 100644 index 0000000000..cdbf1dd58b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +select + avg(cast(key as int)) as key, + cast(sum(cast(subkey as int)) as varchar) as subkey, + min(value) as value +from plato.Input; diff --git a/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_by_value.cfg b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_by_value.cfg new file mode 100644 index 0000000000..8153a1f3e5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_by_value.cfg @@ -0,0 +1 @@ +in Input avg_and_sum_by_value.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_by_value.sql b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_by_value.sql new file mode 100644 index 0000000000..c8a43bc533 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_by_value.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +select + avg(cast(key as int)) + 0.3 as key, + cast(sum(cast(subkey as int)) as varchar) as subkey, + value +from plato.Input +group by value +order by value; diff --git a/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_by_value.txt b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_by_value.txt new file mode 100644 index 0000000000..cebd64e139 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/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/aggregate/avg_and_sum_float.cfg b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_float.cfg new file mode 100644 index 0000000000..812be3893b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_float.cfg @@ -0,0 +1 @@ +in Input input4.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_float.sql b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_float.sql new file mode 100644 index 0000000000..dc2a4d3157 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/avg_and_sum_float.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +select + key, + avg(cast(subkey as Float)) as avg, + sum(cast(subkey as Float)) as sum, +from plato.Input +group by key +order by key; diff --git a/yql/essentials/tests/sql/suites/aggregate/avg_interval.sql b/yql/essentials/tests/sql/suites/aggregate/avg_interval.sql new file mode 100644 index 0000000000..dadd2fdd61 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/avg_interval.sql @@ -0,0 +1,2 @@ +/* syntax version 1 */ +discard select EnsureType(avg(cast(key As Interval)), Interval?) from plato.Input; diff --git a/yql/essentials/tests/sql/suites/aggregate/avg_with_having.sql b/yql/essentials/tests/sql/suites/aggregate/avg_with_having.sql new file mode 100644 index 0000000000..f48f0dcfc1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/avg_with_having.sql @@ -0,0 +1,2 @@ +/* syntax version 1 */ +select value, avg(cast(key as int)) + 0.3 as key from plato.Input group by value having value > "foo" order by key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/columns.txt b/yql/essentials/tests/sql/suites/aggregate/columns.txt new file mode 100644 index 0000000000..24785da79e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/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/aggregate/columns.txt.attr b/yql/essentials/tests/sql/suites/aggregate/columns.txt.attr new file mode 100644 index 0000000000..70da6cab1d --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/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/aggregate/compare_by.cfg b/yql/essentials/tests/sql/suites/aggregate/compare_by.cfg new file mode 100644 index 0000000000..612a5060aa --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/compare_by.cfg @@ -0,0 +1 @@ +in Input input_sorted.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/compare_by.sql b/yql/essentials/tests/sql/suites/aggregate/compare_by.sql new file mode 100644 index 0000000000..f5c13b5348 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/compare_by.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +select + min_by(sub, key)as min, + max_by(value, sub) as max, + min_by(key, length(sub), 2) as min_list, + min_by(empty, length(sub), 2) as empty_result, + max_by(key, empty, 2) as empty_by +from + (select cast(key as int) as key, + Unwrap(cast(subkey as int)) as sub, value as value, + cast(value AS int) AS empty from plato.Input);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/compare_by_nulls.sql b/yql/essentials/tests/sql/suites/aggregate/compare_by_nulls.sql new file mode 100644 index 0000000000..7f48154923 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/compare_by_nulls.sql @@ -0,0 +1,19 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$src = select null as key, value from Input; +$src_opt = select null as key, Just(value) as value from Input; +$src_null = select null as key, null as value from Input; + + +select min_by(value, key) from $src; +select max_by(value, key) from $src_opt; +select min_by(value, key) from $src_null; + +select max_by(value, key) from (select * from $src limit 0); +select min_by(value, key) from (select * from $src_opt limit 0); +select max_by(value, key) from (select * from $src_null limit 0); + + +select min_by(value, key) from (select Nothing(String?) as key, value from Input); diff --git a/yql/essentials/tests/sql/suites/aggregate/compare_by_tuple.cfg b/yql/essentials/tests/sql/suites/aggregate/compare_by_tuple.cfg new file mode 100644 index 0000000000..d13818b046 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/compare_by_tuple.cfg @@ -0,0 +1 @@ +in Input input_compare.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/compare_by_tuple.sql b/yql/essentials/tests/sql/suites/aggregate/compare_by_tuple.sql new file mode 100644 index 0000000000..efe9622f19 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/compare_by_tuple.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +select + key, + min_by(AsTuple(subkey, value), AsTuple(subkey, value)) as min, + max_by(AsTuple(subkey, value), AsTuple(subkey, value)) as max +from ( + select + key, + (case when length(subkey) != 0 then subkey else null end) as subkey, + (case when length(value) != 0 then value else null end) as value + from plato.Input +) group by key diff --git a/yql/essentials/tests/sql/suites/aggregate/compare_tuple.cfg b/yql/essentials/tests/sql/suites/aggregate/compare_tuple.cfg new file mode 100644 index 0000000000..d13818b046 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/compare_tuple.cfg @@ -0,0 +1 @@ +in Input input_compare.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/compare_tuple.sql b/yql/essentials/tests/sql/suites/aggregate/compare_tuple.sql new file mode 100644 index 0000000000..ca1b69e071 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/compare_tuple.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +select + key, + min(AsTuple(subkey, value)) as min, + max(AsTuple(subkey, value)) as max +from ( + select + key, + (case when length(subkey) != 0 then subkey else null end) as subkey, + (case when length(value) != 0 then value else null end) as value + from plato.Input +) group by key +order by key diff --git a/yql/essentials/tests/sql/suites/aggregate/count_distinct_with_filter.cfg b/yql/essentials/tests/sql/suites/aggregate/count_distinct_with_filter.cfg new file mode 100644 index 0000000000..be223abe99 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/count_distinct_with_filter.cfg @@ -0,0 +1,2 @@ +in Input5 input5.txt + diff --git a/yql/essentials/tests/sql/suites/aggregate/count_distinct_with_filter.sql b/yql/essentials/tests/sql/suites/aggregate/count_distinct_with_filter.sql new file mode 100644 index 0000000000..c17ac1c3b9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/count_distinct_with_filter.sql @@ -0,0 +1,4 @@ +SELECT count (distinct value) AS Count +FROM plato.Input5 +WHERE `key` = '150'; + diff --git a/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.cfg b/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.cfg new file mode 100644 index 0000000000..0b877ec216 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.cfg @@ -0,0 +1 @@ +in Input dedup_state_keys.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.sql b/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.sql new file mode 100644 index 0000000000..9adbc0dad1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.sql @@ -0,0 +1,10 @@ +USE plato; +SELECT + key, + value, + count(*) AS c +FROM Input +GROUP BY + key, + value +ORDER BY c, key, value; diff --git a/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.txt b/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.txt new file mode 100644 index 0000000000..6f323708f5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.txt @@ -0,0 +1,10 @@ +{"key"="023";"subkey"="3";"value"=1u}; +{"key"="023";"subkey"="5";"value"=1u}; +{"key"="075";"subkey"="1";"value"=3u}; +{"key"="150";"subkey"="1";"value"=4u}; +{"key"="150";"subkey"="3";"value"=5u}; +{"key"="150";"subkey"="8";"value"=6u}; +{"key"="200";"subkey"="7";"value"=7u}; +{"key"="527";"subkey"="4";"value"=8u}; +{"key"="761";"subkey"="6";"value"=9u}; +{"key"="911";"subkey"="2";"value"=10u}; diff --git a/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.txt.attr b/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.txt.attr new file mode 100644 index 0000000000..fc8a038324 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/dedup_state_keys.txt.attr @@ -0,0 +1,11 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["subkey";["DataType";"String"]]; + ["value";["DataType";"Uint64"]] + ]]; + "SortDirections"=[1;1;]; + "SortedBy"=["key";"subkey";]; + "SortedByTypes"=[["DataType";"String";];["DataType";"String";];]; + "SortMembers"=["key";"subkey";]; +}} diff --git a/yql/essentials/tests/sql/suites/aggregate/default.cfg b/yql/essentials/tests/sql/suites/aggregate/default.cfg new file mode 100644 index 0000000000..389a8e8f21 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/default.cfg @@ -0,0 +1,4 @@ +in Input input.txt +in Input2 input2.txt +in Input3 input3.txt +in Input4 input4.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/descending.txt b/yql/essentials/tests/sql/suites/aggregate/descending.txt new file mode 100644 index 0000000000..8e63b46e56 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/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/aggregate/descending.txt.attr b/yql/essentials/tests/sql/suites/aggregate/descending.txt.attr new file mode 100644 index 0000000000..7024a9fc9c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/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/aggregate/disable_blocks_with_spilling.cfg b/yql/essentials/tests/sql/suites/aggregate/disable_blocks_with_spilling.cfg new file mode 100644 index 0000000000..bb375970b4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/disable_blocks_with_spilling.cfg @@ -0,0 +1,5 @@ +in Input input.txt + +providers dq +pragma dq.SpillingEngine="file"; +pragma dq.EnableSpillingNodes="Aggregation"; diff --git a/yql/essentials/tests/sql/suites/aggregate/disable_blocks_with_spilling.sql b/yql/essentials/tests/sql/suites/aggregate/disable_blocks_with_spilling.sql new file mode 100644 index 0000000000..0d8671bf0b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/disable_blocks_with_spilling.sql @@ -0,0 +1,2 @@ +pragma BlockEngine='force'; +select count(key) from plato.Input group by key; diff --git a/yql/essentials/tests/sql/suites/aggregate/ensure_count.sql b/yql/essentials/tests/sql/suites/aggregate/ensure_count.sql new file mode 100644 index 0000000000..373ce7aa8b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/ensure_count.sql @@ -0,0 +1 @@ +select Ensure(COUNT(*), COUNT(*) > 3U, "WTF?") from plato.Input; diff --git a/yql/essentials/tests/sql/suites/aggregate/fail_group_by_struct_member.sqlx b/yql/essentials/tests/sql/suites/aggregate/fail_group_by_struct_member.sqlx new file mode 100644 index 0000000000..0dc3a1ee82 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/fail_group_by_struct_member.sqlx @@ -0,0 +1,16 @@ +/* postgres can not */ +$input = ( + SELECT AsStruct("a" AS value) AS s, "1" AS value + UNION ALL + SELECT AsStruct("b" AS value) AS s, "2" AS value + UNION ALL + SELECT AsStruct("c" AS value) AS s, "3" AS value + UNION ALL + SELECT AsStruct("b" AS value) AS s, "1" AS value + UNION ALL + SELECT AsStruct("b" AS value) AS s, "2" AS value + UNION ALL + SELECT AsStruct("a" AS value) AS s, "3" AS value +); + +SELECT count(*) FROM $input GROUP BY s.value; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_column.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_column.sql new file mode 100644 index 0000000000..b6490dbe58 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_column.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +-- order to have same on yt and yamr +select count(1),z from plato.Input group by key as z order by z; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse.sql new file mode 100644 index 0000000000..a5c8707341 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +--INSERT INTO Output +SELECT + a.key as kk, +-- key as kkk, + aggregate_list(subkey) +FROM plato.Input4 as a +GROUP BY a.key as kk +ORDER BY kk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse_for_join.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse_for_join.cfg new file mode 100644 index 0000000000..a1f7a5a9b7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse_for_join.cfg @@ -0,0 +1,4 @@ +in Input input.txt +in Input2 input2.txt +in Input3 input3.txt +in Input4 input4.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse_for_join.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse_for_join.sql new file mode 100644 index 0000000000..e04f898efe --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_column_alias_reuse_for_join.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +--INSERT INTO Output +SELECT + a.key as kk, +-- kk, + aggregate_list(b.subkey) +FROM plato.Input as a +JOIN plato.Input4 as b +ON a.subkey == b.key +GROUP BY a.key as kk +ORDER BY kk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_duo.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_duo.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_duo.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_duo.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_duo.sql new file mode 100644 index 0000000000..6b5384cba8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_duo.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(length(value)), key, subkey from plato.Input group by cube(key,subkey) order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_expr_trio.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_expr_trio.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_expr_trio.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_expr_trio.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_expr_trio.sql new file mode 100644 index 0000000000..771fa9a2dc --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_expr_trio.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma sampleselect; + +select sum(length(value)) as s, m0, m1, m2 +from plato.Input +group by rollup(cast(key as uint32) as m0, cast(key as uint32) % 10u as m1, cast(key as uint32) % 100u as m2) +order by s, m0, m1, m2; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping.sql new file mode 100644 index 0000000000..eed0fd11fc --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(length(value)) as s, m0, m1, m2, 2u * (2u * grouping(m0) + grouping(m1)) + grouping(m2) as ggg3 +from plato.Input +group by cube(cast(key as uint32) as m0, cast(key as uint32) % 10u as m1, cast(key as uint32) % 100u as m2) +order by s, m0, m1, m2; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping_and_expr.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping_and_expr.sql new file mode 100644 index 0000000000..290d87b25b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_grouping_and_expr.sql @@ -0,0 +1,20 @@ +/* syntax version 1 */ +/* postgres can not */ +SELECT + key, + subkey, + value, + 2u * (2u * grouping(key) + grouping(subkey)) + grouping(value) as ggg3, +FROM + ( + SELECT + subkey, + value, + cast(key as Int32) as opt, + FROM + plato.Input + ) +GROUP BY + Unwrap(opt) AS key, + CUBE(subkey, value) +ORDER BY key, subkey, value; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_join_count.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_join_count.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_join_count.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_cube_join_count.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_join_count.sql new file mode 100644 index 0000000000..5f849d2be0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_cube_join_count.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma sampleselect; +select kk, sk, grouping(kk, sk),count(1) FROM plato.Input AS t1 INNER JOIN plato.Input AS t2 USING (key) +GROUP BY CUBE(t1.key as kk, t1.subkey as sk) ORDER BY kk, sk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr.cfg new file mode 100644 index 0000000000..c7e99df4d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr.cfg @@ -0,0 +1 @@ +in Input input_expr.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr.sql new file mode 100644 index 0000000000..8a65d11bb7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(cast(subkey as uint32)) as s from plato.Input group by cast(key as uint32) % 10 order by s; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_alias_on_subexp.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_alias_on_subexp.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_alias_on_subexp.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_alias_on_subexp.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_alias_on_subexp.sql new file mode 100644 index 0000000000..aa6a7a6c28 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_alias_on_subexp.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + key, sk, aggregate_list(value) as values +FROM + (SELECT * FROM Input) +GROUP BY key, cast(subkey as uint32) % 2 as sk +ORDER BY key, sk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_and_having.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_and_having.cfg new file mode 100644 index 0000000000..c7e99df4d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_and_having.cfg @@ -0,0 +1 @@ +in Input input_expr.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_and_having.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_and_having.sql new file mode 100644 index 0000000000..41407e9244 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_and_having.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(*) as count, mod_sk from plato.Input as a group by cast(subkey as uint32) % 10 as mod_sk, cast(key as uint32) % 10 as mod_k having mod_k == 7; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_columns_reuse.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_columns_reuse.sql new file mode 100644 index 0000000000..5932fe83e1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_columns_reuse.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + key, count(1) as count +FROM Input +GROUP BY cast(key as uint32) % 10 as key +ORDER BY key, count; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_dict.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_dict.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_dict.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_dict.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_dict.sql new file mode 100644 index 0000000000..b7bc07e73c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_dict.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ +$data_dict = (select mod, Just(YQL::ToIndexDict(ListSort(aggregate_list(key)))) as dk, ListSort(aggregate_list(subkey)) as ls, ListSort(aggregate_list(value)) as lv from plato.Input group by cast(subkey as uint32) % 10 as mod); + +select * from $data_dict as t group by t.dk[0] as gk order by gk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_lookup.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_lookup.cfg new file mode 100644 index 0000000000..4ce1b28a20 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_lookup.cfg @@ -0,0 +1 @@ +in Input input_dict.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_lookup.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_lookup.sql new file mode 100644 index 0000000000..e7171c7bea --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_lookup.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(*) as s from plato.Input group by `dict`["a"] order by s; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_mul_col.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_mul_col.cfg new file mode 100644 index 0000000000..c7e99df4d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_mul_col.cfg @@ -0,0 +1 @@ +in Input input_expr.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_mul_col.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_mul_col.sql new file mode 100644 index 0000000000..113245c1cd --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_mul_col.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(*) as count, mod_sk + mod_k as mod_sum +from plato.Input as a +group by cast(subkey as uint32) % 10 as mod_sk, cast(key as uint32) % 10 as mod_k +order by count, mod_sum;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_only_join.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_only_join.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_only_join.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_only_join.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_only_join.sql new file mode 100644 index 0000000000..6c3ac3a6cc --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_only_join.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select aggregate_list(a.k), aval from (select cast(subkey as uint32) as k, value as val from plato.Input) as a left only join (select cast(key as uint32) as k, cast(subkey as uint32) as s from plato.Input) as b using(k) group by a.val as aval; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_order_by_expr.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_order_by_expr.cfg new file mode 100644 index 0000000000..c7e99df4d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_order_by_expr.cfg @@ -0,0 +1 @@ +in Input input_expr.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_order_by_expr.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_order_by_expr.sql new file mode 100644 index 0000000000..ddf5bf9a45 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_order_by_expr.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(cast(key as uint32)) as keysum from plato.Input group by cast(key as uint32) / 100 + cast(subkey as uint32) % 10 order by keysum desc; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_semi_join.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_semi_join.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_semi_join.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_semi_join.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_semi_join.sql new file mode 100644 index 0000000000..b212c02136 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_semi_join.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +/* postgres can not */ +select ListSort(aggregate_list(b.uk)), ListSort(aggregate_list(b.uk)), bus +from + (select cast(key as uint32) as uk from plato.Input) as a +right semi join + (select cast(key as uint32) as uk, cast(subkey as uint32) as us from plato.Input) as b + using(uk) group by b.us as bus +order by bus; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_join.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_join.cfg new file mode 100644 index 0000000000..bf1b560b58 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_join.cfg @@ -0,0 +1 @@ +in Input input_intersect_sorted.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_join.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_join.sql new file mode 100644 index 0000000000..f078552fb2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_join.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ + +pragma sampleselect; +select kk, ListSort(aggregate_list(t2.key)) FROM plato.Input AS t1 INNER JOIN plato.Input AS t2 ON t1.key==t2.subkey GROUP BY t1.key as kk ORDER by kk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_where.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_where.sql new file mode 100644 index 0000000000..255b003850 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_expr_with_where.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +SELECT ki, count(1) FROM plato.Input WHERE ki IN (75, 20) GROUP BY Cast(key as Uint32) as ki ORDER BY ki; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_full_path.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_full_path.sql new file mode 100644 index 0000000000..a52f5d447b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_full_path.sql @@ -0,0 +1,2 @@ +/* syntax version 1 */ +SELECT a.value FROM plato.Input as a GROUP BY a.value ORDER BY a.value; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_alt_duo.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_alt_duo.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_alt_duo.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_alt_duo.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_alt_duo.sql new file mode 100644 index 0000000000..766cb37e6b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_alt_duo.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(length(value)), key, subkey from plato.Input group by grouping sets (key), grouping sets (subkey) order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_and_having.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_and_having.sql new file mode 100644 index 0000000000..633656ab7c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_and_having.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + Sum(Cast(subkey as Uint32)) as sumLen, + key, + value, + Grouping(key, value) as grouping +FROM Input3 +GROUP BY GROUPING SETS ((key),(value)) +HAVING count(*) > 2 +ORDER BY key, value diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_duo.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_duo.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_duo.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_duo.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_duo.sql new file mode 100644 index 0000000000..5510785d8c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_duo.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(length(value)), key, subkey from plato.Input group by grouping sets ((key),(subkey)) order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_few_empty.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_few_empty.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_few_empty.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_few_empty.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_few_empty.sql new file mode 100644 index 0000000000..494ebb8c4e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_few_empty.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(1), key, subkey, value, grouping(key, subkey, value) from plato.Input group by grouping sets ((), value, rollup(key, subkey), ()) +order by key, subkey, value; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten.sql new file mode 100644 index 0000000000..0ee762b985 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$input = select a.*, [1,2] as lst from Input as a; + +select key, subkey, some(lst) as lst_count +from $input flatten list by lst +where lst != 1 +group by grouping sets ((key), (key, subkey)) +order by key, subkey; + diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten_columns.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten_columns.sql new file mode 100644 index 0000000000..e7c274191e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten_columns.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$input = select a.*, <|k1:1, k2:2|> as s from Input as a; + +select key, subkey, some(k1) as k1, some(k2) as k2 +from $input flatten columns +group by grouping sets ((key), (key, subkey)) +order by key, subkey; + diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten_expr.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten_expr.sql new file mode 100644 index 0000000000..8b1e77e544 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_flatten_expr.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$input = select a.*, [1,2] as lst from Input as a; + +select key, subkey, count(lst) as lst_count +from $input flatten list by (ListExtend(lst, [3,4]) as lst) +where lst != 2 +group by grouping sets ((key), (key, subkey)) +order by key, subkey; + diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_grouping.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_grouping.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_grouping.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_grouping.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_grouping.sql new file mode 100644 index 0000000000..a6f003d445 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_grouping.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(1), key_first, val_first, grouping(key_first, val_first) as group +from plato.Input group by grouping sets (cast(key as uint32) / 100u as key_first, Substring(value, 1, 1) as val_first) +order by key_first, val_first; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_join_aliases.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_join_aliases.sql new file mode 100644 index 0000000000..598ae44056 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_join_aliases.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT k1, k2, b.subkey as kk2, SOME(a.value) as val +FROM plato.Input AS a JOIN plato.Input AS b USING(key) +GROUP BY GROUPING SETS( + (a.key as k1, b.subkey as k2), + (k1), + (b.subkey) +) +ORDER BY k1, kk2; + diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_simp.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_simp.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_simp.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_simp.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_simp.sql new file mode 100644 index 0000000000..09c48d836b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_simp.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(length(value)), key, subkey, grouping(key, subkey) from plato.Input group by grouping sets ((key, subkey), key, subkey) order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_subselect.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_subselect.sql new file mode 100644 index 0000000000..8b28224ab5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_subselect.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$sub = (SELECT + Sum(Cast(subkey as Uint32)) as sumLen, + key, + value, + Grouping(key, value) as grouping +FROM Input +GROUP BY GROUPING SETS ((key),(value)) +); + +--INSERT INTO Output +SELECT t.sumLen, t.key, t.value, t.grouping FROM $sub as t +ORDER BY t.key, t.value
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_subselect_asterisk.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_subselect_asterisk.sql new file mode 100644 index 0000000000..6966339274 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_subselect_asterisk.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$sub = (SELECT * FROM Input LIMIT 5); + +--INSERT INTO Output +SELECT + Sum(Cast(subkey as Uint32)) as sumLen, + key, + value, + Grouping(key, value) as grouping +FROM $sub +GROUP BY GROUPING SETS ((key),(value)) +ORDER BY key, value diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_with_rollup.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_with_rollup.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_with_rollup.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_gs_with_rollup.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_with_rollup.sql new file mode 100644 index 0000000000..ebbdbf828c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_gs_with_rollup.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +--insert into Output +select + key, subkey, count(1) as total_count, value, grouping(key, subkey, value) as group_mask +from Input +group by grouping sets (value, rollup(key, subkey)) +order by group_mask, value, key, subkey, total_count; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop.sql new file mode 100644 index 0000000000..334bf2cb83 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + user, + HOP_START() as ts, + SUM(payload) as payload +FROM plato.Input +GROUP BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"), user; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_compact.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_compact.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_compact.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_compact.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_compact.sql new file mode 100644 index 0000000000..a85dfcf6b5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_compact.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + user, + HOP_START() as ts, + SUM(payload) as payload +FROM plato.Input +GROUP COMPACT BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"), user; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct.sql new file mode 100644 index 0000000000..d9aed1fac9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct.sql @@ -0,0 +1,17 @@ +/* Test is broken for now */ + +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ +/* dq can not */ +/* dqfile can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + user, + HOP_START() as ts, + SUM(DISTINCT payload) as payload +FROM plato.Input +GROUP BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"), user; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct_compact.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct_compact.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct_compact.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct_compact.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct_compact.sql new file mode 100644 index 0000000000..0b44ceabea --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_distinct_compact.sql @@ -0,0 +1,17 @@ +/* Test is broken for now */ + +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ +/* dq can not */ +/* dqfile can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + user, + HOP_START() as ts, + SUM(DISTINCT payload) as payload +FROM plato.Input +GROUP COMPACT BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"), user; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_expr_key.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_expr_key.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_expr_key.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_expr_key.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_expr_key.sql new file mode 100644 index 0000000000..cb3326e825 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_expr_key.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + user, + HOP_START() as ts, + SUM(payload) as payload +FROM plato.Input +GROUP COMPACT BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"), user, substring(user, 1, 1); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_list_key.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_list_key.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_list_key.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_list_key.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_list_key.sql new file mode 100644 index 0000000000..77369585c9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_list_key.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + user, + HOP_START() as ts, + SUM(payload) as payload +FROM plato.Input +GROUP COMPACT BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"), user, AsList(user, "1"); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only.sql new file mode 100644 index 0000000000..302e8aa1a5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + HOP_START() as ts, + SUM(payload) as payload +FROM plato.Input +GROUP BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_distinct.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_distinct.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_distinct.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_distinct.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_distinct.sql new file mode 100644 index 0000000000..1ab7dc5b75 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_distinct.sql @@ -0,0 +1,16 @@ +/* Test is broken for now */ + +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ +/* dq can not */ +/* dqfile can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + HOP_START() as ts, + SUM(DISTINCT payload) as payload +FROM plato.Input +GROUP BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_start.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_start.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_start.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_start.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_start.sql new file mode 100644 index 0000000000..963df3855c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_only_start.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT + HOP_START() +FROM plato.Input +GROUP BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_star.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_star.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_star.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_star.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_star.sql new file mode 100644 index 0000000000..75fbea9671 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_star.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +PRAGMA dq.AnalyticsHopping="true"; + +SELECT * +FROM plato.Input +GROUP BY HOP(DateTime::FromSeconds(CAST(ts as Uint32)), "PT10S", "PT10S", "PT10S"), user; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_static.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_static.sql new file mode 100644 index 0000000000..fec507c827 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_static.sql @@ -0,0 +1,26 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +$input = SELECT * FROM AS_TABLE([ + <|"time":"2024-01-01T00:00:01Z", "user": 1|>, + <|"time":"2024-01-01T00:00:02Z", "user": 1|>, + <|"time":"2024-01-01T00:00:03Z", "user": 1|>, + <|"time":"2024-01-01T00:00:01Z", "user": 2|>, + <|"time":"2024-01-01T00:00:02Z", "user": 2|>, + <|"time":"2024-01-01T00:00:03Z", "user": 2|>, + <|"time":"2024-01-01T00:00:01Z", "user": 2|>, + <|"time":"2024-01-01T00:00:02Z", "user": 2|>, + <|"time":"2024-01-01T00:00:03Z", "user": 2|>, + <|"time":"2024-01-01T00:00:01Z", "user": 3|>, + <|"time":"2024-01-01T00:00:02Z", "user": 3|>, + <|"time":"2024-01-01T00:00:03Z", "user": 3|> +]); + +SELECT + user, + COUNT(*) as count, + HOP_START() as start, +FROM $input +GROUP BY HOP(CAST(time as Timestamp), 'PT1S', 'PT1S', 'PT1S'), user; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_hop_static_list_key.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_static_list_key.sql new file mode 100644 index 0000000000..3639207bb3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_hop_static_list_key.sql @@ -0,0 +1,26 @@ +/* syntax version 1 */ +/* postgres can not */ +/* ytfile can not */ +/* yt can not */ + +$input = SELECT * FROM AS_TABLE([ + <|"time":"2024-01-01T00:00:01Z", "user": 1|>, + <|"time":"2024-01-01T00:00:02Z", "user": 1|>, + <|"time":"2024-01-01T00:00:03Z", "user": 1|>, + <|"time":"2024-01-01T00:00:01Z", "user": 2|>, + <|"time":"2024-01-01T00:00:02Z", "user": 2|>, + <|"time":"2024-01-01T00:00:03Z", "user": 2|>, + <|"time":"2024-01-01T00:00:01Z", "user": 2|>, + <|"time":"2024-01-01T00:00:02Z", "user": 2|>, + <|"time":"2024-01-01T00:00:03Z", "user": 2|>, + <|"time":"2024-01-01T00:00:01Z", "user": 3|>, + <|"time":"2024-01-01T00:00:02Z", "user": 3|>, + <|"time":"2024-01-01T00:00:03Z", "user": 3|> +]); + +SELECT + user, + COUNT(*) as count, + HOP_START() as start, +FROM $input +GROUP BY HOP(CAST(time as Timestamp), 'PT1S', 'PT1S', 'PT1S'), user, AsList(user, 0); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gb_ru.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gb_ru.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gb_ru.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gb_ru.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gb_ru.sql new file mode 100644 index 0000000000..63492a2fa7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gb_ru.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(length(value)), vf, kf, kl, grouping(vf, kf, kl) as ggg3 from plato.Input group by Substring(value, 0, 1) as vf, cube(cast(key as uint32) % 10u as kl, cast(key as uint32) / 100u as kf) +order by vf, kf, kl; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_expr_and_column.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_expr_and_column.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_expr_and_column.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_expr_and_column.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_expr_and_column.sql new file mode 100644 index 0000000000..df6dc769ba --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_expr_and_column.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +--insert into Output +select + count(1) as count, kf, key, vf, vl, grouping(kf, key, vf, vl) as grouping +from Input group by grouping sets( + (cast(key as uint32) / 100u as kf, key), + (Substring(value, 0, 1) as vf, Substring(value, 2, 1) as vl) +) +order by kf, key, vf, vl; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_gs.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_gs.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_gs.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_gs.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_gs.sql new file mode 100644 index 0000000000..0cefa50a9a --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_gs.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(1), kf, kl, vf, vl, grouping(kf, kl, vf, vl) from plato.Input group by grouping sets(cast(key as uint32) / 100u as kf, cast(key as uint32) % 10u as kl), grouping sets(Substring(value, 0, 1) as vf, Substring(value, 2, 1) as vl) +order by kf, kl, vf, vl; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_ru.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_ru.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_ru.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_ru.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_ru.sql new file mode 100644 index 0000000000..e325b93fff --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_gs_ru.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(1), kf, kl, vf, vl, grouping(kf, kl, vf, vl) as gggg from plato.Input group by grouping sets((cast(key as uint32) / 100u as kf, cast(key as uint32) % 10u as kl)), rollup(Substring(value, 0, 1) as vf, Substring(value, 2, 1) as vl) +order by kf, kl, vf, vl; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_ru_ru.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_ru_ru.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_ru_ru.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_mul_ru_ru.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_ru_ru.sql new file mode 100644 index 0000000000..569539f114 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_mul_ru_ru.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(1), kf, kl, vf, vl, grouping(kf, kl, vf, vl) from plato.Input group by rollup(cast(key as uint32) / 100u as kf, cast(key as uint32) % 10u as kl), rollup(Substring(value, 0, 1) as vf, Substring(value, 2, 1) as vl) +order by kf, kl, vf, vl; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_aggr_expr.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_aggr_expr.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_aggr_expr.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_aggr_expr.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_aggr_expr.sql new file mode 100644 index 0000000000..59a59d1aef --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_aggr_expr.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$input = (select key, subkey, substring(value, 0, 1) == substring(value, 2, 1) as value_from_a from Input); + +--insert into Output +select + key, + subkey, + count_if(value_from_a) as approved, + cast(count_if(value_from_a) as double) / count(*) as approved_share, + count(*) as total +from $input +group by rollup(key, subkey) +order by key, subkey +; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref.sql new file mode 100644 index 0000000000..ee10388e6b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$input=(select cast(key as int32) ?? 0 as kk, cast(subkey as int32) ?? 0 as sk, value from Input); + +--insert into Output +select + kk, sk, count(*) as total_count +from $input +where sk in (23, 37, 75, 150, ) +group by rollup(kk, sk) +order by kk, sk, total_count +; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref_same_names.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref_same_names.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref_same_names.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref_same_names.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref_same_names.sql new file mode 100644 index 0000000000..f03bd9a437 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_ref_same_names.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$input=(select cast(key as uint32) ?? 0 as key, cast(subkey as int32) ?? 0 as subkey, value from Input); + +--insert into Output +select + key, subkey, count(*) as total_count +from $input +where subkey in (23, 37, 75,150) +group by rollup(key, subkey) +order by key, subkey, total_count +; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_reuse.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_reuse.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_reuse.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_reuse.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_reuse.sql new file mode 100644 index 0000000000..07a46979c6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_column_reuse.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +$input=(select cast(key as uint32) ?? 0 as key, cast(subkey as int32) ?? 0 as subkey, value from Input); + +$request = ( + select + key, subkey, count(*) as total_count + from $input + where subkey in (23, 37, 75,150) + group by rollup(key, subkey) +); + +--insert into Output +select key, subkey, total_count from $request +order by key, subkey, total_count; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo.sql new file mode 100644 index 0000000000..93e586766e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(length(value)), key, subkey from plato.Input group by rollup(key,subkey) order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo_opt.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo_opt.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo_opt.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo_opt.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo_opt.sql new file mode 100644 index 0000000000..4a58b473f7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_duo_opt.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(1), k, subkey from plato.Input group by rollup(cast(key as uint32) as k, subkey) order by k, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping.sql new file mode 100644 index 0000000000..48bae25392 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ +select sum(length(value)) as s, m0, m1, m2, grouping(m0, m1, m2) as ggg +from plato.Input +group by rollup(cast(key as uint32) as m0, cast(key as uint32) % 10u as m1, cast(key as uint32) % 100u as m2) +order by s, m0, m1, m2; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum.sql new file mode 100644 index 0000000000..f92cad03fa --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +select count(1) as elements, key_first, val_first, case grouping(key_first, val_first) + when 1 then 'Total By First digit key' + when 2 then 'Total By First char value' + when 3 then 'Grand Total' + else 'Group' +end as group +from plato.Input group by cube(cast(key as uint32) / 100u as key_first, Substring(value, 1, 1) as val_first) +order by elements, key_first, val_first; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum_bind.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum_bind.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum_bind.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum_bind.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum_bind.sql new file mode 100644 index 0000000000..1432eaa2a6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_grouping_hum_bind.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +$hum_gr_kv = ($grouping) -> { + return case $grouping + when 1 then 'Total By First digit key' + when 2 then 'Total By First char value' + when 3 then 'Grand Total' + else 'Group' + end; +}; + +select count(1) as elements, key_first, val_first, $hum_gr_kv(grouping(key_first, val_first)) as group +from plato.Input group by cube(cast(key as uint32) / 100u as key_first, Substring(value, 1, 1) as val_first) +order by elements, key_first, val_first; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_key_check.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_key_check.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_key_check.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_key_check.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_key_check.sql new file mode 100644 index 0000000000..385b50c5d9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_key_check.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +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, prefix; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_rename.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_rename.sql new file mode 100644 index 0000000000..c5098db052 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_rename.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ +$s = ( + select + 1 as x, + 2 as y +); + +select + x as x2, + y +from $s +group by rollup( + x, y +) +order by x2, y; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_udf.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_udf.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_udf.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_udf.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_udf.sql new file mode 100644 index 0000000000..ee546d63e7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_udf.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +select key, subkey, Unicode::ToUpper(CAST(value AS Utf8)) as value, count(1) as cnt from Input GROUP BY ROLLUP(key,subkey, value) ORDER BY key,subkey,value,cnt; + diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_with_filter.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_with_filter.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_with_filter.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_with_filter.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_with_filter.sql new file mode 100644 index 0000000000..cb1e3c2def --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_rollup_with_filter.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +--insert into Output +select + key, subkey, count(*) as total_count +from plato.Input +where key in ('023', '037') +group by rollup(key, subkey) +order by key, subkey +; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join.sql new file mode 100644 index 0000000000..7ef6caf16a --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ + +pragma sampleselect; +select * FROM plato.Input AS t1 INNER JOIN plato.Input AS t2 USING (key) GROUP BY ROLLUP(t1.key as kk, t1.subkey as sk) ORDER BY kk, sk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_agg.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_agg.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_agg.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_agg.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_agg.sql new file mode 100644 index 0000000000..7c65ec4383 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_agg.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ + +pragma sampleselect; +select kk, sk, sum(cast(t2.subkey as Uint32)) FROM plato.Input AS t1 INNER JOIN plato.Input AS t2 USING (key) GROUP BY ROLLUP(t1.key as kk, t1.subkey as sk) ORDER BY kk, sk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_grouping.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_grouping.sql new file mode 100644 index 0000000000..c5e431a9ee --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_grouping.sql @@ -0,0 +1,14 @@ +USE plato; + +$t = select distinct key from Input; + +select + key, subkey, + max(value) as max_val, + grouping(a.key, a.subkey) as g_ks, + grouping(a.subkey, a.key) as g_sk, + grouping(a.key) as g_k, + grouping(a.subkey) as g_s, +from Input as a +join $t as b on a.key = b.key +group by rollup(a.key, a.subkey); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_qualified.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_qualified.sql new file mode 100644 index 0000000000..b782e754f0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_qualified.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + k, + b.subkey as sk, + MIN(a.value) as val, + GROUPING(k, b.subkey) as g, +FROM plato.Input AS a JOIN plato.Input AS b USING(key) +GROUP BY ROLLUP (a.key as k, b.subkey) +ORDER BY g, k, sk +; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple.sql new file mode 100644 index 0000000000..f232af79be --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ + +pragma sampleselect; +select kk, sk, max(t2.subkey) as ss FROM plato.Input AS t1 INNER JOIN plato.Input AS t2 USING (key) GROUP BY ROLLUP(t1.key as kk, t1.subkey as sk) ORDER BY kk, sk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple_fs_multiusage.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple_fs_multiusage.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple_fs_multiusage.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple_fs_multiusage.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple_fs_multiusage.sql new file mode 100644 index 0000000000..1932c58252 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_simple_fs_multiusage.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ + +pragma sampleselect; +pragma config.flags("OptimizerFlags", "FieldSubsetEnableMultiusage"); +select kk, sk, max(t2.subkey) as ss FROM plato.Input AS t1 INNER JOIN plato.Input AS t2 USING (key) GROUP BY ROLLUP(t1.key as kk, t1.subkey as sk) ORDER BY kk, sk; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_star.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_star.sql new file mode 100644 index 0000000000..230c974712 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_join_star.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ + +select * FROM plato.Input AS t1 INNER JOIN plato.Input AS t2 USING (key) GROUP BY ROLLUP(t1.key as kk, t1.subkey) ORDER BY kk, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_partition_by_grouping.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_partition_by_grouping.sql new file mode 100644 index 0000000000..e9d35dde11 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_partition_by_grouping.sql @@ -0,0 +1,13 @@ +use plato; + +select + key, + subkey, + min(value) as mv, + grouping(key) + grouping(subkey) as gsum, + rank() over ( + partition by grouping(key) + grouping(subkey) + order by key, subkey, min(value) + ) as rk, +from Input +group by rollup(key, subkey); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_select_distinct.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_select_distinct.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_select_distinct.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_select_distinct.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_select_distinct.sql new file mode 100644 index 0000000000..30971d58aa --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_select_distinct.sql @@ -0,0 +1,4 @@ +use plato; + +select distinct key from Input group by rollup(key, subkey) order by key; + diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_window_func.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_window_func.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_window_func.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_window_func.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_window_func.sql new file mode 100644 index 0000000000..1a5bbafc85 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_ru_with_window_func.sql @@ -0,0 +1,3 @@ +use plato; + +select row_number() over (order by key) as rn, key from Input group by rollup(key, subkey) order by rn; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session.sql new file mode 100644 index 0000000000..5faa7d4e14 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + MIN(ts) ?? 100500 as session_start, + ListSort(AGGREGATE_LIST(ts ?? 100500)) as session, + COUNT(1) as session_len +FROM plato.Input +GROUP BY SessionWindow(ts, 10), user +ORDER BY user, session_start; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_aliases.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_aliases.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_aliases.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_aliases.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_aliases.sql new file mode 100644 index 0000000000..ef71f18e70 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_aliases.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + session_start, + SessionStart() as session_start1, + SessionStart() ?? 100500 as session_start2, + ListSort(AGGREGATE_LIST(ts ?? 100500)) as session, + COUNT(1) as session_len +FROM plato.Input +GROUP BY SessionWindow(ts, 10) as session_start, user +ORDER BY user, session_start; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_compact.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_compact.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_compact.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_compact.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_compact.sql new file mode 100644 index 0000000000..1cbffd58bd --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_compact.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + MIN(ts) ?? 100500 as session_start, + ListSort(AGGREGATE_LIST(ts ?? 100500)) as session, + COUNT(1) as session_len +FROM plato.Input +GROUP COMPACT BY user, SessionWindow(ts, 10) +ORDER BY user, session_start; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct.sql new file mode 100644 index 0000000000..9a6e3c848b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + MIN(ts) ?? 100500 as session_start, + ListSort(AGGREGATE_LIST(ts ?? 100500)) as session, + COUNT(1) as session_len, + COUNT(DISTINCT payload) as distinct_playloads +FROM plato.Input +GROUP BY SessionWindow(ts, 10), user +ORDER BY user, session_start; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct_compact.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct_compact.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct_compact.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct_compact.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct_compact.sql new file mode 100644 index 0000000000..ba4607de17 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_distinct_compact.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + MIN(ts) ?? 100500 as session_start, + ListSort(AGGREGATE_LIST(ts ?? 100500)) as session, + COUNT(1) as session_len, + COUNT(DISTINCT payload) as distinct_playloads +FROM plato.Input +GROUP COMPACT BY user, SessionWindow(ts, 10) +ORDER BY user, session_start; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended.sql new file mode 100644 index 0000000000..1b9af1d0d8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended.sql @@ -0,0 +1,21 @@ +/* syntax version 1 */ +/* postgres can not */ + +$init = ($row) -> (AsStruct($row.ts ?? 0 as value, 1 as count)); + +$calculate = ($_row, $state) -> ($state.value); +-- split partition into two-element grooups, make session key to be cumulative sum of ts from partition start +$update = ($row, $state) -> { + $state = AsStruct($state.count + 1 as count, $state.value as value); + $state = AsStruct($state.count as count, $state.value + ($row.ts ?? 0) as value); + return AsTuple(Unwrap($state.count % 2) == 1, $state); +}; + +SELECT + user, + SessionStart() as session_start, + ListSort(AGGREGATE_LIST(ts ?? 100500)) as session, + COUNT(1) as session_len +FROM plato.Input +GROUP BY SessionWindow(ts, $init, $update, $calculate), user +ORDER BY user, session_start; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_subset.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_subset.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_subset.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_subset.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_subset.sql new file mode 100644 index 0000000000..55d34f5e27 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_subset.sql @@ -0,0 +1,20 @@ +/* syntax version 1 */ +/* postgres can not */ + +$init = ($row) -> (AsStruct($row.ts ?? 0 as value, 1 as count)); + +$calculate = ($_row, $state) -> ($state.value); +-- split partition into two-element grooups, make session key to be cumulative sum of ts from partition start +$update = ($row, $state) -> { + $state = AsStruct($state.count + 1 as count, $state.value as value); + $state = AsStruct($state.count as count, $state.value + ($row.ts ?? 0) as value); + return AsTuple(Unwrap($state.count % 2) == 1, $state); +}; + +$src = SELECT t.*, (ts ?? 0, payload) as sort_col FROM plato.Input as t; + +SELECT + COUNT(1) as session_len, +FROM $src +GROUP BY user, SessionWindow(sort_col, $init, $update, $calculate) +ORDER BY session_len; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_tuple.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_tuple.sql new file mode 100644 index 0000000000..9400ba8534 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_extended_tuple.sql @@ -0,0 +1,64 @@ +/* postgres can not */ +/* syntax version 1 */ + +$timeout = 60 * 30; + +$init = ($row) -> (AsTuple($row.unixtime, $row.unixtime, $row.video_content_id)); +$update = ($row, $state) -> { + $is_end_session = (($row.unixtime - $state.1) >= $timeout) or ($row.video_content_id is not null and $row.video_content_id != ($state.2 ?? "-")) ?? false; + $new_state = AsTuple( + IF($is_end_session, $row.unixtime, $state.0), + $row.unixtime, + IF( + $is_end_session, + $row.video_content_id, + $state.2 + ) + ); + return AsTuple($is_end_session, $new_state); +}; +$calculate = ($row, $state) -> ( + AsTuple($row.unixtime, $state.2) +); + +$source = [ + <| + vsid: "v", + unixtime: 1650624253, + video_content_id: null, + |>, + <| + vsid: "v", + unixtime: 1650624255, + video_content_id: "b", + |>, + <| + vsid: "v", + unixtime: 1650624256, + video_content_id: null, + |>, + <| + vsid: "v", + unixtime: 1650624257, + video_content_id: "b", + |>, + <| + vsid: "v", + unixtime: 1650634257, + video_content_id: "b", + |>, + <| + vsid: "v", + unixtime: 1650634258, + video_content_id: "c", + |> +]; + + +SELECT + vsid, + session_start, + COUNT(*) AS session_size +FROM as_table($source) +GROUP BY vsid, SessionWindow(unixtime, $init, $update, $calculate) AS session_start +order by vsid, session_start diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_only.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_only.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_only.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_only.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_only.sql new file mode 100644 index 0000000000..f63a3f25b2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_only.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + MIN(ts) ?? 100500 as session_start, +FROM plato.Input +GROUP BY SessionWindow(ts, 9) +ORDER BY session_start diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_only_distinct.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_only_distinct.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_only_distinct.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_only_distinct.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_only_distinct.sql new file mode 100644 index 0000000000..512b6f386f --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_only_distinct.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + MIN(DISTINCT ts) ?? 100500 as session_start, +FROM plato.Input +GROUP BY SessionWindow(ts, 10) +ORDER BY session_start diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_star.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_session_star.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_star.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_session_star.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_session_star.sql new file mode 100644 index 0000000000..b8d2dcd7b2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_session_star.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ + +PRAGMA OrderedColumns; + +SELECT * +FROM plato.Input +GROUP BY user, SessionWindow(ts, 10) as session_start +ORDER BY user, session_start; + +SELECT * +FROM plato.Input +GROUP BY user, SessionWindow(ts, 10) +ORDER BY user, group0; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_tablerow_column.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_tablerow_column.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_tablerow_column.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_tablerow_column.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_tablerow_column.sql new file mode 100644 index 0000000000..a3fc042006 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_tablerow_column.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; +select * +from Input +group by TableRow().key as k +order by k; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_tz_date.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_tz_date.cfg new file mode 100644 index 0000000000..be223abe99 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_tz_date.cfg @@ -0,0 +1,2 @@ +in Input5 input5.txt + diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_tz_date.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_tz_date.sql new file mode 100644 index 0000000000..603c7ea45e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_tz_date.sql @@ -0,0 +1,3 @@ +SELECT SUM(CAST(subkey AS Uint8)) AS sum +FROM plato.Input5 +GROUP BY CAST(CAST(key AS Uint16) AS TzDate); diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_with_udf_by_aggregate.cfg b/yql/essentials/tests/sql/suites/aggregate/group_by_with_udf_by_aggregate.cfg new file mode 100644 index 0000000000..efcd9cfaeb --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_with_udf_by_aggregate.cfg @@ -0,0 +1,3 @@ +in Input input_expr.txt +udf python2_udf +providers yt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_with_udf_by_aggregate.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_with_udf_by_aggregate.sql new file mode 100644 index 0000000000..0ae44d64c5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_with_udf_by_aggregate.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +$majority_vote = Python::majority_vote( + Callable<(List<String?>)->String>, + @@ +def majority_vote(values): + counters = {} + for value in values: + counters[value] = counters.get(value, 0) + 1 + return sorted((count, value) for value, count in counters.items())[-1][1] + @@ +); + +select count(*), val, $majority_vote(aggregate_list(subkey)) from plato.Input group by cast(key as uint32) % 2 as val; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_by_with_where.sql b/yql/essentials/tests/sql/suites/aggregate/group_by_with_where.sql new file mode 100644 index 0000000000..f6ec83308c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_by_with_where.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +SELECT ki, count(1) FROM plato.Input WHERE ki IN ('075', '150') GROUP BY key as ki ORDER BY ki; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted.cfg b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted.cfg new file mode 100644 index 0000000000..5b8e8ea74d --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted.cfg @@ -0,0 +1 @@ +in Input sorted.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted.sql b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted.sql new file mode 100644 index 0000000000..496b6c1d76 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +SELECT + key, count(value) as cnt, min(value) as min, max(value) as max +FROM Input +GROUP COMPACT BY key +ORDER BY key; + diff --git a/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct.cfg b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct.cfg new file mode 100644 index 0000000000..64197c4f2e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +udf set_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct.sql b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct.sql new file mode 100644 index 0000000000..a29fcd4419 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +SELECT + key,count(distinct value) as cnt, + ListSort(aggregate_list(distinct value)) as lst, + min(value) as min, max(value) as max +FROM Input +GROUP COMPACT BY key +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct_complex.cfg b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct_complex.cfg new file mode 100644 index 0000000000..64197c4f2e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct_complex.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +udf set_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct_complex.sql b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct_complex.sql new file mode 100644 index 0000000000..74d23fe2df --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_distinct_complex.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +SELECT + key,count(distinct value) as cnt, + ListSort(ListMap( + aggregate_list(distinct value),($x)->{ return DictItems($x) })) as lst +FROM (SELECT key, AsDict(AsTuple(1, value)) as value from Input) +GROUP COMPACT BY key +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_with_diff_order.cfg b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_with_diff_order.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_with_diff_order.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_with_diff_order.sql b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_with_diff_order.sql new file mode 100644 index 0000000000..f131544139 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/group_compact_sorted_with_diff_order.sql @@ -0,0 +1,38 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +insert into @ksv +select * from Input order by key, subkey, value; + +insert into @vsk +select * from Input order by value, subkey, key; + +insert into @vs +select * from Input order by value, subkey; + +commit; + +select key, subkey, value from @ksv -- YtReduce +group compact by key, subkey, value +order by key, subkey, value; + +select key, subkey, value from @vsk -- YtReduce +group /*+ compact() */ by key, subkey, value +order by key, subkey, value; + +select key, subkey, some(value) as value from @ksv -- YtReduce +group compact by key, subkey +order by key, subkey, value; + +select key, subkey, some(value) as value from @vsk -- YtMapReduce +group compact by key, subkey +order by key, subkey, value; + +select key, subkey, value from concat(@ksv, @vsk) -- YtMapReduce +group compact by key, subkey, value +order by key, subkey, value; + +select some(key) as key, subkey, value from concat(@vs, @vsk) -- YtReduce +group compact by subkey, value +order by key, subkey, value; diff --git a/yql/essentials/tests/sql/suites/aggregate/having_cast.sql b/yql/essentials/tests/sql/suites/aggregate/having_cast.sql new file mode 100644 index 0000000000..15965428c9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/having_cast.sql @@ -0,0 +1 @@ +select value from plato.Input group by value having avg(cast(key as int)) > 100 order by value; diff --git a/yql/essentials/tests/sql/suites/aggregate/having_distinct_expr.cfg b/yql/essentials/tests/sql/suites/aggregate/having_distinct_expr.cfg new file mode 100644 index 0000000000..e377e2a9ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/having_distinct_expr.cfg @@ -0,0 +1 @@ +in Input3 input3.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/having_distinct_expr.sql b/yql/essentials/tests/sql/suites/aggregate/having_distinct_expr.sql new file mode 100644 index 0000000000..66ec18d2c8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/having_distinct_expr.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +use plato; +select key from Input3 group by key having count(distinct subkey || subkey) > 1; diff --git a/yql/essentials/tests/sql/suites/aggregate/having_without_aggregation.sqlx b/yql/essentials/tests/sql/suites/aggregate/having_without_aggregation.sqlx new file mode 100644 index 0000000000..cab0b5503b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/having_without_aggregation.sqlx @@ -0,0 +1,5 @@ +USE plato; + +SELECT * +FROM Input +HAVING key < '100' -- using having mean aggregation GROUP BY (), should use aggregation functions diff --git a/yql/essentials/tests/sql/suites/aggregate/histogram_cdf.sql b/yql/essentials/tests/sql/suites/aggregate/histogram_cdf.sql new file mode 100644 index 0000000000..3708106e4b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/histogram_cdf.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +USE plato; + +SELECT + histogram_cdf(CAST(key AS double)) AS key, + adaptive_ward_histogram_cdf(CAST(subkey AS double)) AS subkey +FROM Input4; diff --git a/yql/essentials/tests/sql/suites/aggregate/input.txt b/yql/essentials/tests/sql/suites/aggregate/input.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/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/aggregate/input2.txt b/yql/essentials/tests/sql/suites/aggregate/input2.txt new file mode 100644 index 0000000000..5c939cf453 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input2.txt @@ -0,0 +1,4 @@ +{"key"="1";"subkey"="10";"value"="FOO"}; +{"key"="1";"subkey"="20";"value"="BAR"}; +{"key"="1";"subkey"="30";"value"="BAR"}; +{"key"="0";"subkey"="40";"value"="FOO"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/input3.txt b/yql/essentials/tests/sql/suites/aggregate/input3.txt new file mode 100644 index 0000000000..48d828f6fd --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input3.txt @@ -0,0 +1,7 @@ +{"key"="1";"subkey"="10";"value"="FOO"}; +{"key"="1";"subkey"="20";"value"="BAR"}; +{"key"="1";"subkey"="30";"value"="BAR"}; +{"key"="0";"subkey"="40";"value"="FOO"}; +{"key"="0";"subkey"="40";"value"="FOO"}; +{"key"="A";"subkey"="50";"value"="WAT"}; +{"key"="A";"subkey"="50";"value"="WAT"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/input4.txt b/yql/essentials/tests/sql/suites/aggregate/input4.txt new file mode 100644 index 0000000000..65f33616b2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input4.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/aggregate/input5.txt b/yql/essentials/tests/sql/suites/aggregate/input5.txt new file mode 100644 index 0000000000..cca9e6118e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input5.txt @@ -0,0 +1,8 @@ +{"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"=#}; +{"key"="150";"subkey"="8";"value"="zzz"}; +{"key"="200";"subkey"="7";"value"="qqq"}; + diff --git a/yql/essentials/tests/sql/suites/aggregate/input5.txt.attr b/yql/essentials/tests/sql/suites/aggregate/input5.txt.attr new file mode 100644 index 0000000000..f0b397616f --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input5.txt.attr @@ -0,0 +1,12 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["subkey";["DataType";"String"]]; + ["value";["OptionalType";["DataType";"String"]]] + ]]; + "SortDirections"=[1;1;]; + "SortedBy"=["key";"subkey";]; + "SortedByTypes"=[["DataType";"String";];["DataType";"String";];]; + "SortMembers"=["key";"subkey";]; +}} + diff --git a/yql/essentials/tests/sql/suites/aggregate/input_coalesce.txt b/yql/essentials/tests/sql/suites/aggregate/input_coalesce.txt new file mode 100644 index 0000000000..acc680146e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_coalesce.txt @@ -0,0 +1,2 @@ +{"key"="1";"subkey"="2";"value"="3"}; +{"key"="4";"subkey"="5";"value"="x"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/input_compare.txt b/yql/essentials/tests/sql/suites/aggregate/input_compare.txt new file mode 100644 index 0000000000..3307820228 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_compare.txt @@ -0,0 +1,10 @@ +{"key"="1";"subkey"="";"value"=""}; +{"key"="1";"subkey"="";"value"="bbb"}; +{"key"="1";"subkey"="aaa";"value"=""}; +{"key"="1";"subkey"="aaa";"value"="bbb"}; +{"key"="2";"subkey"="";"value"="bbb"}; +{"key"="2";"subkey"="aaa";"value"=""}; +{"key"="2";"subkey"="aaa";"value"="bbb"}; +{"key"="3";"subkey"="aaa";"value"=""}; +{"key"="3";"subkey"="aaa";"value"="bbb"}; +{"key"="3";"subkey"="ccc";"value"=""}; diff --git a/yql/essentials/tests/sql/suites/aggregate/input_dict.txt b/yql/essentials/tests/sql/suites/aggregate/input_dict.txt new file mode 100644 index 0000000000..702a8e3c5c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_dict.txt @@ -0,0 +1,3 @@ +{"key"="023";"subkey"="1";"dict"={"a"="1"}}; +{"key"="023";"subkey"="2";"dict"={"a"="2"}}; +{"key"="023";"subkey"="3";"dict"={"a"="1"}}; diff --git a/yql/essentials/tests/sql/suites/aggregate/input_dict.txt.attr b/yql/essentials/tests/sql/suites/aggregate/input_dict.txt.attr new file mode 100644 index 0000000000..1dbf7040c5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_dict.txt.attr @@ -0,0 +1,7 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["subkey";["DataType";"String"]]; + ["dict";["DictType";["DataType";"String"];["DataType";"String"]]] + ]]; +}} diff --git a/yql/essentials/tests/sql/suites/aggregate/input_dsv.txt b/yql/essentials/tests/sql/suites/aggregate/input_dsv.txt new file mode 100644 index 0000000000..ad6793f93c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_dsv.txt @@ -0,0 +1,4 @@ +{"key"="075";"subkey"="1";"value"="a=B@@b=a@@c=a"}; +{"key"="800";"subkey"="2";"value"="b=1@@c=2@@d=3"}; +{"key"="020";"subkey"="3";"value"="a=i@@c=j"}; +{"key"="150";"subkey"="4";"value"="a=A"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/input_dsv.txt.attr b/yql/essentials/tests/sql/suites/aggregate/input_dsv.txt.attr new file mode 100644 index 0000000000..14544c6d7b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_dsv.txt.attr @@ -0,0 +1,32 @@ +{ + "_yql_read_udf"="Dsv.ReadRecord"; + "_yql_read_udf_run_config"="@@"; + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ] + } +} diff --git a/yql/essentials/tests/sql/suites/aggregate/input_expr.txt b/yql/essentials/tests/sql/suites/aggregate/input_expr.txt new file mode 100644 index 0000000000..621e7eaead --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/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/aggregate/input_intersect.txt b/yql/essentials/tests/sql/suites/aggregate/input_intersect.txt new file mode 100644 index 0000000000..42bcf2179e --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_intersect.txt @@ -0,0 +1,14 @@ +{"key"="075";"subkey"="911";"value"="abc"}; +{"key"="911";"subkey"="1";"value"="kkk"}; +{"key"="023";"subkey"="527";"value"="aaa"}; +{"key"="527";"subkey"="023";"value"="bbb"}; +{"key"="037";"subkey"="075";"value"="ddd"}; +{"key"="761";"subkey"="911";"value"="ccc"}; +{"key"="200";"subkey"="075";"value"="qqq"}; +{"key"="150";"subkey"="075";"value"="zzz"}; +{"key"="023";"subkey"="911";"value"="vca"}; +{"key"="527";"subkey"="150";"value"="oef"}; +{"key"="037";"subkey"="761";"value"="vdf"}; +{"key"="761";"subkey"="037";"value"="aet"}; +{"key"="200";"subkey"="150";"value"="fdb"}; +{"key"="150";"subkey"="037";"value"="bfs"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/input_intersect_sorted.txt b/yql/essentials/tests/sql/suites/aggregate/input_intersect_sorted.txt new file mode 100644 index 0000000000..617e73e92f --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_intersect_sorted.txt @@ -0,0 +1,14 @@ +{"key"="023";"subkey"="527";"value"="aaa"}; +{"key"="023";"subkey"="911";"value"="vca"}; +{"key"="037";"subkey"="075";"value"="ddd"}; +{"key"="037";"subkey"="761";"value"="vdf"}; +{"key"="075";"subkey"="911";"value"="abc"}; +{"key"="150";"subkey"="037";"value"="bfs"}; +{"key"="150";"subkey"="075";"value"="zzz"}; +{"key"="200";"subkey"="075";"value"="qqq"}; +{"key"="200";"subkey"="150";"value"="fdb"}; +{"key"="527";"subkey"="023";"value"="bbb"}; +{"key"="527";"subkey"="150";"value"="oef"}; +{"key"="761";"subkey"="037";"value"="aet"}; +{"key"="761";"subkey"="911";"value"="ccc"}; +{"key"="911";"subkey"="1";"value"="kkk"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/input_sorted.txt b/yql/essentials/tests/sql/suites/aggregate/input_sorted.txt new file mode 100644 index 0000000000..004ddc583b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/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/aggregate/input_tutorial_users.txt b/yql/essentials/tests/sql/suites/aggregate/input_tutorial_users.txt new file mode 100644 index 0000000000..4a18a0dd29 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/input_tutorial_users.txt @@ -0,0 +1,12 @@ +{"key"="15";"subkey"="213";"value"="Anya"}; +{"key"="25";"subkey"="225";"value"="Petr"}; +{"key"="17";"subkey"="1";"value"="Masha"}; +{"key"="5";"subkey"="225";"value"="Alena"}; +{"key"="23";"subkey"="2";"value"="Irina"}; +{"key"="13";"subkey"="21";"value"="Inna"}; +{"key"="33";"subkey"="125";"value"="Ivan"}; +{"key"="45";"subkey"="225";"value"="Asya"}; +{"key"="27";"subkey"="125";"value"="German"}; +{"key"="41";"subkey"="225";"value"="Olya"}; +{"key"="35";"subkey"="2";"value"="Slava"}; +{"key"="56";"subkey"="2";"value"="Elena"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/library_error_in_aggregation_fail.cfg b/yql/essentials/tests/sql/suites/aggregate/library_error_in_aggregation_fail.cfg new file mode 100644 index 0000000000..f139073dff --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/library_error_in_aggregation_fail.cfg @@ -0,0 +1,2 @@ +in Input input.txt +xfail diff --git a/yql/essentials/tests/sql/suites/aggregate/library_error_in_aggregation_fail.sql b/yql/essentials/tests/sql/suites/aggregate/library_error_in_aggregation_fail.sql new file mode 100644 index 0000000000..3d7640c756 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/library_error_in_aggregation_fail.sql @@ -0,0 +1,21 @@ +/* syntax version 1 */ +/* postgres can not */ + +USE plato; + +select * from ( +SELECT + a.key as x, sum(b.value) +FROM Input as a +JOIN Input as b +USING (key) +GROUP BY a.key +) where x > "aaa" +ORDER BY x; + +select 1; +select 1; +select 1; +select 1; +select 1; +select 1; diff --git a/yql/essentials/tests/sql/suites/aggregate/list_after_group.sql b/yql/essentials/tests/sql/suites/aggregate/list_after_group.sql new file mode 100644 index 0000000000..d3169432ff --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/list_after_group.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +select aggregate_list(key) as key_list, listsort(aggregate_list(key)) AS sorted_key_list, value as name from plato.Input4 group by value +order by name;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/list_nullable.cfg b/yql/essentials/tests/sql/suites/aggregate/list_nullable.cfg new file mode 100644 index 0000000000..5640285809 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/list_nullable.cfg @@ -0,0 +1 @@ +in Input input_coalesce.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/list_nullable.sql b/yql/essentials/tests/sql/suites/aggregate/list_nullable.sql new file mode 100644 index 0000000000..48a56c7485 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/list_nullable.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select aggregate_list(cast(value as int)) as val_list from plato.Input;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.cfg b/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.cfg new file mode 100644 index 0000000000..4d76f79a62 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.cfg @@ -0,0 +1 @@ +in Input list_with_fold_map.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.sql b/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.sql new file mode 100644 index 0000000000..74df1240ed --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.sql @@ -0,0 +1,21 @@ +/* syntax version 1 */ +USE plato; + +$data = ( + SELECT + YQL::FoldMap( + counters, + names, + ($counter, $names) -> { + RETURN AsTuple(Unwrap($names[$counter]), $names); + } + ) AS profile, + id + FROM Input +); + +SELECT + AGGREGATE_LIST(profile) AS profiles, + id +FROM $data +GROUP BY id; diff --git a/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.txt b/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.txt new file mode 100644 index 0000000000..b8e76e34c6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.txt @@ -0,0 +1,2 @@ +{"counters"=[0;1;];"names"=["a";"b";];"id"="tmp1";}; +{"counters"=[0;0;];"names"=["c";];"id"="tmp2";}; diff --git a/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.txt.attr b/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.txt.attr new file mode 100644 index 0000000000..edb026c6b0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/list_with_fold_map.txt.attr @@ -0,0 +1,8 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["counters";["ListType";["DataType";"Int64"]]]; + ["names";["ListType";["DataType";"String"]]]; + ["id";["DataType";"String"]] + ]]; +}} + diff --git a/yql/essentials/tests/sql/suites/aggregate/listbuiltin_constness.sql b/yql/essentials/tests/sql/suites/aggregate/listbuiltin_constness.sql new file mode 100644 index 0000000000..a9e7a23c03 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/listbuiltin_constness.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + ListMap([1,2,3], ($x) -> ($x)) AS x +FROM AS_TABLE([<|key:1|>,<|key:2|>]) +GROUP BY key; + diff --git a/yql/essentials/tests/sql/suites/aggregate/native_desc_group_compact_by.cfg b/yql/essentials/tests/sql/suites/aggregate/native_desc_group_compact_by.cfg new file mode 100644 index 0000000000..2190809549 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/native_desc_group_compact_by.cfg @@ -0,0 +1,2 @@ +in Input1 descending.txt +in Input2 input5.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/native_desc_group_compact_by.sql b/yql/essentials/tests/sql/suites/aggregate/native_desc_group_compact_by.sql new file mode 100644 index 0000000000..71f8b03302 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/native_desc_group_compact_by.sql @@ -0,0 +1,24 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; +pragma yt.UseNativeDescSort; + +select key, subkey from Input1 -- YtReduce +group compact by key, subkey +order by key, subkey; + +select key, subkey from Input1 -- YtReduce +group compact by subkey, key +order by subkey, key; + +select key from Input1 -- YtReduce +group compact by key +order by key; + +select subkey from Input1 -- YtMapReduce +group compact by subkey +order by subkey; + +select key, subkey from concat(Input1, Input2) -- YtMapReduce, mix of ascending/descending +group compact by key, subkey +order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/aggregate/null_type.sql b/yql/essentials/tests/sql/suites/aggregate/null_type.sql new file mode 100644 index 0000000000..03f1b81d01 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/null_type.sql @@ -0,0 +1,24 @@ +/* syntax version 1 */ +/* postgres can not */ +select + min(x), + count(x), + count(*), + aggregate_list_distinct(x), + aggregate_list(x), + bool_and(x) +from ( + select null as x union all select Null as x +); + +select + min(x), + count(x), + count(*), + aggregate_list_distinct(x), + aggregate_list(x), + bool_and(x) +from ( + select null as x, 1 as y union all select Null as x, 2 as y +) +group by y; diff --git a/yql/essentials/tests/sql/suites/aggregate/parsetype_constness.sql b/yql/essentials/tests/sql/suites/aggregate/parsetype_constness.sql new file mode 100644 index 0000000000..0d25fbf839 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/parsetype_constness.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +SELECT + Yson::ConvertTo("[1,2]"j, ParseType("List<Int32>")) as x +FROM AS_TABLE([<|key:1|>, <|key:2|>]) +GROUP BY key; + diff --git a/yql/essentials/tests/sql/suites/aggregate/percentile_and_avg_grouped.cfg b/yql/essentials/tests/sql/suites/aggregate/percentile_and_avg_grouped.cfg new file mode 100644 index 0000000000..910dfcb7e0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentile_and_avg_grouped.cfg @@ -0,0 +1,3 @@ +in Input percentiles_input.txt +udf stat_udf + diff --git a/yql/essentials/tests/sql/suites/aggregate/percentile_and_avg_grouped.sql b/yql/essentials/tests/sql/suites/aggregate/percentile_and_avg_grouped.sql new file mode 100644 index 0000000000..6dc89752ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentile_and_avg_grouped.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select key, median(val) as med, avg(val) as avg from (select key, cast(value as int) as val from plato.Input) group by key order by key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/percentile_and_variance.cfg b/yql/essentials/tests/sql/suites/aggregate/percentile_and_variance.cfg new file mode 100644 index 0000000000..9410b8e431 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentile_and_variance.cfg @@ -0,0 +1,3 @@ +in Input percentiles_input.txt +udf stat_udf +udf math_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/percentile_and_variance.sql b/yql/essentials/tests/sql/suites/aggregate/percentile_and_variance.sql new file mode 100644 index 0000000000..c75cf50c6c --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentile_and_variance.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select Math::Round(median(val), -3) as med, Math::Round(stddev(val), -3) as dev from (select cast(value as int) as val from plato.Input);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/percentile_interval.sql b/yql/essentials/tests/sql/suites/aggregate/percentile_interval.sql new file mode 100644 index 0000000000..922579f7eb --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentile_interval.sql @@ -0,0 +1,38 @@ +/* syntax version 1 */ + +$zero = unwrap( cast(0 as Interval) ); +$one = unwrap( cast (1 as Interval ) ); + +-- we want to check both optional<interval> and plain interval +$prepared = select + cast (key As Interval) ?? $zero as interval_data + from plato.Input; + +$source = select + interval_data + , interval_data + $one as interval_data2 + , just( interval_data ) as optional_interval_data + from $prepared; + +-- percentile factory can work with plain number and with tuple of numbers. +-- to achive second call we must make several percentile invocations with +-- same column name +$data_plain = select + percentile(interval_data, 0.8) as result + from $source; + +-- optimization should unite this into one call to percentile with tuple as argument +$data_tuple = select + percentile(interval_data2, 0.8) as result_1 + , percentile(interval_data2, 0.6) as result_2 + from $source; + +$data_optional = select + percentile(optional_interval_data, 0.4) as result + from $source; + +select EnsureType(result, Interval?) from $data_plain; +select EnsureType(result_1, Interval?) from $data_tuple; +select EnsureType(result_2, Interval?) from $data_tuple; +select result from $data_optional; + diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_containers.cfg b/yql/essentials/tests/sql/suites/aggregate/percentiles_containers.cfg new file mode 100644 index 0000000000..b0152e1177 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_containers.cfg @@ -0,0 +1,2 @@ +in Input percentiles_input.txt +udf stat_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_containers.sql b/yql/essentials/tests/sql/suites/aggregate/percentiles_containers.sql new file mode 100644 index 0000000000..7bf4633e45 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_containers.sql @@ -0,0 +1,16 @@ +select + key, + median(val) as med, + percentile(val, AsTuple(0.2, 0.4, 0.6)) as ptuple, + percentile(val, AsStruct(0.2 as p20, 0.4 as p40, 0.6 as p60)) as pstruct, + percentile(val, AsList(0.2, 0.4, 0.6)) as plist, +from (select key, cast(value as int) as val from plato.Input) +group by key +order by key; + +select + median(val) as med, + percentile(val, AsTuple(0.2, 0.4, 0.6)) as ptuple, + percentile(val, AsStruct(0.2 as p20, 0.4 as p40, 0.6 as p60)) as pstruct, + percentile(val, AsList(0.2, 0.4, 0.6)) as plist, +from (select key, cast(value as int) as val from plato.Input) diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped.cfg b/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped.cfg new file mode 100644 index 0000000000..b0152e1177 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped.cfg @@ -0,0 +1,2 @@ +in Input percentiles_input.txt +udf stat_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped.sql b/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped.sql new file mode 100644 index 0000000000..f4297e06e3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select median(val) as med, median(distinct val) as distinct_med, percentile(val, 0.8) as p80 from (select key, cast(value as int) as val from plato.Input) group by key order by med;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped_expr.cfg b/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped_expr.cfg new file mode 100644 index 0000000000..b0152e1177 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped_expr.cfg @@ -0,0 +1,2 @@ +in Input percentiles_input.txt +udf stat_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped_expr.sql b/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped_expr.sql new file mode 100644 index 0000000000..1ad133feee --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_grouped_expr.sql @@ -0,0 +1,8 @@ +select + median(val + 1) as med, + median(distinct val + 1) as distinct_med, + percentile(val + 1, 0.8) as p80 +from ( + select key, cast(value as int) as val from plato.Input +) +group by key order by med; diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_input.txt b/yql/essentials/tests/sql/suites/aggregate/percentiles_input.txt new file mode 100644 index 0000000000..a2f0714b6f --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_input.txt @@ -0,0 +1,10 @@ +{"key"="foo";"subkey"="";"value"="1"}; +{"key"="foo";"subkey"="";"value"="1"}; +{"key"="foo";"subkey"="";"value"="1"}; +{"key"="foo";"subkey"="";"value"="1"}; +{"key"="foo";"subkey"="";"value"="1"}; +{"key"="bar";"subkey"="";"value"="1"}; +{"key"="bar";"subkey"="";"value"="2"}; +{"key"="bar";"subkey"="";"value"="3"}; +{"key"="bar";"subkey"="";"value"="4"}; +{"key"="bar";"subkey"="";"value"="5"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_ungrouped.cfg b/yql/essentials/tests/sql/suites/aggregate/percentiles_ungrouped.cfg new file mode 100644 index 0000000000..52e9c9bc90 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_ungrouped.cfg @@ -0,0 +1,2 @@ +in Input percentiles_input.txt +udf stat_udf
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/percentiles_ungrouped.sql b/yql/essentials/tests/sql/suites/aggregate/percentiles_ungrouped.sql new file mode 100644 index 0000000000..045b66c239 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/percentiles_ungrouped.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select median(val) as med, percentile(val, 0.8) as p80 from (select cast(value as int) as val from plato.Input);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/aggregate/rollup_with_dict.cfg b/yql/essentials/tests/sql/suites/aggregate/rollup_with_dict.cfg new file mode 100644 index 0000000000..11a8788b61 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/rollup_with_dict.cfg @@ -0,0 +1,2 @@ +in Input input_dsv.txt +udf dsv_udf diff --git a/yql/essentials/tests/sql/suites/aggregate/rollup_with_dict.sql b/yql/essentials/tests/sql/suites/aggregate/rollup_with_dict.sql new file mode 100644 index 0000000000..d59e69d29b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/rollup_with_dict.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +--insert into Output +select val, count(*) as cnt, grouping(val) as grouping +from Input as t +group by rollup(t.`dict`["c"] as val) +order by val, cnt +; diff --git a/yql/essentials/tests/sql/suites/aggregate/session1.txt b/yql/essentials/tests/sql/suites/aggregate/session1.txt new file mode 100644 index 0000000000..b3825796b1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/session1.txt @@ -0,0 +1,27 @@ +{"user"="u1"; "ts"=2; payload=6 }; +{"user"="u1"; "ts"=3; payload=8 }; +{"user"="u1"; "ts"=4; payload=10 }; +{"user"="u1"; "ts"=11; payload=12 }; + +{"user"="u1"; "ts"=22; payload=14 }; +{"user"="u1"; "ts"=32; payload=16 }; + +{"user"="u1"; "ts"=51; payload=18 }; + +{"user"="u1"; "ts"=#; payload=2 }; +{"user"="u1"; "ts"=#; payload=4 }; + + + +{"user"=#; "ts"=#; payload=1 }; +{"user"=#; "ts"=#; payload=1 }; + +{"user"=#; "ts"=1; payload=5 }; +{"user"=#; "ts"=2; payload=5 }; +{"user"=#; "ts"=3; payload=5 }; +{"user"=#; "ts"=10; payload=11 }; + +{"user"=#; "ts"=21; payload=13 }; +{"user"=#; "ts"=31; payload=15 }; + +{"user"=#; "ts"=50; payload=17 }; diff --git a/yql/essentials/tests/sql/suites/aggregate/session1.txt.attr b/yql/essentials/tests/sql/suites/aggregate/session1.txt.attr new file mode 100644 index 0000000000..0df1a044c1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/session1.txt.attr @@ -0,0 +1,7 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["user";["OptionalType";["DataType";"String"]]]; + ["ts";["OptionalType";["DataType";"Int32"]]]; + ["payload";["DataType";"Int32"]]] + ]; +}} diff --git a/yql/essentials/tests/sql/suites/aggregate/should_use_clone_for_bind_params.sqlx b/yql/essentials/tests/sql/suites/aggregate/should_use_clone_for_bind_params.sqlx new file mode 100644 index 0000000000..a5c275dcdc --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/should_use_clone_for_bind_params.sqlx @@ -0,0 +1,13 @@ +/* postgres can not */ +use plato; + +$puid = ($mission) -> {RETURN Yson::LookupUint64($mission, 'puid');}; +$mid = ($mission) -> {RETURN Yson::LookupString($mission, 'external_id');}; +$customer = ($mission) -> {RETURN Yson::LookupInt64($mission, 'customer');}; + +SELECT count(distinct $puid) from Input; +SELECT count(distinct $mid) from Input; +SELECT count(distinct $customer) from Input; + +SELECT count(distinct $puid,$mid,$customer) from Input; + diff --git a/yql/essentials/tests/sql/suites/aggregate/sorted.txt b/yql/essentials/tests/sql/suites/aggregate/sorted.txt new file mode 100644 index 0000000000..565ce828c0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/sorted.txt @@ -0,0 +1,6 @@ +{"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"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/aggregate/sorted.txt.attr b/yql/essentials/tests/sql/suites/aggregate/sorted.txt.attr new file mode 100644 index 0000000000..ed13e20223 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/sorted.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/yql/essentials/tests/sql/suites/aggregate/subquery_aggregation.cfg b/yql/essentials/tests/sql/suites/aggregate/subquery_aggregation.cfg new file mode 100644 index 0000000000..c7e99df4d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/subquery_aggregation.cfg @@ -0,0 +1 @@ +in Input input_expr.txt diff --git a/yql/essentials/tests/sql/suites/aggregate/subquery_aggregation.sql b/yql/essentials/tests/sql/suites/aggregate/subquery_aggregation.sql new file mode 100644 index 0000000000..b5ed8ae250 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/subquery_aggregation.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$total_count = (SELECT Count(*) from Input); + +SELECT + common, + count(*) as rec_count, + 100. * count(*) / $total_count as part_percent +FROM Input +GROUP BY subkey as common +ORDER BY common +; diff --git a/yql/essentials/tests/sql/suites/aggregate/table_funcs_group_by.sql b/yql/essentials/tests/sql/suites/aggregate/table_funcs_group_by.sql new file mode 100644 index 0000000000..85652ed433 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/table_funcs_group_by.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +--insert into Output +select + groupTribit, + count(*) as count +from Input +GROUP BY TableRecordIndex() % 3 as groupTribit +ORDER BY groupTribit, count diff --git a/yql/essentials/tests/sql/suites/aggregate/table_funcs_spec_aggregation.sqlx b/yql/essentials/tests/sql/suites/aggregate/table_funcs_spec_aggregation.sqlx new file mode 100644 index 0000000000..8b9040a95a --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/table_funcs_spec_aggregation.sqlx @@ -0,0 +1,10 @@ +/* postgres can not */ +use plato; + +--insert into Output +select + key, + max(TablePath()) as table_rec +from Input +group by key +; diff --git a/yql/essentials/tests/sql/suites/aggregate/table_row_aggregation.sql b/yql/essentials/tests/sql/suites/aggregate/table_row_aggregation.sql new file mode 100644 index 0000000000..f35a0d881b --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/table_row_aggregation.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$data = ( + SELECT SOME(TableRow()) + FROM Input4 + GROUP BY key +); + +SELECT * +FROM $data +FLATTEN COLUMNS +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/aggregate/yql-18511.cfg b/yql/essentials/tests/sql/suites/aggregate/yql-18511.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/yql-18511.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql b/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql new file mode 100644 index 0000000000..e0d2d2b0d4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql @@ -0,0 +1,70 @@ +/* yt can not */ + +$round_period = ($day, $period) -> { + RETURN + CASE + WHEN $period = 'd' THEN $day + WHEN $period = 'w' THEN DateTime::MakeDate(DateTime::StartOfWeek($day)) + WHEN $period = 'm' THEN DateTime::MakeDate(DateTime::StartOfMonth($day)) + ELSE $day + END +}; + +$data = +SELECT + $round_period(day, 'd') AS day, + $round_period(day, 'w') AS week, + $round_period(day, 'm') AS month, + IF(user_card_cnt <= 10, user_card_cnt, 11) AS user_cards_segm, + is_proven_owner, + user_id, +FROM ( + SELECT + Date("2024-04-29") AS day, + "ALLO" AS mark, + "???" AS model, + 5 AS user_card_cnt, + 'ACTIVE' AS status, + 999 AS user_id, + 1 AS is_proven_owner, + UNION ALL + SELECT + Date("2024-04-29") AS day, + "ALLO" AS mark, + "!!!!!!" AS model, + 50 AS user_card_cnt, + 'ACTIVE' AS status, + 1111 AS user_id, + 0 AS is_proven_owner, +); + +SELECT + day, + GROUPING(day) AS grouping_day, + week, + GROUPING(week) AS grouping_week, + month, + GROUPING(month) as grouping_month, + CASE + WHEN GROUPING(week) == 1 AND GROUPING(month) == 1 THEN 'd' + WHEN GROUPING(day) == 1 AND GROUPING(month) == 1 THEN 'w' + WHEN GROUPING(day) == 1 AND GROUPING(week) == 1 THEN 'm' + ELSE NULL + END AS period_type, + user_cards_segm, + if(GROUPING(user_cards_segm) = 1, -300, user_cards_segm) AS __user_cards_segm__, + GROUPING(user_cards_segm) as grouping_user_cards_segm, + COUNT(DISTINCT user_id) AS all_user_qty, +FROM $data AS t +GROUP BY + GROUPING SETS( + -- day grouping + (day), + (day, user_cards_segm), + -- -- week grouping + (week), + (week, user_cards_segm), + -- -- month grouping + (month), + (month, user_cards_segm) + ) |