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 2021/07/16 19:47:23 UTC
[asterixdb] branch master updated: [ASTERIXDB-2930][COMP] Support
ORDER BY NULLS FIRST, LAST
This is an automated email from the ASF dual-hosted git repository.
dlych pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/asterixdb.git
The following commit(s) were added to refs/heads/master by this push:
new e5e3ad9 [ASTERIXDB-2930][COMP] Support ORDER BY NULLS FIRST, LAST
e5e3ad9 is described below
commit e5e3ad93c8d38f1806988f1f191879fb050dd657
Author: Dmitry Lychagin <dm...@couchbase.com>
AuthorDate: Thu Jul 15 12:44:47 2021 -0700
[ASTERIXDB-2930][COMP] Support ORDER BY NULLS FIRST, LAST
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
- Add NULLS FIRST, LAST modifier to ORDER BY clause,
including when it is used inside window function calls
- Add testcases and update documentation
Change-Id: I517336d6b56b488222bd6ba0fecb2b14f2024aa5
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/12344
Integration-Tests: Jenkins <je...@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Glenn Galvizo <gg...@uci.edu>
---
.../translator/LangExpressionToPlanTranslator.java | 41 +++++--
.../SqlppExpressionToPlanTranslator.java | 6 +-
.../queries/orderby-nulls-first-last.sqlpp} | 9 +-
.../results/orderby-nulls-first-last.plan | 13 +++
.../order-by-2.1.query.sqlpp} | 32 +++--
.../order-by-2.10.query.sqlpp} | 32 +++--
.../order-by-2.11.query.sqlpp} | 32 +++--
.../order-by-2.12.query.sqlpp} | 32 +++--
.../order-by-2.13.query.sqlpp} | 32 +++--
.../order-by-2.14.query.sqlpp} | 32 +++--
.../order-by-2.15.query.sqlpp} | 32 +++--
.../order-by-2.2.query.sqlpp} | 32 +++--
.../order-by-2.3.query.sqlpp} | 32 +++--
.../order-by-2.4.query.sqlpp} | 32 +++--
.../order-by-2.5.query.sqlpp} | 32 +++--
.../order-by-2.6.query.sqlpp} | 32 +++--
.../order-by-2.7.query.sqlpp} | 32 +++--
.../order-by-2.8.query.sqlpp} | 32 +++--
.../order-by-2.9.query.sqlpp} | 32 +++--
.../order-by-3-negative.1.query.sqlpp} | 6 +-
.../order-by-3-negative.2.query.sqlpp} | 6 +-
.../order-by-from-dataset-2.1.ddl.sqlpp} | 6 +-
.../order-by-from-dataset-2.10.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.11.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.12.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.13.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.14.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.15.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.16.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.17.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.2.update.sqlpp} | 29 ++++-
.../order-by-from-dataset-2.3.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.4.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.5.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.6.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.7.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.8.query.sqlpp} | 4 +-
.../order-by-from-dataset-2.9.query.sqlpp} | 4 +-
.../order-by-from-dataset.1.ddl.sqlpp | 2 -
.../order-by-from-dataset.3.query.sqlpp | 4 +-
...y.sqlpp => order-by-from-dataset.4.query.sqlpp} | 4 +-
...y.sqlpp => order-by-from-dataset.5.query.sqlpp} | 4 +-
...y.sqlpp => order-by-from-dataset.6.query.sqlpp} | 4 +-
...y.sqlpp => order-by-from-dataset.7.query.sqlpp} | 4 +-
...y.sqlpp => order-by-from-dataset.8.query.sqlpp} | 4 +-
.../null-missing/order-by/order-by.1.query.sqlpp | 1 -
...der-by.1.query.sqlpp => order-by.2.query.sqlpp} | 3 +-
...der-by.1.query.sqlpp => order-by.3.query.sqlpp} | 3 +-
...der-by.1.query.sqlpp => order-by.4.query.sqlpp} | 3 +-
...der-by.1.query.sqlpp => order-by.5.query.sqlpp} | 3 +-
...der-by.1.query.sqlpp => order-by.6.query.sqlpp} | 3 +-
.../win_null_missing.4.query.sqlpp | 41 +++++++
.../win_null_missing.5.query.sqlpp | 41 +++++++
.../null-missing/order-by-2/order-by-2.1.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.10.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.11.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.12.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.13.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.14.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.15.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.2.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.3.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.4.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.5.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.6.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.7.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.8.adm | 20 ++++
.../null-missing/order-by-2/order-by-2.9.adm | 20 ++++
.../order-by-from-dataset-2.10.adm | 20 ++++
.../order-by-from-dataset-2.11.adm | 20 ++++
.../order-by-from-dataset-2.12.adm | 20 ++++
.../order-by-from-dataset-2.13.adm | 20 ++++
.../order-by-from-dataset-2.14.adm | 20 ++++
.../order-by-from-dataset-2.15.adm | 20 ++++
.../order-by-from-dataset-2.16.adm | 20 ++++
.../order-by-from-dataset-2.17.adm | 20 ++++
.../order-by-from-dataset-2.3.adm | 20 ++++
.../order-by-from-dataset-2.4.adm | 20 ++++
.../order-by-from-dataset-2.5.adm | 20 ++++
.../order-by-from-dataset-2.6.adm | 20 ++++
.../order-by-from-dataset-2.7.adm | 20 ++++
.../order-by-from-dataset-2.8.adm | 20 ++++
.../order-by-from-dataset-2.9.adm | 20 ++++
...m-dataset.1.adm => order-by-from-dataset.3.adm} | 0
...m-dataset.1.adm => order-by-from-dataset.4.adm} | 0
...m-dataset.1.adm => order-by-from-dataset.5.adm} | 4 +-
...m-dataset.1.adm => order-by-from-dataset.6.adm} | 8 +-
...m-dataset.1.adm => order-by-from-dataset.7.adm} | 6 +-
...m-dataset.1.adm => order-by-from-dataset.8.adm} | 8 +-
.../results/null-missing/order-by/order-by.2.adm | 5 +
.../results/null-missing/order-by/order-by.3.adm | 5 +
.../results/null-missing/order-by/order-by.4.adm | 5 +
.../results/null-missing/order-by/order-by.5.adm | 5 +
.../results/null-missing/order-by/order-by.6.adm | 5 +
.../window/win_null_missing/win_null_missing.4.adm | 6 +
.../window/win_null_missing/win_null_missing.5.adm | 6 +
.../test/resources/runtimets/testsuite_sqlpp.xml | 17 +++
asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf | 4 +-
.../asterix-doc/src/main/markdown/sqlpp/3_query.md | 7 +-
.../asterix/lang/common/clause/OrderbyClause.java | 18 ++-
.../CloneAndSubstituteVariablesVisitor.java | 3 +-
.../lang/common/visitor/FormatPrintVisitor.java | 12 +-
.../lang/common/visitor/QueryPrintVisitor.java | 7 +-
.../lang/sqlpp/expression/WindowExpression.java | 20 +++-
.../lang/sqlpp/visitor/DeepCopyVisitor.java | 15 ++-
.../lang/sqlpp/visitor/SqlppAstPrintVisitor.java | 6 +-
.../SqlppCloneAndSubstituteVariablesVisitor.java | 12 +-
.../sqlpp/visitor/SqlppFormatPrintVisitor.java | 3 +-
.../asterix-lang-sqlpp/src/main/javacc/SQLPP.jj | 129 +++++++++++++--------
109 files changed, 1401 insertions(+), 295 deletions(-)
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 7f3644d..b76fc3e 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
@@ -48,6 +48,7 @@ import org.apache.asterix.lang.common.clause.GroupbyClause;
import org.apache.asterix.lang.common.clause.LetClause;
import org.apache.asterix.lang.common.clause.LimitClause;
import org.apache.asterix.lang.common.clause.OrderbyClause;
+import org.apache.asterix.lang.common.clause.OrderbyClause.NullOrderModifier;
import org.apache.asterix.lang.common.clause.OrderbyClause.OrderModifier;
import org.apache.asterix.lang.common.clause.WhereClause;
import org.apache.asterix.lang.common.expression.CallExpr;
@@ -1266,13 +1267,17 @@ abstract class LangExpressionToPlanTranslator
SourceLocation sourceLoc = oc.getSourceLocation();
OrderOperator ord = new OrderOperator();
ord.setSourceLocation(sourceLoc);
- Iterator<OrderModifier> modifIter = oc.getModifierList().iterator();
+ List<Expression> orderbyList = oc.getOrderbyList();
+ List<OrderModifier> modifierList = oc.getModifierList();
+ List<NullOrderModifier> nullModifierList = oc.getNullModifierList();
Mutable<ILogicalOperator> topOp = tupSource;
- for (Expression e : oc.getOrderbyList()) {
+ for (int i = 0, n = orderbyList.size(); i < n; i++) {
+ Expression e = orderbyList.get(i);
Pair<ILogicalExpression, Mutable<ILogicalOperator>> p = langExprToAlgExpression(e, topOp);
- OrderModifier m = modifIter.next();
- OrderOperator.IOrder comp = translateOrderModifier(m);
- ord.getOrderExpressions().add(new Pair<>(comp, new MutableObject<>(p.first)));
+ ILogicalExpression obyExpr = p.first;
+ OrderModifier modifier = modifierList.get(i);
+ NullOrderModifier nullModifier = nullModifierList.get(i);
+ addOrderByExpression(ord.getOrderExpressions(), obyExpr, modifier, nullModifier);
topOp = p.second;
}
ord.getInputs().add(topOp);
@@ -1283,18 +1288,40 @@ abstract class LangExpressionToPlanTranslator
ord.getAnnotations().put(OperatorAnnotations.MAX_NUMBER_FRAMES, oc.getNumFrames());
}
if (oc.getRangeMap() != null) {
- Iterator<OrderModifier> orderModifIter = oc.getModifierList().iterator();
- boolean ascending = orderModifIter.next() == OrderModifier.ASC;
+ boolean ascending = modifierList.get(0) == OrderModifier.ASC;
RangeMapBuilder.verifyRangeOrder(oc.getRangeMap(), ascending, sourceLoc);
ord.getAnnotations().put(OperatorAnnotations.USE_STATIC_RANGE, oc.getRangeMap());
}
return new Pair<>(ord, null);
}
+ protected void addOrderByExpression(List<Pair<OrderOperator.IOrder, Mutable<ILogicalExpression>>> outOrderList,
+ ILogicalExpression obyExpr, OrderModifier modifier, NullOrderModifier nullModifier) {
+ OrderOperator.IOrder comp = translateOrderModifier(modifier);
+ ILogicalExpression nullModifierExpr = translateNullOrderModifier(obyExpr, modifier, nullModifier);
+ if (nullModifierExpr != null) {
+ outOrderList.add(new Pair<>(comp, new MutableObject<>(nullModifierExpr)));
+ }
+ outOrderList.add(new Pair<>(comp, new MutableObject<>(obyExpr)));
+ }
+
protected OrderOperator.IOrder translateOrderModifier(OrderModifier m) {
return m == OrderModifier.ASC ? OrderOperator.ASC_ORDER : OrderOperator.DESC_ORDER;
}
+ protected ILogicalExpression translateNullOrderModifier(ILogicalExpression obyExpr, OrderModifier m,
+ NullOrderModifier nm) {
+ if ((m == OrderModifier.ASC && nm == NullOrderModifier.LAST)
+ || (m == OrderModifier.DESC && nm == NullOrderModifier.FIRST)) {
+ AbstractFunctionCallExpression isUnknownExpr =
+ createFunctionCallExpression(BuiltinFunctions.IS_UNKNOWN, obyExpr.getSourceLocation());
+ isUnknownExpr.getArguments().add(new MutableObject<>(obyExpr.cloneExpression()));
+ return isUnknownExpr;
+ } else {
+ return null;
+ }
+ }
+
@Override
public Pair<ILogicalOperator, LogicalVariable> visit(QuantifiedExpression qe, Mutable<ILogicalOperator> tupSource)
throws CompilationException {
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 95026a5..48a8f4b 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
@@ -1178,16 +1178,16 @@ public class SqlppExpressionToPlanTranslator extends LangExpressionToPlanTransla
}
List<Expression> orderExprList = winExpr.getOrderbyList();
List<OrderbyClause.OrderModifier> orderModifierList = winExpr.getOrderbyModifierList();
+ List<OrderbyClause.NullOrderModifier> nullOrderModifierList = winExpr.getOrderbyNullModifierList();
orderExprCount = orderExprList.size();
orderExprListOut = new ArrayList<>(orderExprCount);
for (int i = 0; i < orderExprCount; i++) {
Expression orderExpr = orderExprList.get(i);
OrderbyClause.OrderModifier orderModifier = orderModifierList.get(i);
+ OrderbyClause.NullOrderModifier nullOrderModifier = nullOrderModifierList.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)));
+ addOrderByExpression(orderExprListOut, orderExprOut, orderModifier, nullOrderModifier);
currentOpRef = new MutableObject<>(orderExprResult.first);
}
} else if (winExpr.hasFrameDefinition()) {
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-nulls-first-last.sqlpp
similarity index 86%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp
copy to asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-nulls-first-last.sqlpp
index 9ac564e..db4b75b 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-nulls-first-last.sqlpp
@@ -17,16 +17,17 @@
* under the License.
*/
-
-
drop dataverse test if exists;
create dataverse test;
use test;
create type TestType as {
- b: bigint
+ c: bigint
};
-create dataset data(TestType) primary key b;
+create dataset data(TestType) primary key c;
+SELECT VALUE d
+FROM data AS d
+ORDER BY d.a ASC NULLS LAST, d.b DESC NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/orderby-nulls-first-last.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/orderby-nulls-first-last.plan
new file mode 100644
index 0000000..fe5e86f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/orderby-nulls-first-last.plan
@@ -0,0 +1,13 @@
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- SORT_MERGE_EXCHANGE [$$14(ASC), $$17(ASC), $$15(DESC), $$18(DESC) ] |PARTITIONED|
+ -- STABLE_SORT [$$14(ASC), $$17(ASC), $$15(DESC), $$18(DESC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (test.data) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.1.query.sqlpp
similarity index 62%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.1.query.sqlpp
index 567ca72..293f824 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.1.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a, d.b ASC;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.10.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.10.query.sqlpp
index 567ca72..38ec82c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.10.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a ASC NULLS LAST, d.b DESC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.11.query.sqlpp
similarity index 62%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.11.query.sqlpp
index 567ca72..bf17f9f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.11.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a DESC, d.b DESC;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.12.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.12.query.sqlpp
index 567ca72..acb7ea0 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.12.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a DESC NULLS FIRST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.13.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.13.query.sqlpp
index 567ca72..616c160 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.13.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a DESC NULLS FIRST, d.b DESC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.14.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.14.query.sqlpp
index 567ca72..09cc382 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.14.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a DESC NULLS LAST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.15.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.15.query.sqlpp
index 567ca72..09cc382 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.15.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a DESC NULLS LAST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.2.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.2.query.sqlpp
index 567ca72..8c585c1 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.2.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a NULLS FIRST, d.b ASC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.3.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.3.query.sqlpp
index 567ca72..31c41ce 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.3.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a NULLS FIRST, d.b ASC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.4.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.4.query.sqlpp
index 567ca72..af5c3c5 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.4.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a NULLS LAST, d.b ASC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.5.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.5.query.sqlpp
index 567ca72..e48bbf7 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.5.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a NULLS LAST, d.b ASC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.6.query.sqlpp
similarity index 62%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.6.query.sqlpp
index 567ca72..5566c48 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.6.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a ASC, d.b DESC;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.7.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.7.query.sqlpp
index 567ca72..edb760d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.7.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a ASC NULLS FIRST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.8.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.8.query.sqlpp
index 567ca72..5fc05de 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.8.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a ASC NULLS FIRST, d.b DESC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.9.query.sqlpp
similarity index 61%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.9.query.sqlpp
index 567ca72..96b33a9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.9.query.sqlpp
@@ -17,15 +17,33 @@
* under the License.
*/
+WITH data AS [
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-WITH data AS
-[ {"a":1, "b":2},
- {"a": NULL, "b": 7},
- {"b":4},
- {"a": "1", "b":8},
- {"a": 3.0, "b":9}
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
]
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a ASC NULLS LAST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.1.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.1.query.sqlpp
index 567ca72..1d9941f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.1.query.sqlpp
@@ -16,7 +16,9 @@
* specific language governing permissions and limitations
* under the License.
*/
-
+/*
+ * Failure: Syntax error
+ */
WITH data AS
[ {"a":1, "b":2},
@@ -28,4 +30,4 @@ WITH data AS
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a NULLS;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.2.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.2.query.sqlpp
index 567ca72..40a9cad 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.2.query.sqlpp
@@ -16,7 +16,9 @@
* specific language governing permissions and limitations
* under the License.
*/
-
+/*
+ * Failure: Syntax error
+ */
WITH data AS
[ {"a":1, "b":2},
@@ -28,4 +30,4 @@ WITH data AS
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a NULLS NULLS;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.1.ddl.sqlpp
similarity index 93%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.1.ddl.sqlpp
index 9ac564e..4c098ec 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.1.ddl.sqlpp
@@ -17,16 +17,14 @@
* under the License.
*/
-
-
drop dataverse test if exists;
create dataverse test;
use test;
create type TestType as {
- b: bigint
+ c: bigint
};
-create dataset data(TestType) primary key b;
+create dataset data(TestType) primary key c;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.10.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.10.query.sqlpp
index 3758ff2..a5af7c3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.10.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a ASC NULLS FIRST, d.b DESC NULLS LAST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.11.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.11.query.sqlpp
index 3758ff2..66978dc 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.11.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a ASC NULLS LAST, d.b DESC NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.12.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.12.query.sqlpp
index 3758ff2..f0c70cc 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.12.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a ASC NULLS LAST, d.b DESC NULLS LAST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.13.query.sqlpp
similarity index 96%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.13.query.sqlpp
index 3758ff2..3b6d826 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.13.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a DESC, d.b DESC;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.14.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.14.query.sqlpp
index 3758ff2..ec1ec83 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.14.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a DESC NULLS FIRST, d.b DESC NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.15.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.15.query.sqlpp
index 3758ff2..0521b07 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.15.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a DESC NULLS FIRST, d.b DESC NULLS LAST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.16.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.16.query.sqlpp
index 3758ff2..8e85354 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.16.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a DESC NULLS LAST, d.b DESC NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.17.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.17.query.sqlpp
index 3758ff2..583b5cc 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.17.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a DESC NULLS LAST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.2.update.sqlpp
similarity index 62%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.2.update.sqlpp
index 3758ff2..a642f52 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.2.update.sqlpp
@@ -19,8 +19,31 @@
USE test;
+INSERT INTO data (
+[
+ {"a":1,"c":1},
+ {"a":1,"b":1,"c":2},
+ {"a":1,"b":NULL,"c":3},
+ {"a":1,"b":2,"c":4},
-SELECT VALUE d
-FROM data AS d
-ORDER BY d.a;
+ {"a":NULL,"c":5},
+ {"a":NULL,"b":3,"c":6},
+ {"a":NULL,"b":NULL,"c":7},
+ {"a":NULL,"b":4,"c":8},
+ {"c":9},
+ {"b":5,"c":10},
+ {"b":NULL,"c":11},
+ {"b":6,"c":12},
+
+ {"a":"1","c":13},
+ {"a":"1","b":7,"c":14},
+ {"a":"1","b":NULL,"c":15},
+ {"a":"1","b":8,"c":16},
+
+ {"a":3.0,"c":17},
+ {"a":3.0,"b":9,"c":18},
+ {"a":3.0,"b":NULL,"c":19},
+ {"a":3.0,"b":10,"c":20}
+]
+);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.3.query.sqlpp
similarity index 97%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.3.query.sqlpp
index 3758ff2..d66429f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.3.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a, d.b ASC;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.4.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.4.query.sqlpp
index 3758ff2..04644fa 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.4.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a NULLS FIRST, d.b ASC NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.5.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.5.query.sqlpp
index 3758ff2..6173a3e 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.5.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a NULLS FIRST, d.b ASC NULLS LAST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.6.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.6.query.sqlpp
index 3758ff2..e25a0c4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.6.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a NULLS LAST, d.b ASC NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.7.query.sqlpp
similarity index 95%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.7.query.sqlpp
index 3758ff2..b630030 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.7.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a NULLS LAST, d.b ASC NULLS LAST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.8.query.sqlpp
similarity index 96%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.8.query.sqlpp
index 3758ff2..fadd49e 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.8.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a ASC, d.b DESC;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.9.query.sqlpp
similarity index 94%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.9.query.sqlpp
index 3758ff2..aab89f1 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.9.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a ASC NULLS FIRST, d.b DESC NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp
index 9ac564e..d58c3eb 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.1.ddl.sqlpp
@@ -17,8 +17,6 @@
* under the License.
*/
-
-
drop dataverse test if exists;
create dataverse test;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
index 3758ff2..44d2a73 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.4.query.sqlpp
similarity index 97%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.4.query.sqlpp
index 3758ff2..538870f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.4.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.5.query.sqlpp
similarity index 96%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.5.query.sqlpp
index 3758ff2..e4fe10d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.5.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a ASC NULLS LAST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.6.query.sqlpp
similarity index 97%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.6.query.sqlpp
index 3758ff2..243b0f4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.6.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a DESC;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.7.query.sqlpp
similarity index 96%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.7.query.sqlpp
index 3758ff2..2a3ad56 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.7.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a DESC NULLS FIRST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.8.query.sqlpp
similarity index 96%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.8.query.sqlpp
index 3758ff2..5b5c067 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.8.query.sqlpp
@@ -19,8 +19,6 @@
USE test;
-
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
-
+ORDER BY d.a DESC NULLS LAST;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
index 567ca72..16f939d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
@@ -17,7 +17,6 @@
* under the License.
*/
-
WITH data AS
[ {"a":1, "b":2},
{"a": NULL, "b": 7},
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.2.query.sqlpp
similarity index 97%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.2.query.sqlpp
index 567ca72..0c70a22 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.2.query.sqlpp
@@ -17,7 +17,6 @@
* under the License.
*/
-
WITH data AS
[ {"a":1, "b":2},
{"a": NULL, "b": 7},
@@ -28,4 +27,4 @@ WITH data AS
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.3.query.sqlpp
similarity index 97%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.3.query.sqlpp
index 567ca72..0a77b97 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.3.query.sqlpp
@@ -17,7 +17,6 @@
* under the License.
*/
-
WITH data AS
[ {"a":1, "b":2},
{"a": NULL, "b": 7},
@@ -28,4 +27,4 @@ WITH data AS
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a ASC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.4.query.sqlpp
similarity index 98%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.4.query.sqlpp
index 567ca72..5c345cc 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.4.query.sqlpp
@@ -17,7 +17,6 @@
* under the License.
*/
-
WITH data AS
[ {"a":1, "b":2},
{"a": NULL, "b": 7},
@@ -28,4 +27,4 @@ WITH data AS
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a DESC;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.5.query.sqlpp
similarity index 96%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.5.query.sqlpp
index 567ca72..efbeb55 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.5.query.sqlpp
@@ -17,7 +17,6 @@
* under the License.
*/
-
WITH data AS
[ {"a":1, "b":2},
{"a": NULL, "b": 7},
@@ -28,4 +27,4 @@ WITH data AS
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.6.query.sqlpp
similarity index 96%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.6.query.sqlpp
index 567ca72..2f26dc1 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.1.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.6.query.sqlpp
@@ -17,7 +17,6 @@
* under the License.
*/
-
WITH data AS
[ {"a":1, "b":2},
{"a": NULL, "b": 7},
@@ -28,4 +27,4 @@ WITH data AS
SELECT VALUE d
FROM data AS d
-ORDER BY d.a;
+ORDER BY d.a DESC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_null_missing/win_null_missing.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_null_missing/win_null_missing.4.query.sqlpp
new file mode 100644
index 0000000..b1c215c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_null_missing/win_null_missing.4.query.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * 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 : ORDER BY MISSING/NULL/complex NULLS FIRST
+ * Expected Res : SUCCESS
+ */
+
+from [
+ { "y": "m" },
+ { "x": null, "y": "n" },
+ { "x": 1, "y": "i" },
+ { "x": "a", "y": "s" },
+ { "x": [ "b" ], "y": "a" },
+ { "x": { "c": 1 }, "y": "o" }
+] t
+select
+ nth_value(y, 0) over (order by x nulls first rows between unbounded preceding and unbounded following) w0,
+ nth_value(y, 1) over (order by x nulls first rows between unbounded preceding and unbounded following) w1,
+ nth_value(y, 2) over (order by x nulls first rows between unbounded preceding and unbounded following) w2,
+ nth_value(y, 3) over (order by x nulls first rows between unbounded preceding and unbounded following) w3,
+ nth_value(y, 4) over (order by x nulls first rows between unbounded preceding and unbounded following) w4,
+ nth_value(y, 5) over (order by x nulls first rows between unbounded preceding and unbounded following) w5,
+ nth_value(y, 6) over (order by x nulls first rows between unbounded preceding and unbounded following) w6,
+ x, y
+order by x, y
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_null_missing/win_null_missing.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_null_missing/win_null_missing.5.query.sqlpp
new file mode 100644
index 0000000..8d35f92
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_null_missing/win_null_missing.5.query.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * 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 : ORDER BY MISSING/NULL/complex NULLS LAST
+ * Expected Res : SUCCESS
+ */
+
+from [
+ { "y": "m" },
+ { "x": null, "y": "n" },
+ { "x": 1, "y": "i" },
+ { "x": "a", "y": "s" },
+ { "x": [ "b" ], "y": "a" },
+ { "x": { "c": 1 }, "y": "o" }
+] t
+select
+ nth_value(y, 0) over (order by x nulls last rows between unbounded preceding and unbounded following) w0,
+ nth_value(y, 1) over (order by x nulls last rows between unbounded preceding and unbounded following) w1,
+ nth_value(y, 2) over (order by x nulls last rows between unbounded preceding and unbounded following) w2,
+ nth_value(y, 3) over (order by x nulls last rows between unbounded preceding and unbounded following) w3,
+ nth_value(y, 4) over (order by x nulls last rows between unbounded preceding and unbounded following) w4,
+ nth_value(y, 5) over (order by x nulls last rows between unbounded preceding and unbounded following) w5,
+ nth_value(y, 6) over (order by x nulls last rows between unbounded preceding and unbounded following) w6,
+ x, y
+order by x, y
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.1.adm
new file mode 100644
index 0000000..18cf1ed
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.1.adm
@@ -0,0 +1,20 @@
+{ "c": 9 }
+{ "b": null, "c": 11 }
+{ "b": 5, "c": 10 }
+{ "b": 6, "c": 12 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": 8, "c": 16 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.10.adm
new file mode 100644
index 0000000..8c27a90
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.10.adm
@@ -0,0 +1,20 @@
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.11.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.11.adm
new file mode 100644
index 0000000..db745b0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.11.adm
@@ -0,0 +1,20 @@
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.12.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.12.adm
new file mode 100644
index 0000000..69dbeb6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.12.adm
@@ -0,0 +1,20 @@
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.13.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.13.adm
new file mode 100644
index 0000000..cab4536
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.13.adm
@@ -0,0 +1,20 @@
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.14.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.14.adm
new file mode 100644
index 0000000..2206ce8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.14.adm
@@ -0,0 +1,20 @@
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.15.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.15.adm
new file mode 100644
index 0000000..2206ce8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.15.adm
@@ -0,0 +1,20 @@
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.2.adm
new file mode 100644
index 0000000..18cf1ed
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.2.adm
@@ -0,0 +1,20 @@
+{ "c": 9 }
+{ "b": null, "c": 11 }
+{ "b": 5, "c": 10 }
+{ "b": 6, "c": 12 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": 8, "c": 16 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.3.adm
new file mode 100644
index 0000000..ab6cbad
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.3.adm
@@ -0,0 +1,20 @@
+{ "b": 5, "c": 10 }
+{ "b": 6, "c": 12 }
+{ "c": 9 }
+{ "b": null, "c": 11 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": null, "c": 15 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.4.adm
new file mode 100644
index 0000000..b9c2916
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.4.adm
@@ -0,0 +1,20 @@
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "c": 9 }
+{ "b": null, "c": 11 }
+{ "b": 5, "c": 10 }
+{ "b": 6, "c": 12 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": 4, "c": 8 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.5.adm
new file mode 100644
index 0000000..867353f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.5.adm
@@ -0,0 +1,20 @@
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": null, "c": 15 }
+{ "b": 5, "c": 10 }
+{ "b": 6, "c": 12 }
+{ "c": 9 }
+{ "b": null, "c": 11 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": null, "c": 7 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.6.adm
new file mode 100644
index 0000000..ebe21d3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.6.adm
@@ -0,0 +1,20 @@
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.7.adm
new file mode 100644
index 0000000..8c68e70
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.7.adm
@@ -0,0 +1,20 @@
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.8.adm
new file mode 100644
index 0000000..ebe21d3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.8.adm
@@ -0,0 +1,20 @@
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.9.adm
new file mode 100644
index 0000000..c5a3c81
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-2/order-by-2.9.adm
@@ -0,0 +1,20 @@
+{ "a": 1, "b": null, "c": 3 }
+{ "a": 1, "c": 1 }
+{ "a": 1, "b": 2, "c": 4 }
+{ "a": 1, "b": 1, "c": 2 }
+{ "a": 3.0, "b": null, "c": 19 }
+{ "a": 3.0, "c": 17 }
+{ "a": 3.0, "b": 10, "c": 20 }
+{ "a": 3.0, "b": 9, "c": 18 }
+{ "a": "1", "b": null, "c": 15 }
+{ "a": "1", "c": 13 }
+{ "a": "1", "b": 8, "c": 16 }
+{ "a": "1", "b": 7, "c": 14 }
+{ "b": null, "c": 11 }
+{ "c": 9 }
+{ "b": 6, "c": 12 }
+{ "b": 5, "c": 10 }
+{ "a": null, "b": null, "c": 7 }
+{ "a": null, "c": 5 }
+{ "a": null, "b": 4, "c": 8 }
+{ "a": null, "b": 3, "c": 6 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.10.adm
new file mode 100644
index 0000000..bb1aaeb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.10.adm
@@ -0,0 +1,20 @@
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.11.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.11.adm
new file mode 100644
index 0000000..8e493f8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.11.adm
@@ -0,0 +1,20 @@
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.12.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.12.adm
new file mode 100644
index 0000000..5889e26
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.12.adm
@@ -0,0 +1,20 @@
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.13.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.13.adm
new file mode 100644
index 0000000..737cf1b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.13.adm
@@ -0,0 +1,20 @@
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
+{ "c": 11, "b": null }
+{ "c": 9 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.14.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.14.adm
new file mode 100644
index 0000000..7b9f08e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.14.adm
@@ -0,0 +1,20 @@
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.15.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.15.adm
new file mode 100644
index 0000000..65b9dde
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.15.adm
@@ -0,0 +1,20 @@
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.16.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.16.adm
new file mode 100644
index 0000000..f9cc447
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.16.adm
@@ -0,0 +1,20 @@
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.17.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.17.adm
new file mode 100644
index 0000000..f9cc447
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.17.adm
@@ -0,0 +1,20 @@
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.3.adm
new file mode 100644
index 0000000..462070c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.3.adm
@@ -0,0 +1,20 @@
+{ "c": 9 }
+{ "c": 11, "b": null }
+{ "c": 10, "b": 5 }
+{ "c": 12, "b": 6 }
+{ "c": 5, "a": null }
+{ "c": 7, "a": null, "b": null }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 1, "a": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 17, "a": 3.0 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 13, "a": "1" }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 16, "a": "1", "b": 8 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.4.adm
new file mode 100644
index 0000000..462070c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.4.adm
@@ -0,0 +1,20 @@
+{ "c": 9 }
+{ "c": 11, "b": null }
+{ "c": 10, "b": 5 }
+{ "c": 12, "b": 6 }
+{ "c": 5, "a": null }
+{ "c": 7, "a": null, "b": null }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 1, "a": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 17, "a": 3.0 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 13, "a": "1" }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 16, "a": "1", "b": 8 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.5.adm
new file mode 100644
index 0000000..ca31db4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.5.adm
@@ -0,0 +1,20 @@
+{ "c": 10, "b": 5 }
+{ "c": 12, "b": 6 }
+{ "c": 9 }
+{ "c": 11, "b": null }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 5, "a": null }
+{ "c": 7, "a": null, "b": null }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 1, "a": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 17, "a": 3.0 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 13, "a": "1" }
+{ "c": 15, "a": "1", "b": null }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.6.adm
new file mode 100644
index 0000000..336b3bf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.6.adm
@@ -0,0 +1,20 @@
+{ "c": 1, "a": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 17, "a": 3.0 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 13, "a": "1" }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 9 }
+{ "c": 11, "b": null }
+{ "c": 10, "b": 5 }
+{ "c": 12, "b": 6 }
+{ "c": 5, "a": null }
+{ "c": 7, "a": null, "b": null }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 8, "a": null, "b": 4 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.7.adm
new file mode 100644
index 0000000..833b380
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.7.adm
@@ -0,0 +1,20 @@
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 1, "a": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 17, "a": 3.0 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 13, "a": "1" }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 10, "b": 5 }
+{ "c": 12, "b": 6 }
+{ "c": 9 }
+{ "c": 11, "b": null }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 5, "a": null }
+{ "c": 7, "a": null, "b": null }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.8.adm
new file mode 100644
index 0000000..bb1aaeb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.8.adm
@@ -0,0 +1,20 @@
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.9.adm
new file mode 100644
index 0000000..9ed9734
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.9.adm
@@ -0,0 +1,20 @@
+{ "c": 11, "b": null }
+{ "c": 9 }
+{ "c": 12, "b": 6 }
+{ "c": 10, "b": 5 }
+{ "c": 7, "a": null, "b": null }
+{ "c": 5, "a": null }
+{ "c": 8, "a": null, "b": 4 }
+{ "c": 6, "a": null, "b": 3 }
+{ "c": 3, "a": 1, "b": null }
+{ "c": 1, "a": 1 }
+{ "c": 4, "a": 1, "b": 2 }
+{ "c": 2, "a": 1, "b": 1 }
+{ "c": 19, "a": 3.0, "b": null }
+{ "c": 17, "a": 3.0 }
+{ "c": 20, "a": 3.0, "b": 10 }
+{ "c": 18, "a": 3.0, "b": 9 }
+{ "c": 15, "a": "1", "b": null }
+{ "c": 13, "a": "1" }
+{ "c": 16, "a": "1", "b": 8 }
+{ "c": 14, "a": "1", "b": 7 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.3.adm
similarity index 100%
copy from asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
copy to asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.3.adm
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.4.adm
similarity index 100%
copy from asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
copy to asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.4.adm
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.5.adm
similarity index 76%
copy from asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
copy to asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.5.adm
index 3c6d828..78c3ef4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.5.adm
@@ -1,5 +1,5 @@
-{ "b": 4 }
-{ "b": 7, "a": null }
{ "b": 2, "a": 1 }
{ "b": 9, "a": 3.0 }
{ "b": 8, "a": "1" }
+{ "b": 4 }
+{ "b": 7, "a": null }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.6.adm
similarity index 88%
copy from asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
copy to asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.6.adm
index 3c6d828..5e6cb57 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.6.adm
@@ -1,5 +1,5 @@
-{ "b": 4 }
-{ "b": 7, "a": null }
-{ "b": 2, "a": 1 }
-{ "b": 9, "a": 3.0 }
{ "b": 8, "a": "1" }
+{ "b": 9, "a": 3.0 }
+{ "b": 2, "a": 1 }
+{ "b": 7, "a": null }
+{ "b": 4 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.7.adm
similarity index 79%
copy from asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
copy to asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.7.adm
index 3c6d828..7c3f599 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.7.adm
@@ -1,5 +1,5 @@
-{ "b": 4 }
{ "b": 7, "a": null }
-{ "b": 2, "a": 1 }
-{ "b": 9, "a": 3.0 }
+{ "b": 4 }
{ "b": 8, "a": "1" }
+{ "b": 9, "a": 3.0 }
+{ "b": 2, "a": 1 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.8.adm
similarity index 88%
rename from asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
rename to asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.8.adm
index 3c6d828..5e6cb57 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.8.adm
@@ -1,5 +1,5 @@
-{ "b": 4 }
-{ "b": 7, "a": null }
-{ "b": 2, "a": 1 }
-{ "b": 9, "a": 3.0 }
{ "b": 8, "a": "1" }
+{ "b": 9, "a": 3.0 }
+{ "b": 2, "a": 1 }
+{ "b": 7, "a": null }
+{ "b": 4 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.2.adm
new file mode 100644
index 0000000..df08dd2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.2.adm
@@ -0,0 +1,5 @@
+{ "b": 4 }
+{ "a": null, "b": 7 }
+{ "a": 1, "b": 2 }
+{ "a": 3.0, "b": 9 }
+{ "a": "1", "b": 8 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.3.adm
new file mode 100644
index 0000000..0d262a8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.3.adm
@@ -0,0 +1,5 @@
+{ "a": 1, "b": 2 }
+{ "a": 3.0, "b": 9 }
+{ "a": "1", "b": 8 }
+{ "b": 4 }
+{ "a": null, "b": 7 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.4.adm
new file mode 100644
index 0000000..86c085e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.4.adm
@@ -0,0 +1,5 @@
+{ "a": "1", "b": 8 }
+{ "a": 3.0, "b": 9 }
+{ "a": 1, "b": 2 }
+{ "a": null, "b": 7 }
+{ "b": 4 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.5.adm
new file mode 100644
index 0000000..a27ff5a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.5.adm
@@ -0,0 +1,5 @@
+{ "a": null, "b": 7 }
+{ "b": 4 }
+{ "a": "1", "b": 8 }
+{ "a": 3.0, "b": 9 }
+{ "a": 1, "b": 2 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.6.adm
new file mode 100644
index 0000000..86c085e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by/order-by.6.adm
@@ -0,0 +1,5 @@
+{ "a": "1", "b": 8 }
+{ "a": 3.0, "b": 9 }
+{ "a": 1, "b": 2 }
+{ "a": null, "b": 7 }
+{ "b": 4 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_null_missing/win_null_missing.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_null_missing/win_null_missing.4.adm
new file mode 100644
index 0000000..9818aa3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_null_missing/win_null_missing.4.adm
@@ -0,0 +1,6 @@
+{ "w0": null, "w1": "m", "w2": "n", "w3": "i", "w4": "s", "w5": "a", "w6": "o", "y": "m" }
+{ "w0": null, "w1": "m", "w2": "n", "w3": "i", "w4": "s", "w5": "a", "w6": "o", "x": null, "y": "n" }
+{ "w0": null, "w1": "m", "w2": "n", "w3": "i", "w4": "s", "w5": "a", "w6": "o", "x": 1, "y": "i" }
+{ "w0": null, "w1": "m", "w2": "n", "w3": "i", "w4": "s", "w5": "a", "w6": "o", "x": "a", "y": "s" }
+{ "w0": null, "w1": "m", "w2": "n", "w3": "i", "w4": "s", "w5": "a", "w6": "o", "x": [ "b" ], "y": "a" }
+{ "w0": null, "w1": "m", "w2": "n", "w3": "i", "w4": "s", "w5": "a", "w6": "o", "x": { "c": 1 }, "y": "o" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_null_missing/win_null_missing.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_null_missing/win_null_missing.5.adm
new file mode 100644
index 0000000..6aac76f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/win_null_missing/win_null_missing.5.adm
@@ -0,0 +1,6 @@
+{ "w0": null, "w1": "i", "w2": "s", "w3": "a", "w4": "o", "w5": "m", "w6": "n", "y": "m" }
+{ "w0": null, "w1": "i", "w2": "s", "w3": "a", "w4": "o", "w5": "m", "w6": "n", "x": null, "y": "n" }
+{ "w0": null, "w1": "i", "w2": "s", "w3": "a", "w4": "o", "w5": "m", "w6": "n", "x": 1, "y": "i" }
+{ "w0": null, "w1": "i", "w2": "s", "w3": "a", "w4": "o", "w5": "m", "w6": "n", "x": "a", "y": "s" }
+{ "w0": null, "w1": "i", "w2": "s", "w3": "a", "w4": "o", "w5": "m", "w6": "n", "x": [ "b" ], "y": "a" }
+{ "w0": null, "w1": "i", "w2": "s", "w3": "a", "w4": "o", "w5": "m", "w6": "n", "x": { "c": 1 }, "y": "o" }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 9a92a6c..b9e38a1 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -8462,11 +8462,28 @@
</compilation-unit>
</test-case>
<test-case FilePath="null-missing">
+ <compilation-unit name="order-by-2">
+ <output-dir compare="Text">order-by-2</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="null-missing">
+ <compilation-unit name="order-by-3-negative">
+ <output-dir compare="Text">none</output-dir>
+ <expected-error><![CDATA[ASX1001: Syntax error: In line 33 >>ORDER BY d.a NULLS;<< Encountered ";" at column 19]]></expected-error>
+ <expected-error><![CDATA[ASX1001: Syntax error: Unexpected token: NULLS (in line 33, at column 20)]]></expected-error>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="null-missing">
<compilation-unit name="order-by-from-dataset">
<output-dir compare="Text">order-by-from-dataset</output-dir>
</compilation-unit>
</test-case>
<test-case FilePath="null-missing">
+ <compilation-unit name="order-by-from-dataset-2">
+ <output-dir compare="Text">order-by-from-dataset-2</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="null-missing">
<compilation-unit name="scan-collection">
<output-dir compare="Text">scan-collection</output-dir>
</compilation-unit>
diff --git a/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf b/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
index 8dd08fa..98cef8a 100644
--- a/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
+++ b/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
@@ -96,7 +96,7 @@ UnionOption ::= "UNION ALL" (QueryBlock | Subquery)
WithClause ::= "WITH" Variable "AS" Expr ("," Variable "AS" Expr)*
-OrderbyClause ::= "ORDER BY" Expr ( "ASC" | "DESC" )? ( "," Expr ( "ASC" | "DESC" )? )*
+OrderbyClause ::= "ORDER BY" Expr ( "ASC" | "DESC" )? ( "NULLS" ( "FIRST" | "LAST" ) )? ( "," Expr ( "ASC" | "DESC" )? ( "NULLS" ( "FIRST" | "LAST" ) )? )*
LimitClause ::= "LIMIT" Expr OffsetClause?
@@ -116,7 +116,7 @@ WindowDefinition ::= WindowPartitionClause? (WindowOrderClause (WindowFrameClaus
WindowPartitionClause ::= "PARTITION" "BY" Expr ("," Expr)*
-WindowOrderClause ::= "ORDER" "BY" Expr ("ASC"|"DESC")? ("," Expr ("ASC" | "DESC")?)*
+WindowOrderClause ::= "ORDER" "BY" Expr ("ASC" | "DESC")? ( "NULLS" ( "FIRST" | "LAST" ) )? ("," Expr ("ASC" | "DESC")? ( "NULLS" ( "FIRST" | "LAST" ) )? )*
WindowFrameClause ::= ("ROWS" | "RANGE" | "GROUPS") WindowFrameExtent
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
index 3e70922..fe9a3ad 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
@@ -1220,9 +1220,12 @@ Result:
The last three (optional) clauses to be processed in a query are `ORDER BY`, `LIMIT`, and `OFFSET`.
The `ORDER BY` clause is used to globally sort data in either ascending order (i.e., `ASC`) or descending order (i.e., `DESC`).
-During ordering, `MISSING` and `NULL` are treated as being smaller than any other value if they are encountered
+During ordering (if the `NULLS` modifier is not specified), `MISSING` and `NULL` are treated as being smaller than any other value if they are encountered
in the ordering key(s). `MISSING` is treated as smaller than `NULL` if both occur in the data being sorted.
-The ordering of values of a given type is consistent with its type's `<=` ordering; the ordering of values across types is implementation-defined but stable.
+The `NULLS` modifier determines how `MISSING` and `NULL` are ordered relative to all other values:
+first (`NULLS` `FIRST`) or last (`NULLS` `LAST`). The relative order between `MISSING` and `NULL` is not affected by the `NULLS` modifier
+(i.e. `MISSING` is still treated as smaller than `NULL`). The ordering of values of a given type is consistent with its type's `<=` ordering;
+the ordering of values across types is implementation-defined but stable.
The `LIMIT` clause is used to limit the result set to a specified maximum size.
The optional `OFFSET` clause is used to specify a number of items in the output stream to be discarded before the query result begins.
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/clause/OrderbyClause.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/clause/OrderbyClause.java
index af8c725..0793cae 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/clause/OrderbyClause.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/clause/OrderbyClause.java
@@ -30,6 +30,7 @@ import org.apache.hyracks.dataflow.common.data.partition.range.RangeMap;
public class OrderbyClause extends AbstractClause {
private List<Expression> orderbyList;
private List<OrderModifier> modifierList;
+ private List<NullOrderModifier> nullModifierList;
private RangeMap rangeMap; // can be null
private int numFrames = -1;
private int numTuples = -1;
@@ -38,9 +39,11 @@ public class OrderbyClause extends AbstractClause {
// Default constructor.
}
- public OrderbyClause(List<Expression> orderbyList, List<OrderModifier> modifierList) {
+ public OrderbyClause(List<Expression> orderbyList, List<OrderModifier> modifierList,
+ List<NullOrderModifier> nullModifierList) {
this.orderbyList = orderbyList;
this.modifierList = modifierList;
+ this.nullModifierList = nullModifierList;
}
public List<Expression> getOrderbyList() {
@@ -59,6 +62,14 @@ public class OrderbyClause extends AbstractClause {
this.modifierList = modifierList;
}
+ public List<NullOrderModifier> getNullModifierList() {
+ return nullModifierList;
+ }
+
+ public void setNullModifierList(List<NullOrderModifier> nullModifierList) {
+ this.nullModifierList = nullModifierList;
+ }
+
@Override
public ClauseType getClauseType() {
return ClauseType.ORDER_BY_CLAUSE;
@@ -69,6 +80,11 @@ public class OrderbyClause extends AbstractClause {
DESC
}
+ public enum NullOrderModifier {
+ FIRST,
+ LAST
+ }
+
@Override
public <R, T> R accept(ILangVisitor<R, T> visitor, T arg) throws CompilationException {
return visitor.visit(this, arg);
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/CloneAndSubstituteVariablesVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/CloneAndSubstituteVariablesVisitor.java
index 4b30c97..d73c264 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/CloneAndSubstituteVariablesVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/CloneAndSubstituteVariablesVisitor.java
@@ -250,7 +250,8 @@ public class CloneAndSubstituteVariablesVisitor extends
VariableSubstitutionEnvironment env) throws CompilationException {
List<Expression> exprList =
VariableCloneAndSubstitutionUtil.visitAndCloneExprList(oc.getOrderbyList(), env, this);
- OrderbyClause oc2 = new OrderbyClause(exprList, new ArrayList<>(oc.getModifierList()));
+ OrderbyClause oc2 = new OrderbyClause(exprList, new ArrayList<>(oc.getModifierList()),
+ new ArrayList<>(oc.getNullModifierList()));
oc2.setNumFrames(oc.getNumFrames());
oc2.setNumTuples(oc.getNumTuples());
oc2.setRangeMap(oc.getRangeMap());
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/FormatPrintVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/FormatPrintVisitor.java
index 12a1bd3..0ddbeb4 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/FormatPrintVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/FormatPrintVisitor.java
@@ -39,6 +39,7 @@ import org.apache.asterix.lang.common.base.Literal;
import org.apache.asterix.lang.common.clause.LetClause;
import org.apache.asterix.lang.common.clause.LimitClause;
import org.apache.asterix.lang.common.clause.OrderbyClause;
+import org.apache.asterix.lang.common.clause.OrderbyClause.NullOrderModifier;
import org.apache.asterix.lang.common.clause.OrderbyClause.OrderModifier;
import org.apache.asterix.lang.common.clause.UpdateClause;
import org.apache.asterix.lang.common.clause.WhereClause;
@@ -317,7 +318,7 @@ public abstract class FormatPrintVisitor implements ILangVisitor<Void, Integer>
@Override
public Void visit(OrderbyClause oc, Integer step) throws CompilationException {
out.print(skip(step) + "order by ");
- printDelimitedObyExpressions(oc.getOrderbyList(), oc.getModifierList(), step);
+ printDelimitedObyExpressions(oc.getOrderbyList(), oc.getModifierList(), oc.getNullModifierList(), step);
out.println();
return null;
}
@@ -1037,8 +1038,8 @@ public abstract class FormatPrintVisitor implements ILangVisitor<Void, Integer>
}
}
- protected void printDelimitedObyExpressions(List<Expression> list, List<OrderModifier> mlist, Integer step)
- throws CompilationException {
+ protected void printDelimitedObyExpressions(List<Expression> list, List<OrderModifier> mlist,
+ List<NullOrderModifier> nlist, Integer step) throws CompilationException {
int index = 0;
int size = list.size();
for (Expression expr : list) {
@@ -1047,6 +1048,11 @@ public abstract class FormatPrintVisitor implements ILangVisitor<Void, Integer>
if (orderModifier != OrderModifier.ASC) {
out.print(orderModifier.toString().toLowerCase());
}
+ NullOrderModifier nullModifier = nlist.get(index);
+ if (nullModifier != null) {
+ out.print(" nulls ");
+ out.print(nullModifier.toString().toLowerCase());
+ }
if (++index < size) {
out.print(COMMA);
}
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/QueryPrintVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/QueryPrintVisitor.java
index e756eee..79e90a4 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/QueryPrintVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/QueryPrintVisitor.java
@@ -31,6 +31,7 @@ import org.apache.asterix.lang.common.base.Literal;
import org.apache.asterix.lang.common.clause.LetClause;
import org.apache.asterix.lang.common.clause.LimitClause;
import org.apache.asterix.lang.common.clause.OrderbyClause;
+import org.apache.asterix.lang.common.clause.OrderbyClause.NullOrderModifier;
import org.apache.asterix.lang.common.clause.OrderbyClause.OrderModifier;
import org.apache.asterix.lang.common.clause.WhereClause;
import org.apache.asterix.lang.common.expression.CallExpr;
@@ -241,10 +242,14 @@ public abstract class QueryPrintVisitor extends AbstractQueryExpressionVisitor<V
public Void visit(OrderbyClause oc, Integer step) throws CompilationException {
out.println(skip(step) + "Orderby");
List<OrderModifier> mlist = oc.getModifierList();
+ List<NullOrderModifier> nlist = oc.getNullModifierList();
List<Expression> list = oc.getOrderbyList();
for (int i = 0; i < list.size(); i++) {
list.get(i).accept(this, step + 1);
- out.println(skip(step + 1) + mlist.get(i).toString());
+ OrderModifier orderModifier = mlist.get(i);
+ NullOrderModifier nullOrderModifier = nlist.get(i);
+ out.println(
+ skip(step + 1) + orderModifier + (nullOrderModifier != null ? " NULLS " + nullOrderModifier : ""));
}
out.println();
return null;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/expression/WindowExpression.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/expression/WindowExpression.java
index 417cae6..85b9578 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/expression/WindowExpression.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/expression/WindowExpression.java
@@ -40,6 +40,7 @@ public class WindowExpression extends AbstractCallExpression {
private List<Expression> partitionList;
private List<Expression> orderbyList;
private List<OrderbyClause.OrderModifier> orderbyModifierList;
+ private List<OrderbyClause.NullOrderModifier> orderbyNullModifierList;
private FrameMode frameMode;
private FrameBoundaryKind frameStartKind;
@@ -56,7 +57,8 @@ public class WindowExpression extends AbstractCallExpression {
public WindowExpression(FunctionSignature functionSignature, List<Expression> exprList, Expression aggFilterExpr,
List<Expression> partitionList, List<Expression> orderbyList,
- List<OrderbyClause.OrderModifier> orderbyModifierList, FrameMode frameMode,
+ List<OrderbyClause.OrderModifier> orderbyModifierList,
+ List<OrderbyClause.NullOrderModifier> orderbyNullModifierList, FrameMode frameMode,
FrameBoundaryKind frameStartKind, Expression frameStartExpr, FrameBoundaryKind frameEndKind,
Expression frameEndExpr, FrameExclusionKind frameExclusionKind, VariableExpr windowVar,
List<Pair<Expression, Identifier>> windowFieldList, Boolean ignoreNulls, Boolean fromLast) {
@@ -64,6 +66,7 @@ public class WindowExpression extends AbstractCallExpression {
this.partitionList = partitionList;
this.orderbyList = orderbyList;
this.orderbyModifierList = orderbyModifierList;
+ this.orderbyNullModifierList = orderbyNullModifierList;
this.frameMode = frameMode;
this.frameStartKind = frameStartKind;
this.frameStartExpr = frameStartExpr;
@@ -113,6 +116,14 @@ public class WindowExpression extends AbstractCallExpression {
this.orderbyModifierList = orderbyModifierList;
}
+ public List<OrderbyClause.NullOrderModifier> getOrderbyNullModifierList() {
+ return orderbyNullModifierList;
+ }
+
+ public void setOrderbyNullModifierList(List<OrderbyClause.NullOrderModifier> orderbyNullModifierList) {
+ this.orderbyNullModifierList = orderbyNullModifierList;
+ }
+
public boolean hasFrameDefinition() {
return frameMode != null;
}
@@ -292,7 +303,12 @@ public class WindowExpression extends AbstractCallExpression {
if (i > 0) {
sb.append(',');
}
- sb.append(orderbyList.get(i)).append(' ').append(orderbyModifierList.get(i));
+ OrderbyClause.OrderModifier orderModifier = orderbyModifierList.get(i);
+ OrderbyClause.NullOrderModifier nullOrderModifier = orderbyNullModifierList.get(i);
+ sb.append(orderbyList.get(i)).append(' ').append(orderModifier);
+ if (nullOrderModifier != null) {
+ sb.append(" NULLS ").append(nullOrderModifier);
+ }
}
}
if (hasFrameDefinition()) {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
index 998d8e6..ec6de6e 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
@@ -276,7 +276,8 @@ public class DeepCopyVisitor extends AbstractSqlppQueryExpressionVisitor<ILangEx
for (Expression orderExpr : oc.getOrderbyList()) {
newOrderbyList.add((Expression) orderExpr.accept(this, arg));
}
- OrderbyClause copy = new OrderbyClause(newOrderbyList, new ArrayList<>(oc.getModifierList()));
+ OrderbyClause copy = new OrderbyClause(newOrderbyList, new ArrayList<>(oc.getModifierList()),
+ new ArrayList<>(oc.getNullModifierList()));
copy.setSourceLocation(oc.getSourceLocation());
return copy;
}
@@ -526,6 +527,8 @@ public class DeepCopyVisitor extends AbstractSqlppQueryExpressionVisitor<ILangEx
List<Expression> newOrderbyList = winExpr.hasOrderByList() ? copyExprList(winExpr.getOrderbyList(), arg) : null;
List<OrderbyClause.OrderModifier> newOrderbyModifierList =
winExpr.hasOrderByList() ? new ArrayList<>(winExpr.getOrderbyModifierList()) : null;
+ List<OrderbyClause.NullOrderModifier> newOrderbyNullModifierList =
+ winExpr.hasOrderByList() ? new ArrayList<>(winExpr.getOrderbyNullModifierList()) : null;
Expression newFrameStartExpr =
winExpr.hasFrameStartExpr() ? (Expression) winExpr.getFrameStartExpr().accept(this, arg) : null;
Expression newFrameEndExpr =
@@ -534,11 +537,11 @@ public class DeepCopyVisitor extends AbstractSqlppQueryExpressionVisitor<ILangEx
winExpr.hasWindowVar() ? (VariableExpr) winExpr.getWindowVar().accept(this, arg) : null;
List<Pair<Expression, Identifier>> newWindowFieldList =
winExpr.hasWindowFieldList() ? copyFieldList(winExpr.getWindowFieldList(), arg) : null;
- WindowExpression copy =
- new WindowExpression(winExpr.getFunctionSignature(), newExprList, newAggFilterExpr, newPartitionList,
- newOrderbyList, newOrderbyModifierList, winExpr.getFrameMode(), winExpr.getFrameStartKind(),
- newFrameStartExpr, winExpr.getFrameEndKind(), newFrameEndExpr, winExpr.getFrameExclusionKind(),
- newWindowVar, newWindowFieldList, winExpr.getIgnoreNulls(), winExpr.getFromLast());
+ WindowExpression copy = new WindowExpression(winExpr.getFunctionSignature(), newExprList, newAggFilterExpr,
+ newPartitionList, newOrderbyList, newOrderbyModifierList, newOrderbyNullModifierList,
+ winExpr.getFrameMode(), winExpr.getFrameStartKind(), newFrameStartExpr, winExpr.getFrameEndKind(),
+ newFrameEndExpr, winExpr.getFrameExclusionKind(), newWindowVar, newWindowFieldList,
+ winExpr.getIgnoreNulls(), winExpr.getFromLast());
copy.setSourceLocation(winExpr.getSourceLocation());
copy.addHints(winExpr.getHints());
return copy;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
index efb9446..90e7448 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
@@ -393,9 +393,13 @@ public class SqlppAstPrintVisitor extends QueryPrintVisitor implements ISqlppVis
out.println(skip(step + 1) + "ORDER BY");
List<Expression> orderbyList = winExpr.getOrderbyList();
List<OrderbyClause.OrderModifier> orderbyModifierList = winExpr.getOrderbyModifierList();
+ List<OrderbyClause.NullOrderModifier> orderbyNullModifierList = winExpr.getOrderbyNullModifierList();
for (int i = 0, ln = orderbyList.size(); i < ln; i++) {
orderbyList.get(i).accept(this, step + 2);
- out.println(skip(step + 2) + orderbyModifierList.get(i));
+ OrderbyClause.OrderModifier orderModifier = orderbyModifierList.get(i);
+ OrderbyClause.NullOrderModifier nullOrderModifier = orderbyNullModifierList.get(i);
+ out.println(skip(step + 2) + orderModifier
+ + (nullOrderModifier != null ? " NULLS " + nullOrderModifier : ""));
}
}
if (winExpr.hasFrameDefinition()) {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
index efe1c3d..bd64314 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
@@ -414,6 +414,8 @@ public class SqlppCloneAndSubstituteVariablesVisitor extends CloneAndSubstituteV
? VariableCloneAndSubstitutionUtil.visitAndCloneExprList(winExpr.getOrderbyList(), env, this) : null;
List<OrderbyClause.OrderModifier> newOrderbyModifierList =
winExpr.hasOrderByList() ? new ArrayList<>(winExpr.getOrderbyModifierList()) : null;
+ List<OrderbyClause.NullOrderModifier> newOrderbyNullModifierList =
+ winExpr.hasOrderByList() ? new ArrayList<>(winExpr.getOrderbyNullModifierList()) : null;
Expression newFrameStartExpr =
winExpr.hasFrameStartExpr() ? (Expression) winExpr.getFrameStartExpr().accept(this, env).first : null;
Expression newFrameEndExpr =
@@ -422,11 +424,11 @@ public class SqlppCloneAndSubstituteVariablesVisitor extends CloneAndSubstituteV
winExpr.hasWindowVar() ? (VariableExpr) winExpr.getWindowVar().accept(this, env).first : null;
List<Pair<Expression, Identifier>> newWindowFieldList = winExpr.hasWindowFieldList()
? VariableCloneAndSubstitutionUtil.substInFieldList(winExpr.getWindowFieldList(), env, this) : null;
- WindowExpression newWinExpr =
- new WindowExpression(winExpr.getFunctionSignature(), newExprList, newAggFilterExpr, newPartitionList,
- newOrderbyList, newOrderbyModifierList, winExpr.getFrameMode(), winExpr.getFrameStartKind(),
- newFrameStartExpr, winExpr.getFrameEndKind(), newFrameEndExpr, winExpr.getFrameExclusionKind(),
- newWindowVar, newWindowFieldList, winExpr.getIgnoreNulls(), winExpr.getFromLast());
+ WindowExpression newWinExpr = new WindowExpression(winExpr.getFunctionSignature(), newExprList,
+ newAggFilterExpr, newPartitionList, newOrderbyList, newOrderbyModifierList, newOrderbyNullModifierList,
+ winExpr.getFrameMode(), winExpr.getFrameStartKind(), newFrameStartExpr, winExpr.getFrameEndKind(),
+ newFrameEndExpr, winExpr.getFrameExclusionKind(), newWindowVar, newWindowFieldList,
+ winExpr.getIgnoreNulls(), winExpr.getFromLast());
newWinExpr.setSourceLocation(winExpr.getSourceLocation());
newWinExpr.addHints(winExpr.getHints());
return new Pair<>(newWinExpr, env);
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
index bdf5c9d..395e916 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
@@ -364,7 +364,8 @@ public class SqlppFormatPrintVisitor extends FormatPrintVisitor implements ISqlp
}
if (windowExpr.hasOrderByList()) {
out.print(skip(step + 1) + "order by ");
- printDelimitedObyExpressions(windowExpr.getOrderbyList(), windowExpr.getOrderbyModifierList(), step + 2);
+ printDelimitedObyExpressions(windowExpr.getOrderbyList(), windowExpr.getOrderbyModifierList(),
+ windowExpr.getOrderbyNullModifierList(), step + 2);
out.println();
}
if (windowExpr.hasFrameDefinition()) {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index cae5400..5d3004f 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -3984,6 +3984,7 @@ Expression FunctionCallExpr() throws ParseException:
WindowExpression WindowExpr(FunctionSignature signature, List<Expression> argList, Expression aggFilterExpr)
throws ParseException:
{
+ WindowExpression windowExpr = null;
Boolean fromLast = null, ignoreNulls = null;
}
{
@@ -4021,9 +4022,9 @@ WindowExpression WindowExpr(FunctionSignature signature, List<Expression> argLis
}
}
)?
- <OVER>
+ <OVER> windowExpr = OverClause(signature, argList, aggFilterExpr, token, fromLast, ignoreNulls)
{
- return OverClause(signature, argList, aggFilterExpr, token, fromLast, ignoreNulls);
+ return windowExpr;
}
}
@@ -4035,6 +4036,7 @@ WindowExpression OverClause(FunctionSignature signature, List<Expression> argLis
OrderbyClause orderByClause = null;
List<Expression> orderbyList = null;
List<OrderbyClause.OrderModifier> orderbyModifierList = null;
+ List<OrderbyClause.NullOrderModifier> orderbyNullModifierList = null;
WindowExpression.FrameMode frameMode = null;
Pair<WindowExpression.FrameBoundaryKind, Expression> frameStart = null, frameEnd = null;
WindowExpression.FrameBoundaryKind frameStartKind = null, frameEndKind = null;
@@ -4063,6 +4065,7 @@ WindowExpression OverClause(FunctionSignature signature, List<Expression> argLis
{
orderbyList = orderByClause.getOrderbyList();
orderbyModifierList = orderByClause.getModifierList();
+ orderbyNullModifierList = orderByClause.getNullModifierList();
}
(
frameMode = WindowFrameMode()
@@ -4089,8 +4092,8 @@ WindowExpression OverClause(FunctionSignature signature, List<Expression> argLis
<RIGHTPAREN>
{
WindowExpression winExpr = new WindowExpression(signature, argList, aggFilterExpr, partitionExprs, orderbyList,
- orderbyModifierList, frameMode, frameStartKind, frameStartExpr, frameEndKind, frameEndExpr, frameExclusionKind,
- windowVar, windowFieldList, ignoreNulls, fromLast);
+ orderbyModifierList, orderbyNullModifierList, frameMode, frameStartKind, frameStartExpr, frameEndKind,
+ frameEndExpr, frameExclusionKind, windowVar, windowFieldList, ignoreNulls, fromLast);
return addSourceLocation(winExpr, startToken);
}
}
@@ -4617,65 +4620,89 @@ OrderbyClause OrderbyClause() throws ParseException :
{
Token startToken = null;
OrderbyClause oc = new OrderbyClause();
- Expression orderbyExpr;
+ Triple<Expression, OrderbyClause.OrderModifier, OrderbyClause.NullOrderModifier> orderbyExpr = null;
List<Expression> orderbyList = new ArrayList<Expression>();
List<OrderbyClause.OrderModifier> modifierList = new ArrayList<OrderbyClause.OrderModifier>();
- int numOfOrderby = 0;
+ List<OrderbyClause.NullOrderModifier> nullModifierList = new ArrayList<OrderbyClause.NullOrderModifier>();
}
{
- <ORDER>
- {
- startToken = token;
- Token hintToken = fetchHint(token, SqlppHint.INMEMORY_HINT, SqlppHint.RANGE_HINT);
- if (hintToken != null) {
- switch (hintToken.hint) {
- case INMEMORY_HINT:
- String[] splits = hintToken.hintParams.split("\\s+");
- int numFrames = Integer.parseInt(splits[0]);
- int numTuples = Integer.parseInt(splits[1]);
- oc.setNumFrames(numFrames);
- oc.setNumTuples(numTuples);
- break;
- case RANGE_HINT:
- try {
- Expression rangeExpr = parseExpression(hintToken.hintParams);
- RangeMap rangeMap = RangeMapBuilder.parseHint(rangeExpr);
- oc.setRangeMap(rangeMap);
- } catch (CompilationException e) {
- throw new SqlppParseException(getSourceLocation(hintToken), e.getMessage());
- }
- break;
+ <ORDER>
+ {
+ startToken = token;
+ Token hintToken = fetchHint(token, SqlppHint.INMEMORY_HINT, SqlppHint.RANGE_HINT);
+ if (hintToken != null) {
+ switch (hintToken.hint) {
+ case INMEMORY_HINT:
+ String[] splits = hintToken.hintParams.split("\\s+");
+ int numFrames = Integer.parseInt(splits[0]);
+ int numTuples = Integer.parseInt(splits[1]);
+ oc.setNumFrames(numFrames);
+ oc.setNumTuples(numTuples);
+ break;
+ case RANGE_HINT:
+ try {
+ Expression rangeExpr = parseExpression(hintToken.hintParams);
+ RangeMap rangeMap = RangeMapBuilder.parseHint(rangeExpr);
+ oc.setRangeMap(rangeMap);
+ } catch (CompilationException e) {
+ throw new SqlppParseException(getSourceLocation(hintToken), e.getMessage());
}
- }
+ break;
}
- <BY> orderbyExpr = Expression()
- {
- orderbyList.add(orderbyExpr);
- OrderbyClause.OrderModifier modif = OrderbyClause.OrderModifier.ASC;
}
- ( (<ASC> { modif = OrderbyClause.OrderModifier.ASC; })
- | (<DESC> { modif = OrderbyClause.OrderModifier.DESC; }))?
- {
- modifierList.add(modif);
- }
-
- (LOOKAHEAD(2) <COMMA> orderbyExpr = Expression()
- {
- orderbyList.add(orderbyExpr);
- modif = OrderbyClause.OrderModifier.ASC;
- }
- ( (<ASC> { modif = OrderbyClause.OrderModifier.ASC; })
- | (<DESC> { modif = OrderbyClause.OrderModifier.DESC; }))?
+ }
+ <BY> orderbyExpr = OrderByExpression()
+ {
+ orderbyList.add(orderbyExpr.first);
+ modifierList.add(orderbyExpr.second);
+ nullModifierList.add(orderbyExpr.third);
+ }
+ (
+ LOOKAHEAD(2) <COMMA> orderbyExpr = OrderByExpression()
{
- modifierList.add(modif);
+ orderbyList.add(orderbyExpr.first);
+ modifierList.add(orderbyExpr.second);
+ nullModifierList.add(orderbyExpr.third);
}
- )*
+ )*
+ {
+ oc.setOrderbyList(orderbyList);
+ oc.setModifierList(modifierList);
+ oc.setNullModifierList(nullModifierList);
+ return addSourceLocation(oc, startToken);
+ }
+}
+Triple<Expression, OrderbyClause.OrderModifier, OrderbyClause.NullOrderModifier> OrderByExpression()
+ throws ParseException:
+{
+ Expression orderbyExpr = null;
+ OrderbyClause.OrderModifier modif = OrderbyClause.OrderModifier.ASC;
+ OrderbyClause.NullOrderModifier nullModif = null;
+}
+{
+ orderbyExpr = Expression()
+ (
+ <ASC> { modif = OrderbyClause.OrderModifier.ASC; }
+ |
+ <DESC> { modif = OrderbyClause.OrderModifier.DESC; }
+ )?
+ (
+ LOOKAHEAD({ laIdentifier(NULLS) }) <IDENTIFIER> { expectToken(NULLS); } <IDENTIFIER>
{
- oc.setModifierList(modifierList);
- oc.setOrderbyList(orderbyList);
- return addSourceLocation(oc, startToken);
+ if (isToken(FIRST)) {
+ nullModif = OrderbyClause.NullOrderModifier.FIRST;
+ } else if (isToken(LAST)) {
+ nullModif = OrderbyClause.NullOrderModifier.LAST;
+ } else {
+ throw createUnexpectedTokenError();
+ }
}
+ )?
+ {
+ return new Triple<Expression, OrderbyClause.OrderModifier, OrderbyClause.NullOrderModifier>(orderbyExpr, modif,
+ nullModif);
+ }
}
GroupbyClause GroupbyClause()throws ParseException :