Add the BETWEEN operator.
Change-Id: I5df7370154aa30db7547f7fd47ea69b5b76dcf49
Reviewed-on: https://asterix-gerrit.ics.uci.edu/1143
Sonar-Qube: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Till Westmann <tillw@apache.org>
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-between-join_01.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-between-join_01.sqlpp
new file mode 100644
index 0000000..106fc9d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-between-join_01.sqlpp
@@ -0,0 +1,37 @@
+/*
+ * 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 test1 if exists;
+create dataverse test1;
+use test1;
+
+create type TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset DsOne(TestType) primary key key1;
+create dataset DsTwo(TestType) primary key key1;
+
+FROM DsOne x, DsTwo y
+WHERE x.key2 /*+ indexnl */ BETWEEN y.key1 AND 10
+SELECT VALUE x
+;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-between-join_01.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-between-join_01.plan
new file mode 100644
index 0000000..05af741
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-between-join_01.plan
@@ -0,0 +1,13 @@
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.1.ddl.sqlpp
new file mode 100644
index 0000000..20aff6d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.1.ddl.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * 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 Tweet as
+ closed {
+ id : int64,
+ tweetid : int64,
+ loc : point,
+ time : datetime,
+ text : string
+}
+
+create dataset TwitterData(Tweet) primary key id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.2.update.sqlpp
new file mode 100644
index 0000000..f9d5e21
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.2.update.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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;
+
+
+load dataset TwitterData using localfs ((`path`=`asterix_nc1://data/twitter/smalltweets.txt`),(`format`=`adm`))
+pre-sorted;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.3.query.sqlpp
new file mode 100644
index 0000000..42ce98c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.3.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+
+select element {'id':t.id}
+from TwitterData as t
+where t.time between datetime('2011-05-15T16:00:00Z') and datetime('2011-05-15T21:59:59Z')
+order by t.id
+;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/int_not_between/int_not_between.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/int_not_between/int_not_between.3.query.sqlpp
new file mode 100644
index 0000000..913d394
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/int_not_between/int_not_between.3.query.sqlpp
@@ -0,0 +1,23 @@
+/*
+ * 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.
+ */
+
+SELECT VALUE x
+FROM [1,3,2] x
+WHERE x NOT BETWEEN 2 AND 3
+;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/comparison/int_not_between/int_not_between.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/comparison/int_not_between/int_not_between.1.adm
new file mode 100644
index 0000000..d00491f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/comparison/int_not_between/int_not_between.1.adm
@@ -0,0 +1 @@
+1
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 d5fe65b..a074df9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -933,6 +933,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="comparison">
+ <compilation-unit name="datetime_range_between">
+ <output-dir compare="Text">datetime_range</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="comparison">
<compilation-unit name="datetime_tzeq">
<output-dir compare="Text">datetime_tzeq</output-dir>
</compilation-unit>
@@ -1053,6 +1058,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="comparison">
+ <compilation-unit name="int_not_between">
+ <output-dir compare="Text">int_not_between</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="comparison">
<compilation-unit name="lt_01">
<output-dir compare="Text">lt_01</output-dir>
</compilation-unit>
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
index a5a1bb8..f00d3ca 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java
@@ -43,7 +43,9 @@
LIKE("like"),
NOT_LIKE("not_like"),
IN("in"),
- NOT_IN("not_in");
+ NOT_IN("not_in"),
+ BETWEEN("between"),
+ NOT_BETWEEN("not_between");
private final String symbol;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java
index bbe7c27..60c4aa0 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java
@@ -36,6 +36,7 @@
import org.apache.asterix.lang.common.struct.OperatorType;
import org.apache.asterix.lang.common.struct.QuantifiedPair;
import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppExpressionScopingVisitor;
+import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
public class OperatorExpressionVisitor extends AbstractSqlppExpressionScopingVisitor {
@@ -64,6 +65,9 @@
case CONCAT:
// There can be multiple "||"s in one operator expression (according to the grammar).
return processConcatOperator(operatorExpr);
+ case BETWEEN:
+ case NOT_BETWEEN:
+ return processBetweenOperator(operatorExpr, opType);
default:
break;
}
@@ -103,4 +107,37 @@
return new CallExpr(new FunctionSignature(null, CONCAT, 1), operatorExpr.getExprList());
}
+ private Expression processBetweenOperator(OperatorExpr operatorExpr, OperatorType opType) throws AsterixException {
+ // The grammar guarantees that the BETWEEN operator gets exactly three expressions.
+ Expression target = operatorExpr.getExprList().get(0);
+ Expression left = operatorExpr.getExprList().get(1);
+ Expression right = operatorExpr.getExprList().get(2);
+
+ // Creates the expression left <= target.
+ Expression leftComparison = createLessThanExpression(left, target, operatorExpr.getHints());
+ // Creates the expression target <= right.
+ Expression rightComparison = createLessThanExpression(target, right, operatorExpr.getHints());
+ OperatorExpr andExpr = new OperatorExpr();
+ andExpr.addOperand(leftComparison);
+ andExpr.addOperand(rightComparison);
+ andExpr.addOperator("and");
+ return opType == OperatorType.BETWEEN ? andExpr :
+ new CallExpr(new FunctionSignature(null, "not", 1),
+ new ArrayList<>(Collections.singletonList(andExpr)));
+ }
+
+ private Expression createLessThanExpression(Expression lhs, Expression rhs, List<IExpressionAnnotation> hints)
+ throws AsterixException {
+ OperatorExpr comparison = new OperatorExpr();
+ comparison.addOperand(lhs);
+ comparison.addOperand(rhs);
+ comparison.addOperator("<=");
+ if (hints != null) {
+ for (IExpressionAnnotation hint : hints) {
+ comparison.addHint(hint);
+ }
+ }
+ return comparison;
+ }
+
}
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index 3d8c4d9..2f738ff 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -1750,7 +1750,7 @@
IExpressionAnnotation annotation = null;
}
{
- operand = IsExpr()
+ operand = BetweenExpr()
{
if (operand instanceof VariableExpr) {
String hint = getHint(token);
@@ -1788,7 +1788,7 @@
}
}
- operand = IsExpr()
+ operand = BetweenExpr()
{
broadcast = false;
if (operand instanceof VariableExpr) {
@@ -1809,6 +1809,63 @@
}
}
+Expression BetweenExpr()throws ParseException:
+{
+ boolean not = false;
+ OperatorExpr op = null;
+ Expression operand = null;
+ IExpressionAnnotation annotation = null;
+}
+{
+ operand = IsExpr()
+ (
+ LOOKAHEAD(2)
+ (<NOT> { not = true; })? <BETWEEN>
+ {
+ String mhint = getHint(token);
+ if (mhint != null) {
+ if (mhint.equals(INDEXED_NESTED_LOOP_JOIN_HINT)) {
+ annotation = IndexedNLJoinExpressionAnnotation.INSTANCE;
+ } else if (mhint.equals(SKIP_SECONDARY_INDEX_SEARCH_HINT)) {
+ annotation = SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE;
+ }
+ }
+ String operator = token.image.toLowerCase();
+ if(not){
+ operator = "not_" + operator;
+ }
+ if (op == null) {
+ op = new OperatorExpr();
+ op.addOperand(operand);
+ op.setCurrentop(true);
+ }
+ try{
+ op.addOperator(operator);
+ } catch (AsterixException e){
+ throw new ParseException(e.getMessage());
+ }
+ }
+
+ operand = IsExpr()
+ {
+ op.addOperand(operand);
+ }
+
+ <AND>
+ operand = IsExpr()
+ {
+ op.addOperand(operand);
+ }
+ )?
+
+ {
+ if (annotation != null) {
+ op.addHint(annotation);
+ }
+ return op==null? operand: op;
+ }
+}
+
Expression IsExpr() throws ParseException:
{
Expression expr = null;