[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 <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Glenn Galvizo <ggalvizo@uci.edu>
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.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 @@
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 @@
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 @@
}
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/optimizerts/queries/orderby-nulls-first-last.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-nulls-first-last.sqlpp
new file mode 100644
index 0000000..db4b75b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-nulls-first-last.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type TestType as {
+ c: bigint
+};
+
+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-2/order-by-2.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.1.query.sqlpp
new file mode 100644
index 0000000..293f824
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.1.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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, d.b ASC;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.10.query.sqlpp
new file mode 100644
index 0000000..38ec82c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.10.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 ASC NULLS LAST, d.b DESC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.11.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.11.query.sqlpp
new file mode 100644
index 0000000..bf17f9f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.11.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 DESC, d.b DESC;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.12.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.12.query.sqlpp
new file mode 100644
index 0000000..acb7ea0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.12.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 DESC NULLS FIRST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.13.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.13.query.sqlpp
new file mode 100644
index 0000000..616c160
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.13.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 DESC NULLS FIRST, d.b DESC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.14.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.14.query.sqlpp
new file mode 100644
index 0000000..09cc382
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.14.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 DESC NULLS LAST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.15.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.15.query.sqlpp
new file mode 100644
index 0000000..09cc382
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.15.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 DESC NULLS LAST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.2.query.sqlpp
new file mode 100644
index 0000000..8c585c1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.2.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 NULLS FIRST, d.b ASC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.3.query.sqlpp
new file mode 100644
index 0000000..31c41ce
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.3.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 NULLS FIRST, d.b ASC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.4.query.sqlpp
new file mode 100644
index 0000000..af5c3c5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.4.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 NULLS LAST, d.b ASC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.5.query.sqlpp
new file mode 100644
index 0000000..e48bbf7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.5.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 NULLS LAST, d.b ASC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.6.query.sqlpp
new file mode 100644
index 0000000..5566c48
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.6.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 ASC, d.b DESC;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.7.query.sqlpp
new file mode 100644
index 0000000..edb760d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.7.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 ASC NULLS FIRST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.8.query.sqlpp
new file mode 100644
index 0000000..5fc05de
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.8.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 ASC NULLS FIRST, d.b DESC NULLS LAST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.9.query.sqlpp
new file mode 100644
index 0000000..96b33a9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-2/order-by-2.9.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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 ASC NULLS LAST, d.b DESC NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.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
new file mode 100644
index 0000000..1d9941f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.1.query.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Failure: Syntax error
+ */
+
+WITH data AS
+[ {"a":1, "b":2},
+ {"a": NULL, "b": 7},
+ {"b":4},
+ {"a": "1", "b":8},
+ {"a": 3.0, "b":9}
+]
+
+SELECT VALUE d
+FROM data AS d
+ORDER BY d.a NULLS;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.2.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
new file mode 100644
index 0000000..40a9cad
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-3-negative/order-by-3-negative.2.query.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Failure: Syntax error
+ */
+
+WITH data AS
+[ {"a":1, "b":2},
+ {"a": NULL, "b": 7},
+ {"b":4},
+ {"a": "1", "b":8},
+ {"a": 3.0, "b":9}
+]
+
+SELECT VALUE d
+FROM data AS d
+ORDER BY d.a NULLS NULLS;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.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
new file mode 100644
index 0000000..4c098ec
--- /dev/null
+++ 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
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type TestType as {
+ c: bigint
+};
+
+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-2/order-by-from-dataset-2.10.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
new file mode 100644
index 0000000..a5af7c3
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.11.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
new file mode 100644
index 0000000..66978dc
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+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/runtimets/queries_sqlpp/null-missing/order-by-from-dataset-2/order-by-from-dataset-2.12.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
new file mode 100644
index 0000000..f0c70cc
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.13.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
new file mode 100644
index 0000000..3b6d826
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.14.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
new file mode 100644
index 0000000..ec1ec83
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.15.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
new file mode 100644
index 0000000..0521b07
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.16.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
new file mode 100644
index 0000000..8e85354
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.17.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
new file mode 100644
index 0000000..583b5cc
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.2.update.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
new file mode 100644
index 0000000..a642f52
--- /dev/null
+++ 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
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+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},
+
+ {"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-2/order-by-from-dataset-2.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
new file mode 100644
index 0000000..d66429f
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.4.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
new file mode 100644
index 0000000..04644fa
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.5.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
new file mode 100644
index 0000000..6173a3e
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.6.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
new file mode 100644
index 0000000..e25a0c4
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.7.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
new file mode 100644
index 0000000..b630030
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.8.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
new file mode 100644
index 0000000..fadd49e
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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-2/order-by-from-dataset-2.9.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
new file mode 100644
index 0000000..aab89f1
--- /dev/null
+++ 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
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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.4.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
new file mode 100644
index 0000000..538870f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.4.query.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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.5.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
new file mode 100644
index 0000000..e4fe10d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.5.query.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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.6.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
new file mode 100644
index 0000000..243b0f4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.6.query.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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.7.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
new file mode 100644
index 0000000..2a3ad56
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.7.query.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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.8.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
new file mode 100644
index 0000000..5b5c067
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by-from-dataset/order-by-from-dataset.8.query.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+SELECT VALUE d
+FROM data AS d
+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.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.2.query.sqlpp
new file mode 100644
index 0000000..0c70a22
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.2.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+WITH data AS
+[ {"a":1, "b":2},
+ {"a": NULL, "b": 7},
+ {"b":4},
+ {"a": "1", "b":8},
+ {"a": 3.0, "b":9}
+]
+
+SELECT VALUE d
+FROM data AS d
+ORDER BY d.a NULLS FIRST;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.3.query.sqlpp
new file mode 100644
index 0000000..0a77b97
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+WITH data AS
+[ {"a":1, "b":2},
+ {"a": NULL, "b": 7},
+ {"b":4},
+ {"a": "1", "b":8},
+ {"a": 3.0, "b":9}
+]
+
+SELECT VALUE d
+FROM data AS d
+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.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.4.query.sqlpp
new file mode 100644
index 0000000..5c345cc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.4.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+WITH data AS
+[ {"a":1, "b":2},
+ {"a": NULL, "b": 7},
+ {"b":4},
+ {"a": "1", "b":8},
+ {"a": 3.0, "b":9}
+]
+
+SELECT VALUE d
+FROM data AS d
+ORDER BY d.a DESC;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.5.query.sqlpp
new file mode 100644
index 0000000..efbeb55
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.5.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+WITH data AS
+[ {"a":1, "b":2},
+ {"a": NULL, "b": 7},
+ {"b":4},
+ {"a": "1", "b":8},
+ {"a": 3.0, "b":9}
+]
+
+SELECT VALUE d
+FROM data AS d
+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.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.6.query.sqlpp
new file mode 100644
index 0000000..2f26dc1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/null-missing/order-by/order-by.6.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+WITH data AS
+[ {"a":1, "b":2},
+ {"a": NULL, "b": 7},
+ {"b":4},
+ {"a": "1", "b":8},
+ {"a": 3.0, "b":9}
+]
+
+SELECT VALUE d
+FROM data AS d
+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%
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.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.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.5.adm
new file mode 100644
index 0000000..78c3ef4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.5.adm
@@ -0,0 +1,5 @@
+{ "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.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.6.adm
new file mode 100644
index 0000000..5e6cb57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.6.adm
@@ -0,0 +1,5 @@
+{ "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.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.7.adm
new file mode 100644
index 0000000..7c3f599
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.7.adm
@@ -0,0 +1,5 @@
+{ "b": 7, "a": null }
+{ "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.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.8.adm
new file mode 100644
index 0000000..5e6cb57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/null-missing/order-by-from-dataset/order-by-from-dataset.8.adm
@@ -0,0 +1,5 @@
+{ "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 @@
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 @@
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 @@
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 @@
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 @@
// 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 @@
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 @@
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 @@
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.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 @@
@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 @@
}
}
- 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 @@
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.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 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 @@
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 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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
? 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 @@
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 @@
}
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 @@
WindowExpression WindowExpr(FunctionSignature signature, List<Expression> argList, Expression aggFilterExpr)
throws ParseException:
{
+ WindowExpression windowExpr = null;
Boolean fromLast = null, ignoreNulls = null;
}
{
@@ -4021,9 +4022,9 @@
}
}
)?
- <OVER>
+ <OVER> windowExpr = OverClause(signature, argList, aggFilterExpr, token, fromLast, ignoreNulls)
{
- return OverClause(signature, argList, aggFilterExpr, token, fromLast, ignoreNulls);
+ return windowExpr;
}
}
@@ -4035,6 +4036,7 @@
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 @@
{
orderbyList = orderByClause.getOrderbyList();
orderbyModifierList = orderByClause.getModifierList();
+ orderbyNullModifierList = orderByClause.getNullModifierList();
}
(
frameMode = WindowFrameMode()
@@ -4089,8 +4092,8 @@
<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 @@
{
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; }))?
+ }
+ <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);
+ }
+}
- (LOOKAHEAD(2) <COMMA> orderbyExpr = Expression()
+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>
{
- orderbyList.add(orderbyExpr);
- modif = OrderbyClause.OrderModifier.ASC;
+ if (isToken(FIRST)) {
+ nullModif = OrderbyClause.NullOrderModifier.FIRST;
+ } else if (isToken(LAST)) {
+ nullModif = OrderbyClause.NullOrderModifier.LAST;
+ } else {
+ throw createUnexpectedTokenError();
+ }
}
- ( (<ASC> { modif = OrderbyClause.OrderModifier.ASC; })
- | (<DESC> { modif = OrderbyClause.OrderModifier.DESC; }))?
- {
- modifierList.add(modif);
- }
- )*
-
- {
- oc.setModifierList(modifierList);
- oc.setOrderbyList(orderbyList);
- return addSourceLocation(oc, startToken);
- }
+ )?
+ {
+ return new Triple<Expression, OrderbyClause.OrderModifier, OrderbyClause.NullOrderModifier>(orderbyExpr, modif,
+ nullModif);
+ }
}
GroupbyClause GroupbyClause()throws ParseException :