Skip to content

Commit f766b3f

Browse files
dai-chengithub-actions[bot]
dai-chen
authored and
github-actions[bot]
committed
Add BETWEEN expression in v2 engine (#1163)
* Add between grammar and in-memory impl Signed-off-by: Chen Dai <daichen@amazon.com> * Add comparison test for between Signed-off-by: Chen Dai <daichen@amazon.com> * Add doctest for between Signed-off-by: Chen Dai <daichen@amazon.com> * Add not between support Signed-off-by: Chen Dai <daichen@amazon.com> * Fix doctest failure Signed-off-by: Chen Dai <daichen@amazon.com> * Refactor to rewrite to basic comparison expression Signed-off-by: Chen Dai <daichen@amazon.com> * Clean up unused code Signed-off-by: Chen Dai <daichen@amazon.com> * Prepare to publish PR Signed-off-by: Chen Dai <daichen@amazon.com> Signed-off-by: Chen Dai <daichen@amazon.com> (cherry picked from commit 6c0af83)
1 parent 5073215 commit f766b3f

9 files changed

Lines changed: 169 additions & 17 deletions

File tree

‎core/src/main/java/org/opensearch/sql/analysis/ExpressionAnalyzer.java‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,11 @@
66

77
package org.opensearch.sql.analysis;
88

9+
import static org.opensearch.sql.ast.dsl.AstDSL.and;
10+
import static org.opensearch.sql.ast.dsl.AstDSL.compare;
11+
import static org.opensearch.sql.expression.function.BuiltinFunctionName.GTE;
12+
import static org.opensearch.sql.expression.function.BuiltinFunctionName.LTE;
13+
914
import com.google.common.collect.ImmutableList;
1015
import com.google.common.collect.ImmutableMap;
1116
import com.google.common.collect.ImmutableSet;
@@ -22,6 +27,7 @@
2227
import org.opensearch.sql.ast.expression.AggregateFunction;
2328
import org.opensearch.sql.ast.expression.AllFields;
2429
import org.opensearch.sql.ast.expression.And;
30+
import org.opensearch.sql.ast.expression.Between;
2531
import org.opensearch.sql.ast.expression.Case;
2632
import org.opensearch.sql.ast.expression.Cast;
2733
import org.opensearch.sql.ast.expression.Compare;
@@ -229,6 +235,14 @@ public Expression visitCompare(Compare node, AnalysisContext context) {
229235
functionName, Arrays.asList(left, right));
230236
}
231237

238+
@Override
239+
public Expression visitBetween(Between node, AnalysisContext context) {
240+
return and(
241+
compare(">=", node.getValue(), node.getLowerBound()),
242+
compare("<=", node.getValue(), node.getUpperBound())
243+
).accept(this, context);
244+
}
245+
232246
@Override
233247
public Expression visitCase(Case node, AnalysisContext context) {
234248
List<WhenClause> whens = new ArrayList<>();

‎core/src/main/java/org/opensearch/sql/ast/AbstractNodeVisitor.java‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@
1212
import org.opensearch.sql.ast.expression.And;
1313
import org.opensearch.sql.ast.expression.Argument;
1414
import org.opensearch.sql.ast.expression.AttributeList;
15+
import org.opensearch.sql.ast.expression.Between;
1516
import org.opensearch.sql.ast.expression.Case;
1617
import org.opensearch.sql.ast.expression.Cast;
1718
import org.opensearch.sql.ast.expression.Compare;
@@ -173,6 +174,10 @@ public T visitCompare(Compare node, C context) {
173174
return visitChildren(node, context);
174175
}
175176

177+
public T visitBetween(Between node, C context) {
178+
return visitChildren(node, context);
179+
}
180+
176181
public T visitArgument(Argument node, C context) {
177182
return visitChildren(node, context);
178183
}

‎core/src/main/java/org/opensearch/sql/ast/dsl/AstDSL.java‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@
1616
import org.opensearch.sql.ast.expression.AllFields;
1717
import org.opensearch.sql.ast.expression.And;
1818
import org.opensearch.sql.ast.expression.Argument;
19+
import org.opensearch.sql.ast.expression.Between;
1920
import org.opensearch.sql.ast.expression.Case;
2021
import org.opensearch.sql.ast.expression.Cast;
2122
import org.opensearch.sql.ast.expression.Compare;
@@ -59,6 +60,7 @@
5960
import org.opensearch.sql.ast.tree.TableFunction;
6061
import org.opensearch.sql.ast.tree.UnresolvedPlan;
6162
import org.opensearch.sql.ast.tree.Values;
63+
import org.opensearch.sql.expression.function.BuiltinFunctionName;
6264

6365
/**
6466
* Class of static methods to create specific node instances.
@@ -320,6 +322,12 @@ public static UnresolvedExpression compare(
320322
return new Compare(operator, left, right);
321323
}
322324

325+
public static UnresolvedExpression between(UnresolvedExpression value,
326+
UnresolvedExpression lowerBound,
327+
UnresolvedExpression upperBound) {
328+
return new Between(value, lowerBound, upperBound);
329+
}
330+
323331
public static Argument argument(String argName, Literal argValue) {
324332
return new Argument(argName, argValue);
325333
}

‎core/src/main/java/org/opensearch/sql/ast/expression/Between.java‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
package org.opensearch.sql.ast.expression;
7+
8+
import java.util.Arrays;
9+
import java.util.List;
10+
import lombok.Data;
11+
import lombok.EqualsAndHashCode;
12+
import org.opensearch.sql.ast.AbstractNodeVisitor;
13+
import org.opensearch.sql.ast.Node;
14+
15+
/**
16+
* Unresolved expression for BETWEEN.
17+
*/
18+
@Data
19+
@EqualsAndHashCode(callSuper = false)
20+
public class Between extends UnresolvedExpression {
21+
22+
/** Value for range check. */
23+
private final UnresolvedExpression value;
24+
25+
/** Lower bound of the range (inclusive). */
26+
private final UnresolvedExpression lowerBound;
27+
28+
/** Upper bound of the range (inclusive). */
29+
private final UnresolvedExpression upperBound;
30+
31+
@Override
32+
public List<? extends Node> getChild() {
33+
return Arrays.asList(value, lowerBound, upperBound);
34+
}
35+
36+
@Override
37+
public <T, C> T accept(AbstractNodeVisitor<T, C> nodeVisitor, C context) {
38+
return nodeVisitor.visitBetween(this, context);
39+
}
40+
}

‎core/src/test/java/org/opensearch/sql/analysis/ExpressionAnalyzerTest.java‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -107,6 +107,22 @@ public void qualified_name() {
107107
);
108108
}
109109

