Till Westmann has submitted this change and it was merged. Change subject: Add the BETWEEN operator. ......................................................................
Add the BETWEEN operator. Change-Id: I5df7370154aa30db7547f7fd47ea69b5b76dcf49 Reviewed-on: https://asterix-gerrit.ics.uci.edu/1143 Sonar-Qube: Jenkins <[email protected]> Tested-by: Jenkins <[email protected]> Reviewed-by: Till Westmann <[email protected]> --- A asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-between-join_01.sqlpp A asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-between-join_01.plan A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/int_not_between/int_not_between.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/comparison/int_not_between/int_not_between.1.adm M asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml M asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java M asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java M asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj 11 files changed, 270 insertions(+), 3 deletions(-) Approvals: Till Westmann: Looks good to me, approved Jenkins: Verified; No violations found 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; -- To view, visit https://asterix-gerrit.ics.uci.edu/1143 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-MessageType: merged Gerrit-Change-Id: I5df7370154aa30db7547f7fd47ea69b5b76dcf49 Gerrit-PatchSet: 6 Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-Owner: Yingyi Bu <[email protected]> Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Till Westmann <[email protected]> Gerrit-Reviewer: Yingyi Bu <[email protected]>
