diff options
| author | vitya-smirnov <[email protected]> | 2025-09-05 11:36:09 +0300 |
|---|---|---|
| committer | vitya-smirnov <[email protected]> | 2025-09-05 13:24:55 +0300 |
| commit | 2db41830cd5fce47e0bac5493c1e1472908cc4c5 (patch) | |
| tree | e69466e8db1cd55453de95f6ca7d108cae990466 | |
| parent | c16c8277df06c43957bef277c57200b22772d046 (diff) | |
YQL-20234: Remove predicate argument on middle aggregation phases
Also added tests for:
- max_by
- agg_list
- some
- avg_if
- sum_if
- count_if
commit_hash:0da4f3b9b1767850e65c914b727bc362cf4cc125
50 files changed, 4611 insertions, 2 deletions
diff --git a/yql/essentials/sql/v1/aggregation.cpp b/yql/essentials/sql/v1/aggregation.cpp index d7324012c13..87a1859f208 100644 --- a/yql/essentials/sql/v1/aggregation.cpp +++ b/yql/essentials/sql/v1/aggregation.cpp @@ -370,7 +370,24 @@ public: private: bool InitAggr(TContext& ctx, bool isFactory, ISource* src, TAstListNode& node, const TVector<TNodePtr>& exprs) final { - ui32 adjustArgsCount = isFactory ? 0 : 2; + TStringBuf suffix; + if (src != nullptr) { + suffix = src->GetGroupBySuffix(); + } else { + suffix = ""; + } + + bool isStateRecieving = !(suffix == "" || suffix == "Combine" || suffix == "Finalize"); + + ui32 adjustArgsCount; + if (isFactory) { + adjustArgsCount = 0; + } else if (isStateRecieving) { + adjustArgsCount = 1; + } else { + adjustArgsCount = 2; + } + if (exprs.size() != adjustArgsCount) { ctx.Error(Pos_) << "Aggregation function " << (isFactory ? "factory " : "") << Name_ << " requires " << adjustArgsCount << " arguments, given: " << exprs.size(); @@ -383,7 +400,11 @@ private: if (!isFactory) { Payload_ = exprs.front(); - Predicate_ = exprs.back(); + if (!isStateRecieving) { + Predicate_ = exprs.back(); + } else { + Predicate_ = Y("InstanceOf", Y("DataType", Q("Bool"))); + } Name_ = src->MakeLocalName(Name_); } diff --git a/yql/essentials/sql/v1/source.cpp b/yql/essentials/sql/v1/source.cpp index 853421c8952..1a6038b5e6a 100644 --- a/yql/essentials/sql/v1/source.cpp +++ b/yql/essentials/sql/v1/source.cpp @@ -111,6 +111,10 @@ void ISource::SetCompactGroupBy(bool compactGroupBy) { CompactGroupBy_ = compactGroupBy; } +TStringBuf ISource::GetGroupBySuffix() const { + return GroupBySuffix_; +} + void ISource::SetGroupBySuffix(const TString& suffix) { GroupBySuffix_ = suffix; } diff --git a/yql/essentials/sql/v1/source.h b/yql/essentials/sql/v1/source.h index 8aaadd2b840..20f81135cd4 100644 --- a/yql/essentials/sql/v1/source.h +++ b/yql/essentials/sql/v1/source.h @@ -47,6 +47,7 @@ namespace NSQLTranslationV1 { virtual bool AddFilter(TContext& ctx, TNodePtr filter); virtual bool AddGroupKey(TContext& ctx, const TString& column); virtual void SetCompactGroupBy(bool compactGroupBy); + virtual TStringBuf GetGroupBySuffix() const; virtual void SetGroupBySuffix(const TString& suffix); virtual TString MakeLocalName(const TString& name); virtual bool AddAggregation(TContext& ctx, TAggregationPtr aggr); diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h index 5451bf4b844..a63fc01c29d 100644 --- a/yql/essentials/sql/v1/sql_ut_common.h +++ b/yql/essentials/sql/v1/sql_ut_common.h @@ -9508,7 +9508,27 @@ Y_UNIT_TEST_SUITE(Aggregation) { UNIT_ASSERT_VALUES_EQUAL(1, count["percentile_traits_factory"]); } +} + +Y_UNIT_TEST_SUITE(AggregationPhases) { + Y_UNIT_TEST(TwoArg) { + NYql::TAstParseResult res = SqlToYql(R"sql( + SELECT AvgIf(a, a % 2 == 0) FROM (SELECT 1 AS k, 2 AS a) GROUP BY k; + SELECT AvgIf(a, a % 2 == 0) FROM (SELECT 1 AS k, 2 AS a) GROUP BY k WITH Combine; + SELECT AvgIf(a, a % 2 == 0) FROM (SELECT 1 AS k, 2 AS a) GROUP BY k WITH Finalize; + )sql"); + + UNIT_ASSERT_C(res.IsOk(), res.Issues.ToString()); + } + + Y_UNIT_TEST(SingleArg) { + NYql::TAstParseResult res = SqlToYql(R"sql( + SELECT AvgIf(a) FROM (SELECT 1 AS k, 2 AS a) GROUP BY k WITH CombineState; + SELECT AvgIf(a) FROM (SELECT 1 AS k, 2 AS a) GROUP BY k WITH MergeState; + )sql"); + UNIT_ASSERT_C(res.IsOk(), res.Issues.ToString()); + } } Y_UNIT_TEST_SUITE(Watermarks) { diff --git a/yql/essentials/tests/sql/minirun/part0/canondata/result.json b/yql/essentials/tests/sql/minirun/part0/canondata/result.json index a51c6ca401b..1c396145928 100644 --- a/yql/essentials/tests/sql/minirun/part0/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part0/canondata/result.json @@ -125,6 +125,20 @@ "uri": "https://{canondata_backend}/1937001/cbe99eacfe62f79e3e707178e989fd7313913633/resource.tar.gz#test.test_agg_phases-hll-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-max_by_null-default.txt-Debug]": [ + { + "checksum": "adefd887e77def34b481faac68bb6dde", + "size": 1873, + "uri": "https://{canondata_backend}/1871002/0bd7bae2e159fe65a412266480c9ae3b6baa1ac9/resource.tar.gz#test.test_agg_phases-max_by_null-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-max_by_null-default.txt-Results]": [ + { + "checksum": "200ff2930124c82a55e22120feee5388", + "size": 5576, + "uri": "https://{canondata_backend}/1871002/0bd7bae2e159fe65a412266480c9ae3b6baa1ac9/resource.tar.gz#test.test_agg_phases-max_by_null-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-percentile_opt-default.txt-Debug]": [ { "checksum": "69193de44c665a300c15948edb7e3a7e", diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/result.json b/yql/essentials/tests/sql/minirun/part2/canondata/result.json index 7ff7cd2f25d..e81150d55a4 100644 --- a/yql/essentials/tests/sql/minirun/part2/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part2/canondata/result.json @@ -83,6 +83,20 @@ "uri": "https://{canondata_backend}/1773845/e276b05a77b889bd8fbb83a0bc0087dea7abd8f4/resource.tar.gz#test.test_agg_apply-sum_signed-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-agg_list_n_opt-default.txt-Debug]": [ + { + "checksum": "ba667d2543df22e0719b8b686faeb850", + "size": 2384, + "uri": "https://{canondata_backend}/1925842/3cbaf5e3913576ae3e4d9ca25810da6667949627/resource.tar.gz#test.test_agg_phases-agg_list_n_opt-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-agg_list_n_opt-default.txt-Results]": [ + { + "checksum": "54af7bf1109833cc0d2f8c2acd4f13d4", + "size": 7184, + "uri": "https://{canondata_backend}/1925842/3cbaf5e3913576ae3e4d9ca25810da6667949627/resource.tar.gz#test.test_agg_phases-agg_list_n_opt-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-count_all-default.txt-Debug]": [ { "checksum": "a9d6b939210fe1ecd71f8c572e7d5142", @@ -97,6 +111,20 @@ "uri": "https://{canondata_backend}/1942671/d2c52b40b9b65d562a014f1192c6a7678cc156c6/resource.tar.gz#test.test_agg_phases-count_all-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-count_if-default.txt-Debug]": [ + { + "checksum": "f43d93ac1499018a6555f6d1c29fe27c", + "size": 2240, + "uri": "https://{canondata_backend}/1937424/8d9a02e15416afd852826996fd27464ad683cf28/resource.tar.gz#test.test_agg_phases-count_if-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-count_if-default.txt-Results]": [ + { + "checksum": "9ae8382194a4739bae2e02214d2d5fbd", + "size": 5864, + "uri": "https://{canondata_backend}/1937424/8d9a02e15416afd852826996fd27464ad683cf28/resource.tar.gz#test.test_agg_phases-count_if-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-max-default.txt-Debug]": [ { "checksum": "93a5d098ec1fb36b00b545e46350e1c4", @@ -111,6 +139,20 @@ "uri": "https://{canondata_backend}/1942671/d2c52b40b9b65d562a014f1192c6a7678cc156c6/resource.tar.gz#test.test_agg_phases-max-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-sum_if_opt-default.txt-Debug]": [ + { + "checksum": "8a93c0ea7c82d00c03dc4f52d1c64346", + "size": 2330, + "uri": "https://{canondata_backend}/1942100/914d9699421ed6c10c74a4fba3e6daea2546351a/resource.tar.gz#test.test_agg_phases-sum_if_opt-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-sum_if_opt-default.txt-Results]": [ + { + "checksum": "857008d4a5a43eed84f7034f5f4e7e4c", + "size": 7010, + "uri": "https://{canondata_backend}/1942100/914d9699421ed6c10c74a4fba3e6daea2546351a/resource.tar.gz#test.test_agg_phases-sum_if_opt-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases_agg_apply-count_null-default.txt-Debug]": [ { "checksum": "f8f506dee8b955cb3ec68dd9e714ddbb", diff --git a/yql/essentials/tests/sql/minirun/part4/canondata/result.json b/yql/essentials/tests/sql/minirun/part4/canondata/result.json index de4f5eeeb08..1823c3a25ac 100644 --- a/yql/essentials/tests/sql/minirun/part4/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part4/canondata/result.json @@ -97,6 +97,20 @@ "uri": "https://{canondata_backend}/1781765/7ff2578974c8f5e77edbe8e187037900c143b7bf/resource.tar.gz#test.test_agg_apply-sum_double-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-agg_list-default.txt-Debug]": [ + { + "checksum": "084210700c995728ce540c4a57041417", + "size": 2116, + "uri": "https://{canondata_backend}/1775059/2c185863ccfab84e64074b1a264d7bdad8f953db/resource.tar.gz#test.test_agg_phases-agg_list-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-agg_list-default.txt-Results]": [ + { + "checksum": "b9341db0ac522b4c1114bd6ed91c63e3", + "size": 7382, + "uri": "https://{canondata_backend}/1775059/2c185863ccfab84e64074b1a264d7bdad8f953db/resource.tar.gz#test.test_agg_phases-agg_list-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-avg-default.txt-Debug]": [ { "checksum": "517f16b0d4dc745dab9182c2c72fa76f", @@ -111,6 +125,20 @@ "uri": "https://{canondata_backend}/1942671/51dbc459938e37e70bfadc48150552cf3194f399/resource.tar.gz#test.test_agg_phases-avg-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-avg_if-default.txt-Debug]": [ + { + "checksum": "678ffeb2a050551817e3ab1ef0cb5177", + "size": 2822, + "uri": "https://{canondata_backend}/1942100/de4286ebae2208adb7d59ec6e551ff7be1e282bd/resource.tar.gz#test.test_agg_phases-avg_if-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-avg_if-default.txt-Results]": [ + { + "checksum": "ee42615d3bb7830efb31837a4c8c0f39", + "size": 8684, + "uri": "https://{canondata_backend}/1942100/de4286ebae2208adb7d59ec6e551ff7be1e282bd/resource.tar.gz#test.test_agg_phases-avg_if-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-avg_opt-default.txt-Debug]": [ { "checksum": "7c4369c52fecd441bf0308ff628ea396", @@ -125,6 +153,20 @@ "uri": "https://{canondata_backend}/1942671/51dbc459938e37e70bfadc48150552cf3194f399/resource.tar.gz#test.test_agg_phases-avg_opt-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-count_if_opt-default.txt-Debug]": [ + { + "checksum": "d4b0a98aea0397a7f6b729e70023ed0d", + "size": 2269, + "uri": "https://{canondata_backend}/1937429/0081e997e334c5336b14acac30adb498d53de519/resource.tar.gz#test.test_agg_phases-count_if_opt-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-count_if_opt-default.txt-Results]": [ + { + "checksum": "9ae8382194a4739bae2e02214d2d5fbd", + "size": 5864, + "uri": "https://{canondata_backend}/1937429/0081e997e334c5336b14acac30adb498d53de519/resource.tar.gz#test.test_agg_phases-count_if_opt-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-count_null-default.txt-Debug]": [ { "checksum": "1c8b6dcfabd437dd7d3e59f642093817", @@ -181,6 +223,34 @@ "uri": "https://{canondata_backend}/1942671/51dbc459938e37e70bfadc48150552cf3194f399/resource.tar.gz#test.test_agg_phases-min_by-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-some-default.txt-Debug]": [ + { + "checksum": "61653edcbff258c47db9345d1971f56b", + "size": 1944, + "uri": "https://{canondata_backend}/1946324/2443d02e009b04e614f7ef41a502dd56aa7109f9/resource.tar.gz#test.test_agg_phases-some-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-some-default.txt-Results]": [ + { + "checksum": "6c46544a8cbba54541c9a2c96ffa6edf", + "size": 5858, + "uri": "https://{canondata_backend}/1946324/2443d02e009b04e614f7ef41a502dd56aa7109f9/resource.tar.gz#test.test_agg_phases-some-default.txt-Results_/results.txt" + } + ], + "test.test[agg_phases-sum_if-default.txt-Debug]": [ + { + "checksum": "ce998f9457735cd76a8d31b605b07e7b", + "size": 2293, + "uri": "https://{canondata_backend}/1942100/de4286ebae2208adb7d59ec6e551ff7be1e282bd/resource.tar.gz#test.test_agg_phases-sum_if-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-sum_if-default.txt-Results]": [ + { + "checksum": "857008d4a5a43eed84f7034f5f4e7e4c", + "size": 7010, + "uri": "https://{canondata_backend}/1942100/de4286ebae2208adb7d59ec6e551ff7be1e282bd/resource.tar.gz#test.test_agg_phases-sum_if-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases_agg_apply-count_all_null-default.txt-Debug]": [ { "checksum": "defcd4527c0040910a5cce97dcde8924", diff --git a/yql/essentials/tests/sql/minirun/part5/canondata/result.json b/yql/essentials/tests/sql/minirun/part5/canondata/result.json index a35ebf7982b..c92d0592ea1 100644 --- a/yql/essentials/tests/sql/minirun/part5/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part5/canondata/result.json @@ -181,6 +181,48 @@ "uri": "https://{canondata_backend}/1889210/7b025ccc7688fd68c0684128e0143c02dd489a8d/resource.tar.gz#test.test_agg_apply-sum_type_empty-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-agg_list_null-default.txt-Debug]": [ + { + "checksum": "0f7d80f323572c8e5e9d0337cd6ebefa", + "size": 1953, + "uri": "https://{canondata_backend}/1937001/95c56edd26256f0e187f99210a7c54325e7bdf95/resource.tar.gz#test.test_agg_phases-agg_list_null-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-agg_list_null-default.txt-Results]": [ + { + "checksum": "244ed89b85af16aa0d28d7d8536b631f", + "size": 5936, + "uri": "https://{canondata_backend}/1936947/ef836f8e93e487f52d635e45c17d7c5f55f8ed4a/resource.tar.gz#test.test_agg_phases-agg_list_null-default.txt-Results_/results.txt" + } + ], + "test.test[agg_phases-agg_list_opt-default.txt-Debug]": [ + { + "checksum": "df640cf8986c192353ddacfd8a6bb391", + "size": 2188, + "uri": "https://{canondata_backend}/1925842/5a619903505c23cf11eca55d19535114c4128061/resource.tar.gz#test.test_agg_phases-agg_list_opt-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-agg_list_opt-default.txt-Results]": [ + { + "checksum": "b9341db0ac522b4c1114bd6ed91c63e3", + "size": 7382, + "uri": "https://{canondata_backend}/1925842/5a619903505c23cf11eca55d19535114c4128061/resource.tar.gz#test.test_agg_phases-agg_list_opt-default.txt-Results_/results.txt" + } + ], + "test.test[agg_phases-some_null-default.txt-Debug]": [ + { + "checksum": "ec383e14a1a00a11eb654f3c0edd92c1", + "size": 1915, + "uri": "https://{canondata_backend}/1899731/83fbff6ba94332bd9d4011b2343de136af391dfb/resource.tar.gz#test.test_agg_phases-some_null-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-some_null-default.txt-Results]": [ + { + "checksum": "200ff2930124c82a55e22120feee5388", + "size": 5576, + "uri": "https://{canondata_backend}/1899731/83fbff6ba94332bd9d4011b2343de136af391dfb/resource.tar.gz#test.test_agg_phases-some_null-default.txt-Results_/results.txt" + } + ], "test.test[aggr_factory-multi_list_distinct_expr-default.txt-Debug]": [ { "checksum": "f26d567b7905ecc6e0021e9b8b949831", diff --git a/yql/essentials/tests/sql/minirun/part6/canondata/result.json b/yql/essentials/tests/sql/minirun/part6/canondata/result.json index a0761bd0bee..e0ad9324827 100644 --- a/yql/essentials/tests/sql/minirun/part6/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part6/canondata/result.json @@ -139,6 +139,20 @@ "uri": "https://{canondata_backend}/1936947/0ddea11997745cd059fbd894ec6ff75cd45330e7/resource.tar.gz#test.test_agg_apply-sum_unsigned-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-agg_list_n-default.txt-Debug]": [ + { + "checksum": "b3079acd25dd8715e1fac508de5238a1", + "size": 2312, + "uri": "https://{canondata_backend}/1775059/9cb2a0388bc5f0081370592bfdffbf85a6268f97/resource.tar.gz#test.test_agg_phases-agg_list_n-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-agg_list_n-default.txt-Results]": [ + { + "checksum": "54af7bf1109833cc0d2f8c2acd4f13d4", + "size": 7184, + "uri": "https://{canondata_backend}/1775059/9cb2a0388bc5f0081370592bfdffbf85a6268f97/resource.tar.gz#test.test_agg_phases-agg_list_n-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-count-default.txt-Debug]": [ { "checksum": "a9d6b939210fe1ecd71f8c572e7d5142", @@ -153,6 +167,20 @@ "uri": "https://{canondata_backend}/1784117/5d18b9f51fee850ccb082661b6f55e00760de053/resource.tar.gz#test.test_agg_phases-count-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-count_if_null-default.txt-Debug]": [ + { + "checksum": "5bc5ec5d156af3cadd67843e46d95d38", + "size": 1921, + "uri": "https://{canondata_backend}/212715/3b8bb7fa27460bee4cc403208280fda1cfce3844/resource.tar.gz#test.test_agg_phases-count_if_null-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-count_if_null-default.txt-Results]": [ + { + "checksum": "07d46492a2938b43c78afad77c37c602", + "size": 5864, + "uri": "https://{canondata_backend}/212715/3b8bb7fa27460bee4cc403208280fda1cfce3844/resource.tar.gz#test.test_agg_phases-count_if_null-default.txt-Results_/results.txt" + } + ], "test.test[aggr_factory-def_value_full_table-default.txt-Debug]": [ { "checksum": "d8e3f6ad500976b415fa40954adeea71", diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/result.json b/yql/essentials/tests/sql/minirun/part7/canondata/result.json index 3193b7099c1..41bea8da200 100644 --- a/yql/essentials/tests/sql/minirun/part7/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part7/canondata/result.json @@ -69,6 +69,20 @@ "uri": "https://{canondata_backend}/1781765/743cf0e75e32b2f5c69c058edeac66f0effcf488/resource.tar.gz#test.test_agg_apply-sum_type-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-avg_if_opt-default.txt-Debug]": [ + { + "checksum": "6ed0552fdb6eda8eb5ac9443a91a6d9e", + "size": 2907, + "uri": "https://{canondata_backend}/1942100/793c017a998f1cd43e8a7796b3e083d0ed5b7640/resource.tar.gz#test.test_agg_phases-avg_if_opt-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-avg_if_opt-default.txt-Results]": [ + { + "checksum": "ee42615d3bb7830efb31837a4c8c0f39", + "size": 8684, + "uri": "https://{canondata_backend}/1881367/62aa5cf1ea1a43653061ab4ada3045d69c970bab/resource.tar.gz#test.test_agg_phases-avg_if_opt-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-count_all_null-default.txt-Debug]": [ { "checksum": "2968249412aba52dbc021fc8ff7add80", @@ -83,6 +97,20 @@ "uri": "https://{canondata_backend}/1784117/0861081e9a27c636cbdf4a677c7d177df206a99e/resource.tar.gz#test.test_agg_phases-count_all_null-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-max_by_opt-default.txt-Debug]": [ + { + "checksum": "249e842d0665e26640681d8d0014d45a", + "size": 2670, + "uri": "https://{canondata_backend}/1871102/168b8cd1f3dd087ed10e7173b62fb7b67c732f61/resource.tar.gz#test.test_agg_phases-max_by_opt-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-max_by_opt-default.txt-Results]": [ + { + "checksum": "e7472d33cc90f1e00b912c3bffc517a7", + "size": 9407, + "uri": "https://{canondata_backend}/1871102/168b8cd1f3dd087ed10e7173b62fb7b67c732f61/resource.tar.gz#test.test_agg_phases-max_by_opt-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-max_null-default.txt-Debug]": [ { "checksum": "470e4e5ca70c6ca708b411f0b88ad8f8", @@ -97,6 +125,20 @@ "uri": "https://{canondata_backend}/1784117/0861081e9a27c636cbdf4a677c7d177df206a99e/resource.tar.gz#test.test_agg_phases-max_null-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-some_opt-default.txt-Debug]": [ + { + "checksum": "f3bff7c9525569a43ea31c8eb08f2b54", + "size": 2021, + "uri": "https://{canondata_backend}/995452/f652f799c526a3fceb19f0a13148141f38387833/resource.tar.gz#test.test_agg_phases-some_opt-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-some_opt-default.txt-Results]": [ + { + "checksum": "8a6ecadcd5c25c8e16d5164a4bcf6926", + "size": 7010, + "uri": "https://{canondata_backend}/995452/f652f799c526a3fceb19f0a13148141f38387833/resource.tar.gz#test.test_agg_phases-some_opt-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-sum_null-default.txt-Debug]": [ { "checksum": "cb771596ea01cc71c92f816c70706b2c", diff --git a/yql/essentials/tests/sql/minirun/part8/canondata/result.json b/yql/essentials/tests/sql/minirun/part8/canondata/result.json index b1dbed84c07..c3acfb92b93 100644 --- a/yql/essentials/tests/sql/minirun/part8/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part8/canondata/result.json @@ -153,6 +153,20 @@ "uri": "https://{canondata_backend}/1903280/3699ae4c92af7f4904493b5c372c9966ecfea9cc/resource.tar.gz#test.test_agg_apply-sum_type_group_by-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-agg_list_n_null-default.txt-Debug]": [ + { + "checksum": "0f7d80f323572c8e5e9d0337cd6ebefa", + "size": 1953, + "uri": "https://{canondata_backend}/1775059/1a03de67ea83d8e1a68ef0d18e7c006bc9d9165e/resource.tar.gz#test.test_agg_phases-agg_list_n_null-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-agg_list_n_null-default.txt-Results]": [ + { + "checksum": "244ed89b85af16aa0d28d7d8536b631f", + "size": 5936, + "uri": "https://{canondata_backend}/1775059/1a03de67ea83d8e1a68ef0d18e7c006bc9d9165e/resource.tar.gz#test.test_agg_phases-agg_list_n_null-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases-count_opt-default.txt-Debug]": [ { "checksum": "fa0346f0a27ba3c37e3ec024837bda1d", @@ -167,6 +181,20 @@ "uri": "https://{canondata_backend}/1784117/7d6bbbf5f8e161e9f0d062dde40c8534c2579f6f/resource.tar.gz#test.test_agg_phases-count_opt-default.txt-Results_/results.txt" } ], + "test.test[agg_phases-max_by-default.txt-Debug]": [ + { + "checksum": "8c8770c921e80fd55e586204df457fb6", + "size": 2302, + "uri": "https://{canondata_backend}/1936947/654dad2a3a65d8b8a14b753d29d2216416e09d55/resource.tar.gz#test.test_agg_phases-max_by-default.txt-Debug_/opt.yql" + } + ], + "test.test[agg_phases-max_by-default.txt-Results]": [ + { + "checksum": "1567f1d1e5f8bbc5c25805f82db3c814", + "size": 7379, + "uri": "https://{canondata_backend}/1936947/654dad2a3a65d8b8a14b753d29d2216416e09d55/resource.tar.gz#test.test_agg_phases-max_by-default.txt-Results_/results.txt" + } + ], "test.test[agg_phases_agg_apply-avg_decimal-default.txt-Debug]": [ { "checksum": "94de780eb9e6c3880954d2c7e4c05ef4", diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json index 048eb93d269..3ea2ecd4444 100644 --- a/yql/essentials/tests/sql/sql2yql/canondata/result.json +++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json @@ -531,6 +531,48 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_agg_apply-sum_unsigned_/sql.yql" } ], + "test_sql2yql.test[agg_phases-agg_list]": [ + { + "checksum": "5a2e3b3ea9209bb82e8333c62761fa35", + "size": 13372, + "uri": "https://{canondata_backend}/1775059/bd804acd0131e67ad1ead7e1f72acf705fb71421/resource.tar.gz#test_sql2yql.test_agg_phases-agg_list_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-agg_list_n]": [ + { + "checksum": "c94b7b44d4a98ba3baf17d952f9d67cb", + "size": 13378, + "uri": "https://{canondata_backend}/1775059/bd804acd0131e67ad1ead7e1f72acf705fb71421/resource.tar.gz#test_sql2yql.test_agg_phases-agg_list_n_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-agg_list_n_null]": [ + { + "checksum": "92008dd229babe70bccceec3ec77bd21", + "size": 13360, + "uri": "https://{canondata_backend}/1775059/bd804acd0131e67ad1ead7e1f72acf705fb71421/resource.tar.gz#test_sql2yql.test_agg_phases-agg_list_n_null_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-agg_list_n_opt]": [ + { + "checksum": "ab03eb2e17f7b3c28e88103b0c17d70f", + "size": 13399, + "uri": "https://{canondata_backend}/1775059/bd804acd0131e67ad1ead7e1f72acf705fb71421/resource.tar.gz#test_sql2yql.test_agg_phases-agg_list_n_opt_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-agg_list_null]": [ + { + "checksum": "360b6846a9f0b48cc1efeb1f66efc62a", + "size": 13354, + "uri": "https://{canondata_backend}/1775059/bd804acd0131e67ad1ead7e1f72acf705fb71421/resource.tar.gz#test_sql2yql.test_agg_phases-agg_list_null_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-agg_list_opt]": [ + { + "checksum": "1eb62090f643161e8a5534dc3e878a76", + "size": 13393, + "uri": "https://{canondata_backend}/1775059/bd804acd0131e67ad1ead7e1f72acf705fb71421/resource.tar.gz#test_sql2yql.test_agg_phases-agg_list_opt_/sql.yql" + } + ], "test_sql2yql.test[agg_phases-avg]": [ { "checksum": "fadb025244930b729914db4b0a447150", @@ -538,6 +580,20 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_agg_phases-avg_/sql.yql" } ], + "test_sql2yql.test[agg_phases-avg_if]": [ + { + "checksum": "dba99c7dfb664aa35fc85ab872d64ae2", + "size": 13431, + "uri": "https://{canondata_backend}/1600758/63d44d6cbb74eb8b5ad513d2885906c449ac1582/resource.tar.gz#test_sql2yql.test_agg_phases-avg_if_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-avg_if_opt]": [ + { + "checksum": "8e9c56466d21371e037916827326adfd", + "size": 13463, + "uri": "https://{canondata_backend}/1600758/63d44d6cbb74eb8b5ad513d2885906c449ac1582/resource.tar.gz#test_sql2yql.test_agg_phases-avg_if_opt_/sql.yql" + } + ], "test_sql2yql.test[agg_phases-avg_null]": [ { "checksum": "4aa8f1b4154d8c2dc92d9840b8356c89", @@ -580,6 +636,27 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_agg_phases-count_all_opt_/sql.yql" } ], + "test_sql2yql.test[agg_phases-count_if]": [ + { + "checksum": "71984c23ca16eaf8f32692f6038ffec8", + "size": 13378, + "uri": "https://{canondata_backend}/1900335/ec13744d8c6e27f0425f190d4365746ac71f3cc1/resource.tar.gz#test_sql2yql.test_agg_phases-count_if_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-count_if_null]": [ + { + "checksum": "ac60f934b485ee38127360334df2aae4", + "size": 13366, + "uri": "https://{canondata_backend}/1937429/3e996c23e6c9027a7f8c7e6b6f2423f1e88d9750/resource.tar.gz#test_sql2yql.test_agg_phases-count_if_null_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-count_if_opt]": [ + { + "checksum": "71a3ca73ee66739447dc978230a1d2af", + "size": 13392, + "uri": "https://{canondata_backend}/1937429/3e996c23e6c9027a7f8c7e6b6f2423f1e88d9750/resource.tar.gz#test_sql2yql.test_agg_phases-count_if_opt_/sql.yql" + } + ], "test_sql2yql.test[agg_phases-count_null]": [ { "checksum": "b44195317b4a8b88e70654769f9356d8", @@ -608,6 +685,27 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_agg_phases-max_/sql.yql" } ], + "test_sql2yql.test[agg_phases-max_by]": [ + { + "checksum": "b65607ecb99583be4b64fdd59c8cab87", + "size": 13297, + "uri": "https://{canondata_backend}/1942278/906531cc1a6d0f9f2baf6a9d053464bd56ddd3eb/resource.tar.gz#test_sql2yql.test_agg_phases-max_by_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-max_by_null]": [ + { + "checksum": "e3722f394d85934693b4a4247cb63f7d", + "size": 13285, + "uri": "https://{canondata_backend}/1942278/906531cc1a6d0f9f2baf6a9d053464bd56ddd3eb/resource.tar.gz#test_sql2yql.test_agg_phases-max_by_null_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-max_by_opt]": [ + { + "checksum": "a9220b98f384f26e04c3b89e39ec5947", + "size": 13311, + "uri": "https://{canondata_backend}/1942278/906531cc1a6d0f9f2baf6a9d053464bd56ddd3eb/resource.tar.gz#test_sql2yql.test_agg_phases-max_by_opt_/sql.yql" + } + ], "test_sql2yql.test[agg_phases-max_null]": [ { "checksum": "afce8a91e4a80d63e3d80167340d20b0", @@ -685,6 +783,27 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_agg_phases-percentile_opt_/sql.yql" } ], + "test_sql2yql.test[agg_phases-some]": [ + { + "checksum": "6f84538f06382cf0025c48eefa0a429a", + "size": 13226, + "uri": "https://{canondata_backend}/1899731/6b35057795747271d5f6eb1be9429e1c471f2970/resource.tar.gz#test_sql2yql.test_agg_phases-some_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-some_null]": [ + { + "checksum": "b4ac9efcc74bf2a086139baadb2fda12", + "size": 13214, + "uri": "https://{canondata_backend}/1899731/6b35057795747271d5f6eb1be9429e1c471f2970/resource.tar.gz#test_sql2yql.test_agg_phases-some_null_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-some_opt]": [ + { + "checksum": "9f8c94137271af6987490a30243e0da6", + "size": 13240, + "uri": "https://{canondata_backend}/1899731/6b35057795747271d5f6eb1be9429e1c471f2970/resource.tar.gz#test_sql2yql.test_agg_phases-some_opt_/sql.yql" + } + ], "test_sql2yql.test[agg_phases-sum]": [ { "checksum": "32589adddbe16bbffae5bc79e4aad401", @@ -692,6 +811,20 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_agg_phases-sum_/sql.yql" } ], + "test_sql2yql.test[agg_phases-sum_if]": [ + { + "checksum": "696de7acdb24ee69eb2c4daa77cb250e", + "size": 13431, + "uri": "https://{canondata_backend}/1871182/c526585503e968379728935f1d8ba23a2b34f7ef/resource.tar.gz#test_sql2yql.test_agg_phases-sum_if_/sql.yql" + } + ], + "test_sql2yql.test[agg_phases-sum_if_opt]": [ + { + "checksum": "17823eecb08d6b4d0ac42de270e98652", + "size": 13445, + "uri": "https://{canondata_backend}/1871182/c526585503e968379728935f1d8ba23a2b34f7ef/resource.tar.gz#test_sql2yql.test_agg_phases-sum_if_opt_/sql.yql" + } + ], "test_sql2yql.test[agg_phases-sum_null]": [ { "checksum": "7d09fd0caa092bbfda07996785ae4a94", @@ -8632,11 +8765,51 @@ "uri": "file://test_sql_format.test_agg_apply-sum_unsigned_/formatted.sql" } ], + "test_sql_format.test[agg_phases-agg_list]": [ + { + "uri": "file://test_sql_format.test_agg_phases-agg_list_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-agg_list_n]": [ + { + "uri": "file://test_sql_format.test_agg_phases-agg_list_n_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-agg_list_n_null]": [ + { + "uri": "file://test_sql_format.test_agg_phases-agg_list_n_null_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-agg_list_n_opt]": [ + { + "uri": "file://test_sql_format.test_agg_phases-agg_list_n_opt_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-agg_list_null]": [ + { + "uri": "file://test_sql_format.test_agg_phases-agg_list_null_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-agg_list_opt]": [ + { + "uri": "file://test_sql_format.test_agg_phases-agg_list_opt_/formatted.sql" + } + ], "test_sql_format.test[agg_phases-avg]": [ { "uri": "file://test_sql_format.test_agg_phases-avg_/formatted.sql" } ], + "test_sql_format.test[agg_phases-avg_if]": [ + { + "uri": "file://test_sql_format.test_agg_phases-avg_if_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-avg_if_opt]": [ + { + "uri": "file://test_sql_format.test_agg_phases-avg_if_opt_/formatted.sql" + } + ], "test_sql_format.test[agg_phases-avg_null]": [ { "uri": "file://test_sql_format.test_agg_phases-avg_null_/formatted.sql" @@ -8667,6 +8840,21 @@ "uri": "file://test_sql_format.test_agg_phases-count_all_opt_/formatted.sql" } ], + "test_sql_format.test[agg_phases-count_if]": [ + { + "uri": "file://test_sql_format.test_agg_phases-count_if_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-count_if_null]": [ + { + "uri": "file://test_sql_format.test_agg_phases-count_if_null_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-count_if_opt]": [ + { + "uri": "file://test_sql_format.test_agg_phases-count_if_opt_/formatted.sql" + } + ], "test_sql_format.test[agg_phases-count_null]": [ { "uri": "file://test_sql_format.test_agg_phases-count_null_/formatted.sql" @@ -8687,6 +8875,21 @@ "uri": "file://test_sql_format.test_agg_phases-max_/formatted.sql" } ], + "test_sql_format.test[agg_phases-max_by]": [ + { + "uri": "file://test_sql_format.test_agg_phases-max_by_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-max_by_null]": [ + { + "uri": "file://test_sql_format.test_agg_phases-max_by_null_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-max_by_opt]": [ + { + "uri": "file://test_sql_format.test_agg_phases-max_by_opt_/formatted.sql" + } + ], "test_sql_format.test[agg_phases-max_null]": [ { "uri": "file://test_sql_format.test_agg_phases-max_null_/formatted.sql" @@ -8742,11 +8945,36 @@ "uri": "file://test_sql_format.test_agg_phases-percentile_opt_/formatted.sql" } ], + "test_sql_format.test[agg_phases-some]": [ + { + "uri": "file://test_sql_format.test_agg_phases-some_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-some_null]": [ + { + "uri": "file://test_sql_format.test_agg_phases-some_null_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-some_opt]": [ + { + "uri": "file://test_sql_format.test_agg_phases-some_opt_/formatted.sql" + } + ], "test_sql_format.test[agg_phases-sum]": [ { "uri": "file://test_sql_format.test_agg_phases-sum_/formatted.sql" } ], + "test_sql_format.test[agg_phases-sum_if]": [ + { + "uri": "file://test_sql_format.test_agg_phases-sum_if_/formatted.sql" + } + ], + "test_sql_format.test[agg_phases-sum_if_opt]": [ + { + "uri": "file://test_sql_format.test_agg_phases-sum_if_opt_/formatted.sql" + } + ], "test_sql_format.test[agg_phases-sum_null]": [ { "uri": "file://test_sql_format.test_agg_phases-sum_null_/formatted.sql" diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_/formatted.sql new file mode 100644 index 00000000000..097788a2f00 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: 2|>, <|key: 1, value: 3|>, <|key: 1, value: 4|>]) +); + +$p = ( + SELECT + key, + AggList(value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + AggList(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + AggList(value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_/formatted.sql new file mode 100644 index 00000000000..86f5f8351ca --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: 2|>, <|key: 1, value: 3|>, <|key: 1, value: 4|>]) +); + +$p = ( + SELECT + key, + AggList(value, 2) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + AggList(a, 2) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + AggList(value, 2) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_null_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_null_/formatted.sql new file mode 100644 index 00000000000..23ed714b101 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_null_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: NULL|>, <|key: 1, value: NULL|>, <|key: 1, value: NULL|>]) +); + +$p = ( + SELECT + key, + AggList(value, 2) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + AggList(a, 2) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + AggList(value, 2) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_opt_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_opt_/formatted.sql new file mode 100644 index 00000000000..90a7e25a3fe --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_n_opt_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: just(2)|>, <|key: 1, value: just(3)|>, <|key: 1, value: just(4)|>]) +); + +$p = ( + SELECT + key, + AggList(value, 2) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + AggList(a, 2) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + AggList(a, 2) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + AggList(value, 2) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_null_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_null_/formatted.sql new file mode 100644 index 00000000000..391abe95f1e --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_null_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: NULL|>, <|key: 1, value: NULL|>, <|key: 1, value: NULL|>]) +); + +$p = ( + SELECT + key, + AggList(value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + AggList(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + AggList(value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_opt_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_opt_/formatted.sql new file mode 100644 index 00000000000..ec0e03a2f60 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-agg_list_opt_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: just(2)|>, <|key: 1, value: just(3)|>, <|key: 1, value: just(4)|>]) +); + +$p = ( + SELECT + key, + AggList(value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + AggList(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + AggList(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + AggList(value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-avg_if_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-avg_if_/formatted.sql new file mode 100644 index 00000000000..cbfb0f1197e --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-avg_if_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: 2|>, <|key: 1, value: 3|>]) +); + +$p = ( + SELECT + key, + AvgIf(value, value % 2 == 0) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AvgIf(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AvgIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + AvgIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + AvgIf(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + AvgIf(value, value % 2 == 0) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-avg_if_opt_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-avg_if_opt_/formatted.sql new file mode 100644 index 00000000000..ed90fdb4e16 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-avg_if_opt_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: Just(2)|>, <|key: 1, value: Just(3)|>]) +); + +$p = ( + SELECT + key, + AvgIf(value, Unwrap(value) % 2 == 0) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AvgIf(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + AvgIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + AvgIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + AvgIf(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + AvgIf(value, Unwrap(value) % 2 == 0) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_/formatted.sql new file mode 100644 index 00000000000..6a6218d23db --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: 2|>, <|key: 1, value: 3|>]) +); + +$p = ( + SELECT + key, + CountIf(value % 2 == 1) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + CountIf(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + CountIf(value % 2 == 1) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_null_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_null_/formatted.sql new file mode 100644 index 00000000000..bdef136e861 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_null_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: NULL|>, <|key: 1, value: NULL|>]) +); + +$p = ( + SELECT + key, + CountIf(value % 2 == 1) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + CountIf(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + CountIf(value % 2 == 1) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_opt_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_opt_/formatted.sql new file mode 100644 index 00000000000..570708377c7 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-count_if_opt_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: just(2)|>, <|key: 1, value: just(3)|>]) +); + +$p = ( + SELECT + key, + CountIf(value % 2 == 1) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + CountIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + CountIf(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + CountIf(value % 2 == 1) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_/formatted.sql new file mode 100644 index 00000000000..e8f06eb61de --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_/formatted.sql @@ -0,0 +1,145 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: 2|>, <|key: 1, value: 3|>]) +); + +$p = ( + SELECT + key, + max_by(value, value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +--select FormatType(TypeOf($p1)); +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + max_by(a, a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +--select FormatType(TypeOf($p2)); +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + max_by(value, value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_null_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_null_/formatted.sql new file mode 100644 index 00000000000..5b63628e835 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_null_/formatted.sql @@ -0,0 +1,145 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: NULL|>, <|key: 1, value: NULL|>]) +); + +$p = ( + SELECT + key, + max_by(value, value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +--select FormatType(TypeOf($p1)); +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + max_by(a, a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +--select FormatType(TypeOf($p2)); +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + max_by(value, value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_opt_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_opt_/formatted.sql new file mode 100644 index 00000000000..293091fb63b --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-max_by_opt_/formatted.sql @@ -0,0 +1,145 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: just(2)|>, <|key: 1, value: just(3)|>]) +); + +$p = ( + SELECT + key, + max_by(value, value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + max_by(a, a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +--select FormatType(TypeOf($p1)); +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + max_by(a, a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +--select FormatType(TypeOf($p2)); +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + max_by(value, value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +--select FormatType(TypeOf($p)); +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_/formatted.sql new file mode 100644 index 00000000000..5548c760c5a --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: 2|>, <|key: 1, value: 3|>]) +); + +$p = ( + SELECT + key, + Some(value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + Some(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + Some(value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_null_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_null_/formatted.sql new file mode 100644 index 00000000000..c6b0ba05cc5 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_null_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: NULL|>, <|key: 1, value: NULL|>]) +); + +$p = ( + SELECT + key, + Some(value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + Some(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + Some(value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_opt_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_opt_/formatted.sql new file mode 100644 index 00000000000..74683ead73f --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-some_opt_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: just(2)|>, <|key: 1, value: just(3)|>]) +); + +$p = ( + SELECT + key, + Some(value) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + Some(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + Some(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + Some(value) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-sum_if_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-sum_if_/formatted.sql new file mode 100644 index 00000000000..efd768b7212 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-sum_if_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: 2|>, <|key: 1, value: 3|>]) +); + +$p = ( + SELECT + key, + SumIf(value, value % 2 == 0) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + SumIf(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + SumIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + SumIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + SumIf(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + SumIf(value, value % 2 == 0) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-sum_if_opt_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-sum_if_opt_/formatted.sql new file mode 100644 index 00000000000..cb4cb3221dd --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_agg_phases-sum_if_opt_/formatted.sql @@ -0,0 +1,139 @@ +/* syntax version 1 */ +/* postgres can not */ +$t = ( + SELECT + * + FROM + AS_TABLE([<|key: 1, value: just(2)|>, <|key: 1, value: just(3)|>]) +); + +$p = ( + SELECT + key, + SumIf(value, value % 2 == 0) AS a + FROM + $t + GROUP BY + key + WITH combine +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + SumIf(a) AS a + FROM + $p + GROUP BY + key + WITH combinestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p = ( + SELECT + key, + SumIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergestate +); + +$p = ( + PROCESS $p +); + +SELECT + * +FROM + $p +; + +$p1 = ( + SELECT + key, + SumIf(a) AS a + FROM + $p + GROUP BY + key + WITH mergefinalize +); + +$p1 = ( + PROCESS $p1 +); + +SELECT + * +FROM + $p1 +; + +$p2 = ( + SELECT + key, + SumIf(a) AS a + FROM ( + SELECT + key, + just(a) AS a + FROM + $p + ) + GROUP BY + key + WITH mergemanyfinalize +); + +$p2 = ( + PROCESS $p2 +); + +SELECT + * +FROM + $p2 +; + +$p3 = ( + SELECT + key, + SumIf(value, value % 2 == 0) AS a + FROM + $t + GROUP BY + key + WITH finalize +); + +$p3 = ( + PROCESS $p3 +); + +SELECT + * +FROM + $p3 +; diff --git a/yql/essentials/tests/sql/suites/agg_phases/agg_list.yql b/yql/essentials/tests/sql/suites/agg_phases/agg_list.yql new file mode 100644 index 00000000000..5344d9b9e59 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/agg_list.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: 2|>,<|key: 1, value: 3|>, <|key: 1, value: 4|>]); + +$p = +SELECT + key,AggList(value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,AggList(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,AggList(value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/agg_list_n.yql b/yql/essentials/tests/sql/suites/agg_phases/agg_list_n.yql new file mode 100644 index 00000000000..2acc54ddad7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/agg_list_n.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: 2|>,<|key: 1, value: 3|>, <|key: 1, value: 4|>]); + +$p = +SELECT + key,AggList(value, 2) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,AggList(a, 2) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,AggList(value, 2) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/agg_list_n_null.yql b/yql/essentials/tests/sql/suites/agg_phases/agg_list_n_null.yql new file mode 100644 index 00000000000..d2c42ccecb6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/agg_list_n_null.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: null|>,<|key: 1, value: null|>, <|key: 1, value: null|>]); + +$p = +SELECT + key,AggList(value, 2) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,AggList(a, 2) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,AggList(value, 2) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/agg_list_n_opt.yql b/yql/essentials/tests/sql/suites/agg_phases/agg_list_n_opt.yql new file mode 100644 index 00000000000..4e184d0f84b --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/agg_list_n_opt.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: just(2)|>,<|key: 1, value: just(3)|>, <|key: 1, value: just(4)|>]); + +$p = +SELECT + key,AggList(value, 2) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,AggList(a, 2) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,AggList(a, 2) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,AggList(value, 2) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/agg_list_null.yql b/yql/essentials/tests/sql/suites/agg_phases/agg_list_null.yql new file mode 100644 index 00000000000..44b6e13440d --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/agg_list_null.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: null|>,<|key: 1, value: null|>, <|key: 1, value: null|>]); + +$p = +SELECT + key,AggList(value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,AggList(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,AggList(value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/agg_list_opt.yql b/yql/essentials/tests/sql/suites/agg_phases/agg_list_opt.yql new file mode 100644 index 00000000000..c2f512a7ae3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/agg_list_opt.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: just(2)|>,<|key: 1, value: just(3)|>, <|key: 1, value: just(4)|>]); + +$p = +SELECT + key,AggList(value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,AggList(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,AggList(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,AggList(value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/avg_if.yql b/yql/essentials/tests/sql/suites/agg_phases/avg_if.yql new file mode 100644 index 00000000000..3eb18c115bf --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/avg_if.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: 2|>,<|key: 1, value: 3|>]); + +$p = +SELECT + key,AvgIf(value, value % 2 == 0) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AvgIf(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AvgIf(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,AvgIf(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,AvgIf(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,AvgIf(value, value % 2 == 0) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/avg_if_opt.yql b/yql/essentials/tests/sql/suites/agg_phases/avg_if_opt.yql new file mode 100644 index 00000000000..68450ab5c09 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/avg_if_opt.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: Just(2)|>,<|key: 1, value: Just(3)|>]); + +$p = +SELECT + key,AvgIf(value, Unwrap(value) % 2 == 0) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AvgIf(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,AvgIf(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,AvgIf(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,AvgIf(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,AvgIf(value, Unwrap(value) % 2 == 0) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/count_if.yql b/yql/essentials/tests/sql/suites/agg_phases/count_if.yql new file mode 100644 index 00000000000..0691bdeb5a1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/count_if.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: 2|>,<|key: 1, value: 3|>]); + +$p = +SELECT + key,CountIf(value % 2 == 1) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,CountIf(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,CountIf(value % 2 == 1) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/count_if_null.yql b/yql/essentials/tests/sql/suites/agg_phases/count_if_null.yql new file mode 100644 index 00000000000..cf298fc505d --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/count_if_null.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: null|>,<|key: 1, value: NULL|>]); + +$p = +SELECT + key,CountIf(value % 2 == 1) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,CountIf(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,CountIf(value % 2 == 1) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/count_if_opt.yql b/yql/essentials/tests/sql/suites/agg_phases/count_if_opt.yql new file mode 100644 index 00000000000..0679b5c1eab --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/count_if_opt.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: just(2)|>,<|key: 1, value: just(3)|>]); + +$p = +SELECT + key,CountIf(value % 2 == 1) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,CountIf(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,CountIf(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,CountIf(value % 2 == 1) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/max_by.yql b/yql/essentials/tests/sql/suites/agg_phases/max_by.yql new file mode 100644 index 00000000000..a236889d257 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/max_by.yql @@ -0,0 +1,80 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: 2|>,<|key: 1, value: 3|>]); + +$p = +SELECT + key,max_by(value,value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + +$p = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + +$p = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + + +$p1 = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +--select FormatType(TypeOf($p1)); +select * from $p1; + +$p2 = SELECT + key,max_by(a,a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +--select FormatType(TypeOf($p2)); +select * from $p2; + +$p3 = +SELECT + key,max_by(value,value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +--select FormatType(TypeOf($p)); +select * from $p3; + diff --git a/yql/essentials/tests/sql/suites/agg_phases/max_by_null.yql b/yql/essentials/tests/sql/suites/agg_phases/max_by_null.yql new file mode 100644 index 00000000000..2ac870035ef --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/max_by_null.yql @@ -0,0 +1,80 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: null|>,<|key: 1, value: null|>]); + +$p = +SELECT + key,max_by(value,value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + +$p = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + +$p = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + + +$p1 = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +--select FormatType(TypeOf($p1)); +select * from $p1; + +$p2 = SELECT + key,max_by(a,a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +--select FormatType(TypeOf($p2)); +select * from $p2; + +$p3 = +SELECT + key,max_by(value,value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +--select FormatType(TypeOf($p)); +select * from $p3; + diff --git a/yql/essentials/tests/sql/suites/agg_phases/max_by_opt.yql b/yql/essentials/tests/sql/suites/agg_phases/max_by_opt.yql new file mode 100644 index 00000000000..92ed4deb37d --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/max_by_opt.yql @@ -0,0 +1,80 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: just(2)|>,<|key: 1, value: just(3)|>]); + +$p = +SELECT + key,max_by(value,value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + +$p = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + +$p = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +--select FormatType(TypeOf($p)); +select * from $p; + + +$p1 = SELECT + key,max_by(a,a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +--select FormatType(TypeOf($p1)); +select * from $p1; + +$p2 = SELECT + key,max_by(a,a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +--select FormatType(TypeOf($p2)); +select * from $p2; + +$p3 = +SELECT + key,max_by(value,value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +--select FormatType(TypeOf($p)); +select * from $p3; + diff --git a/yql/essentials/tests/sql/suites/agg_phases/some.yql b/yql/essentials/tests/sql/suites/agg_phases/some.yql new file mode 100644 index 00000000000..b39bee1334f --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/some.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: 2|>,<|key: 1, value: 3|>]); + +$p = +SELECT + key,Some(value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,Some(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,Some(value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/some_null.yql b/yql/essentials/tests/sql/suites/agg_phases/some_null.yql new file mode 100644 index 00000000000..e5dc21217a2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/some_null.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: null|>,<|key: 1, value: null|>]); + +$p = +SELECT + key,Some(value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,Some(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,Some(value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; diff --git a/yql/essentials/tests/sql/suites/agg_phases/some_opt.yql b/yql/essentials/tests/sql/suites/agg_phases/some_opt.yql new file mode 100644 index 00000000000..77743a1c526 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/some_opt.yql @@ -0,0 +1,74 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: just(2)|>,<|key: 1, value: just(3)|>]); + +$p = +SELECT + key,Some(value) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,Some(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,Some(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,Some(value) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; + diff --git a/yql/essentials/tests/sql/suites/agg_phases/sum_if.yql b/yql/essentials/tests/sql/suites/agg_phases/sum_if.yql new file mode 100644 index 00000000000..24ecf50713c --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/sum_if.yql @@ -0,0 +1,74 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: 2|>,<|key: 1, value: 3|>]); + +$p = +SELECT + key,SumIf(value, value % 2 == 0) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,SumIf(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,SumIf(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,SumIf(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,SumIf(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,SumIf(value, value % 2 == 0) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; + diff --git a/yql/essentials/tests/sql/suites/agg_phases/sum_if_opt.yql b/yql/essentials/tests/sql/suites/agg_phases/sum_if_opt.yql new file mode 100644 index 00000000000..8464ce3f6d9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/agg_phases/sum_if_opt.yql @@ -0,0 +1,73 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t = SELECT * FROM AS_TABLE([<|key: 1, value: just(2)|>,<|key: 1, value: just(3)|>]); + +$p = +SELECT + key,SumIf(value, value % 2 == 0) as a +FROM $t +GROUP BY + key + with combine + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,SumIf(a) as a +FROM $p +GROUP BY + key + with combinestate + ; + +$p = PROCESS $p; +select * from $p; + +$p = SELECT + key,SumIf(a) as a +FROM $p +GROUP BY + key + with mergestate + ; + +$p = PROCESS $p; +select * from $p; + + +$p1 = SELECT + key,SumIf(a) as a +FROM $p +GROUP BY + key + with mergefinalize + ; + +$p1 = PROCESS $p1; +select * from $p1; + +$p2 = SELECT + key,SumIf(a) as a +FROM (select key, just(a) as a from $p) +GROUP BY + key + with mergemanyfinalize + ; + +$p2 = PROCESS $p2; +select * from $p2; + +$p3 = +SELECT + key,SumIf(value, value % 2 == 0) as a +FROM $t +GROUP BY + key + with finalize + ; + +$p3 = PROCESS $p3; +select * from $p3; |