110+
@Test
111+
public void between() {
112+
assertAnalyzeEqual(
113+
DSL.and(
114+
DSL.gte(
115+
DSL.ref("integer_value", INTEGER),
116+
DSL.literal(20)),
117+
DSL.lte(
118+
DSL.ref("integer_value", INTEGER),
119+
DSL.literal(30))),
120+
AstDSL.between(
121+
qualifiedName("integer_value"),
122+
AstDSL.intLiteral(20),
123+
AstDSL.intLiteral(30)));
124+
}
125+
110126
@Test
111127
public void case_value() {
112128
assertAnalyzeEqual(

‎docs/user/dql/expressions.rst‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,10 @@ Operators
132132
+----------------+----------------------------------------+
133133
| NOT IN | NOT IN value list test |
134134
+----------------+----------------------------------------+
135+
| BETWEEN | Between a range (endpoint inclusive) |
136+
+----------------+----------------------------------------+
137+
| NOT BETWEEN | Not between a range (BETWEEN negation) |
138+
+----------------+----------------------------------------+
135139

136140
Basic Comparison Operator
137141
-------------------------
@@ -199,6 +203,23 @@ Here is an example for IN value test::
199203
| True | True |
200204
+---------------+-------------------+
201205

206+
BETWEEN range test
207+
------------------
208+
209+
Here is an example for range test by BETWEEN expression::
210+
211+
os> SELECT
212+
... 1 BETWEEN 1 AND 3,
213+
... 4 BETWEEN 1 AND 3,
214+
... 4 NOT BETWEEN 1 AND 3;
215+
fetched rows / total rows = 1/1
216+
+---------------------+---------------------+-------------------------+
217+
| 1 BETWEEN 1 AND 3 | 4 BETWEEN 1 AND 3 | 4 NOT BETWEEN 1 AND 3 |
218+
|---------------------+---------------------+-------------------------|
219+
| True | False | True |
220+
+---------------------+---------------------+-------------------------+
221+
222+
202223
Function Call
203224
=============
204225

‎sql/src/main/antlr/OpenSearchSQLParser.g4‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,6 +278,7 @@ predicate
278278
: expressionAtom #expressionAtomPredicate
279279
| left=predicate comparisonOperator right=predicate #binaryComparisonPredicate
280280
| predicate IS nullNotnull #isNullPredicate
281+
| predicate NOT? BETWEEN predicate AND predicate #betweenPredicate
281282
| left=predicate NOT? LIKE right=predicate #likePredicate
282283
| left=predicate REGEXP right=predicate #regexpPredicate
283284
| predicate NOT? IN '(' expressions ')' #inPredicate

0 commit comments

Comments
 (0)