Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 6E7B1200B83 for ; Sat, 3 Sep 2016 06:21:00 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 6CEFC160ACB; Sat, 3 Sep 2016 04:21:00 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 4063E160A8C for ; Sat, 3 Sep 2016 06:20:59 +0200 (CEST) Received: (qmail 16047 invoked by uid 500); 3 Sep 2016 04:20:58 -0000 Mailing-List: contact commits-help@asterixdb.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@asterixdb.apache.org Delivered-To: mailing list commits@asterixdb.apache.org Received: (qmail 16037 invoked by uid 99); 3 Sep 2016 04:20:58 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 03 Sep 2016 04:20:58 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 4B6C9DFD9F; Sat, 3 Sep 2016 04:20:58 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: tillw@apache.org To: commits@asterixdb.apache.org Message-Id: <35ebbeb73a1940429cb609d06be7d878@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: asterixdb git commit: Add the BETWEEN operator. Date: Sat, 3 Sep 2016 04:20:58 +0000 (UTC) archived-at: Sat, 03 Sep 2016 04:21:00 -0000 Repository: asterixdb Updated Branches: refs/heads/master b64b169b1 -> 6c63834dc Add the BETWEEN operator. Change-Id: I5df7370154aa30db7547f7fd47ea69b5b76dcf49 Reviewed-on: https://asterix-gerrit.ics.uci.edu/1143 Sonar-Qube: Jenkins Tested-by: Jenkins Reviewed-by: Till Westmann Project: http://git-wip-us.apache.org/repos/asf/asterixdb/repo Commit: http://git-wip-us.apache.org/repos/asf/asterixdb/commit/6c63834d Tree: http://git-wip-us.apache.org/repos/asf/asterixdb/tree/6c63834d Diff: http://git-wip-us.apache.org/repos/asf/asterixdb/diff/6c63834d Branch: refs/heads/master Commit: 6c63834dcda2d2efe2bf1fe25e53945e78b2de86 Parents: b64b169 Author: Yingyi Bu Authored: Fri Sep 2 17:54:34 2016 -0700 Committer: Till Westmann Committed: Fri Sep 2 21:20:33 2016 -0700 ---------------------------------------------------------------------- .../primary-between-join_01.sqlpp | 37 ++++++++++++ .../primary-between-join_01.plan | 13 +++++ .../datetime_range_between.1.ddl.sqlpp | 35 +++++++++++ .../datetime_range_between.2.update.sqlpp | 25 ++++++++ .../datetime_range_between.3.query.sqlpp | 27 +++++++++ .../int_not_between.3.query.sqlpp | 23 ++++++++ .../int_not_between/int_not_between.1.adm | 1 + .../resources/runtimets/testsuite_sqlpp.xml | 10 ++++ .../lang/common/struct/OperatorType.java | 4 +- .../visitor/OperatorExpressionVisitor.java | 37 ++++++++++++ .../asterix-lang-sqlpp/src/main/javacc/SQLPP.jj | 61 +++++++++++++++++++- 11 files changed, 270 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-between-join_01.sqlpp ---------------------------------------------------------------------- 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 +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-between-join_01.plan ---------------------------------------------------------------------- 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| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.1.ddl.sqlpp ---------------------------------------------------------------------- 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; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.2.update.sqlpp ---------------------------------------------------------------------- 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; + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/datetime_range_between/datetime_range_between.3.query.sqlpp ---------------------------------------------------------------------- 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 +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/comparison/int_not_between/int_not_between.3.query.sqlpp ---------------------------------------------------------------------- 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 +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-app/src/test/resources/runtimets/results/comparison/int_not_between/int_not_between.1.adm ---------------------------------------------------------------------- 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 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml ---------------------------------------------------------------------- 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 @@ + + datetime_range + + + datetime_tzeq @@ -1053,6 +1058,11 @@ + + int_not_between + + + lt_01 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/struct/OperatorType.java ---------------------------------------------------------------------- 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 @@ public enum OperatorType { 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; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/OperatorExpressionVisitor.java ---------------------------------------------------------------------- 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.rewrites.LangRewritingContext; 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 @@ public class OperatorExpressionVisitor extends AbstractSqlppExpressionScopingVis 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 @@ public class OperatorExpressionVisitor extends AbstractSqlppExpressionScopingVis 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 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; + } + } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/6c63834d/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj ---------------------------------------------------------------------- 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 @@ Expression RelExpr()throws ParseException: IExpressionAnnotation annotation = null; } { - operand = IsExpr() + operand = BetweenExpr() { if (operand instanceof VariableExpr) { String hint = getHint(token); @@ -1788,7 +1788,7 @@ Expression RelExpr()throws ParseException: } } - operand = IsExpr() + operand = BetweenExpr() { broadcast = false; if (operand instanceof VariableExpr) { @@ -1809,6 +1809,63 @@ Expression RelExpr()throws ParseException: } } +Expression BetweenExpr()throws ParseException: +{ + boolean not = false; + OperatorExpr op = null; + Expression operand = null; + IExpressionAnnotation annotation = null; +} +{ + operand = IsExpr() + ( + LOOKAHEAD(2) + ( { not = true; })? + { + 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); + } + + + operand = IsExpr() + { + op.addOperand(operand); + } + )? + + { + if (annotation != null) { + op.addHint(annotation); + } + return op==null? operand: op; + } +} + Expression IsExpr() throws ParseException: { Expression expr = null;