[ASTERIXDB-2505][COMP] Consolidate window operators rule - user model changes: no - storage format changes: no - interface changes: no
Details: - Add optimizer rules that merge two window operators into one - Improve existing optimizer rules to handle window operators - Add tests for distinct aggregate in window operator Change-Id: I5d0739d604d09f5445237d4fa3d56a2af8126bd7 Reviewed-on: https://asterix-gerrit.ics.uci.edu/3107 Integration-Tests: Jenkins <[email protected]> Tested-by: Jenkins <[email protected]> Contrib: Jenkins <[email protected]> Reviewed-by: Ali Alsuliman <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/asterixdb/repo Commit: http://git-wip-us.apache.org/repos/asf/asterixdb/commit/faf9791d Tree: http://git-wip-us.apache.org/repos/asf/asterixdb/tree/faf9791d Diff: http://git-wip-us.apache.org/repos/asf/asterixdb/diff/faf9791d Branch: refs/heads/master Commit: faf9791d2f21831fa025031eb4358d651e7a719b Parents: 8efd847 Author: Dmitry Lychagin <[email protected]> Authored: Mon Jan 7 21:03:15 2019 -0800 Committer: Dmitry Lychagin <[email protected]> Committed: Tue Jan 8 17:15:43 2019 -0800 ---------------------------------------------------------------------- .../asterix/optimizer/base/RuleCollections.java | 6 + .../optimizer/rules/LoadRecordFieldsRule.java | 6 +- .../queries/window/win_misc/win_misc_01.sqlpp | 58 +++++++ .../queries/window/win_misc/win_misc_02.sqlpp | 39 +++++ .../window/win_opt_01/win_opt_01_1.sqlpp | 29 ++++ .../window/win_opt_01/win_opt_01_2.sqlpp | 29 ++++ .../window/win_opt_01/win_opt_01_3.sqlpp | 30 ++++ .../window/win_opt_01/win_opt_01_4.sqlpp | 31 ++++ .../window/win_opt_01/win_opt_01_5.sqlpp | 29 ++++ .../window/win_opt_01/win_opt_01_6.sqlpp | 30 ++++ .../window/win_opt_01/win_opt_01_7.sqlpp | 29 ++++ .../optimizerts/queries/window/window_01.sqlpp | 58 ------- .../optimizerts/queries/window/window_02.sqlpp | 39 ----- .../results/window/win_misc/win_misc_01.plan | 29 ++++ .../results/window/win_misc/win_misc_02.plan | 23 +++ .../results/window/win_opt_01/win_opt_01_1.plan | 11 ++ .../results/window/win_opt_01/win_opt_01_2.plan | 15 ++ .../results/window/win_opt_01/win_opt_01_3.plan | 15 ++ .../results/window/win_opt_01/win_opt_01_4.plan | 15 ++ .../results/window/win_opt_01/win_opt_01_5.plan | 11 ++ .../results/window/win_opt_01/win_opt_01_6.plan | 12 ++ .../results/window/win_opt_01/win_opt_01_7.plan | 17 ++ .../optimizerts/results/window/window_01.plan | 30 ---- .../optimizerts/results/window/window_02.plan | 23 --- .../window/misc_01/misc_01.7.query.sqlpp | 29 ++++ .../window/win_opt_01/win_opt_01.1.query.sqlpp | 29 ++++ .../window/win_opt_01/win_opt_01.2.query.sqlpp | 29 ++++ .../window/win_opt_01/win_opt_01.3.query.sqlpp | 30 ++++ .../window/win_opt_01/win_opt_01.4.query.sqlpp | 31 ++++ .../window/win_opt_01/win_opt_01.5.query.sqlpp | 29 ++++ .../window/win_opt_01/win_opt_01.6.query.sqlpp | 30 ++++ .../window/win_opt_01/win_opt_01.7.query.sqlpp | 29 ++++ .../results/window/misc_01/misc_01.7.adm | 20 +++ .../results/window/win_opt_01/win_opt_01.1.adm | 20 +++ .../results/window/win_opt_01/win_opt_01.2.adm | 20 +++ .../results/window/win_opt_01/win_opt_01.3.adm | 20 +++ .../results/window/win_opt_01/win_opt_01.4.adm | 20 +++ .../results/window/win_opt_01/win_opt_01.5.adm | 16 ++ .../results/window/win_opt_01/win_opt_01.6.adm | 16 ++ .../results/window/win_opt_01/win_opt_01.7.adm | 20 +++ .../window/misc_01/misc_01.7.ast | 58 +++++++ .../resources/runtimets/testsuite_sqlpp.xml | 5 + .../SqlppDistinctAggregationSugarVisitor.java | 43 +++-- .../visitor/VariableCheckAndRewriteVisitor.java | 1 + .../sqlpp/visitor/SqlppAstPrintVisitor.java | 4 +- .../asterix/om/functions/BuiltinFunctions.java | 4 +- .../operators/logical/WindowOperator.java | 6 +- .../visitors/IsomorphismOperatorVisitor.java | 60 ++++--- .../IsomorphismVariableMappingVisitor.java | 17 +- .../algebra/util/OperatorManipulationUtil.java | 22 +++ .../rules/ConsolidateWindowOperatorsRule.java | 155 +++++++++++++++++++ .../rules/InlineAssignIntoAggregateRule.java | 65 +++++--- .../rewriter/rules/InlineVariablesRule.java | 8 +- .../rules/ReuseWindowAggregateRule.java | 119 ++++++++++++++ 54 files changed, 1340 insertions(+), 229 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java index ece78f8..80469a7 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java @@ -94,6 +94,8 @@ import org.apache.hyracks.algebricks.rewriter.rules.BreakSelectIntoConjunctsRule import org.apache.hyracks.algebricks.rewriter.rules.ComplexUnnestToProductRule; import org.apache.hyracks.algebricks.rewriter.rules.ConsolidateAssignsRule; import org.apache.hyracks.algebricks.rewriter.rules.ConsolidateSelectsRule; +import org.apache.hyracks.algebricks.rewriter.rules.ReuseWindowAggregateRule; +import org.apache.hyracks.algebricks.rewriter.rules.ConsolidateWindowOperatorsRule; import org.apache.hyracks.algebricks.rewriter.rules.CopyLimitDownRule; import org.apache.hyracks.algebricks.rewriter.rules.EliminateGroupByEmptyKeyRule; import org.apache.hyracks.algebricks.rewriter.rules.EnforceOrderByAfterSubplan; @@ -291,6 +293,10 @@ public final class RuleCollections { //PushUnnestDownUnion => RemoveRedundantListifyRule cause these rules are correlated consolidation.add(new PushUnnestDownThroughUnionRule()); consolidation.add(new RemoveRedundantListifyRule()); + // Window operator consolidation rules + consolidation.add(new ConsolidateWindowOperatorsRule()); + consolidation.add(new ReuseWindowAggregateRule()); + consolidation.add(new RemoveRedundantVariablesRule()); return consolidation; } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/LoadRecordFieldsRule.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/LoadRecordFieldsRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/LoadRecordFieldsRule.java index 32fa744..4d61307 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/LoadRecordFieldsRule.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/LoadRecordFieldsRule.java @@ -321,10 +321,10 @@ public class LoadRecordFieldsRule implements IAlgebraicRewriteRule { } } else if (opChild.getOperatorTag() == LogicalOperatorTag.NESTEDTUPLESOURCE) { NestedTupleSourceOperator nts = (NestedTupleSourceOperator) opChild; - AbstractLogicalOperator opBelowNestedPlan = - (AbstractLogicalOperator) nts.getDataSourceReference().getValue().getInputs().get(0).getValue(); + AbstractLogicalOperator opWithNestedPlan = + (AbstractLogicalOperator) nts.getDataSourceReference().getValue(); ILogicalExpression expr1 = - findFieldExpression(opBelowNestedPlan, recordVar, accessKey, typeEnvironment, resolver); + findFieldExpression(opWithNestedPlan, recordVar, accessKey, typeEnvironment, resolver); if (expr1 != null) { return expr1; } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_misc/win_misc_01.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_misc/win_misc_01.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_misc/win_misc_01.sqlpp new file mode 100644 index 0000000..25613c6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_misc/win_misc_01.sqlpp @@ -0,0 +1,58 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test multiple window functions in a single statement + * Expected Res : SUCCESS + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type test.t1Type as open { + c1 : bigint +}; + +create dataset t1(t1Type) primary key c1; + +create function q1_mixed(P, N, D) { + from + t1, + range(t1.one, P) p, + range(t1.one, N) n, + range(t1.one, D) d + let + rank_result_expected = (N - n) * D + 1, + rank_result_actual = rank() over ( partition by t1.c2, p order by n desc ), + rank_result_delta = rank_result_expected - rank_result_actual, + + percent_rank_result_expected = (rank_result_expected - 1) / (N * D - 1), + percent_rank_result_actual = percent_rank() over ( partition by t1.c2, p order by n desc ), + percent_rank_result_delta_raw = percent_rank_result_expected - percent_rank_result_actual, + percent_rank_result_delta = case when percent_rank_result_delta_raw < 0.001 then 0 else percent_rank_result_delta_raw end + + select + min(rank_result_delta) rank_min_delta, + max(rank_result_delta) rank_max_delta, + min(percent_rank_result_delta) percent_rank_min_delta, + max(percent_rank_result_delta) percent_rank_max_delta +}; + +q1_mixed(2, 2, 2); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_misc/win_misc_02.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_misc/win_misc_02.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_misc/win_misc_02.sqlpp new file mode 100644 index 0000000..c7934e3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_misc/win_misc_02.sqlpp @@ -0,0 +1,39 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window function with frame definition + * Expected Res : SUCCESS + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type test.t1Type as open { + c1 : bigint +}; + +create dataset t1(t1Type) primary key c1; + +from t1 +select c1, + array_sum((from w select value w.t1.c1)) over w as + (partition by (c2 mod 2) order by c1 rows unbounded preceding exclude current row) as `sum` +order by c1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_1.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_1.sqlpp new file mode 100644 index 0000000..d53d092 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_1.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of two running aggregates + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, + row_number() over (partition by m order by t) as `row_nubmer`, + round_half_to_even(percent_rank() over (partition by m order by t), 2) as `percent_rank` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_2.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_2.sqlpp new file mode 100644 index 0000000..c403726 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_2.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of one running aggregate and one regular aggregate + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, + row_number() over (partition by m order by t) as `row_nubmer`, + sum(t) over (partition by m order by t) as `sum` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_3.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_3.sqlpp new file mode 100644 index 0000000..25d559d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_3.sqlpp @@ -0,0 +1,30 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of one running aggregate + * and one regular aggregate with complex argument + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, + row_number() over (partition by m order by t) as `row_nubmer`, + sum(t + m) over (partition by m order by t) as `sum` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_4.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_4.sqlpp new file mode 100644 index 0000000..06f5ac3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_4.sqlpp @@ -0,0 +1,31 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of one running aggregate and + * two regular aggregates with complex arguments + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, + row_number() over (partition by m order by t) as `row_nubmer`, + sum(t + m) over (partition by m order by t) as `sum`, + round_half_to_even(avg(t - m) over (partition by m order by t), 2) as `avg` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_5.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_5.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_5.sqlpp new file mode 100644 index 0000000..b6bfd66 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_5.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of two window functions in LET clauses + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, row_number, ntile +FROM range(1, 16) t +LET m = t % 4, + row_number = row_number() over (partition by m order by t), + ntile = ntile(2) over (partition by m order by t) +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_6.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_6.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_6.sqlpp new file mode 100644 index 0000000..0019ba8 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_6.sqlpp @@ -0,0 +1,30 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of data dependency between two window functions. + * No operator consolidation in this case. + * Expected Res : SUCCESS (two window operators in the optimized plan) + */ + +SELECT m, t, row_number, ntile +FROM range(1, 16) t +LET m = t % 4, + row_number = row_number() over (partition by m order by t), + ntile = ntile(row_number + 1) over (partition by m order by t) +ORDER BY m, t; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_7.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_7.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_7.sqlpp new file mode 100644 index 0000000..f139ab8 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/win_opt_01/win_opt_01_7.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of ROW frame mode + * Expected Res : SUCCESS (two window operators in the optimized plan) + */ + +SELECT m, t, + sum(t) over (partition by m order by t rows between unbounded preceding and current row) as `sum`, + round_half_to_even(avg(t) over (partition by m order by t rows between unbounded preceding and current row), 2) as `avg` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_01.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_01.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_01.sqlpp deleted file mode 100644 index 25613c6..0000000 --- a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_01.sqlpp +++ /dev/null @@ -1,58 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -/* - * Description : Test multiple window functions in a single statement - * Expected Res : SUCCESS - */ - -drop dataverse test if exists; -create dataverse test; - -use test; - -create type test.t1Type as open { - c1 : bigint -}; - -create dataset t1(t1Type) primary key c1; - -create function q1_mixed(P, N, D) { - from - t1, - range(t1.one, P) p, - range(t1.one, N) n, - range(t1.one, D) d - let - rank_result_expected = (N - n) * D + 1, - rank_result_actual = rank() over ( partition by t1.c2, p order by n desc ), - rank_result_delta = rank_result_expected - rank_result_actual, - - percent_rank_result_expected = (rank_result_expected - 1) / (N * D - 1), - percent_rank_result_actual = percent_rank() over ( partition by t1.c2, p order by n desc ), - percent_rank_result_delta_raw = percent_rank_result_expected - percent_rank_result_actual, - percent_rank_result_delta = case when percent_rank_result_delta_raw < 0.001 then 0 else percent_rank_result_delta_raw end - - select - min(rank_result_delta) rank_min_delta, - max(rank_result_delta) rank_max_delta, - min(percent_rank_result_delta) percent_rank_min_delta, - max(percent_rank_result_delta) percent_rank_max_delta -}; - -q1_mixed(2, 2, 2); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_02.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_02.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_02.sqlpp deleted file mode 100644 index c7934e3..0000000 --- a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_02.sqlpp +++ /dev/null @@ -1,39 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -/* - * Description : Test window function with frame definition - * Expected Res : SUCCESS - */ - -drop dataverse test if exists; -create dataverse test; - -use test; - -create type test.t1Type as open { - c1 : bigint -}; - -create dataset t1(t1Type) primary key c1; - -from t1 -select c1, - array_sum((from w select value w.t1.c1)) over w as - (partition by (c2 mod 2) order by c1 rows unbounded preceding exclude current row) as `sum` -order by c1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_misc/win_misc_01.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_misc/win_misc_01.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_misc/win_misc_01.plan new file mode 100644 index 0000000..8191e74 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_misc/win_misc_01.plan @@ -0,0 +1,29 @@ +-- DISTRIBUTE_RESULT |UNPARTITIONED| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- STREAM_PROJECT |UNPARTITIONED| + -- ASSIGN |UNPARTITIONED| + -- AGGREGATE |UNPARTITIONED| + -- RANDOM_MERGE_EXCHANGE |PARTITIONED| + -- AGGREGATE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- WINDOW |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STABLE_SORT [$$t1.c2(ASC), $$p(ASC), $$n(DESC)] |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$t1.c2, $$p] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- UNNEST |PARTITIONED| + -- UNNEST |PARTITIONED| + -- UNNEST |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_misc/win_misc_02.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_misc/win_misc_02.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_misc/win_misc_02.plan new file mode 100644 index 0000000..e452d03 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_misc/win_misc_02.plan @@ -0,0 +1,23 @@ +-- DISTRIBUTE_RESULT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- SORT_MERGE_EXCHANGE [$$48(ASC) ] |PARTITIONED| + -- STABLE_SORT [$$48(ASC)] |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- WINDOW |PARTITIONED| + { + -- AGGREGATE |LOCAL| + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- WINDOW |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STABLE_SORT [$$34(ASC), $$48(ASC)] |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$34] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_1.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_1.plan new file mode 100644 index 0000000..871ea8e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_1.plan @@ -0,0 +1,11 @@ +-- DISTRIBUTE_RESULT |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STREAM_PROJECT |LOCAL| + -- ASSIGN |LOCAL| + -- WINDOW |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STABLE_SORT [$$m(ASC), $$t(ASC)] |LOCAL| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- ASSIGN |UNPARTITIONED| + -- UNNEST |UNPARTITIONED| + -- EMPTY_TUPLE_SOURCE |UNPARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_2.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_2.plan new file mode 100644 index 0000000..a131487 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_2.plan @@ -0,0 +1,15 @@ +-- DISTRIBUTE_RESULT |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STREAM_PROJECT |LOCAL| + -- ASSIGN |LOCAL| + -- WINDOW |LOCAL| + { + -- AGGREGATE |LOCAL| + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STABLE_SORT [$$m(ASC), $$t(ASC)] |LOCAL| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- ASSIGN |UNPARTITIONED| + -- UNNEST |UNPARTITIONED| + -- EMPTY_TUPLE_SOURCE |UNPARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_3.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_3.plan new file mode 100644 index 0000000..a131487 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_3.plan @@ -0,0 +1,15 @@ +-- DISTRIBUTE_RESULT |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STREAM_PROJECT |LOCAL| + -- ASSIGN |LOCAL| + -- WINDOW |LOCAL| + { + -- AGGREGATE |LOCAL| + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STABLE_SORT [$$m(ASC), $$t(ASC)] |LOCAL| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- ASSIGN |UNPARTITIONED| + -- UNNEST |UNPARTITIONED| + -- EMPTY_TUPLE_SOURCE |UNPARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_4.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_4.plan new file mode 100644 index 0000000..a131487 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_4.plan @@ -0,0 +1,15 @@ +-- DISTRIBUTE_RESULT |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STREAM_PROJECT |LOCAL| + -- ASSIGN |LOCAL| + -- WINDOW |LOCAL| + { + -- AGGREGATE |LOCAL| + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STABLE_SORT [$$m(ASC), $$t(ASC)] |LOCAL| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- ASSIGN |UNPARTITIONED| + -- UNNEST |UNPARTITIONED| + -- EMPTY_TUPLE_SOURCE |UNPARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_5.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_5.plan new file mode 100644 index 0000000..871ea8e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_5.plan @@ -0,0 +1,11 @@ +-- DISTRIBUTE_RESULT |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STREAM_PROJECT |LOCAL| + -- ASSIGN |LOCAL| + -- WINDOW |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STABLE_SORT [$$m(ASC), $$t(ASC)] |LOCAL| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- ASSIGN |UNPARTITIONED| + -- UNNEST |UNPARTITIONED| + -- EMPTY_TUPLE_SOURCE |UNPARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_6.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_6.plan new file mode 100644 index 0000000..ab78ecc --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_6.plan @@ -0,0 +1,12 @@ +-- DISTRIBUTE_RESULT |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STREAM_PROJECT |LOCAL| + -- ASSIGN |LOCAL| + -- WINDOW |LOCAL| + -- WINDOW |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STABLE_SORT [$$m(ASC), $$t(ASC)] |LOCAL| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- ASSIGN |UNPARTITIONED| + -- UNNEST |UNPARTITIONED| + -- EMPTY_TUPLE_SOURCE |UNPARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_7.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_7.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_7.plan new file mode 100644 index 0000000..5b3d480 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/win_opt_01/win_opt_01_7.plan @@ -0,0 +1,17 @@ +-- DISTRIBUTE_RESULT |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STREAM_PROJECT |LOCAL| + -- ASSIGN |LOCAL| + -- STREAM_PROJECT |LOCAL| + -- WINDOW |LOCAL| + { + -- AGGREGATE |LOCAL| + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- WINDOW |LOCAL| + -- ONE_TO_ONE_EXCHANGE |LOCAL| + -- STABLE_SORT [$$m(ASC), $$t(ASC)] |LOCAL| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- ASSIGN |UNPARTITIONED| + -- UNNEST |UNPARTITIONED| + -- EMPTY_TUPLE_SOURCE |UNPARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_01.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_01.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_01.plan deleted file mode 100644 index 91e9cfc..0000000 --- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_01.plan +++ /dev/null @@ -1,30 +0,0 @@ --- DISTRIBUTE_RESULT |UNPARTITIONED| - -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| - -- STREAM_PROJECT |UNPARTITIONED| - -- ASSIGN |UNPARTITIONED| - -- AGGREGATE |UNPARTITIONED| - -- RANDOM_MERGE_EXCHANGE |PARTITIONED| - -- AGGREGATE |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- ASSIGN |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- ASSIGN |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- ASSIGN |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- WINDOW |PARTITIONED| - -- WINDOW |PARTITIONED| - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - -- STABLE_SORT [$$t1.c2(ASC), $$p(ASC), $$n(DESC)] |PARTITIONED| - -- HASH_PARTITION_EXCHANGE [$$t1.c2, $$p] |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- UNNEST |PARTITIONED| - -- UNNEST |PARTITIONED| - -- UNNEST |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- ASSIGN |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - -- DATASOURCE_SCAN |PARTITIONED| - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - -- EMPTY_TUPLE_SOURCE |PARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_02.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_02.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_02.plan deleted file mode 100644 index e452d03..0000000 --- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_02.plan +++ /dev/null @@ -1,23 +0,0 @@ --- DISTRIBUTE_RESULT |PARTITIONED| - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- ASSIGN |PARTITIONED| - -- SORT_MERGE_EXCHANGE [$$48(ASC) ] |PARTITIONED| - -- STABLE_SORT [$$48(ASC)] |PARTITIONED| - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- WINDOW |PARTITIONED| - { - -- AGGREGATE |LOCAL| - -- NESTED_TUPLE_SOURCE |LOCAL| - } - -- WINDOW |PARTITIONED| - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - -- STABLE_SORT [$$34(ASC), $$48(ASC)] |PARTITIONED| - -- HASH_PARTITION_EXCHANGE [$$34] |PARTITIONED| - -- STREAM_PROJECT |PARTITIONED| - -- ASSIGN |PARTITIONED| - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - -- DATASOURCE_SCAN |PARTITIONED| - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - -- EMPTY_TUPLE_SOURCE |PARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.7.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.7.query.sqlpp new file mode 100644 index 0000000..a04ab08 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.7.query.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window function distinct aggregate + * Expected Res : SUCCESS + */ + +SELECT m, t, + sum(t % 4) over (partition by m order by t rows between unbounded preceding and current row) as `sum`, + sum(distinct t % 4) over (partition by m order by t rows between unbounded preceding and current row) as `sum_distinct` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.1.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.1.query.sqlpp new file mode 100644 index 0000000..d53d092 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.1.query.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of two running aggregates + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, + row_number() over (partition by m order by t) as `row_nubmer`, + round_half_to_even(percent_rank() over (partition by m order by t), 2) as `percent_rank` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.2.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.2.query.sqlpp new file mode 100644 index 0000000..c403726 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.2.query.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of one running aggregate and one regular aggregate + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, + row_number() over (partition by m order by t) as `row_nubmer`, + sum(t) over (partition by m order by t) as `sum` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.3.query.sqlpp new file mode 100644 index 0000000..25d559d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.3.query.sqlpp @@ -0,0 +1,30 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of one running aggregate + * and one regular aggregate with complex argument + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, + row_number() over (partition by m order by t) as `row_nubmer`, + sum(t + m) over (partition by m order by t) as `sum` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.4.query.sqlpp new file mode 100644 index 0000000..06f5ac3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.4.query.sqlpp @@ -0,0 +1,31 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of one running aggregate and + * two regular aggregates with complex arguments + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, + row_number() over (partition by m order by t) as `row_nubmer`, + sum(t + m) over (partition by m order by t) as `sum`, + round_half_to_even(avg(t - m) over (partition by m order by t), 2) as `avg` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.5.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.5.query.sqlpp new file mode 100644 index 0000000..b6bfd66 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.5.query.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of two window functions in LET clauses + * Expected Res : SUCCESS (one window operator in the optimized plan) + */ + +SELECT m, t, row_number, ntile +FROM range(1, 16) t +LET m = t % 4, + row_number = row_number() over (partition by m order by t), + ntile = ntile(2) over (partition by m order by t) +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.6.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.6.query.sqlpp new file mode 100644 index 0000000..0019ba8 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.6.query.sqlpp @@ -0,0 +1,30 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of data dependency between two window functions. + * No operator consolidation in this case. + * Expected Res : SUCCESS (two window operators in the optimized plan) + */ + +SELECT m, t, row_number, ntile +FROM range(1, 16) t +LET m = t % 4, + row_number = row_number() over (partition by m order by t), + ntile = ntile(row_number + 1) over (partition by m order by t) +ORDER BY m, t; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.7.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.7.query.sqlpp new file mode 100644 index 0000000..f139ab8 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_opt_01/win_opt_01.7.query.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test window operator consolidation rule handling of ROW frame mode + * Expected Res : SUCCESS (two window operators in the optimized plan) + */ + +SELECT m, t, + sum(t) over (partition by m order by t rows between unbounded preceding and current row) as `sum`, + round_half_to_even(avg(t) over (partition by m order by t rows between unbounded preceding and current row), 2) as `avg` +FROM range(1, 20) t +LET m = t % 4 +ORDER BY m, t; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results/window/misc_01/misc_01.7.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/misc_01/misc_01.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/misc_01/misc_01.7.adm new file mode 100644 index 0000000..9deb329 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/misc_01/misc_01.7.adm @@ -0,0 +1,20 @@ +{ "m": 0, "t": 4, "sum": 0, "sum_distinct": 0 } +{ "m": 0, "t": 8, "sum": 0, "sum_distinct": 0 } +{ "m": 0, "t": 12, "sum": 0, "sum_distinct": 0 } +{ "m": 0, "t": 16, "sum": 0, "sum_distinct": 0 } +{ "m": 0, "t": 20, "sum": 0, "sum_distinct": 0 } +{ "m": 1, "t": 1, "sum": 1, "sum_distinct": 1 } +{ "m": 1, "t": 5, "sum": 2, "sum_distinct": 1 } +{ "m": 1, "t": 9, "sum": 3, "sum_distinct": 1 } +{ "m": 1, "t": 13, "sum": 4, "sum_distinct": 1 } +{ "m": 1, "t": 17, "sum": 5, "sum_distinct": 1 } +{ "m": 2, "t": 2, "sum": 2, "sum_distinct": 2 } +{ "m": 2, "t": 6, "sum": 4, "sum_distinct": 2 } +{ "m": 2, "t": 10, "sum": 6, "sum_distinct": 2 } +{ "m": 2, "t": 14, "sum": 8, "sum_distinct": 2 } +{ "m": 2, "t": 18, "sum": 10, "sum_distinct": 2 } +{ "m": 3, "t": 3, "sum": 3, "sum_distinct": 3 } +{ "m": 3, "t": 7, "sum": 6, "sum_distinct": 3 } +{ "m": 3, "t": 11, "sum": 9, "sum_distinct": 3 } +{ "m": 3, "t": 15, "sum": 12, "sum_distinct": 3 } +{ "m": 3, "t": 19, "sum": 15, "sum_distinct": 3 } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.1.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.1.adm new file mode 100644 index 0000000..75263ed --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.1.adm @@ -0,0 +1,20 @@ +{ "m": 0, "t": 4, "row_nubmer": 1, "percent_rank": 0.0 } +{ "m": 0, "t": 8, "row_nubmer": 2, "percent_rank": 0.25 } +{ "m": 0, "t": 12, "row_nubmer": 3, "percent_rank": 0.5 } +{ "m": 0, "t": 16, "row_nubmer": 4, "percent_rank": 0.75 } +{ "m": 0, "t": 20, "row_nubmer": 5, "percent_rank": 1.0 } +{ "m": 1, "t": 1, "row_nubmer": 1, "percent_rank": 0.0 } +{ "m": 1, "t": 5, "row_nubmer": 2, "percent_rank": 0.25 } +{ "m": 1, "t": 9, "row_nubmer": 3, "percent_rank": 0.5 } +{ "m": 1, "t": 13, "row_nubmer": 4, "percent_rank": 0.75 } +{ "m": 1, "t": 17, "row_nubmer": 5, "percent_rank": 1.0 } +{ "m": 2, "t": 2, "row_nubmer": 1, "percent_rank": 0.0 } +{ "m": 2, "t": 6, "row_nubmer": 2, "percent_rank": 0.25 } +{ "m": 2, "t": 10, "row_nubmer": 3, "percent_rank": 0.5 } +{ "m": 2, "t": 14, "row_nubmer": 4, "percent_rank": 0.75 } +{ "m": 2, "t": 18, "row_nubmer": 5, "percent_rank": 1.0 } +{ "m": 3, "t": 3, "row_nubmer": 1, "percent_rank": 0.0 } +{ "m": 3, "t": 7, "row_nubmer": 2, "percent_rank": 0.25 } +{ "m": 3, "t": 11, "row_nubmer": 3, "percent_rank": 0.5 } +{ "m": 3, "t": 15, "row_nubmer": 4, "percent_rank": 0.75 } +{ "m": 3, "t": 19, "row_nubmer": 5, "percent_rank": 1.0 } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.2.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.2.adm new file mode 100644 index 0000000..a08ac96 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.2.adm @@ -0,0 +1,20 @@ +{ "m": 0, "t": 4, "row_nubmer": 1, "sum": 4 } +{ "m": 0, "t": 8, "row_nubmer": 2, "sum": 12 } +{ "m": 0, "t": 12, "row_nubmer": 3, "sum": 24 } +{ "m": 0, "t": 16, "row_nubmer": 4, "sum": 40 } +{ "m": 0, "t": 20, "row_nubmer": 5, "sum": 60 } +{ "m": 1, "t": 1, "row_nubmer": 1, "sum": 1 } +{ "m": 1, "t": 5, "row_nubmer": 2, "sum": 6 } +{ "m": 1, "t": 9, "row_nubmer": 3, "sum": 15 } +{ "m": 1, "t": 13, "row_nubmer": 4, "sum": 28 } +{ "m": 1, "t": 17, "row_nubmer": 5, "sum": 45 } +{ "m": 2, "t": 2, "row_nubmer": 1, "sum": 2 } +{ "m": 2, "t": 6, "row_nubmer": 2, "sum": 8 } +{ "m": 2, "t": 10, "row_nubmer": 3, "sum": 18 } +{ "m": 2, "t": 14, "row_nubmer": 4, "sum": 32 } +{ "m": 2, "t": 18, "row_nubmer": 5, "sum": 50 } +{ "m": 3, "t": 3, "row_nubmer": 1, "sum": 3 } +{ "m": 3, "t": 7, "row_nubmer": 2, "sum": 10 } +{ "m": 3, "t": 11, "row_nubmer": 3, "sum": 21 } +{ "m": 3, "t": 15, "row_nubmer": 4, "sum": 36 } +{ "m": 3, "t": 19, "row_nubmer": 5, "sum": 55 } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.3.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.3.adm new file mode 100644 index 0000000..f356720 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.3.adm @@ -0,0 +1,20 @@ +{ "m": 0, "t": 4, "row_nubmer": 1, "sum": 4 } +{ "m": 0, "t": 8, "row_nubmer": 2, "sum": 12 } +{ "m": 0, "t": 12, "row_nubmer": 3, "sum": 24 } +{ "m": 0, "t": 16, "row_nubmer": 4, "sum": 40 } +{ "m": 0, "t": 20, "row_nubmer": 5, "sum": 60 } +{ "m": 1, "t": 1, "row_nubmer": 1, "sum": 2 } +{ "m": 1, "t": 5, "row_nubmer": 2, "sum": 8 } +{ "m": 1, "t": 9, "row_nubmer": 3, "sum": 18 } +{ "m": 1, "t": 13, "row_nubmer": 4, "sum": 32 } +{ "m": 1, "t": 17, "row_nubmer": 5, "sum": 50 } +{ "m": 2, "t": 2, "row_nubmer": 1, "sum": 4 } +{ "m": 2, "t": 6, "row_nubmer": 2, "sum": 12 } +{ "m": 2, "t": 10, "row_nubmer": 3, "sum": 24 } +{ "m": 2, "t": 14, "row_nubmer": 4, "sum": 40 } +{ "m": 2, "t": 18, "row_nubmer": 5, "sum": 60 } +{ "m": 3, "t": 3, "row_nubmer": 1, "sum": 6 } +{ "m": 3, "t": 7, "row_nubmer": 2, "sum": 16 } +{ "m": 3, "t": 11, "row_nubmer": 3, "sum": 30 } +{ "m": 3, "t": 15, "row_nubmer": 4, "sum": 48 } +{ "m": 3, "t": 19, "row_nubmer": 5, "sum": 70 } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.4.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.4.adm new file mode 100644 index 0000000..e14d3a2 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.4.adm @@ -0,0 +1,20 @@ +{ "m": 0, "t": 4, "row_nubmer": 1, "sum": 4, "avg": 4.0 } +{ "m": 0, "t": 8, "row_nubmer": 2, "sum": 12, "avg": 6.0 } +{ "m": 0, "t": 12, "row_nubmer": 3, "sum": 24, "avg": 8.0 } +{ "m": 0, "t": 16, "row_nubmer": 4, "sum": 40, "avg": 10.0 } +{ "m": 0, "t": 20, "row_nubmer": 5, "sum": 60, "avg": 12.0 } +{ "m": 1, "t": 1, "row_nubmer": 1, "sum": 2, "avg": 0.0 } +{ "m": 1, "t": 5, "row_nubmer": 2, "sum": 8, "avg": 2.0 } +{ "m": 1, "t": 9, "row_nubmer": 3, "sum": 18, "avg": 4.0 } +{ "m": 1, "t": 13, "row_nubmer": 4, "sum": 32, "avg": 6.0 } +{ "m": 1, "t": 17, "row_nubmer": 5, "sum": 50, "avg": 8.0 } +{ "m": 2, "t": 2, "row_nubmer": 1, "sum": 4, "avg": 0.0 } +{ "m": 2, "t": 6, "row_nubmer": 2, "sum": 12, "avg": 2.0 } +{ "m": 2, "t": 10, "row_nubmer": 3, "sum": 24, "avg": 4.0 } +{ "m": 2, "t": 14, "row_nubmer": 4, "sum": 40, "avg": 6.0 } +{ "m": 2, "t": 18, "row_nubmer": 5, "sum": 60, "avg": 8.0 } +{ "m": 3, "t": 3, "row_nubmer": 1, "sum": 6, "avg": 0.0 } +{ "m": 3, "t": 7, "row_nubmer": 2, "sum": 16, "avg": 2.0 } +{ "m": 3, "t": 11, "row_nubmer": 3, "sum": 30, "avg": 4.0 } +{ "m": 3, "t": 15, "row_nubmer": 4, "sum": 48, "avg": 6.0 } +{ "m": 3, "t": 19, "row_nubmer": 5, "sum": 70, "avg": 8.0 } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.5.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.5.adm new file mode 100644 index 0000000..fa8f4af --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.5.adm @@ -0,0 +1,16 @@ +{ "m": 0, "t": 4, "row_number": 1, "ntile": 1 } +{ "m": 0, "t": 8, "row_number": 2, "ntile": 1 } +{ "m": 0, "t": 12, "row_number": 3, "ntile": 2 } +{ "m": 0, "t": 16, "row_number": 4, "ntile": 2 } +{ "m": 1, "t": 1, "row_number": 1, "ntile": 1 } +{ "m": 1, "t": 5, "row_number": 2, "ntile": 1 } +{ "m": 1, "t": 9, "row_number": 3, "ntile": 2 } +{ "m": 1, "t": 13, "row_number": 4, "ntile": 2 } +{ "m": 2, "t": 2, "row_number": 1, "ntile": 1 } +{ "m": 2, "t": 6, "row_number": 2, "ntile": 1 } +{ "m": 2, "t": 10, "row_number": 3, "ntile": 2 } +{ "m": 2, "t": 14, "row_number": 4, "ntile": 2 } +{ "m": 3, "t": 3, "row_number": 1, "ntile": 1 } +{ "m": 3, "t": 7, "row_number": 2, "ntile": 1 } +{ "m": 3, "t": 11, "row_number": 3, "ntile": 2 } +{ "m": 3, "t": 15, "row_number": 4, "ntile": 2 } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.6.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.6.adm new file mode 100644 index 0000000..fa8f4af --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.6.adm @@ -0,0 +1,16 @@ +{ "m": 0, "t": 4, "row_number": 1, "ntile": 1 } +{ "m": 0, "t": 8, "row_number": 2, "ntile": 1 } +{ "m": 0, "t": 12, "row_number": 3, "ntile": 2 } +{ "m": 0, "t": 16, "row_number": 4, "ntile": 2 } +{ "m": 1, "t": 1, "row_number": 1, "ntile": 1 } +{ "m": 1, "t": 5, "row_number": 2, "ntile": 1 } +{ "m": 1, "t": 9, "row_number": 3, "ntile": 2 } +{ "m": 1, "t": 13, "row_number": 4, "ntile": 2 } +{ "m": 2, "t": 2, "row_number": 1, "ntile": 1 } +{ "m": 2, "t": 6, "row_number": 2, "ntile": 1 } +{ "m": 2, "t": 10, "row_number": 3, "ntile": 2 } +{ "m": 2, "t": 14, "row_number": 4, "ntile": 2 } +{ "m": 3, "t": 3, "row_number": 1, "ntile": 1 } +{ "m": 3, "t": 7, "row_number": 2, "ntile": 1 } +{ "m": 3, "t": 11, "row_number": 3, "ntile": 2 } +{ "m": 3, "t": 15, "row_number": 4, "ntile": 2 } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.7.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.7.adm new file mode 100644 index 0000000..30ed6e2 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_opt_01/win_opt_01.7.adm @@ -0,0 +1,20 @@ +{ "m": 0, "t": 4, "sum": 4, "avg": 4.0 } +{ "m": 0, "t": 8, "sum": 12, "avg": 6.0 } +{ "m": 0, "t": 12, "sum": 24, "avg": 8.0 } +{ "m": 0, "t": 16, "sum": 40, "avg": 10.0 } +{ "m": 0, "t": 20, "sum": 60, "avg": 12.0 } +{ "m": 1, "t": 1, "sum": 1, "avg": 1.0 } +{ "m": 1, "t": 5, "sum": 6, "avg": 3.0 } +{ "m": 1, "t": 9, "sum": 15, "avg": 5.0 } +{ "m": 1, "t": 13, "sum": 28, "avg": 7.0 } +{ "m": 1, "t": 17, "sum": 45, "avg": 9.0 } +{ "m": 2, "t": 2, "sum": 2, "avg": 2.0 } +{ "m": 2, "t": 6, "sum": 8, "avg": 4.0 } +{ "m": 2, "t": 10, "sum": 18, "avg": 6.0 } +{ "m": 2, "t": 14, "sum": 32, "avg": 8.0 } +{ "m": 2, "t": 18, "sum": 50, "avg": 10.0 } +{ "m": 3, "t": 3, "sum": 3, "avg": 3.0 } +{ "m": 3, "t": 7, "sum": 10, "avg": 5.0 } +{ "m": 3, "t": 11, "sum": 21, "avg": 7.0 } +{ "m": 3, "t": 15, "sum": 36, "avg": 9.0 } +{ "m": 3, "t": 19, "sum": 55, "avg": 11.0 } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/window/misc_01/misc_01.7.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/window/misc_01/misc_01.7.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/window/misc_01/misc_01.7.ast new file mode 100644 index 0000000..2271af5 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/window/misc_01/misc_01.7.ast @@ -0,0 +1,58 @@ +Query: +SELECT [ +Variable [ Name=$m ] +m +Variable [ Name=$t ] +t +WINDOW null.sum@1[ + OperatorExpr [ + Variable [ Name=$t ] + mod + LiteralExpr [LONG] [4] + ] +] +OVER ( + PARTITION BY + Variable [ Name=$m ] + ORDER BY + Variable [ Name=$t ] + ASC + rows between unbounded preceding and current row exclude no others +) +sum +WINDOW null.sum-distinct@1[ + OperatorExpr [ + Variable [ Name=$t ] + mod + LiteralExpr [LONG] [4] + ] +] +OVER ( + PARTITION BY + Variable [ Name=$m ] + ORDER BY + Variable [ Name=$t ] + ASC + rows between unbounded preceding and current row exclude no others +) +sum_distinct +] +FROM [ FunctionCall null.range@2[ + LiteralExpr [LONG] [1] + LiteralExpr [LONG] [20] + ] + AS Variable [ Name=$t ] +] +Let Variable [ Name=$m ] + := + OperatorExpr [ + Variable [ Name=$t ] + mod + LiteralExpr [LONG] [4] + ] +Orderby + Variable [ Name=$m ] + ASC + Variable [ Name=$t ] + ASC + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml index 7bd1eaf..3a857c1 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml @@ -9260,6 +9260,11 @@ <output-dir compare="Text">row_number_01</output-dir> </compilation-unit> </test-case> + <test-case FilePath="window"> + <compilation-unit name="win_opt_01"> + <output-dir compare="Text">win_opt_01</output-dir> + </compilation-unit> + </test-case> </test-group> <test-group name="writers"> <test-case FilePath="writers"> http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppDistinctAggregationSugarVisitor.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppDistinctAggregationSugarVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppDistinctAggregationSugarVisitor.java index 80844ab..efcd7e0 100644 --- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppDistinctAggregationSugarVisitor.java +++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppDistinctAggregationSugarVisitor.java @@ -38,6 +38,7 @@ import org.apache.asterix.lang.sqlpp.clause.SelectClause; import org.apache.asterix.lang.sqlpp.clause.SelectElement; import org.apache.asterix.lang.sqlpp.clause.SelectSetOperation; import org.apache.asterix.lang.sqlpp.expression.SelectExpression; +import org.apache.asterix.lang.sqlpp.expression.WindowExpression; import org.apache.asterix.lang.sqlpp.struct.SetOperationInput; import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppSimpleExpressionVisitor; import org.apache.asterix.om.functions.BuiltinFunctions; @@ -59,25 +60,45 @@ public class SqlppDistinctAggregationSugarVisitor extends AbstractSqlppSimpleExp @Override public Expression visit(CallExpr callExpr, ILangExpression arg) throws CompilationException { - FunctionSignature signature = callExpr.getFunctionSignature(); - IFunctionInfo finfo = FunctionUtil.getFunctionInfo(signature); - FunctionIdentifier aggFn = - finfo != null ? BuiltinFunctions.getAggregateFunction(finfo.getFunctionIdentifier()) : null; - FunctionIdentifier newAggFn = aggFn != null ? BuiltinFunctions.getAggregateFunctionForDistinct(aggFn) : null; + FunctionIdentifier newAggFn = getAggregateFunctionForDistinct(callExpr.getFunctionSignature()); if (newAggFn == null) { return super.visit(callExpr, arg); } - List<Expression> exprList = callExpr.getExprList(); - List<Expression> newExprList = new ArrayList<>(exprList.size()); - for (Expression expr : exprList) { - Expression newExpr = rewriteArgument(expr); - newExprList.add(newExpr.accept(this, arg)); - } + List<Expression> newExprList = rewriteArgumentList(callExpr.getExprList(), arg); callExpr.setFunctionSignature(new FunctionSignature(newAggFn)); callExpr.setExprList(newExprList); return callExpr; } + @Override + public Expression visit(WindowExpression winExpr, ILangExpression arg) throws CompilationException { + FunctionIdentifier newAggFn = getAggregateFunctionForDistinct(winExpr.getFunctionSignature()); + if (newAggFn == null) { + return super.visit(winExpr, arg); + } + List<Expression> newExprList = rewriteArgumentList(winExpr.getExprList(), arg); + winExpr.setFunctionSignature(new FunctionSignature(newAggFn)); + winExpr.setExprList(newExprList); + return winExpr; + } + + private FunctionIdentifier getAggregateFunctionForDistinct(FunctionSignature signature) { + IFunctionInfo finfo = FunctionUtil.getFunctionInfo(signature); + FunctionIdentifier aggFn = + finfo != null ? BuiltinFunctions.getAggregateFunction(finfo.getFunctionIdentifier()) : null; + return aggFn != null ? BuiltinFunctions.getAggregateFunctionForDistinct(aggFn) : null; + } + + private List<Expression> rewriteArgumentList(List<Expression> exprList, ILangExpression arg) + throws CompilationException { + List<Expression> result = new ArrayList<>(exprList.size()); + for (Expression expr : exprList) { + Expression newExpr = rewriteArgument(expr); + result.add(newExpr.accept(this, arg)); + } + return result; + } + /** * rewrites {@code expr -> FROM expr AS i SELECT DISTINCT VALUE i} */ http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java index b48e024..7ffebc2 100644 --- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java +++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java @@ -204,6 +204,7 @@ public class VariableCheckAndRewriteVisitor extends AbstractSqlppExpressionScopi return path.length == 2 && metadataProvider.findDataset(path[0], path[1]) != null; } + @Override public Expression visit(CallExpr callExpr, ILangExpression arg) throws CompilationException { // skip variables inside SQL-92 aggregates (they will be resolved by SqlppGroupByAggregationSugarVisitor) if (FunctionMapUtil.isSql92AggregateFunction(callExpr.getFunctionSignature())) { http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java index f1039ff..d906a82 100644 --- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java +++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java @@ -379,8 +379,8 @@ public class SqlppAstPrintVisitor extends QueryPrintVisitor implements ISqlppVis } } if (winExpr.hasFrameDefinition()) { - out.println(skip(step + 1) + winExpr.getFrameMode() + ' ' + winExpr.getFrameStartKind() + ' ' - + winExpr.getFrameEndKind() + " EXCLUDE " + winExpr.getFrameExclusionKind()); + out.println(skip(step + 1) + winExpr.getFrameMode() + " between " + winExpr.getFrameStartKind() + " and " + + winExpr.getFrameEndKind() + " exclude " + winExpr.getFrameExclusionKind()); if (winExpr.hasFrameStartExpr()) { winExpr.getFrameStartExpr().accept(this, step + 2); } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java b/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java index 08f5139..54c8330 100644 --- a/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java +++ b/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java @@ -1790,9 +1790,9 @@ public class BuiltinFunctions { addFunction(SCALAR_SQL_COUNT_DISTINCT, AInt64TypeComputer.INSTANCE, true); addFunction(SUM_DISTINCT, NumericSumAggTypeComputer.INSTANCE, true); - addFunction(SCALAR_SUM_DISTINCT, NumericSumAggTypeComputer.INSTANCE, true); + addFunction(SCALAR_SUM_DISTINCT, ScalarVersionOfAggregateResultType.INSTANCE, true); addFunction(SQL_SUM_DISTINCT, NumericSumAggTypeComputer.INSTANCE, true); - addFunction(SCALAR_SQL_SUM_DISTINCT, NumericSumAggTypeComputer.INSTANCE, true); + addFunction(SCALAR_SQL_SUM_DISTINCT, ScalarVersionOfAggregateResultType.INSTANCE, true); addFunction(AVG_DISTINCT, NullableDoubleTypeComputer.INSTANCE, true); addFunction(SCALAR_AVG_DISTINCT, NullableDoubleTypeComputer.INSTANCE, true); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/faf9791d/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/WindowOperator.java ---------------------------------------------------------------------- diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/WindowOperator.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/WindowOperator.java index e09b471..8f563b3 100644 --- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/WindowOperator.java +++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/WindowOperator.java @@ -68,7 +68,7 @@ public class WindowOperator extends AbstractOperatorWithNestedPlans { private final List<Mutable<ILogicalExpression>> frameExcludeExpressions; - private final int frameExcludeNegationStartIdx; + private int frameExcludeNegationStartIdx; private final Mutable<ILogicalExpression> frameOffset; @@ -175,6 +175,10 @@ public class WindowOperator extends AbstractOperatorWithNestedPlans { return frameExcludeNegationStartIdx; } + public void setFrameExcludeNegationStartIdx(int value) { + this.frameExcludeNegationStartIdx = value; + } + public Mutable<ILogicalExpression> getFrameOffset() { return frameOffset; }
