You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by dl...@apache.org on 2018/11/01 03:54:43 UTC
[5/5] asterixdb git commit: [ASTERIXDB-2466][FUN] Implement window functions

[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 <je...@fulliautomatix.ics.uci.edu>
Contrib: Jenkins <je...@fulliautomatix.ics.uci.edu>
Integration-Tests: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Ali Alsuliman <al...@gmail.com>


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 <dm...@couchbase.com>
Authored: Tue Oct 30 18:12:40 2018 -0700
Committer: Dmitry Lychagin <dm...@couchbase.com>
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)