You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by ht...@apache.org on 2020/11/20 11:15:43 UTC
[asterixdb] branch master updated: [NO ISSUE][COMP] Support OFFSET
without LIMIT
This is an automated email from the ASF dual-hosted git repository.
htowaileb pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/asterixdb.git
The following commit(s) were added to refs/heads/master by this push:
new 761c9a6 [NO ISSUE][COMP] Support OFFSET without LIMIT
761c9a6 is described below
commit 761c9a6b38b7f176648ffa0e348d5cd92c30037b
Author: Dmitry Lychagin <dm...@couchbase.com>
AuthorDate: Thu Nov 19 10:22:11 2020 -0800
[NO ISSUE][COMP] Support OFFSET without LIMIT
- user model changes: yes
- storage format changes: no
- interface changes: no
Details:
- Add support for a standalone OFFSET clause
(without LIMIT clause)
- Add testcases and update documentation
Change-Id: I1c8b968fcc8beaa1028b8370610ff490e391d6f9
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/8963
Integration-Tests: Jenkins <je...@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Dmitry Lychagin <dm...@couchbase.com>
Reviewed-by: Ali Alsuliman <al...@gmail.com>
---
.../optimizer/rules/PushLimitIntoOrderByRule.java | 8 +-
.../translator/LangExpressionToPlanTranslator.java | 38 ++---
.../limit_negative_value.1.ddl.sqlpp | 2 +-
.../offset_without_limit.1.ddl.sqlpp} | 5 +-
.../offset_without_limit.2.update.sqlpp} | 20 +--
.../offset_without_limit.3.query.sqlpp} | 22 +--
.../offset_without_limit.4.query.sqlpp} | 22 +--
.../offset_without_limit.5.query.sqlpp} | 21 +--
.../offset_without_limit.6.query.sqlpp} | 21 +--
.../offset_without_limit.3.adm | 2 +
.../offset_without_limit.4.adm | 1 +
.../offset_without_limit.5.adm | 2 +
.../offset_without_limit.6.adm | 18 ++
.../push-limit-to-primary-lookup-select.3.adm | 2 +-
.../push-limit-to-primary-lookup.3.adm | 2 +-
.../push-limit-to-primary-lookup.5.adm | 2 +-
.../push-limit-to-primary-scan-select.3.adm | 2 +-
.../push-limit-to-primary-scan.3.adm | 2 +-
.../push-limit-to-primary-scan.5.adm | 2 +-
.../push-limit-to-primary-scan.8.adm | 2 +-
.../test/resources/runtimets/testsuite_sqlpp.xml | 5 +
asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf | 6 +-
.../asterix-doc/src/main/markdown/sqlpp/0_toc.md | 2 +-
.../asterix-doc/src/main/markdown/sqlpp/3_query.md | 186 +++++++++++----------
.../asterix/lang/common/clause/LimitClause.java | 19 ++-
.../common/visitor/AbstractInlineUdfsVisitor.java | 13 +-
.../CloneAndSubstituteVariablesVisitor.java | 18 +-
.../lang/common/visitor/FormatPrintVisitor.java | 15 +-
.../common/visitor/GatherFunctionCallsVisitor.java | 6 +-
.../lang/common/visitor/QueryPrintVisitor.java | 17 +-
.../lang/sqlpp/visitor/DeepCopyVisitor.java | 3 +-
.../AbstractSqlppExpressionScopingVisitor.java | 4 +-
.../base/AbstractSqlppSimpleExpressionVisitor.java | 4 +-
.../asterix-lang-sqlpp/src/main/javacc/SQLPP.jj | 21 ++-
.../algebra/operators/logical/LimitOperator.java | 42 +++--
.../visitors/IsomorphismOperatorVisitor.java | 6 +-
.../visitors/SubstituteVariableVisitor.java | 9 +-
.../logical/visitors/UsedVariableVisitor.java | 9 +-
.../operators/physical/StreamLimitPOperator.java | 10 +-
.../LogicalOperatorPrettyPrintVisitor.java | 10 +-
.../LogicalOperatorPrettyPrintVisitorJson.java | 19 ++-
.../core/utils/LogicalOperatorDotVisitor.java | 10 +-
.../rewriter/rules/CopyLimitDownRule.java | 4 +-
.../operators/std/StreamLimitRuntimeFactory.java | 49 +++---
44 files changed, 361 insertions(+), 322 deletions(-)
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushLimitIntoOrderByRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushLimitIntoOrderByRule.java
index 51e536a..62ea303 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushLimitIntoOrderByRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushLimitIntoOrderByRule.java
@@ -116,6 +116,10 @@ public class PushLimitIntoOrderByRule implements IAlgebraicRewriteRule {
}
static Integer getOutputLimit(LimitOperator limitOp) {
+ if (!limitOp.hasMaxObjects()) {
+ // No limit
+ return null;
+ }
// Currently, we support LIMIT with a constant value.
ILogicalExpression maxObjectsExpr = limitOp.getMaxObjects().getValue();
IAObject maxObjectsValue = ConstantExpressionUtil.getConstantIaObject(maxObjectsExpr, ATypeTag.INTEGER);
@@ -130,8 +134,8 @@ public class PushLimitIntoOrderByRule implements IAlgebraicRewriteRule {
// Get the offset constant if there is one. If one presents, then topK = topK + offset.
// This is because we can't apply offset to the external sort.
// Final topK will be applied through LIMIT.
- ILogicalExpression offsetExpr = limitOp.getOffset().getValue();
- if (offsetExpr != null) {
+ if (limitOp.hasOffset()) {
+ ILogicalExpression offsetExpr = limitOp.getOffset().getValue();
IAObject offsetValue = ConstantExpressionUtil.getConstantIaObject(offsetExpr, ATypeTag.INTEGER);
if (offsetValue == null) {
return null;
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 93cb403..6759f1c 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
@@ -1469,26 +1469,26 @@ abstract class LangExpressionToPlanTranslator
@Override
public Pair<ILogicalOperator, LogicalVariable> visit(LimitClause lc, Mutable<ILogicalOperator> tupSource)
throws CompilationException {
- SourceLocation sourceLoc = lc.getSourceLocation();
- LimitOperator opLim;
-
- Pair<ILogicalExpression, Mutable<ILogicalOperator>> p1 = langExprToAlgExpression(lc.getLimitExpr(), tupSource);
- ILogicalExpression maxObjectsExpr =
- createLimitOffsetValueExpression(p1.first, lc.getLimitExpr().getSourceLocation());
- Expression offset = lc.getOffset();
- if (offset != null) {
- Pair<ILogicalExpression, Mutable<ILogicalOperator>> p2 = langExprToAlgExpression(offset, p1.second);
- ILogicalExpression offsetExpr =
- createLimitOffsetValueExpression(p2.first, lc.getOffset().getSourceLocation());
- opLim = new LimitOperator(maxObjectsExpr, offsetExpr);
- opLim.getInputs().add(p2.second);
- opLim.setSourceLocation(sourceLoc);
- } else {
- opLim = new LimitOperator(maxObjectsExpr);
- opLim.getInputs().add(p1.second);
- opLim.setSourceLocation(sourceLoc);
+ Mutable<ILogicalOperator> topOp = tupSource;
+ ILogicalExpression maxObjectsExpr = null;
+ if (lc.hasLimitExpr()) {
+ Pair<ILogicalExpression, Mutable<ILogicalOperator>> p1 = langExprToAlgExpression(lc.getLimitExpr(), topOp);
+ // if user did provide the limit expression and it is NULL or MISSING then it'll be coerced to 0
+ maxObjectsExpr = createLimitOffsetValueExpression(p1.first, lc.getLimitExpr().getSourceLocation());
+ topOp = p1.second;
}
- return new Pair<>(opLim, null);
+ ILogicalExpression offsetExpr = null;
+ if (lc.hasOffset()) {
+ Pair<ILogicalExpression, Mutable<ILogicalOperator>> p2 = langExprToAlgExpression(lc.getOffset(), topOp);
+ offsetExpr = createLimitOffsetValueExpression(p2.first, lc.getOffset().getSourceLocation());
+ topOp = p2.second;
+ }
+
+ LimitOperator limitOp = new LimitOperator(maxObjectsExpr, offsetExpr);
+ limitOp.getInputs().add(topOp);
+ limitOp.setSourceLocation(lc.getSourceLocation());
+
+ return new Pair<>(limitOp, null);
}
private ILogicalExpression createLimitOffsetValueExpression(ILogicalExpression inputExpr, SourceLocation sourceLoc)
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
index 33a9c58..527aadd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
@@ -17,7 +17,7 @@
* under the License.
*/
/*
- * Description : Test push down limit into the primary index scan operator
+ * Description : Test negative limit and offset values
* Expected Result : Success
*/
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.1.ddl.sqlpp
similarity index 89%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.1.ddl.sqlpp
index 33a9c58..2478dde 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.1.ddl.sqlpp
@@ -17,7 +17,7 @@
* under the License.
*/
/*
- * Description : Test push down limit into the primary index scan operator
+ * Description : Test offset clause without limit clause
* Expected Result : Success
*/
@@ -35,5 +35,4 @@ create type test.DBLPType as
misc : string
};
-create dataset DBLP1(DBLPType) primary key id;
-
+create dataset DBLP1(DBLPType) primary key id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.2.update.sqlpp
similarity index 70%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.2.update.sqlpp
index 33a9c58..06ceb9a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.2.update.sqlpp
@@ -16,24 +16,6 @@
* specific language governing permissions and limitations
* under the License.
*/
-/*
- * Description : Test push down limit into the primary index scan operator
- * Expected Result : Success
- */
-
-drop dataverse test if exists;
-create dataverse test;
-
use test;
-create type test.DBLPType as
-{
- id : bigint,
- dblpid : string,
- title : string,
- authors : string,
- misc : string
-};
-
-create dataset DBLP1(DBLPType) primary key id;
-
+load dataset DBLP1 using localfs ((`path`=`asterix_nc1://data/dblp-small/dblp-small-id.txt`),(`format`=`delimited-text`),(`delimiter`=`:`));
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.3.query.sqlpp
similarity index 72%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.3.query.sqlpp
index 33a9c58..ab2af72 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.3.query.sqlpp
@@ -17,23 +17,11 @@
* under the License.
*/
/*
- * Description : Test push down limit into the primary index scan operator
+ * Description : Test offset clause without limit clause (with order by)
* Expected Result : Success
*/
-drop dataverse test if exists;
-create dataverse test;
-
-use test;
-
-create type test.DBLPType as
-{
- id : bigint,
- dblpid : string,
- title : string,
- authors : string,
- misc : string
-};
-
-create dataset DBLP1(DBLPType) primary key id;
-
+select value t
+from [6,5,4,3,2,1] t
+order by t
+offset 4
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.4.query.sqlpp
similarity index 73%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.4.query.sqlpp
index 33a9c58..2088016 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.4.query.sqlpp
@@ -16,24 +16,16 @@
* specific language governing permissions and limitations
* under the License.
*/
+
/*
- * Description : Test push down limit into the primary index scan operator
+ * Description : Test offset clause without limit clause (without order by)
* Expected Result : Success
*/
-drop dataverse test if exists;
-create dataverse test;
-
use test;
-create type test.DBLPType as
-{
- id : bigint,
- dblpid : string,
- title : string,
- authors : string,
- misc : string
-};
-
-create dataset DBLP1(DBLPType) primary key id;
-
+array_sum((
+ select value t
+ from [2,2,2,2,2,2] t
+ offset 4
+))
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.5.query.sqlpp
similarity index 73%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.5.query.sqlpp
index 33a9c58..d32190a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.5.query.sqlpp
@@ -16,24 +16,15 @@
* specific language governing permissions and limitations
* under the License.
*/
+
/*
- * Description : Test push down limit into the primary index scan operator
+ * Description : Test offset clause without limit clause (with order by)
* Expected Result : Success
*/
-drop dataverse test if exists;
-create dataverse test;
-
use test;
-create type test.DBLPType as
-{
- id : bigint,
- dblpid : string,
- title : string,
- authors : string,
- misc : string
-};
-
-create dataset DBLP1(DBLPType) primary key id;
-
+select id, dblpid
+from DBLP1 as paper
+order by id
+offset 98
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.6.query.sqlpp
similarity index 73%
copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.6.query.sqlpp
index 33a9c58..8501d99 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/limit_negative_value/limit_negative_value.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/offset_without_limit/offset_without_limit.6.query.sqlpp
@@ -16,24 +16,17 @@
* specific language governing permissions and limitations
* under the License.
*/
+
/*
- * Description : Test push down limit into the primary index scan operator
+ * Description : Test that offset without limit is NOT pushed into a primary scan
* Expected Result : Success
*/
-drop dataverse test if exists;
-create dataverse test;
-
use test;
-create type test.DBLPType as
-{
- id : bigint,
- dblpid : string,
- title : string,
- authors : string,
- misc : string
-};
-
-create dataset DBLP1(DBLPType) primary key id;
+explain
+select id, dblpid
+from DBLP1 as paper
+order by id
+offset 98
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.3.adm
new file mode 100644
index 0000000..1f7a723
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.3.adm
@@ -0,0 +1,2 @@
+5
+6
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.4.adm
new file mode 100644
index 0000000..bf0d87a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.4.adm
@@ -0,0 +1 @@
+4
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.5.adm
new file mode 100644
index 0000000..6687400
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.5.adm
@@ -0,0 +1,2 @@
+{ "id": 99, "dblpid": "series/synthesis/2009Weintraub" }
+{ "id": 100, "dblpid": "series/synthesis/2009Brozos" }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.6.adm
new file mode 100644
index 0000000..726ee49
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/offset_without_limit/offset_without_limit.6.adm
@@ -0,0 +1,18 @@
+distribute result [$$15]
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ limit offset 98
+ -- STREAM_LIMIT |UNPARTITIONED|
+ project ([$$15])
+ -- STREAM_PROJECT |PARTITIONED|
+ assign [$$15] <- [{"id": $$17, "dblpid": $$paper.getField(1)}]
+ -- ASSIGN |PARTITIONED|
+ exchange
+ -- SORT_MERGE_EXCHANGE [$$17(ASC) ] |PARTITIONED|
+ data-scan []<-[$$17, $$paper] <- test.DBLP1
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup-select/push-limit-to-primary-lookup-select.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup-select/push-limit-to-primary-lookup-select.3.adm
index 21618d0..3543f5d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup-select/push-limit-to-primary-lookup-select.3.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup-select/push-limit-to-primary-lookup-select.3.adm
@@ -2,7 +2,7 @@ distribute result [$$c]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
- limit 5, 5
+ limit 5 offset 5
-- STREAM_LIMIT |UNPARTITIONED|
project ([$$c])
-- STREAM_PROJECT |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup/push-limit-to-primary-lookup.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup/push-limit-to-primary-lookup.3.adm
index d070b2b..a0a3c84 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup/push-limit-to-primary-lookup.3.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup/push-limit-to-primary-lookup.3.adm
@@ -2,7 +2,7 @@ distribute result [$$c]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
- limit 5, 5
+ limit 5 offset 5
-- STREAM_LIMIT |UNPARTITIONED|
project ([$$c])
-- STREAM_PROJECT |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup/push-limit-to-primary-lookup.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup/push-limit-to-primary-lookup.5.adm
index 1e25eea..44507f4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup/push-limit-to-primary-lookup.5.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-lookup/push-limit-to-primary-lookup.5.adm
@@ -2,7 +2,7 @@ distribute result [$$c]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
- limit 5, 5
+ limit 5 offset 5
-- STREAM_LIMIT |UNPARTITIONED|
project ([$$c])
-- STREAM_PROJECT |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan-select/push-limit-to-primary-scan-select.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan-select/push-limit-to-primary-scan-select.3.adm
index a1f79bb..db1c3d8 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan-select/push-limit-to-primary-scan-select.3.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan-select/push-limit-to-primary-scan-select.3.adm
@@ -2,7 +2,7 @@ distribute result [$$paper]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
- limit 5, 5
+ limit 5 offset 5
-- STREAM_LIMIT |UNPARTITIONED|
project ([$$paper])
-- STREAM_PROJECT |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.3.adm
index ee3e565..aaf0c53 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.3.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.3.adm
@@ -2,7 +2,7 @@ distribute result [$$paper]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
- limit 5, 5
+ limit 5 offset 5
-- STREAM_LIMIT |UNPARTITIONED|
project ([$$paper])
-- STREAM_PROJECT |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.5.adm
index 939637d..2176e36 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.5.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.5.adm
@@ -2,7 +2,7 @@ distribute result [$$paper]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
- limit 5, 5
+ limit 5 offset 5
-- STREAM_LIMIT |UNPARTITIONED|
project ([$$paper])
-- STREAM_PROJECT |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.8.adm
index e11c19d..06a28e4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.8.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-primary-scan/push-limit-to-primary-scan.8.adm
@@ -2,7 +2,7 @@ distribute result [$$75]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
- limit 5, 5
+ limit 5 offset 5
-- STREAM_LIMIT |UNPARTITIONED|
project ([$$75])
-- STREAM_PROJECT |PARTITIONED|
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 7f002ee..be711d3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -13949,6 +13949,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="limit">
+ <compilation-unit name="offset_without_limit">
+ <output-dir compare="Text">offset_without_limit</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="limit">
<compilation-unit name="push-limit-to-external-scan">
<output-dir compare="Text">push-limit-to-external-scan</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 aaf6761..9904d92 100644
--- a/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
+++ b/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
@@ -82,7 +82,7 @@ HavingClause ::= "HAVING" Expr
GroupAsClause ::= "GROUP AS" Identifier
-Selection ::= WithClause? QueryBlock UnionOption* OrderByClause? LimitClause?
+Selection ::= WithClause? QueryBlock UnionOption* OrderByClause? ( LimitClause | | OffsetClause )?
UnionOption ::= "UNION ALL" (QueryBlock | Subquery)
@@ -92,7 +92,9 @@ WithClause ::= "WITH" Identifier "AS" Expr
OrderbyClause ::= "ORDER BY" Expr ( "ASC" | "DESC" )?
( "," Expr ( "ASC" | "DESC" )? )*
-LimitClause ::= "LIMIT" Expr ("OFFSET" Expr)?
+LimitClause ::= "LIMIT" Expr OffsetClause?
+
+OffsetClause ::= "OFFSET" Expr
Subquery ::= "(" Selection ")"
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md
index 1ac6ab0..5d084c5 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/0_toc.md
@@ -55,7 +55,7 @@
* [GROUP AS Clause](#GROUP_AS_Clause)
* [Selection and UNION ALL](#Union_all)
* [WITH Clauses](#With_clauses)
- * [ORDER By and LIMIT Clauses](#Order_By_clauses)
+ * [ORDER BY, LIMIT, and OFFSET Clauses](#Order_By_clauses)
* [Subqueries](#Subqueries)
* [4. Window Functions](#Over_clauses)
* [Window Function Call](#Window_function_call)
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 5e4358f..17e6339 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
@@ -47,7 +47,7 @@ In SQL++, the `SELECT` clause may appear either at the beginning or at the end o
### <a id="Select_element">SELECT VALUE</a>
-
+
The `SELECT VALUE` clause returns an array or multiset that contains the results of evaluating the `VALUE` expression, with one evaluation being performed per "binding tuple" (i.e., per `FROM` clause item) satisfying the statement's selection criteria.
If there is no `FROM` clause, the expression after `VALUE` is evaluated once with no binding tuples
(except those inherited from an outer environment).
@@ -103,7 +103,7 @@ Returns:
"customer_name": "T. Henry"
}
]
-
+
### <a id="Select_star">SELECT *</a>
As in SQL, the phrase `SELECT *` suggests, "select everything."
@@ -136,7 +136,7 @@ The following example applies `SELECT *` to a single collection.
FROM ages AS a
SELECT * ;
-
+
Result:
[
@@ -190,12 +190,12 @@ Result:
{ "name": "Bill", "age": 21 },
{ "name": "Sue", "age": 32 }
]
-
+
Note that, for queries over a single collection, `SELECT` *variable* `.*` returns a simpler result and therefore may be preferable to `SELECT *`. In fact, `SELECT` *variable* `.*`, like `SELECT *` in SQL, is equivalent to a `SELECT` clause that enumerates all the fields of the collection, as in (Q3.4d):
##### Example
-(Q3.4d) Return all the information in the `ages` collection.
+(Q3.4d) Return all the information in the `ages` collection.
FROM ages AS a
SELECT a.name, a.age
@@ -224,7 +224,7 @@ Result:
### <a id="Select_distinct">SELECT DISTINCT</a>
-The `DISTINCT` keyword is used to eliminate duplicate items from the results of a query block.
+The `DISTINCT` keyword is used to eliminate duplicate items from the results of a query block.
##### Example
@@ -234,7 +234,7 @@ The `DISTINCT` keyword is used to eliminate duplicate items from the results of
SELECT DISTINCT c.address.city;
Result:
-
+
[
{
"city": "Boston, MA"
@@ -248,7 +248,7 @@ Result:
{
"city": "Rome, Italy"
}
- ]
+ ]
### <a id="Unnamed_projections">Unnamed Projections</a>
Similar to standard SQL, the query language supports unnamed projections (a.k.a, unnamed `SELECT` clause items), for which names are generated rather than user-provided.
@@ -286,10 +286,10 @@ As in standard SQL, field access expressions can be abbreviated when there is no
##### Example
-(Q3.7) Same as Q3.6, omitting the variable reference for the order number and date and providing custom names for `SELECT` clause items.
+(Q3.7) Same as Q3.6, omitting the variable reference for the order number and date and providing custom names for `SELECT` clause items.
FROM orders AS o
- WHERE o.custid = "C41"
+ WHERE o.custid = "C41"
SELECT orderno % 1000 AS last_digit, order_date;
Result:
@@ -326,24 +326,24 @@ Result:
##### Synonyms for `UNNEST`: `CORRELATE`, `FLATTEN`
---
-The purpose of a `FROM` clause is to iterate over a collection, binding a variable to each item in turn. Here's a query that iterates over the `customers` dataset, choosing certain customers and returning some of their attributes.
+The purpose of a `FROM` clause is to iterate over a collection, binding a variable to each item in turn. Here's a query that iterates over the `customers` dataset, choosing certain customers and returning some of their attributes.
##### Example
-
+
(Q3.8) List the customer ids and names of the customers in zipcode 63101, in order by their customer IDs.
-
+
FROM customers
WHERE address.zipcode = "63101"
SELECT custid AS customer_id, name
ORDER BY customer_id;
-
+
Result:
-
+
[
{
@@ -359,43 +359,43 @@ Result:
"name": "R. Dodge"
}
]
-
+
Let's take a closer look at what this `FROM` clause is doing. A `FROM` clause always produces a stream of bindings, in which an iteration variable is bound in turn to each item in a collection. In Q3.8, since no explicit iteration variable is provided, the `FROM` clause defines an implicit variable named `customers`, the same name as the dataset that is being iterated over. The implicit iteration variable serves as the object-name for all field-names in the query block that do not have e [...]
You may also provide an explicit iteration variable, as in this version of the same query:
-##### Example
+##### Example
(Q3.9) Alternative version of Q3.8 (same result).
-
+
FROM customers AS c
WHERE c.address.zipcode = "63101"
SELECT c.custid AS customer_id, c.name
ORDER BY customer_id;
-
+
In Q3.9, the variable `c` is bound to each `customer` object in turn as the query iterates over the `customers` dataset. An explicit iteration variable can be used to identify the fields of the referenced object, as in `c.name` in the `SELECT` clause of Q3.9. When referencing a field of an object, the iteration variable can be omitted when there is no ambiguity. For example, `c.name` could be replaced by `name` in the `SELECT` clause of Q3.9. That's why field-names like `name` and `custi [...]
-
+
In the examples above, the `FROM` clause iterates over the objects in a dataset. But in general, a `FROM` clause can iterate over any collection. For example, the objects in the `orders` dataset each contain a field called `items`, which is an array of nested objects. In some cases, you will write a `FROM` clause that iterates over a nested array like `items`.
-
+
The stream of objects (more accurately, variable bindings) that is produced by the `FROM` clause does not have any particular order. The system will choose the most efficient order for the iteration. If you want your query result to have a specific order, you must use an `ORDER BY` clause.
-
+
It's good practice to specify an explicit iteration variable for each collection in the `FROM` clause, and to use these variables to qualify the field-names in other clauses. Here are some reasons for this convention:
-
+
- It's nice to have different names for the collection as a whole and an object in the collection. For example, in the clause `FROM customers AS c`, the name `customers` represents the dataset and the name `c` represents one object in the dataset.
-
+
- In some cases, iteration variables are required. For example, when joining a dataset to itself, distinct iteration variables are required to distinguish the left side of the join from the right side.
-
+
- In a subquery it's sometimes necessary to refer to an object in an outer query block (this is called a *correlated subquery*). To avoid confusion in correlated subqueries, it's best to use explicit variables.
-
+
### <a id="Left_outer_unnests">Joins</a>
@@ -405,7 +405,7 @@ A `FROM` clause gets more interesting when there is more than one collection inv
(Q3.10) Create a packing list for order number 1001, showing the customer name and address and all the items in the order.
-
+
FROM customers AS c, orders AS o
WHERE c.custid = o.custid
AND o.orderno = 1001
@@ -414,7 +414,7 @@ A `FROM` clause gets more interesting when there is more than one collection inv
c.address,
o.items AS items_ordered;
-
+
Result:
[
@@ -441,11 +441,11 @@ Result:
}
]
-
+
Q3.10 is called a *join query* because it joins the `customers` collection and the `orders` collection, using the join condition `c.custid = o.custid`. In SQL++, as in SQL, you can express this query more explicitly by a `JOIN` clause that includes the join condition, as follows:
-
+
##### Example
(Q3.11) Alternative statement of Q3.10 (same result).
@@ -459,10 +459,10 @@ Q3.10 is called a *join query* because it joins the `customers` collection and t
c.address,
o.items AS items_ordered;
-
+
Whether you express the join condition in a `JOIN` clause or in a `WHERE` clause is a matter of taste; the result is the same. This manual will generally use a comma-separated list of collection-names in the `FROM` clause, leaving the join condition to be expressed elsewhere. As we'll soon see, in some query blocks the join condition can be omitted entirely.
-
+
There is, however, one case in which an explicit `JOIN` clause is necessary. That is when you need to join collection A to collection B, and you want to make sure that every item in collection A is present in the query result, even if it doesn't match any item in collection B. This kind of query is called a *left outer join*, and it is illustrated by the following example.
##### Example
@@ -475,7 +475,7 @@ There is, however, one case in which an explicit `JOIN` clause is necessary. Tha
SELECT c.custid, c.name, o.orderno, o.order_date
ORDER BY c.custid, o.order_date;
-
+
Result:
@@ -509,17 +509,17 @@ Result:
"name": "M. Sinclair"
}
]
-
+
As you can see from the result of this left outer join, our data includes four orders from customer T. Cody, but no orders from customer M. Sinclair. The behavior of left outer join in SQL++ is different from that of SQL. SQL would have provided M. Sinclair with an order in which all the fields were `null`. SQL++, on the other hand, deals with schemaless data, which permits it to simply omit the order fields from the outer join.
Now we're ready to look at a new kind of join that was not provided (or needed) in original SQL. Consider this query:
-##### Example
+##### Example
(Q3.13) For every case in which an item is ordered in a quantity greater than 100, show the order number, date, item number, and quantity.
-
+
FROM orders AS o, o.items AS i
WHERE i.qty > 100
@@ -549,13 +549,13 @@ Result:
"quantity": 120
}
]
-
+
Q3.13 illustrates a feature called *left-correlation* in the `FROM` clause. Notice that we are joining `orders`, which is a dataset, to `items`, which is an array nested inside each order. In effect, for each order, we are unnesting the `items` array and joining it to the `order` as though it were a separate collection. For this reason, this kind of query is sometimes called an *unnesting query*. The keyword `UNNEST` may be used whenever left-correlation is used in a `FROM` clause, as sh [...]
-
-
-##### Example
+
+
+##### Example
(Q3.14) Alternative statement of Q3.13 (same result).
@@ -565,16 +565,16 @@ Q3.13 illustrates a feature called *left-correlation* in the `FROM` clause. Noti
i.qty AS quantity
ORDER BY o.orderno, item_number;
-
+
The results of Q3.13 and Q3.14 are exactly the same. `UNNEST` serves as a reminder that left-correlation is being used to join an object with its nested items. The join condition in Q3.14 is expressed by the left-correlation: each order `o` is joined to its own items, referenced as `o.items`. The result of the `FROM` clause is a stream of binding tuples, each containing two variables, `o` and `i`. The variable `o` is bound to an order and the variable `i` is bound to one item inside that order.
Like `JOIN`, `UNNEST` has a `LEFT OUTER` option. Q3.14 could have specified:
-
+
FROM orders AS o LEFT OUTER UNNEST o.items AS i
-
+
In this case, orders that have no nested items would appear in the query result.
@@ -592,10 +592,10 @@ In this case, orders that have no nested items would appear in the query result.
`LET` clauses can be useful when a (complex) expression is used several times within a query, allowing it to be written once to make the query more concise. The word `LETTING` can also be used, although this is not as common. The next query shows an example.
##### Example
-
+
(Q3.15) For each item in an order, the revenue is defined as the quantity times the price of that item. Find individual items for which the revenue is greater than 5000. For each of these, list the order number, item number, and revenue, in descending order by revenue.
-
+
FROM orders AS o, o.items AS i
LET revenue = i.qty * i.price
@@ -622,7 +622,7 @@ Result:
"revenue": 5525
}
]
-
+
The expression for computing revenue is defined once in the `LET` clause and then used three times in the remainder of the query. Avoiding repetition of the revenue expression makes the query shorter and less prone to errors.
@@ -698,13 +698,13 @@ In the `GROUP BY`clause, you may optionally define an alias for the grouping exp
Q3.16 had a single grouping expression, `o.custid`. If a query has multiple grouping expressions, the combination of grouping expressions is evaluated for every binding tuple, and the stream of binding tuples is partitioned into groups that have values in common for all of the grouping expressions. We'll see an example of such a query in Q3.18.
-
+
After grouping, the number of binding tuples is reduced: instead of a binding tuple for each of the input objects, there is a binding tuple for each group. The grouping expressions (identified by their aliases, if any) are bound to the results of their evaluations. However, all the non-grouping fields (that is, fields that were not named in the grouping expressions), are accessible only in a special way: as an argument of one of the special aggregation pseudo-functions such as: `SUM`, `A [...]
You may notice that the results of Q3.16 do not include customers who have no `orders`. If we want to include these `customers`, we need to use an outer join between the `customers` and `orders` collections. This is illustrated by the following example, which also includes the name of each customer.
##### Example
-
+
(Q3.17) List the number of orders placed by each customer including those customers who have placed no orders.
SELECT c.custid, c.name, COUNT(o.orderno) AS `order count`
@@ -752,7 +752,7 @@ You may notice that the results of Q3.16 do not include customers who have no `o
}
]
-
+
Notice in Q3.17 what happens when the special aggregation function `COUNT` is applied to a collection that does not exist, such as the orders of M. Sinclair: it returns zero. This behavior is unlike that of the other special aggregation functions `SUM`, `AVG`, `MAX`, and `MIN`, which return `null` if their operand does not exist. This should make you cautious about the `COUNT` function: If it returns zero, that may mean that the collection you are counting has zero members, or that it do [...]
Q3.17 also shows how a query block can have more than one grouping expression. In general, the `GROUP BY`clause produces a binding tuple for each different combination of values for the grouping expressions. In Q3.17, the `c.custid` field uniquely identifies a customer, so adding `c.name` as a grouping expression does not result in any more groups. Nevertheless, `c.name` must be included as a grouping expression if it is to be referenced outside (after) the `GROUP BY` clause. If `c.name` [...]
@@ -802,9 +802,9 @@ Q3.19 also shows how a `LET` clause can be used after a `GROUP BY` clause to def
LET total_revenue = sum(i.qty * i.price)
SELECT o.orderno, total_revenue
ORDER BY total_revenue desc;
-
+
Result:
-
+
[
{
"orderno": 1002,
@@ -836,7 +836,7 @@ By adding a `HAVING` clause to Q3.19, we can filter the results to include only
##### Example
-
+
(Q3.20) Modify Q3.19 to include only orders whose total revenue is greater than 5000.
FROM orders AS o, o.items as i
@@ -861,12 +861,12 @@ Result:
SQL provides several special functions for performing aggregations on groups including: `SUM`, `AVG`, `MAX`, `MIN`, and `COUNT` (some implementations provide more). These same functions are supported in SQL++. However, it's worth spending some time on these special functions because they don't behave like ordinary functions. They are called "pseudo-functions" here because they don't evaluate their operands in the same way as ordinary functions. To see the difference, consider these two e [...]
##### Example 1:
-
+
SELECT LENGTH(name) FROM customers
In Example 1, `LENGTH` is an ordinary function. It simply evaluates its operand (name) and then returns a result computed from the operand.
-##### Example 2:
+##### Example 2:
SELECT AVG(rating) FROM customers
The effect of `AVG` in Example 2 is quite different. Rather than performing a computation on an individual rating value, `AVG` has a global effect: it effectively restructures the query. As a pseudo-function, `AVG` requires its operand to be a group; therefore, it automatically collects all the rating values from the query block and forms them into a group.
@@ -906,7 +906,7 @@ When an aggregation pseudo-function is used without an explicit `GROUP BY` claus
##### Example
(Q3.22) Find the average credit rating among all customers.
-
+
FROM customers AS c
SELECT AVG(c.rating) AS `avg credit rating`;
@@ -919,15 +919,15 @@ Result:
}
]
-
+
The aggregation pseudo-function `COUNT` has a special form in which its operand is `*` instead of an expression. For example, `SELECT COUNT(*) FROM customers` simply returns the total number of customers, whereas `SELECT COUNT(rating) FROM customers` returns the number of customers who have known ratings (that is, their ratings are not `null` or `missing`).
-
+
Because the aggregation pseudo-functions sometimes restructure their operands, they can be used only in query blocks where (explicit or implicit) grouping is being done. Therefore the pseudo-functions cannot operate directly on arrays or multisets. For operating directly on JSON collections, SQL++ provides a set of ordinary functions for computing aggregations. Each ordinary aggregation function (except the ones corresponding to `COUNT` and `ARRAY_AGG`) has two versions: one that ignore [...]
-
-| Aggregation pseudo-function; operates on groups only | ordinary functions: Ignores NULL or MISSING values | ordinary functions: Returns NULL if NULL or MISSING are encountered|
+
+| Aggregation pseudo-function; operates on groups only | ordinary functions: Ignores NULL or MISSING values | ordinary functions: Returns NULL if NULL or MISSING are encountered|
|----------|----------|--------|
|SUM| ARRAY_SUM| STRICT_SUM |
| AVG |ARRAY_MAX| STRICT_MAX |
@@ -947,15 +947,15 @@ The aggregation pseudo-function `COUNT` has a special form in which its operand
Note that the ordinary aggregation functions that ignore `null` have names beginning with "ARRAY." This naming convention has historical roots. Despite their names, the functions operate on both arrays and multisets.
-
+
Because of the special properties of the aggregation pseudo-functions, SQL (and therefore SQL++) is not a pure functional language. But every query that uses a pseudo-function can be expressed as an equivalent query that uses an ordinary function. Q3.23 is an example of how queries can be expressed without pseudo-functions. A more detailed explanation of all of the functions is also available [here](builtins.html#AggregateFunctions) .
-##### Example
+##### Example
(Q3.23) Alternative form of Q3.22, using the ordinary function `ARRAY_AVG` rather than the aggregating pseudo-function `AVG`.
-
+
SELECT ARRAY_AVG(
(SELECT VALUE c.rating
@@ -963,7 +963,7 @@ Because of the special properties of the aggregation pseudo-functions, SQL (and
Result (same as Q3.22):
-
+
[
{
"avg credit rating": 670
@@ -986,17 +986,17 @@ If the function `STRICT_AVG` had been used in Q3.23 in place of `ARRAY_AVG`, the
JSON is a hierarchical format, and a fully featured JSON query language needs to be able to produce hierarchies of its own, with computed data at every level of the hierarchy. The key feature of SQL++ that makes this possible is the `GROUP AS` clause.
-
+
A query may have a `GROUP AS` clause only if it has a `GROUP BY` clause. The `GROUP BY` clause "hides" the original objects in each group, exposing only the grouping expressions and special aggregation functions on the non-grouping fields. The purpose of the `GROUP AS` clause is to make the original objects in the group visible to subsequent clauses. Thus the query can generate output data both for the group as a whole and for the individual objects inside the group.
-
+
For each group, the `GROUP AS` clause preserves all the objects in the group, just as they were before grouping, and gives a name to this preserved group. The group name can then be used in the `FROM` clause of a subquery to process and return the individual objects in the group.
-
-To see how this works, we'll write some queries that investigate the customers in each zipcode and their credit ratings. This would be a good time to review the sample database in Appendix 4. A part of the data is summarized below.
+
+To see how this works, we'll write some queries that investigate the customers in each zipcode and their credit ratings. This would be a good time to review the sample database in Appendix 4. A part of the data is summarized below.
Customers in zipcode 02115:
C35, J. Roberts, rating 565
@@ -1009,11 +1009,11 @@ To see how this works, we'll write some queries that investigate the customers i
C13, T. Cody, rating 750
C31, B. Pruitt, (no rating)
C41, R. Dodge, rating 640
-
+
Customers with no zipcode:
C47, S. Logan, rating 625
-
+
Now let's consider the effect of the following clauses:
@@ -1022,12 +1022,12 @@ Now let's consider the effect of the following clauses:
GROUP AS g
This query fragment iterates over the `customers` objects, using the iteration variable `c`. The `GROUP BY` clause forms the objects into groups, each with a common zipcode (including one group for customers with no zipcode). After the `GROUP BY` clause, we can see the grouping expression, `c.address.zipcode`, but other fields such as `c.custid` and `c.name` are visible only to special aggregation functions.
-
+
The clause `GROUP AS g` now makes the original objects visible again. For each group in turn, the variable `g` is bound to a multiset of objects, each of which has a field named `c`, which in turn contains one of the original objects. Thus after `GROUP AS g`, for the group with zipcode 02115, `g` is bound to the following multiset:
-
- [
- { "c":
+
+ [
+ { "c":
{ "custid": "C35",
"name": "J. Roberts",
"address":
@@ -1051,7 +1051,7 @@ The clause `GROUP AS g` now makes the original objects visible again. For each g
}
]
-
+
Thus, the clauses following `GROUP AS` can see the original objects by writing subqueries that iterate over the multiset `g`.
@@ -1064,7 +1064,7 @@ The extra level named `c` was introduced into this multiset because the groups m
In this case, following `GROUP AS g`, the variable `g` would be bound to the following collection:
- [
+ [
{ "c": { an original customers object },
"o": { an original orders object }
},
@@ -1078,7 +1078,7 @@ After using `GROUP AS` to make the content of a group accessible, you will proba
Now we are ready to take a look at how `GROUP AS` might be used in a query. Suppose that we want to group customers by zipcode, and for each group we want to see the average credit rating and a list of the individual customers in the group. Here's a query that does that:
-##### Example
+##### Example
(Q3.24) For each zipcode, list the average credit rating in that zipcode, followed by the customer numbers and names in numeric order.
FROM customers AS c
@@ -1166,13 +1166,13 @@ When two or more query blocks are connected by `UNION ALL`, they can be followed
In this example, a customer might be selected because he has ordered more than two different items (first query block) or because he has a high credit rating (second query block). By adding an explanatory string to each query block, the query writer can cause the output objects to be labeled to distinguish these two cases.
-
+
##### Example
(Q3.25a) Find customer ids for customers who have placed orders for more than two different items or who have a credit rating greater than 700, with labels to distinguish these cases.
-
+
FROM orders AS o, o.items AS i
GROUP BY o.orderno, o.custid
@@ -1188,7 +1188,7 @@ In this example, a customer might be selected because he has ordered more than t
Result:
-
+
[
{
"reason": "High rating",
@@ -1208,15 +1208,15 @@ Result:
}
]
-
+
If, on the other hand, you simply want a list of the customer ids and you don't care to preserve the reasons, you can simplify your output by using `SELECT VALUE`, as follows:
-
+
(Q3.25b) Simplify Q3.25a to return a simple list of unlabeled customer ids.
-
+
FROM orders AS o, o.items AS i
GROUP BY o.orderno, o.custid
@@ -1252,7 +1252,7 @@ As in standard SQL, a `WITH` clause can be used to improve the modularity of a q
##### Example
-(Q3.26) Find the minimum, maximum, and average revenue among all orders in 2020, rounded to the nearest integer.
+(Q3.26) Find the minimum, maximum, and average revenue among all orders in 2020, rounded to the nearest integer.
WITH order_revenue AS
(FROM orders AS o, o.items AS i
@@ -1264,7 +1264,7 @@ As in standard SQL, a `WITH` clause can be used to improve the modularity of a q
SELECT AVG(revenue) AS average,
MIN(revenue) AS minimum,
MAX(revenue) AS maximum;
-
+
Result:
@@ -1278,7 +1278,7 @@ Result:
`WITH` can be particularly useful when a value needs to be used several times in a query.
-## <a id="Order_By_clauses">ORDER BY and LIMIT Clauses</a>
+## <a id="Order_By_clauses">ORDER BY, LIMIT, and OFFSET Clauses</a>
---
### OrderbyClause
@@ -1287,16 +1287,20 @@ Result:
### LimitClause
****
+### OffsetClause
+****
---
-
-The last two (optional) clauses to be processed in a query are `ORDER BY` and `LIMIT`.
+
+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
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 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.
+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.
+The `OFFSET` can also be used as a standalone clause, without the `LIMIT`.
The following example illustrates use of the `ORDER BY` and `LIMIT` clauses.
@@ -1365,7 +1369,7 @@ A subquery is simply a query surrounded by parentheses. In SQL++, a subquery can
##### Example
(Q3.29)(Subquery in SELECT clause)
-For every order that includes item no. 120, find the order number, customer id, and customer name.
+For every order that includes item no. 120, find the order number, customer id, and customer name.
Here, the subquery is used to find a customer name, given a customer id. Since the outer query expects a scalar result, the subquery uses SELECT VALUE and is followed by the indexing operator [0].
@@ -1398,7 +1402,7 @@ Find the customer number, name, and rating of all customers whose rating is grea
Here, the subquery is used to find the average rating among all customers. Once again, SELECT VALUE and indexing [0] have been used to get a single scalar value.
-
+
FROM customers AS c1
WHERE c1.rating >
(FROM customers AS c2
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/clause/LimitClause.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/clause/LimitClause.java
index d597bc2..25ddce4 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/clause/LimitClause.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/clause/LimitClause.java
@@ -26,15 +26,16 @@ import org.apache.asterix.lang.common.base.Expression;
import org.apache.asterix.lang.common.visitor.base.ILangVisitor;
public class LimitClause extends AbstractClause {
+
private Expression limitExpr;
- private Expression offset;
- public LimitClause() {
- // Default constructor.
- }
+ private Expression offset;
- public LimitClause(Expression limitexpr, Expression offset) {
- this.limitExpr = limitexpr;
+ public LimitClause(Expression limitExpr, Expression offset) {
+ if (limitExpr == null && offset == null) {
+ throw new IllegalArgumentException();
+ }
+ this.limitExpr = limitExpr;
this.offset = offset;
}
@@ -46,6 +47,10 @@ public class LimitClause extends AbstractClause {
this.limitExpr = limitexpr;
}
+ public boolean hasLimitExpr() {
+ return limitExpr != null;
+ }
+
public Expression getOffset() {
return offset;
}
@@ -82,6 +87,6 @@ public class LimitClause extends AbstractClause {
return false;
}
LimitClause target = (LimitClause) object;
- return limitExpr.equals(target.getLimitExpr()) && Objects.equals(offset, target.getOffset());
+ return Objects.equals(limitExpr, target.getLimitExpr()) && Objects.equals(offset, target.getOffset());
}
}
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/AbstractInlineUdfsVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/AbstractInlineUdfsVisitor.java
index c866eff..f1bcb93 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/AbstractInlineUdfsVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/AbstractInlineUdfsVisitor.java
@@ -244,13 +244,16 @@ public abstract class AbstractInlineUdfsVisitor extends AbstractQueryExpressionV
@Override
public Boolean visit(LimitClause lc, List<FunctionDecl> arg) throws CompilationException {
- Pair<Boolean, Expression> p1 = inlineUdfsInExpr(lc.getLimitExpr(), arg);
- lc.setLimitExpr(p1.second);
- boolean changed = p1.first;
- if (lc.getOffset() != null) {
+ boolean changed = false;
+ if (lc.hasLimitExpr()) {
+ Pair<Boolean, Expression> p1 = inlineUdfsInExpr(lc.getLimitExpr(), arg);
+ lc.setLimitExpr(p1.second);
+ changed = p1.first;
+ }
+ if (lc.hasOffset()) {
Pair<Boolean, Expression> p2 = inlineUdfsInExpr(lc.getOffset(), arg);
lc.setOffset(p2.second);
- changed = changed || p2.first;
+ changed |= p2.first;
}
return changed;
}
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 97701e4..1fcf822 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
@@ -198,15 +198,17 @@ public class CloneAndSubstituteVariablesVisitor extends
@Override
public Pair<ILangExpression, VariableSubstitutionEnvironment> visit(LimitClause lc,
VariableSubstitutionEnvironment env) throws CompilationException {
- Pair<ILangExpression, VariableSubstitutionEnvironment> p1 = lc.getLimitExpr().accept(this, env);
- Pair<ILangExpression, VariableSubstitutionEnvironment> p2;
- Expression lcOffsetExpr = lc.getOffset();
- if (lcOffsetExpr != null) {
- p2 = lcOffsetExpr.accept(this, env);
- } else {
- p2 = new Pair<>(null, null);
+ Expression newLimitExpr = null;
+ if (lc.hasLimitExpr()) {
+ Pair<ILangExpression, VariableSubstitutionEnvironment> p1 = lc.getLimitExpr().accept(this, env);
+ newLimitExpr = (Expression) p1.first;
+ }
+ Expression newOffsetExpr = null;
+ if (lc.hasOffset()) {
+ Pair<ILangExpression, VariableSubstitutionEnvironment> p2 = lc.getOffset().accept(this, env);
+ newOffsetExpr = (Expression) p2.first;
}
- LimitClause c = new LimitClause((Expression) p1.first, (Expression) p2.first);
+ LimitClause c = new LimitClause(newLimitExpr, newOffsetExpr);
c.setSourceLocation(lc.getSourceLocation());
return new Pair<>(c, env);
}
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 290f9ea..0474c0e 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
@@ -317,11 +317,18 @@ public abstract class FormatPrintVisitor implements ILangVisitor<Void, Integer>
@Override
public Void visit(LimitClause lc, Integer step) throws CompilationException {
- out.print(skip(step) + "limit ");
- lc.getLimitExpr().accept(this, step + 1);
- if (lc.getOffset() != null) {
- out.print(" offset ");
+ if (lc.hasLimitExpr()) {
+ out.print(skip(step) + "limit ");
+ lc.getLimitExpr().accept(this, step + 1);
+ if (lc.hasOffset()) {
+ out.print(" offset ");
+ lc.getOffset().accept(this, step + 1);
+ }
+ } else if (lc.hasOffset()) {
+ out.print(skip(step) + "offset ");
lc.getOffset().accept(this, step + 1);
+ } else {
+ throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, lc.getSourceLocation(), "");
}
out.println();
return null;
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/GatherFunctionCallsVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/GatherFunctionCallsVisitor.java
index 858729c..44b2092 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/GatherFunctionCallsVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/GatherFunctionCallsVisitor.java
@@ -138,8 +138,10 @@ public class GatherFunctionCallsVisitor extends AbstractQueryExpressionVisitor<V
@Override
public Void visit(LimitClause lc, Void arg) throws CompilationException {
- lc.getLimitExpr().accept(this, arg);
- if (lc.getOffset() != null) {
+ if (lc.hasLimitExpr()) {
+ lc.getLimitExpr().accept(this, arg);
+ }
+ if (lc.hasOffset()) {
lc.getOffset().accept(this, arg);
}
return null;
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 64d97f3..e756eee 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
@@ -252,11 +252,18 @@ public abstract class QueryPrintVisitor extends AbstractQueryExpressionVisitor<V
@Override
public Void visit(LimitClause lc, Integer step) throws CompilationException {
- out.println(skip(step) + "Limit");
- lc.getLimitExpr().accept(this, step + 1);
- if (lc.getOffset() != null) {
- out.println(skip(step + 1) + "Offset");
- lc.getOffset().accept(this, step + 2);
+ if (lc.hasLimitExpr()) {
+ out.println(skip(step) + "Limit");
+ lc.getLimitExpr().accept(this, step + 1);
+ if (lc.hasOffset()) {
+ out.println(skip(step + 1) + "Offset");
+ lc.getOffset().accept(this, step + 2);
+ }
+ } else if (lc.hasOffset()) {
+ out.println(skip(step) + "Offset");
+ lc.getOffset().accept(this, step + 1);
+ } else {
+ throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, lc.getSourceLocation(), "");
}
return null;
}
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 51bbe98..b5375d2 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
@@ -324,7 +324,8 @@ public class DeepCopyVisitor extends AbstractSqlppQueryExpressionVisitor<ILangEx
@Override
public LimitClause visit(LimitClause limitClause, Void arg) throws CompilationException {
- Expression limitExpr = (Expression) limitClause.getLimitExpr().accept(this, arg);
+ Expression limitExpr =
+ limitClause.hasLimitExpr() ? (Expression) limitClause.getLimitExpr().accept(this, arg) : null;
Expression offsetExpr = limitClause.hasOffset() ? (Expression) limitClause.getOffset().accept(this, arg) : null;
LimitClause copy = new LimitClause(limitExpr, offsetExpr);
copy.setSourceLocation(limitClause.getSourceLocation());
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppExpressionScopingVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppExpressionScopingVisitor.java
index f0a9d9a..7539046 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppExpressionScopingVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppExpressionScopingVisitor.java
@@ -286,7 +286,9 @@ public class AbstractSqlppExpressionScopingVisitor extends AbstractSqlppSimpleEx
@Override
public Expression visit(LimitClause limitClause, ILangExpression arg) throws CompilationException {
scopeChecker.pushForbiddenScope(scopeChecker.getCurrentScope());
- limitClause.setLimitExpr(visit(limitClause.getLimitExpr(), limitClause));
+ if (limitClause.hasLimitExpr()) {
+ limitClause.setLimitExpr(visit(limitClause.getLimitExpr(), limitClause));
+ }
if (limitClause.hasOffset()) {
limitClause.setOffset(visit(limitClause.getOffset(), limitClause));
}
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppSimpleExpressionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppSimpleExpressionVisitor.java
index 6dacea6..e331173 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppSimpleExpressionVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppSimpleExpressionVisitor.java
@@ -223,7 +223,9 @@ public class AbstractSqlppSimpleExpressionVisitor
@Override
public Expression visit(LimitClause limitClause, ILangExpression arg) throws CompilationException {
- limitClause.setLimitExpr(visit(limitClause.getLimitExpr(), limitClause));
+ if (limitClause.hasLimitExpr()) {
+ limitClause.setLimitExpr(visit(limitClause.getLimitExpr(), limitClause));
+ }
if (limitClause.hasOffset()) {
limitClause.setOffset(visit(limitClause.getOffset(), limitClause));
}
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index 5ecc35f..a4995da 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -4417,16 +4417,23 @@ HavingClause HavingClause() throws ParseException:
LimitClause LimitClause() throws ParseException:
{
Token startToken = null;
- LimitClause lc = new LimitClause();
- Expression expr;
- pushForbiddenScope(getCurrentScope());
+ Expression limitExpr = null, offsetExpr = null;
}
{
- <LIMIT> { startToken = token; } expr = Expression() { lc.setLimitExpr(expr); }
- (<OFFSET> expr = Expression() { lc.setOffset(expr); })?
-
+ (
+ (
+ <LIMIT> { startToken = token; pushForbiddenScope(getCurrentScope()); } limitExpr = Expression()
+ ( <OFFSET> offsetExpr = Expression() )?
+ { popForbiddenScope(); }
+ )
+ |
+ (
+ <OFFSET> { startToken = token; pushForbiddenScope(getCurrentScope()); } offsetExpr = Expression()
+ { popForbiddenScope(); }
+ )
+ )
{
- popForbiddenScope();
+ LimitClause lc = new LimitClause(limitExpr, offsetExpr);
return addSourceLocation(lc, startToken);
}
}
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/LimitOperator.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/LimitOperator.java
index 313b77e..7f13f61 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/LimitOperator.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/LimitOperator.java
@@ -25,7 +25,6 @@ import org.apache.commons.lang3.mutable.MutableObject;
import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
-import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
import org.apache.hyracks.algebricks.core.algebra.expressions.IVariableTypeEnvironment;
import org.apache.hyracks.algebricks.core.algebra.properties.VariablePropagationPolicy;
import org.apache.hyracks.algebricks.core.algebra.typing.ITypingContext;
@@ -34,32 +33,43 @@ import org.apache.hyracks.algebricks.core.algebra.visitors.ILogicalOperatorVisit
public class LimitOperator extends AbstractLogicalOperator {
- private final Mutable<ILogicalExpression> maxObjects; // mandatory
- private final Mutable<ILogicalExpression> offset; // optional
- private boolean topmost;
+ private final Mutable<ILogicalExpression> maxObjects; // optional, if not specified then offset is required
+ private final Mutable<ILogicalExpression> offset; // optional if maxObjects is specified, required otherwise
+ private final boolean topmost;
public LimitOperator(ILogicalExpression maxObjectsExpr, ILogicalExpression offsetExpr, boolean topmost) {
- this.maxObjects = new MutableObject<ILogicalExpression>(maxObjectsExpr);
- this.offset = new MutableObject<ILogicalExpression>(offsetExpr);
+ if (maxObjectsExpr == null && offsetExpr == null) {
+ throw new IllegalArgumentException();
+ }
+ this.maxObjects = new MutableObject<>(maxObjectsExpr);
+ this.offset = new MutableObject<>(offsetExpr);
this.topmost = topmost;
}
+ public LimitOperator(ILogicalExpression maxObjects, ILogicalExpression offset) {
+ this(maxObjects, offset, true);
+ }
+
public LimitOperator(ILogicalExpression maxObjectsExpr, boolean topmost) {
this(maxObjectsExpr, null, topmost);
}
- public LimitOperator(ILogicalExpression maxObjects, ILogicalExpression offset) {
- this(maxObjects, offset, true);
+ public LimitOperator(ILogicalExpression maxObjects) {
+ this(maxObjects, true);
}
- public LimitOperator(ILogicalExpression maxObjects) {
- this(maxObjects, null, true);
+ public boolean hasMaxObjects() {
+ return maxObjects.getValue() != null;
}
public Mutable<ILogicalExpression> getMaxObjects() {
return maxObjects;
}
+ public boolean hasOffset() {
+ return offset.getValue() != null;
+ }
+
public Mutable<ILogicalExpression> getOffset() {
return offset;
}
@@ -70,7 +80,7 @@ public class LimitOperator extends AbstractLogicalOperator {
@Override
public void recomputeSchema() {
- schema = new ArrayList<LogicalVariable>();
+ schema = new ArrayList<>();
schema.addAll(inputs.get(0).getValue().getSchema());
}
@@ -82,13 +92,11 @@ public class LimitOperator extends AbstractLogicalOperator {
@Override
public boolean acceptExpressionTransform(ILogicalExpressionReferenceTransform visitor) throws AlgebricksException {
boolean b = false;
- if (visitor.transform(maxObjects)) {
- b = true;
+ if (hasMaxObjects()) {
+ b = visitor.transform(maxObjects);
}
- if (offset.getValue() != null) {
- if (visitor.transform(offset)) {
- b = true;
- }
+ if (hasOffset()) {
+ b |= visitor.transform(offset);
}
return b;
}
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismOperatorVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismOperatorVisitor.java
index 5dfdbbd..09d0c14 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismOperatorVisitor.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismOperatorVisitor.java
@@ -197,11 +197,13 @@ public class IsomorphismOperatorVisitor implements ILogicalOperatorVisitor<Boole
return Boolean.FALSE;
}
LimitOperator limitOpArg = (LimitOperator) copyAndSubstituteVar(op, arg);
+ if (!Objects.equals(op.getMaxObjects().getValue(), limitOpArg.getMaxObjects().getValue())) {
+ return Boolean.FALSE;
+ }
if (!Objects.equals(op.getOffset().getValue(), limitOpArg.getOffset().getValue())) {
return Boolean.FALSE;
}
- boolean isomorphic = op.getMaxObjects().getValue().equals(limitOpArg.getMaxObjects().getValue());
- return isomorphic;
+ return Boolean.TRUE;
}
@Override
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/SubstituteVariableVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/SubstituteVariableVisitor.java
index f7c7287..61da1b5 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/SubstituteVariableVisitor.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/SubstituteVariableVisitor.java
@@ -179,10 +179,11 @@ public class SubstituteVariableVisitor
@Override
public Void visitLimitOperator(LimitOperator op, Pair<LogicalVariable, LogicalVariable> pair)
throws AlgebricksException {
- op.getMaxObjects().getValue().substituteVar(pair.first, pair.second);
- ILogicalExpression offset = op.getOffset().getValue();
- if (offset != null) {
- offset.substituteVar(pair.first, pair.second);
+ if (op.hasMaxObjects()) {
+ op.getMaxObjects().getValue().substituteVar(pair.first, pair.second);
+ }
+ if (op.hasOffset()) {
+ op.getOffset().getValue().substituteVar(pair.first, pair.second);
}
substVarTypes(op, pair);
return null;
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/UsedVariableVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/UsedVariableVisitor.java
index 182f61d..65e9023 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/UsedVariableVisitor.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/UsedVariableVisitor.java
@@ -219,10 +219,11 @@ public class UsedVariableVisitor implements ILogicalOperatorVisitor<Void, Void>
@Override
public Void visitLimitOperator(LimitOperator op, Void arg) {
- op.getMaxObjects().getValue().getUsedVariables(usedVariables);
- ILogicalExpression offsetExpr = op.getOffset().getValue();
- if (offsetExpr != null) {
- offsetExpr.getUsedVariables(usedVariables);
+ if (op.hasMaxObjects()) {
+ op.getMaxObjects().getValue().getUsedVariables(usedVariables);
+ }
+ if (op.hasOffset()) {
+ op.getOffset().getValue().getUsedVariables(usedVariables);
}
return null;
}
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/physical/StreamLimitPOperator.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/physical/StreamLimitPOperator.java
index 90732ce..5939ec2 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/physical/StreamLimitPOperator.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/physical/StreamLimitPOperator.java
@@ -20,7 +20,6 @@ package org.apache.hyracks.algebricks.core.algebra.operators.physical;
import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
import org.apache.hyracks.algebricks.core.algebra.base.IHyracksJobBuilder;
-import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
import org.apache.hyracks.algebricks.core.algebra.base.PhysicalOperatorTag;
@@ -89,11 +88,10 @@ public class StreamLimitPOperator extends AbstractPhysicalOperator {
LimitOperator limit = (LimitOperator) op;
IExpressionRuntimeProvider expressionRuntimeProvider = context.getExpressionRuntimeProvider();
IVariableTypeEnvironment env = context.getTypeEnvironment(op);
- IScalarEvaluatorFactory maxObjectsFact = expressionRuntimeProvider
- .createEvaluatorFactory(limit.getMaxObjects().getValue(), env, inputSchemas, context);
- ILogicalExpression offsetExpr = limit.getOffset().getValue();
- IScalarEvaluatorFactory offsetFact = (offsetExpr == null) ? null
- : expressionRuntimeProvider.createEvaluatorFactory(offsetExpr, env, inputSchemas, context);
+ IScalarEvaluatorFactory maxObjectsFact = limit.hasMaxObjects() ? expressionRuntimeProvider
+ .createEvaluatorFactory(limit.getMaxObjects().getValue(), env, inputSchemas, context) : null;
+ IScalarEvaluatorFactory offsetFact = limit.hasOffset() ? expressionRuntimeProvider
+ .createEvaluatorFactory(limit.getOffset().getValue(), env, inputSchemas, context) : null;
RecordDescriptor recDesc =
JobGenHelper.mkRecordDescriptor(context.getTypeEnvironment(op), propagatedSchema, context);
StreamLimitRuntimeFactory runtime = new StreamLimitRuntimeFactory(maxObjectsFact, offsetFact, null,
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/prettyprint/LogicalOperatorPrettyPrintVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/prettyprint/LogicalOperatorPrettyPrintVisitor.java
index bc8e024..cccd4ae 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/prettyprint/LogicalOperatorPrettyPrintVisitor.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/prettyprint/LogicalOperatorPrettyPrintVisitor.java
@@ -386,10 +386,12 @@ public class LogicalOperatorPrettyPrintVisitor extends AbstractLogicalOperatorPr
@Override
public Void visitLimitOperator(LimitOperator op, Integer indent) throws AlgebricksException {
- addIndent(indent).append("limit " + op.getMaxObjects().getValue().accept(exprVisitor, indent));
- ILogicalExpression offset = op.getOffset().getValue();
- if (offset != null) {
- buffer.append(", " + offset.accept(exprVisitor, indent));
+ addIndent(indent).append("limit");
+ if (op.hasMaxObjects()) {
+ buffer.append(' ').append(op.getMaxObjects().getValue().accept(exprVisitor, indent));
+ }
+ if (op.hasOffset()) {
+ buffer.append(" offset ").append(op.getOffset().getValue().accept(exprVisitor, indent));
}
return null;
}
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/prettyprint/LogicalOperatorPrettyPrintVisitorJson.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/prettyprint/LogicalOperatorPrettyPrintVisitorJson.java
index ac559d2..4cffaeb 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/prettyprint/LogicalOperatorPrettyPrintVisitorJson.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/prettyprint/LogicalOperatorPrettyPrintVisitorJson.java
@@ -511,10 +511,11 @@ public class LogicalOperatorPrettyPrintVisitorJson extends AbstractLogicalOperat
public Void visitLimitOperator(LimitOperator op, Void indent) throws AlgebricksException {
try {
jsonGenerator.writeStringField(OPERATOR_FIELD, "limit");
- writeStringFieldExpression("value", op.getMaxObjects(), indent);
- Mutable<ILogicalExpression> offsetRef = op.getOffset();
- if (offsetRef != null && offsetRef.getValue() != null) {
- writeStringFieldExpression("offset", offsetRef, indent);
+ if (op.hasMaxObjects()) {
+ writeStringFieldExpression("value", op.getMaxObjects(), indent);
+ }
+ if (op.hasOffset()) {
+ writeStringFieldExpression("offset", op.getOffset().getValue(), indent);
}
return null;
} catch (IOException e) {
@@ -858,9 +859,15 @@ public class LogicalOperatorPrettyPrintVisitorJson extends AbstractLogicalOperat
/////////////// string fields ///////////////
/** Writes "fieldName": "expr" */
- private void writeStringFieldExpression(String fieldName, Mutable<ILogicalExpression> expression, Void indent)
+ private void writeStringFieldExpression(String fieldName, Mutable<ILogicalExpression> expressionRef, Void indent)
+ throws AlgebricksException, IOException {
+ writeStringFieldExpression(fieldName, expressionRef.getValue(), indent);
+ }
+
+ /** Writes "fieldName": "expr" */
+ private void writeStringFieldExpression(String fieldName, ILogicalExpression expression, Void indent)
throws AlgebricksException, IOException {
- jsonGenerator.writeStringField(fieldName, expression.getValue().accept(exprVisitor, indent));
+ jsonGenerator.writeStringField(fieldName, expression.accept(exprVisitor, indent));
}
/////////////// array fields ///////////////
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/LogicalOperatorDotVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/LogicalOperatorDotVisitor.java
index 5a8c128..67963ce 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/LogicalOperatorDotVisitor.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/LogicalOperatorDotVisitor.java
@@ -425,10 +425,12 @@ public class LogicalOperatorDotVisitor implements ILogicalOperatorVisitor<String
@Override
public String visitLimitOperator(LimitOperator op, Boolean showDetails) throws AlgebricksException {
stringBuilder.setLength(0);
- stringBuilder.append("limit ").append(op.getMaxObjects().getValue().toString());
- ILogicalExpression offset = op.getOffset().getValue();
- if (offset != null) {
- stringBuilder.append(", ").append(offset.toString());
+ stringBuilder.append("limit");
+ if (op.hasMaxObjects()) {
+ stringBuilder.append(' ').append(op.getMaxObjects().getValue());
+ }
+ if (op.hasOffset()) {
+ stringBuilder.append(" offset ").append(op.getOffset().getValue());
}
appendSchema(op, showDetails);
appendAnnotations(op, showDetails);
diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/CopyLimitDownRule.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/CopyLimitDownRule.java
index 7dd86b1..672234c 100644
--- a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/CopyLimitDownRule.java
+++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/CopyLimitDownRule.java
@@ -56,7 +56,7 @@ public class CopyLimitDownRule implements IAlgebraicRewriteRule {
return false;
}
LimitOperator limitOp = (LimitOperator) op;
- if (!limitOp.isTopmostLimitOp()) {
+ if (!limitOp.isTopmostLimitOp() || !limitOp.hasMaxObjects()) {
return false;
}
@@ -79,7 +79,7 @@ public class CopyLimitDownRule implements IAlgebraicRewriteRule {
ILogicalOperator unsafeOp = unsafeOpRef.getValue();
ILogicalExpression maxObjectsExpr = limitOp.getMaxObjects().getValue();
ILogicalExpression newMaxObjectsExpr;
- if (limitOp.getOffset().getValue() == null) {
+ if (!limitOp.hasOffset()) {
newMaxObjectsExpr = maxObjectsExpr.cloneExpression();
} else {
// Need to add an offset to the given limit value
diff --git a/hyracks-fullstack/algebricks/algebricks-runtime/src/main/java/org/apache/hyracks/algebricks/runtime/operators/std/StreamLimitRuntimeFactory.java b/hyracks-fullstack/algebricks/algebricks-runtime/src/main/java/org/apache/hyracks/algebricks/runtime/operators/std/StreamLimitRuntimeFactory.java
index 2ae91ef..793f095 100644
--- a/hyracks-fullstack/algebricks/algebricks-runtime/src/main/java/org/apache/hyracks/algebricks/runtime/operators/std/StreamLimitRuntimeFactory.java
+++ b/hyracks-fullstack/algebricks/algebricks-runtime/src/main/java/org/apache/hyracks/algebricks/runtime/operators/std/StreamLimitRuntimeFactory.java
@@ -45,6 +45,9 @@ public class StreamLimitRuntimeFactory extends AbstractOneInputOneOutputRuntimeF
IScalarEvaluatorFactory offsetEvalFactory, int[] projectionList,
IBinaryIntegerInspectorFactory binaryIntegerInspectorFactory) {
super(projectionList);
+ if (maxObjectsEvalFactory == null && offsetEvalFactory == null) {
+ throw new IllegalArgumentException();
+ }
this.maxObjectsEvalFactory = maxObjectsEvalFactory;
this.offsetEvalFactory = offsetEvalFactory;
this.binaryIntegerInspectorFactory = binaryIntegerInspectorFactory;
@@ -61,29 +64,32 @@ public class StreamLimitRuntimeFactory extends AbstractOneInputOneOutputRuntimeF
}
@Override
- public AbstractOneInputOneOutputOneFramePushRuntime createOneOutputPushRuntime(final IHyracksTaskContext ctx) {
+ public AbstractOneInputOneOutputOneFramePushRuntime createOneOutputPushRuntime(final IHyracksTaskContext ctx)
+ throws HyracksDataException {
IEvaluatorContext evalCtx = new EvaluatorContext(ctx);
final IBinaryIntegerInspector bii = binaryIntegerInspectorFactory.createBinaryIntegerInspector(ctx);
return new AbstractOneInputOneOutputOneFramePushRuntime() {
private final IPointable p = VoidPointable.FACTORY.createPointable();
- private IScalarEvaluator evalMaxObjects;
- private IScalarEvaluator evalOffset = null;
- private int toWrite = 0; // how many tuples still to write
- private int toSkip = 0; // how many tuples still to skip
- private boolean firstTuple = true;
- private boolean afterLastTuple = false;
+ private final IScalarEvaluator evalMaxObjects =
+ maxObjectsEvalFactory != null ? maxObjectsEvalFactory.createScalarEvaluator(evalCtx) : null;
+ private final IScalarEvaluator evalOffset =
+ offsetEvalFactory != null ? offsetEvalFactory.createScalarEvaluator(evalCtx) : null;
+ private final boolean toWriteUnlimited = maxObjectsEvalFactory == null;
+ private int toWrite; // how many tuples still to write
+ private int toSkip; // how many tuples still to skip
+ private boolean firstTuple;
+ private boolean afterLastTuple;
@Override
public void open() throws HyracksDataException {
super.open();
- if (evalMaxObjects == null) {
+ if (tRef == null) {
initAccessAppendRef(ctx);
- evalMaxObjects = maxObjectsEvalFactory.createScalarEvaluator(evalCtx);
- if (offsetEvalFactory != null) {
- evalOffset = offsetEvalFactory.createScalarEvaluator(evalCtx);
- }
}
+ firstTuple = true;
afterLastTuple = false;
+ toWrite = 0;
+ toSkip = 0;
}
@Override
@@ -104,14 +110,16 @@ public class StreamLimitRuntimeFactory extends AbstractOneInputOneOutputRuntimeF
for (int t = start; t < nTuple; t++) {
if (firstTuple) {
firstTuple = false;
- toWrite = evaluateInteger(evalMaxObjects, t);
+ if (evalMaxObjects != null) {
+ toWrite = evaluateInteger(evalMaxObjects, t);
+ }
if (evalOffset != null) {
toSkip = evaluateInteger(evalOffset, t);
}
}
if (toSkip > 0) {
toSkip--;
- } else if (toWrite > 0) {
+ } else if (toWriteUnlimited || toWrite > 0) {
toWrite--;
if (projectionList != null) {
appendProjectionToFrame(t, projectionList);
@@ -125,27 +133,16 @@ public class StreamLimitRuntimeFactory extends AbstractOneInputOneOutputRuntimeF
}
}
- @Override
- public void close() throws HyracksDataException {
- toWrite = 0; // how many tuples still to write
- toSkip = 0; // how many tuples still to skip
- firstTuple = true;
- afterLastTuple = false;
- super.close();
- }
-
private int evaluateInteger(IScalarEvaluator eval, int tIdx) throws HyracksDataException {
tRef.reset(tAccess, tIdx);
eval.evaluate(tRef, p);
- int lim = bii.getIntegerValue(p.getByteArray(), p.getStartOffset(), p.getLength());
- return lim;
+ return bii.getIntegerValue(p.getByteArray(), p.getStartOffset(), p.getLength());
}
@Override
public void flush() throws HyracksDataException {
appender.flush(writer);
}
-
};
}
}