[ASTERIXDB-2466][FUN] Implement window functions - user model changes: yes - storage format changes: no - interface changes: no
Details: - Implement window functions with SQL syntax: function() OVER ((PARTITION BY expr1, expr2, ...)? ORDER BY exprA, exprB, ...) - Where supported functions are: ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(), NTILE() Change-Id: Ia28af8773cb11049c38d440c51b9c3cd1ed2bab4 Reviewed-on: https://asterix-gerrit.ics.uci.edu/3002 Tested-by: Jenkins <[email protected]> Contrib: Jenkins <[email protected]> Integration-Tests: 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/fdedf626 Tree: http://git-wip-us.apache.org/repos/asf/asterixdb/tree/fdedf626 Diff: http://git-wip-us.apache.org/repos/asf/asterixdb/diff/fdedf626 Branch: refs/heads/master Commit: fdedf6263cca9bb87a2baf0eccb7864a26c8aaf9 Parents: b9d55c4 Author: Dmitry Lychagin <[email protected]> Authored: Tue Oct 30 18:12:40 2018 -0700 Committer: Dmitry Lychagin <[email protected]> Committed: Wed Oct 31 20:54:12 2018 -0700 ---------------------------------------------------------------------- .../asterix/optimizer/base/RuleCollections.java | 2 + .../rules/ExtractOrderExpressionsRule.java | 34 +--- .../rules/ExtractWindowExpressionsRule.java | 68 +++++++ .../rules/SetAsterixPhysicalOperatorsRule.java | 48 +++++ .../SweepIllegalNonfunctionalFunctions.java | 18 +- .../subplan/InlineAllNtsInSubplanVisitor.java | 6 + ...neLeftNtsInSubplanJoinFlatteningVisitor.java | 7 + .../SubplanSpecialFlatteningCheckVisitor.java | 6 + .../LangExpressionToPlanTranslator.java | 13 +- .../SqlppExpressionToPlanTranslator.java | 78 ++++++++ .../app/resource/OperatorResourcesComputer.java | 10 + .../app/resource/PlanStagesGenerator.java | 7 + .../app/resource/RequiredCapacityVisitor.java | 13 ++ .../optimizerts/queries/window/window_01.sqlpp | 58 ++++++ .../optimizerts/results/window/window_01.plan | 30 +++ .../dense_rank_01/dense_rank_01.1.ddl.sqlpp | 72 +++++++ .../dense_rank_01/dense_rank_01.2.update.sqlpp | 36 ++++ .../dense_rank_01/dense_rank_01.3.query.sqlpp | 26 +++ .../dense_rank_01/dense_rank_01.4.query.sqlpp | 26 +++ .../dense_rank_01/dense_rank_01.5.query.sqlpp | 26 +++ .../dense_rank_01/dense_rank_01.6.query.sqlpp | 27 +++ .../dense_rank_01/dense_rank_01.7.query.sqlpp | 26 +++ .../window/misc_01/misc_01.1.ddl.sqlpp | 33 ++++ .../window/misc_01/misc_01.2.update.sqlpp | 33 ++++ .../window/misc_01/misc_01.3.query.sqlpp | 48 +++++ .../window/misc_01/misc_01.4.query.sqlpp | 32 ++++ .../window/misc_01/misc_01.5.query.sqlpp | 32 ++++ .../window/ntile_01/ntile_01.1.ddl.sqlpp | 75 ++++++++ .../window/ntile_01/ntile_01.2.update.sqlpp | 36 ++++ .../window/ntile_01/ntile_01.3.query.sqlpp | 26 +++ .../window/ntile_01/ntile_01.4.query.sqlpp | 26 +++ .../window/ntile_01/ntile_01.5.query.sqlpp | 26 +++ .../window/ntile_01/ntile_01.6.query.sqlpp | 27 +++ .../window/ntile_01/ntile_01.7.query.sqlpp | 26 +++ .../window/ntile_02/ntile_02.1.query.sqlpp | 25 +++ .../percent_rank_01/percent_rank_01.1.ddl.sqlpp | 76 ++++++++ .../percent_rank_01.2.update.sqlpp | 36 ++++ .../percent_rank_01.3.query.sqlpp | 26 +++ .../percent_rank_01.4.query.sqlpp | 26 +++ .../percent_rank_01.5.query.sqlpp | 26 +++ .../percent_rank_01.6.query.sqlpp | 27 +++ .../percent_rank_01.7.query.sqlpp | 26 +++ .../window/rank_01/rank_01.1.ddl.sqlpp | 74 +++++++ .../window/rank_01/rank_01.2.update.sqlpp | 36 ++++ .../window/rank_01/rank_01.3.query.sqlpp | 26 +++ .../window/rank_01/rank_01.4.query.sqlpp | 26 +++ .../window/rank_01/rank_01.5.query.sqlpp | 26 +++ .../window/rank_01/rank_01.6.query.sqlpp | 27 +++ .../window/rank_01/rank_01.7.query.sqlpp | 26 +++ .../row_number_01/row_number_01.1.ddl.sqlpp | 72 +++++++ .../row_number_01/row_number_01.2.update.sqlpp | 36 ++++ .../row_number_01/row_number_01.3.query.sqlpp | 26 +++ .../row_number_01/row_number_01.4.query.sqlpp | 26 +++ .../row_number_01/row_number_01.5.query.sqlpp | 26 +++ .../row_number_01/row_number_01.6.query.sqlpp | 27 +++ .../row_number_01/row_number_01.7.query.sqlpp | 26 +++ .../window/dense_rank_01/dense_rank_01.3.adm | 1 + .../window/dense_rank_01/dense_rank_01.4.adm | 1 + .../window/dense_rank_01/dense_rank_01.5.adm | 1 + .../dense_rank_01/dense_rank_01.6.regexadm | 1 + .../window/dense_rank_01/dense_rank_01.7.adm | 1 + .../results/window/misc_01/misc_01.3.adm | 1 + .../results/window/misc_01/misc_01.4.adm | 4 + .../results/window/misc_01/misc_01.5.adm | 4 + .../results/window/ntile_01/ntile_01.3.adm | 1 + .../results/window/ntile_01/ntile_01.4.adm | 1 + .../results/window/ntile_01/ntile_01.5.adm | 1 + .../results/window/ntile_01/ntile_01.6.regexadm | 1 + .../results/window/ntile_01/ntile_01.7.adm | 1 + .../percent_rank_01/percent_rank_01.3.adm | 1 + .../percent_rank_01/percent_rank_01.4.adm | 1 + .../percent_rank_01/percent_rank_01.5.adm | 1 + .../percent_rank_01/percent_rank_01.6.regexadm | 1 + .../percent_rank_01/percent_rank_01.7.adm | 1 + .../results/window/rank_01/rank_01.3.adm | 1 + .../results/window/rank_01/rank_01.4.adm | 1 + .../results/window/rank_01/rank_01.5.adm | 1 + .../results/window/rank_01/rank_01.6.regexadm | 1 + .../results/window/rank_01/rank_01.7.adm | 1 + .../window/row_number_01/row_number_01.3.adm | 1 + .../window/row_number_01/row_number_01.4.adm | 1 + .../window/row_number_01/row_number_01.5.adm | 1 + .../row_number_01/row_number_01.6.regexadm | 1 + .../window/row_number_01/row_number_01.7.adm | 1 + .../resources/runtimets/testsuite_sqlpp.xml | 39 ++++ .../asterix/common/exceptions/ErrorCode.java | 1 + .../main/resources/asx_errormsg/en.properties | 1 + .../asterix/lang/common/base/Expression.java | 3 +- .../CloneAndSubstituteVariablesVisitor.java | 2 +- .../lang/common/visitor/FormatPrintVisitor.java | 31 +-- .../lang/sqlpp/expression/WindowExpression.java | 105 ++++++++++ .../lang/sqlpp/rewrites/SqlppQueryRewriter.java | 16 +- .../visitor/SqlppInlineUdfsVisitor.java | 18 +- .../CheckDatasetOnlyResolutionVisitor.java | 6 + .../visitor/CheckSql92AggregateVisitor.java | 7 + .../sqlpp/visitor/CheckSubqueryVisitor.java | 7 + .../lang/sqlpp/visitor/DeepCopyVisitor.java | 16 +- .../lang/sqlpp/visitor/FreeVariableVisitor.java | 11 ++ .../sqlpp/visitor/SqlppAstPrintVisitor.java | 26 +++ ...SqlppCloneAndSubstituteVariablesVisitor.java | 17 ++ .../sqlpp/visitor/SqlppFormatPrintVisitor.java | 22 +++ .../visitor/base/AbstractSqlppAstVisitor.java | 5 + .../AbstractSqlppSimpleExpressionVisitor.java | 11 ++ .../lang/sqlpp/visitor/base/ISqlppVisitor.java | 3 + .../asterix-lang-sqlpp/src/main/javacc/SQLPP.jj | 34 +++- .../asterix/om/functions/BuiltinFunctions.java | 82 ++++++++ .../runtime/functions/FunctionCollection.java | 12 ++ .../AbstractRankRunningAggregateEvaluator.java | 130 +++++++++++++ .../DenseRankRunningAggregateDescriptor.java | 62 ++++++ .../std/NtileRunningAggregateDescriptor.java | 57 ++++++ .../std/NtileRunningAggregateEvaluator.java | 115 +++++++++++ .../PercentRankRunningAggregateDescriptor.java | 62 ++++++ .../PercentRankRunningAggregateEvaluator.java | 62 ++++++ .../std/RankRunningAggregateDescriptor.java | 62 ++++++ .../std/RankRunningAggregateEvaluator.java | 52 +++++ .../RowNumberRunningAggregateDescriptor.java | 56 ++++++ .../std/RowNumberRunningAggregateEvaluator.java | 67 +++++++ .../hyracks/algebricks/common/utils/Pair.java | 16 ++ .../core/algebra/base/ILogicalOperator.java | 2 +- .../core/algebra/base/IPhysicalOperator.java | 2 +- .../core/algebra/base/LogicalOperatorTag.java | 1 + .../core/algebra/base/PhysicalOperatorTag.java | 1 + .../logical/AbstractAssignOperator.java | 14 ++ .../logical/AbstractLogicalOperator.java | 2 +- .../operators/logical/AggregateOperator.java | 11 +- .../operators/logical/AssignOperator.java | 13 +- .../logical/RunningAggregateOperator.java | 12 +- .../operators/logical/WindowOperator.java | 118 ++++++++++++ .../visitors/CardinalityInferenceVisitor.java | 6 + .../visitors/FDsAndEquivClassesVisitor.java | 6 + .../visitors/IsomorphismOperatorVisitor.java | 28 ++- .../IsomorphismVariableMappingVisitor.java | 8 + ...OperatorDeepCopyWithNewVariablesVisitor.java | 15 ++ .../visitors/LogicalPropertiesVisitor.java | 8 +- .../visitors/OperatorDeepCopyVisitor.java | 16 ++ .../visitors/PrimaryKeyVariablesVisitor.java | 5 + .../visitors/ProducedVariableVisitor.java | 7 + .../logical/visitors/SchemaVariableVisitor.java | 6 + .../visitors/SubstituteVariableVisitor.java | 59 +++--- .../logical/visitors/UsedVariableVisitor.java | 14 ++ .../physical/RunningAggregatePOperator.java | 2 +- .../operators/physical/WindowPOperator.java | 191 +++++++++++++++++++ .../LogicalOperatorPrettyPrintVisitor.java | 29 ++- .../LogicalOperatorPrettyPrintVisitorJson.java | 67 +++++-- .../algebra/util/OperatorManipulationUtil.java | 8 + .../visitors/ILogicalOperatorVisitor.java | 3 + .../core/jobgen/impl/JobGenHelper.java | 30 ++- .../core/utils/LogicalOperatorDotVisitor.java | 47 +++-- .../rewriter/rules/AbstractExtractExprRule.java | 47 ++++- .../rules/EnforceStructuralPropertiesRule.java | 2 +- .../rules/ExtractGbyExpressionsRule.java | 67 ++----- ...hNestedOrderByUnderPreSortedGroupByRule.java | 6 + ...placeNtsWithSubplanInputOperatorVisitor.java | 6 + .../algebricks/algebricks-runtime/pom.xml | 5 + .../runtime/base/IWindowAggregateEvaluator.java | 30 +++ .../AbstractRunningAggregatePushRuntime.java | 111 +++++++++++ .../aggrun/AbstractWindowPushRuntime.java | 172 +++++++++++++++++ .../aggrun/MaterializingWindowPushRuntime.java | 152 +++++++++++++++ .../aggrun/RunningAggregatePushRuntime.java | 42 ++++ .../aggrun/RunningAggregateRuntimeFactory.java | 59 ++++++ .../aggrun/SimpleWindowPushRuntime.java | 54 ++++++ .../operators/aggrun/WindowRuntimeFactory.java | 65 +++++++ .../std/RunningAggregateRuntimeFactory.java | 140 -------------- .../tests/pushruntime/PushRuntimeTest.java | 2 +- .../hyracks/api/exceptions/ErrorCode.java | 1 + .../src/main/resources/errormsg/en.properties | 1 + .../dataflow/common/io/RunFileWriter.java | 5 + .../preclustered/PreclusteredGroupWriter.java | 14 +- 168 files changed, 4398 insertions(+), 369 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/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 1010a84..e7fb579 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 @@ -61,6 +61,7 @@ import org.apache.asterix.optimizer.rules.ListifyUnnestingFunctionRule; import org.apache.asterix.optimizer.rules.LoadRecordFieldsRule; import org.apache.asterix.optimizer.rules.MetaFunctionToMetaVariableRule; import org.apache.asterix.optimizer.rules.NestGroupByRule; +import org.apache.asterix.optimizer.rules.ExtractWindowExpressionsRule; import org.apache.asterix.optimizer.rules.PushAggFuncIntoStandaloneAggregateRule; import org.apache.asterix.optimizer.rules.PushAggregateIntoNestedSubplanRule; import org.apache.asterix.optimizer.rules.PushFieldAccessRule; @@ -177,6 +178,7 @@ public final class RuleCollections { normalization.add(new ExtractGbyExpressionsRule()); normalization.add(new ExtractDistinctByExpressionsRule()); normalization.add(new ExtractOrderExpressionsRule()); + normalization.add(new ExtractWindowExpressionsRule()); // IntroduceStaticTypeCastRule should go before // IntroduceDynamicTypeCastRule to http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractOrderExpressionsRule.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractOrderExpressionsRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractOrderExpressionsRule.java index 585077f..826db09 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractOrderExpressionsRule.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractOrderExpressionsRule.java @@ -18,18 +18,16 @@ */ package org.apache.asterix.optimizer.rules; -import org.apache.commons.lang3.mutable.Mutable; +import java.util.List; import org.apache.asterix.optimizer.base.AnalysisUtil; +import org.apache.commons.lang3.mutable.Mutable; import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException; import org.apache.hyracks.algebricks.common.utils.Pair; import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression; import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator; import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext; -import org.apache.hyracks.algebricks.core.algebra.base.LogicalExpressionTag; import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag; -import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable; -import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression; import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator.IOrder; @@ -57,30 +55,12 @@ public class ExtractOrderExpressionsRule extends AbstractExtractExprRule { context.addToDontApplySet(this, op1); OrderOperator oo = (OrderOperator) op1; - if (!orderHasComplexExpr(oo)) { - return false; - } - Mutable<ILogicalOperator> opRef2 = oo.getInputs().get(0); - for (Pair<IOrder, Mutable<ILogicalExpression>> orderPair : oo.getOrderExpressions()) { - ILogicalExpression expr = orderPair.second.getValue(); - if (expr.getExpressionTag() != LogicalExpressionTag.VARIABLE && !AnalysisUtil.isAccessToFieldRecord(expr)) { - LogicalVariable v = extractExprIntoAssignOpRef(expr, opRef2, context); - VariableReferenceExpression vRef = new VariableReferenceExpression(v); - vRef.setSourceLocation(expr.getSourceLocation()); - orderPair.second.setValue(vRef); - } - } - context.computeAndSetTypeEnvironmentForOperator(oo); - return true; + return extractComplexExpressions(oo, oo.getOrderExpressions(), context); } - private boolean orderHasComplexExpr(OrderOperator oo) { - for (Pair<IOrder, Mutable<ILogicalExpression>> orderPair : oo.getOrderExpressions()) { - if (orderPair.second.getValue().getExpressionTag() != LogicalExpressionTag.VARIABLE) { - return true; - } - } - return false; + static boolean extractComplexExpressions(ILogicalOperator op, + List<Pair<IOrder, Mutable<ILogicalExpression>>> exprList, IOptimizationContext context) + throws AlgebricksException { + return extractComplexExpressions(op, exprList, Pair::getSecond, AnalysisUtil::isAccessToFieldRecord, context); } - } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractWindowExpressionsRule.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractWindowExpressionsRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractWindowExpressionsRule.java new file mode 100644 index 0000000..042dae2 --- /dev/null +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ExtractWindowExpressionsRule.java @@ -0,0 +1,68 @@ +/* + * 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. + */ + +package org.apache.asterix.optimizer.rules; + +import java.util.function.Function; + +import org.apache.commons.lang3.mutable.Mutable; +import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException; +import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator; +import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext; +import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; +import org.apache.hyracks.algebricks.rewriter.rules.AbstractExtractExprRule; +import org.apache.hyracks.algebricks.rewriter.rules.ExtractGbyExpressionsRule; + +/** + * Extract complex expressions from window operator's partition and order definitions + */ +public class ExtractWindowExpressionsRule extends AbstractExtractExprRule { + + @Override + public boolean rewritePre(Mutable<ILogicalOperator> opRef, IOptimizationContext context) { + return false; + } + + @Override + public boolean rewritePost(Mutable<ILogicalOperator> opRef, IOptimizationContext context) + throws AlgebricksException { + AbstractLogicalOperator op = (AbstractLogicalOperator) opRef.getValue(); + if (op.getOperatorTag() != LogicalOperatorTag.WINDOW) { + return false; + } + if (context.checkIfInDontApplySet(this, op)) { + return false; + } + context.addToDontApplySet(this, op); + + WindowOperator winOp = (WindowOperator) op; + + boolean rewritten = ExtractGbyExpressionsRule.extractComplexExpressions(winOp, winOp.getPartitionExpressions(), + Function.identity(), context); + + rewritten |= ExtractOrderExpressionsRule.extractComplexExpressions(winOp, winOp.getOrderExpressions(), context); + + if (rewritten) { + context.computeAndSetTypeEnvironmentForOperator(op); + } + return rewritten; + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java index 2dc5f2e..fdebd14 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java @@ -59,9 +59,13 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.AggregateOpe import org.apache.hyracks.algebricks.core.algebra.operators.logical.GroupByOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.InnerJoinOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterJoinOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; import org.apache.hyracks.algebricks.core.algebra.operators.physical.ExternalGroupByPOperator; import org.apache.hyracks.algebricks.core.algebra.operators.physical.PreclusteredGroupByPOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.physical.WindowPOperator; import org.apache.hyracks.algebricks.core.algebra.properties.INodeDomain; +import org.apache.hyracks.algebricks.core.algebra.properties.OrderColumn; import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule; import org.apache.hyracks.algebricks.core.rewriter.base.PhysicalOptimizationConfig; import org.apache.hyracks.algebricks.rewriter.util.JoinUtils; @@ -276,6 +280,12 @@ public class SetAsterixPhysicalOperatorsRule implements IAlgebraicRewriteRule { } break; } + case WINDOW: { + WindowOperator winOp = (WindowOperator) op; + WindowPOperator physOp = createWindowPOperator(winOp); + op.setPhysicalOperator(physOp); + break; + } } } if (op.hasNestedPlans()) { @@ -330,4 +340,42 @@ public class SetAsterixPhysicalOperatorsRule implements IAlgebraicRewriteRule { aggOp.setMergeExpressions(mergeExpressionRefs); } + private static WindowPOperator createWindowPOperator(WindowOperator winOp) throws CompilationException { + List<Mutable<ILogicalExpression>> partitionExprs = winOp.getPartitionExpressions(); + List<LogicalVariable> partitionColumns = new ArrayList<>(partitionExprs.size()); + for (Mutable<ILogicalExpression> pe : partitionExprs) { + ILogicalExpression partExpr = pe.getValue(); + if (partExpr.getExpressionTag() != LogicalExpressionTag.VARIABLE) { + throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, winOp.getSourceLocation(), + "Window partition/order expression has not been normalized"); + } + LogicalVariable var = ((VariableReferenceExpression) partExpr).getVariableReference(); + partitionColumns.add(var); + } + List<Pair<OrderOperator.IOrder, Mutable<ILogicalExpression>>> orderExprs = winOp.getOrderExpressions(); + List<OrderColumn> orderColumns = new ArrayList<>(orderExprs.size()); + for (Pair<OrderOperator.IOrder, Mutable<ILogicalExpression>> p : orderExprs) { + ILogicalExpression orderExpr = p.second.getValue(); + if (orderExpr.getExpressionTag() != LogicalExpressionTag.VARIABLE) { + throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, winOp.getSourceLocation(), + "Window partition/order expression has not been normalized"); + } + LogicalVariable var = ((VariableReferenceExpression) orderExpr).getVariableReference(); + orderColumns.add(new OrderColumn(var, p.first.getKind())); + } + boolean partitionMaterialization = false; + for (Mutable<ILogicalExpression> exprRef : winOp.getExpressions()) { + ILogicalExpression expr = exprRef.getValue(); + if (expr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) { + throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, winOp.getSourceLocation(), + expr.getExpressionTag()); + } + AbstractFunctionCallExpression callExpr = (AbstractFunctionCallExpression) expr; + if (BuiltinFunctions.windowFunctionRequiresMaterialization(callExpr.getFunctionIdentifier())) { + partitionMaterialization = true; + break; + } + } + return new WindowPOperator(partitionColumns, partitionMaterialization, orderColumns); + } } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SweepIllegalNonfunctionalFunctions.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SweepIllegalNonfunctionalFunctions.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SweepIllegalNonfunctionalFunctions.java index 0c91e9b..a4250c0 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SweepIllegalNonfunctionalFunctions.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/SweepIllegalNonfunctionalFunctions.java @@ -65,13 +65,13 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.TokenizeOper import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestMapOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteResultOperator; import org.apache.hyracks.algebricks.core.algebra.visitors.ILogicalOperatorVisitor; import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule; -import org.apache.hyracks.algebricks.rewriter.rules.AbstractExtractExprRule; -public class SweepIllegalNonfunctionalFunctions extends AbstractExtractExprRule implements IAlgebraicRewriteRule { +public class SweepIllegalNonfunctionalFunctions implements IAlgebraicRewriteRule { private final IllegalNonfunctionalFunctionSweeperOperatorVisitor visitor; @@ -313,6 +313,20 @@ public class SweepIllegalNonfunctionalFunctions extends AbstractExtractExprRule sweepExpression(op.getRangeMapExpression().getValue(), op); return null; } + + @Override + public Void visitWindowOperator(WindowOperator op, Void arg) throws AlgebricksException { + for (Mutable<ILogicalExpression> me : op.getPartitionExpressions()) { + sweepExpression(me.getValue(), op); + } + for (Pair<IOrder, Mutable<ILogicalExpression>> p : op.getOrderExpressions()) { + sweepExpression(p.second.getValue(), op); + } + for (Mutable<ILogicalExpression> me : op.getExpressions()) { + sweepExpression(me.getValue(), op); + } + return null; + } } } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java index 22ef303..c540bbc 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java @@ -82,6 +82,7 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.TokenizeOper import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestMapOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.LogicalOperatorDeepCopyWithNewVariablesVisitor; import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities; import org.apache.hyracks.algebricks.core.algebra.plan.ALogicalPlanImpl; @@ -649,6 +650,11 @@ class InlineAllNtsInSubplanVisitor implements IQueryOperatorVisitor<ILogicalOper "Forward operator should have been disqualified for this rewriting!"); } + @Override + public ILogicalOperator visitWindowOperator(WindowOperator op, Void arg) throws AlgebricksException { + return visitSingleInputOperator(op); + } + /** * Wraps an AggregateOperator or RunningAggregateOperator with a group-by * operator where the group-by keys are variables in keyVarsToEnforce. Note http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineLeftNtsInSubplanJoinFlatteningVisitor.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineLeftNtsInSubplanJoinFlatteningVisitor.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineLeftNtsInSubplanJoinFlatteningVisitor.java index b862a6f..3063e0a 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineLeftNtsInSubplanJoinFlatteningVisitor.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineLeftNtsInSubplanJoinFlatteningVisitor.java @@ -65,6 +65,8 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.TokenizeOper import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestMapOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator.IOrder; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities; import org.apache.hyracks.algebricks.core.algebra.visitors.IQueryOperatorVisitor; @@ -384,6 +386,11 @@ class InlineLeftNtsInSubplanJoinFlatteningVisitor implements IQueryOperatorVisit "Nested subplans with a forward operator should have been disqualified for this rewriting!"); } + @Override + public ILogicalOperator visitWindowOperator(WindowOperator op, Void arg) throws AlgebricksException { + return visitSingleInputOperator(op); + } + private ILogicalOperator visitSingleInputOperator(ILogicalOperator op) throws AlgebricksException { if (op.getInputs().size() == 1) { // Deals with single input operators. http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/SubplanSpecialFlatteningCheckVisitor.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/SubplanSpecialFlatteningCheckVisitor.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/SubplanSpecialFlatteningCheckVisitor.java index e2b104d..288b01a 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/SubplanSpecialFlatteningCheckVisitor.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/SubplanSpecialFlatteningCheckVisitor.java @@ -52,6 +52,7 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.TokenizeOper import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestMapOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; import org.apache.hyracks.algebricks.core.algebra.visitors.IQueryOperatorVisitor; /** @@ -238,6 +239,11 @@ class SubplanSpecialFlatteningCheckVisitor implements IQueryOperatorVisitor<Bool "Forward operator should have been disqualified for this rewriting!"); } + @Override + public Boolean visitWindowOperator(WindowOperator op, Void arg) throws AlgebricksException { + return visitInputs(op); + } + private boolean visitInputs(ILogicalOperator op) throws AlgebricksException { for (Mutable<ILogicalOperator> childRef : op.getInputs()) { if (childRef.getValue().accept(this, null)) { http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java index 19fe02d..6d70ba5 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java @@ -120,6 +120,7 @@ import org.apache.hyracks.algebricks.core.algebra.expressions.BroadcastExpressio import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression; import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation; import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression; +import org.apache.hyracks.algebricks.core.algebra.expressions.StatefulFunctionCallExpression; import org.apache.hyracks.algebricks.core.algebra.expressions.UnnestingFunctionCallExpression; import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression; import org.apache.hyracks.algebricks.core.algebra.functions.AlgebricksBuiltinFunctions; @@ -151,6 +152,7 @@ import org.apache.hyracks.algebricks.core.algebra.plan.ALogicalPlanImpl; import org.apache.hyracks.algebricks.core.algebra.properties.INodeDomain; import org.apache.hyracks.algebricks.core.algebra.properties.LocalOrderProperty; import org.apache.hyracks.algebricks.core.algebra.properties.OrderColumn; +import org.apache.hyracks.algebricks.core.algebra.properties.UnpartitionedPropertyComputer; import org.apache.hyracks.algebricks.core.algebra.util.OperatorPropertiesUtil; import org.apache.hyracks.api.exceptions.SourceLocation; import org.apache.hyracks.api.io.FileSplit; @@ -871,6 +873,8 @@ class LangExpressionToPlanTranslator new UnnestingFunctionCallExpression(FunctionUtil.getFunctionInfo(fi), args); ufce.setReturnsUniqueValues(BuiltinFunctions.returnsUniqueValues(fi)); f = ufce; + } else if (BuiltinFunctions.isBuiltinWindowFunction(fi)) { + f = BuiltinFunctions.makeWindowFunctionExpression(fi, args); } else { f = new ScalarFunctionCallExpression(FunctionUtil.getFunctionInfo(fi), args); } @@ -1150,7 +1154,7 @@ class LangExpressionToPlanTranslator for (Expression e : oc.getOrderbyList()) { Pair<ILogicalExpression, Mutable<ILogicalOperator>> p = langExprToAlgExpression(e, topOp); OrderModifier m = modifIter.next(); - OrderOperator.IOrder comp = (m == OrderModifier.ASC) ? OrderOperator.ASC_ORDER : OrderOperator.DESC_ORDER; + OrderOperator.IOrder comp = translateOrderModifier(m); ord.getOrderExpressions().add(new Pair<>(comp, new MutableObject<>(p.first))); topOp = p.second; } @@ -1170,6 +1174,10 @@ class LangExpressionToPlanTranslator return new Pair<>(ord, null); } + protected OrderOperator.IOrder translateOrderModifier(OrderModifier m) { + return m == OrderModifier.ASC ? OrderOperator.ASC_ORDER : OrderOperator.DESC_ORDER; + } + @Override public Pair<ILogicalOperator, LogicalVariable> visit(QuantifiedExpression qe, Mutable<ILogicalOperator> tupSource) throws CompilationException { @@ -1587,8 +1595,7 @@ class LangExpressionToPlanTranslator || k == Kind.FIELD_ACCESSOR_EXPRESSION; noNesting = noNesting || k == Kind.INDEX_ACCESSOR_EXPRESSION || k == Kind.UNARY_EXPRESSION || k == Kind.IF_EXPRESSION; - return noNesting || k == Kind.CASE_EXPRESSION; - + return noNesting || k == Kind.CASE_EXPRESSION || k == Kind.WINDOW_EXPRESSION; } protected <T> List<T> mkSingletonArrayList(T item) { http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java index 03c4bc5..dfe0208 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java @@ -38,6 +38,7 @@ import org.apache.asterix.lang.common.base.Expression.Kind; import org.apache.asterix.lang.common.base.ILangExpression; import org.apache.asterix.lang.common.clause.GroupbyClause; import org.apache.asterix.lang.common.clause.LetClause; +import org.apache.asterix.lang.common.clause.OrderbyClause; import org.apache.asterix.lang.common.expression.CallExpr; import org.apache.asterix.lang.common.expression.FieldBinding; import org.apache.asterix.lang.common.expression.GbyVariableExpressionPair; @@ -68,6 +69,7 @@ import org.apache.asterix.lang.sqlpp.clause.SelectSetOperation; import org.apache.asterix.lang.sqlpp.clause.UnnestClause; import org.apache.asterix.lang.sqlpp.expression.CaseExpression; import org.apache.asterix.lang.sqlpp.expression.SelectExpression; +import org.apache.asterix.lang.sqlpp.expression.WindowExpression; import org.apache.asterix.lang.sqlpp.optype.JoinType; import org.apache.asterix.lang.sqlpp.optype.SetOpType; import org.apache.asterix.lang.sqlpp.struct.SetOperationInput; @@ -94,6 +96,7 @@ import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression; import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator; import org.apache.hyracks.algebricks.core.algebra.base.ILogicalPlan; import org.apache.hyracks.algebricks.core.algebra.base.LogicalExpressionTag; +import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag; import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable; import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression; import org.apache.hyracks.algebricks.core.algebra.expressions.AggregateFunctionCallExpression; @@ -110,10 +113,12 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.DistinctOper import org.apache.hyracks.algebricks.core.algebra.operators.logical.InnerJoinOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterUnnestOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.NestedTupleSourceOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.ProjectOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; import org.apache.hyracks.algebricks.core.algebra.plan.ALogicalPlanImpl; import org.apache.hyracks.api.exceptions.SourceLocation; @@ -1017,4 +1022,77 @@ public class SqlppExpressionToPlanTranslator extends LangExpressionToPlanTransla opExpr.getArguments().add(new MutableObject<>(rhsExpr)); return opExpr; } + + @Override + public Pair<ILogicalOperator, LogicalVariable> visit(WindowExpression winExpr, Mutable<ILogicalOperator> tupSource) + throws CompilationException { + SourceLocation sourceLoc = winExpr.getSourceLocation(); + Mutable<ILogicalOperator> currentOpRef = tupSource; + + List<Mutable<ILogicalExpression>> partExprListOut = null; + if (winExpr.hasPartitionList()) { + List<Expression> partExprList = winExpr.getPartitionList(); + partExprListOut = new ArrayList<>(partExprList.size()); + for (Expression partExpr : partExprList) { + Pair<ILogicalOperator, LogicalVariable> partExprResult = partExpr.accept(this, currentOpRef); + VariableReferenceExpression partExprOut = new VariableReferenceExpression(partExprResult.second); + partExprOut.setSourceLocation(partExpr.getSourceLocation()); + partExprListOut.add(new MutableObject<>(partExprOut)); + currentOpRef = new MutableObject<>(partExprResult.first); + } + } + + List<Expression> orderExprList = winExpr.getOrderbyList(); + List<OrderbyClause.OrderModifier> orderModifierList = winExpr.getOrderbyModifierList(); + List<Pair<OrderOperator.IOrder, Mutable<ILogicalExpression>>> orderExprListOut = + new ArrayList<>(orderExprList.size()); + for (int i = 0, ln = orderExprList.size(); i < ln; i++) { + Expression orderExpr = orderExprList.get(i); + OrderbyClause.OrderModifier orderModifier = orderModifierList.get(i); + Pair<ILogicalOperator, LogicalVariable> orderExprResult = orderExpr.accept(this, currentOpRef); + VariableReferenceExpression orderExprOut = new VariableReferenceExpression(orderExprResult.second); + orderExprOut.setSourceLocation(orderExpr.getSourceLocation()); + OrderOperator.IOrder orderModifierOut = translateOrderModifier(orderModifier); + orderExprListOut.add(new Pair<>(orderModifierOut, new MutableObject<>(orderExprOut))); + currentOpRef = new MutableObject<>(orderExprResult.first); + } + + Expression expr = winExpr.getExpr(); + Pair<ILogicalOperator, LogicalVariable> exprResult = expr.accept(this, currentOpRef); + ILogicalOperator exprOp = exprResult.first; + if (exprOp.getOperatorTag() != LogicalOperatorTag.ASSIGN) { + throw new CompilationException(ErrorCode.COMPILATION_ERROR, sourceLoc); + } + AssignOperator exprAssignOp = (AssignOperator) exprOp; + currentOpRef = exprAssignOp.getInputs().get(0); + List<LogicalVariable> exprAssignVars = exprAssignOp.getVariables(); + if (exprAssignVars.size() != 1) { + throw new CompilationException(ErrorCode.COMPILATION_ERROR, sourceLoc); + } + LogicalVariable exprAssignVar = exprAssignVars.get(0); + List<Mutable<ILogicalExpression>> exprAssignExprs = exprAssignOp.getExpressions(); + ILogicalExpression exprAssignExpr = exprAssignExprs.get(0).getValue(); + if (exprAssignExpr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) { + throw new CompilationException(ErrorCode.COMPILATION_EXPECTED_FUNCTION_CALL, sourceLoc); + } + AbstractFunctionCallExpression callExpr = (AbstractFunctionCallExpression) exprAssignExpr; + if (BuiltinFunctions.windowFunctionRequiresOrderArgs(callExpr.getFunctionIdentifier())) { + List<Mutable<ILogicalExpression>> callArgs = callExpr.getArguments(); + for (Pair<OrderOperator.IOrder, Mutable<ILogicalExpression>> p : orderExprListOut) { + callArgs.add(new MutableObject<>(p.second.getValue().cloneExpression())); + } + } + + WindowOperator winOp = new WindowOperator(partExprListOut, orderExprListOut, exprAssignVars, exprAssignExprs); + winOp.setSourceLocation(sourceLoc); + winOp.getInputs().add(currentOpRef); + + // must return ASSIGN + LogicalVariable assignVar = context.newVar(); + AssignOperator assignOp = + new AssignOperator(assignVar, new MutableObject<>(new VariableReferenceExpression(exprAssignVar))); + assignOp.setSourceLocation(sourceLoc); + assignOp.getInputs().add(new MutableObject<>(winOp)); + return new Pair<>(assignOp, assignVar); + } } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/OperatorResourcesComputer.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/OperatorResourcesComputer.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/OperatorResourcesComputer.java index c7db521..8fc3b82 100644 --- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/OperatorResourcesComputer.java +++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/OperatorResourcesComputer.java @@ -24,6 +24,8 @@ import org.apache.hyracks.algebricks.core.algebra.base.PhysicalOperatorTag; import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractUnnestMapOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.ExchangeOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.physical.WindowPOperator; public class OperatorResourcesComputer { @@ -106,6 +108,8 @@ public class OperatorResourcesComputer { case INNERJOIN: case LEFTOUTERJOIN: return getOperatorRequiredMemory(operator, joinMemorySize); + case WINDOW: + return getWindowRequiredMemory((WindowOperator) operator); default: throw new IllegalStateException("Unrecognized operator: " + operator.getOperatorTag()); } @@ -138,4 +142,10 @@ public class OperatorResourcesComputer { } return 2L * MAX_BUFFER_PER_CONNECTION * numComputationPartitions * numComputationPartitions * frameSize; } + + private long getWindowRequiredMemory(WindowOperator op) { + WindowPOperator physOp = (WindowPOperator) op.getPhysicalOperator(); + int frameCount = physOp.isPartitionMaterialization() ? 3 : 2; + return getOperatorRequiredMemory(op, frameSize * frameCount); + } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/PlanStagesGenerator.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/PlanStagesGenerator.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/PlanStagesGenerator.java index 0023a7a..25e51bb 100644 --- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/PlanStagesGenerator.java +++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/PlanStagesGenerator.java @@ -63,6 +63,7 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.TokenizeOper import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestMapOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteResultOperator; import org.apache.hyracks.algebricks.core.algebra.visitors.ILogicalOperatorVisitor; @@ -316,6 +317,12 @@ public class PlanStagesGenerator implements ILogicalOperatorVisitor<Void, Void> return null; } + @Override + public Void visitWindowOperator(WindowOperator op, Void arg) throws AlgebricksException { + visit(op); + return null; + } + public List<PlanStage> getStages() { return stages; } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/RequiredCapacityVisitor.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/RequiredCapacityVisitor.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/RequiredCapacityVisitor.java index 368a244..c0fca94 100644 --- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/RequiredCapacityVisitor.java +++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/resource/RequiredCapacityVisitor.java @@ -61,8 +61,10 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.TokenizeOper import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestMapOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.WindowOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteResultOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.physical.WindowPOperator; import org.apache.hyracks.algebricks.core.algebra.visitors.ILogicalOperatorVisitor; import org.apache.hyracks.api.job.resource.IClusterCapacity; @@ -311,6 +313,17 @@ public class RequiredCapacityVisitor implements ILogicalOperatorVisitor<Void, Vo return null; } + @Override + public Void visitWindowOperator(WindowOperator op, Void arg) throws AlgebricksException { + WindowPOperator physOp = (WindowPOperator) op.getPhysicalOperator(); + visitInternal(op, true); + addOutputBuffer(op); // + previous frame + if (physOp.isPartitionMaterialization()) { + addOutputBuffer(op); // + run frame + } + return null; + } + // Calculates the memory usage for exchange operators. private void calculateMemoryUsageForExchange(ExchangeOperator op) throws AlgebricksException { visitInternal(op, false); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/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 new file mode 100644 index 0000000..25613c6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/window/window_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/fdedf626/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 new file mode 100644 index 0000000..91e9cfc --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/window/window_01.plan @@ -0,0 +1,30 @@ +-- 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/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.1.ddl.sqlpp new file mode 100644 index 0000000..9b6169e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.1.ddl.sqlpp @@ -0,0 +1,72 @@ +/* + * 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 DENSE_RANK() + * 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 dataset tRnd(t1Type) primary key c1; + +create function q0_rnd() { + let + rnd = tobigint((select value tRnd.rnd from tRnd where c1 = 1)[0] * 1000), + p = case when rnd >= 10 then rnd else 10 end, + n = tobigint( 1000 * 100 / p) + select p, n +}; + +create function q1_dense_rank(P, N, D) { + from + t1, + range(t1.one, P) p, + range(t1.one, N) n, + range(t1.one, D) d + let + result_expected = N - n + 1, + result_actual = dense_rank() over ( partition by t1.c2, p order by n desc ), + result_delta = result_expected - result_actual + select + min(result_delta) min_delta, + max(result_delta) max_delta +}; + +create function q2_dense_rank_no_partition(N, D) { + from + t1, + range(t1.one, N) n, + range(t1.one, D) d + let + result_expected = (t1.c2 - 1) * N + N - n + 1, + result_actual = dense_rank() over ( order by t1.c2, n desc ), + result_delta = result_expected - result_actual + select + min(result_delta) min_delta, + max(result_delta) max_delta +}; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.2.update.sqlpp new file mode 100644 index 0000000..6386573 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.2.update.sqlpp @@ -0,0 +1,36 @@ +/* + * 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 DENSE_RANK() + * Expected Res : SUCCESS + */ + +use test; + +set `import-private-functions` `true`; + +insert into t1 +select element { "c1":1, "c2": 1, "one": 1 }; + +insert into t1 +select v c1, v c2, 1 one +from range(2, (select value count(*) from storage_components("test","t1") t)[0]) v; + +insert into tRnd +select 1 c1, random() rnd; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.3.query.sqlpp new file mode 100644 index 0000000..b88e7ac --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.3.query.sqlpp @@ -0,0 +1,26 @@ +/* + * 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 DENSE_RANK() + * Expected Res : SUCCESS + */ + +use test; + +q1_dense_rank(1, 1, 3) \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.4.query.sqlpp new file mode 100644 index 0000000..5c6e06a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.4.query.sqlpp @@ -0,0 +1,26 @@ +/* + * 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 DENSE_RANK() + * Expected Res : SUCCESS + */ + +use test; + +q1_dense_rank(1000, 10, 3) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.5.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.5.query.sqlpp new file mode 100644 index 0000000..29c8366 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.5.query.sqlpp @@ -0,0 +1,26 @@ +/* + * 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 DENSE_RANK() + * Expected Res : SUCCESS + */ + +use test; + +q1_dense_rank(10, 1000, 3) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.6.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.6.query.sqlpp new file mode 100644 index 0000000..1de6526 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.6.query.sqlpp @@ -0,0 +1,27 @@ +/* + * 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 DENSE_RANK() + * Expected Res : SUCCESS + */ + +use test; + +from q0_rnd() rnd +select q1_dense_rank(rnd.p, rnd.n, 3) res, rnd.p, rnd.n \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.7.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.7.query.sqlpp new file mode 100644 index 0000000..592dc4f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/dense_rank_01/dense_rank_01.7.query.sqlpp @@ -0,0 +1,26 @@ +/* + * 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 DENSE_RANK() without partition clause + * Expected Res : SUCCESS + */ + +use test; + +q2_dense_rank_no_partition(7, 11) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.1.ddl.sqlpp new file mode 100644 index 0000000..b7aa06a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.1.ddl.sqlpp @@ -0,0 +1,33 @@ +/* + * 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 : Miscellaneous window function tests + * 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; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.2.update.sqlpp new file mode 100644 index 0000000..21fd00e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.2.update.sqlpp @@ -0,0 +1,33 @@ +/* + * 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 : Miscellaneous window function tests + * Expected Res : SUCCESS + */ + +use test; + +set `import-private-functions` `true`; + +insert into t1 +select element { "c1":1, "c2": 1, "one": 1 }; + +insert into t1 +select v c1, v c2, 1 one +from range(2, (select value count(*) from storage_components("test","t1") t)[0]) v; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.3.query.sqlpp new file mode 100644 index 0000000..2948a3f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.3.query.sqlpp @@ -0,0 +1,48 @@ +/* + * 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 the same statement + * Expected Res : SUCCESS + */ + +use test; + +with P as 10, N as 10, D as 4 + +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 + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.4.query.sqlpp new file mode 100644 index 0000000..d3f7916 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.4.query.sqlpp @@ -0,0 +1,32 @@ +/* + * 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 after group by + * Expected Res : SUCCESS + */ + +use test; + +with P as 4 + +from t1, range(t1.one, P) p +group by p +select p, row_number() over (order by p desc) rn +order by p + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.5.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.5.query.sqlpp new file mode 100644 index 0000000..c0548c3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/misc_01/misc_01.5.query.sqlpp @@ -0,0 +1,32 @@ +/* + * 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 inside nested plan + * Expected Res : SUCCESS + */ + +use test; + +with P as 4 + +from t1, range(t1.one, P) p +group by p group as g +select p, ( from g select g.t1.c1, row_number() over ( order by g.t1.c2 desc ) rn order by g.t1.c1 ) gg +order by p + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.1.ddl.sqlpp new file mode 100644 index 0000000..a81cab7 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.1.ddl.sqlpp @@ -0,0 +1,75 @@ +/* + * 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 NTILE() + * 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 dataset tRnd(t1Type) primary key c1; + +create function q0_rnd() { + let + rnd = tobigint((select value tRnd.rnd from tRnd where c1 = 1)[0] * 1000), + p = case when rnd >= 10 then rnd else 10 end, + n = tobigint( 1000 * 100 / p) + select p, n +}; + +create function q1_ntile(P, N, D) { + from + t1, + range(t1.one, P) p, + range(t1.one, N) n, + range(t1.one, D) d + let + rownum = (N - n) * D + d, + result_expected = tobigint( (rownum - 1) / N ) + 1, + result_actual = ntile(D) over ( partition by t1.c2, p order by n desc, d ), + result_delta = result_expected - result_actual + select + min(result_delta) min_delta, + max(result_delta) max_delta +}; + +create function q2_ntile_no_partition(N, D) { + from + t1, + range(t1.one, N) n, + range(t1.one, D) d + let + rownum = ((t1.c2 - 1) * N * D) + ((N - n) * D + d), + result_expected = tobigint( (rownum - 1) / (4 * N) ) + 1, + result_actual = ntile(D) over ( order by t1.c2, n desc, d ), + result_delta = result_expected - result_actual + select + min(result_delta) min_delta, + max(result_delta) max_delta +}; + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.2.update.sqlpp new file mode 100644 index 0000000..6286e28 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.2.update.sqlpp @@ -0,0 +1,36 @@ +/* + * 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 NTILE() + * Expected Res : SUCCESS + */ + +use test; + +set `import-private-functions` `true`; + +insert into t1 +select element { "c1":1, "c2": 1, "one": 1 }; + +insert into t1 +select v c1, v c2, 1 one +from range(2, (select value count(*) from storage_components("test","t1") t)[0]) v; + +insert into tRnd +select 1 c1, random() rnd; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.3.query.sqlpp new file mode 100644 index 0000000..245bd58 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.3.query.sqlpp @@ -0,0 +1,26 @@ +/* + * 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 NTILE() + * Expected Res : SUCCESS + */ + +use test; + +q1_ntile(1, 1, 2) \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.4.query.sqlpp new file mode 100644 index 0000000..405c2ea --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.4.query.sqlpp @@ -0,0 +1,26 @@ +/* + * 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 NTILE() + * Expected Res : SUCCESS + */ + +use test; + +q1_ntile(1000, 10, 3) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/fdedf626/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.5.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.5.query.sqlpp new file mode 100644 index 0000000..5655f18 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ntile_01/ntile_01.5.query.sqlpp @@ -0,0 +1,26 @@ +/* + * 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 NTILE() + * Expected Res : SUCCESS + */ + +use test; + +q1_ntile(10, 1000, 4)
