This is an automated email from the ASF dual-hosted git repository.
xiangfu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-pinot.git
The following commit(s) were added to refs/heads/master by this push:
new 3126d56 Support non-literal expressions for right-side operand in
predicate comparison (#5070)
3126d56 is described below
commit 3126d56f7b4688f03bf1afdd9d13ff991376a762
Author: Xiang Fu <fx19880...@gmail.com>
AuthorDate: Wed Feb 26 11:27:09 2020 -0800
Support non-literal expressions for right-side operand in predicate
comparison (#5070)
* Update predicate expression
* Support non-literal expression in right side of comparison operators
* Support case like WHERE 0=b
* Adding more sql tests
* Address comments
---
.../pinot/common/utils/request/RequestUtils.java | 29 +++++-
.../apache/pinot/sql/parsers/CalciteSqlParser.java | 100 ++++++++++++++++++++-
.../pinot/sql/parsers/CalciteSqlCompilerTest.java | 95 ++++++++++++++++++--
...rformance_2014_100k_subset.test_queries_10K.sql | 7 +-
4 files changed, 220 insertions(+), 11 deletions(-)
diff --git
a/pinot-common/src/main/java/org/apache/pinot/common/utils/request/RequestUtils.java
b/pinot-common/src/main/java/org/apache/pinot/common/utils/request/RequestUtils.java
index a82eb6a..d1e3eda 100644
---
a/pinot-common/src/main/java/org/apache/pinot/common/utils/request/RequestUtils.java
+++
b/pinot-common/src/main/java/org/apache/pinot/common/utils/request/RequestUtils.java
@@ -126,14 +126,39 @@ public class RequestUtils {
return expression;
}
- public static Expression getLiteralExpression(String value) {
+ public static Expression createNewLiteralExpression() {
Expression expression = new Expression(ExpressionType.LITERAL);
Literal literal = new Literal();
- literal.setStringValue(value);
expression.setLiteral(literal);
return expression;
}
+ public static Expression getLiteralExpression(String value) {
+ Expression expression = createNewLiteralExpression();
+ expression.getLiteral().setStringValue(value);
+ return expression;
+ }
+
+ public static Expression getLiteralExpression(Integer value) {
+ return getLiteralExpression(value.longValue());
+ }
+
+ public static Expression getLiteralExpression(Long value) {
+ Expression expression = createNewLiteralExpression();
+ expression.getLiteral().setLongValue(value);
+ return expression;
+ }
+
+ public static Expression getLiteralExpression(Float value) {
+ return getLiteralExpression(value.doubleValue());
+ }
+
+ public static Expression getLiteralExpression(Double value) {
+ Expression expression = createNewLiteralExpression();
+ expression.getLiteral().setDoubleValue(value);
+ return expression;
+ }
+
public static Expression getFunctionExpression(String operator) {
Expression expression = new Expression(ExpressionType.FUNCTION);
Function function = new Function(operator);
diff --git
a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
index 5a85b38..3596356 100644
---
a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
+++
b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
@@ -283,10 +283,108 @@ public class CalciteSqlParser {
throw new RuntimeException(
"Unable to convert SqlNode: " + sqlNode + " to PinotQuery. Unknown
node type: " + sqlNode.getKind());
}
+ queryReWrite(pinotQuery);
+ return pinotQuery;
+ }
+
+ private static void queryReWrite(PinotQuery pinotQuery) {
+ // Update Predicate Comparison
+ if (pinotQuery.isSetFilterExpression()) {
+ Expression filterExpression = pinotQuery.getFilterExpression();
+ Expression updatedFilterExpression =
updateComparisonPredicate(filterExpression);
+ pinotQuery.setFilterExpression(updatedFilterExpression);
+ }
+
+ // Update alias
Map<Identifier, Expression> aliasMap =
extractAlias(pinotQuery.getSelectList());
applyAlias(aliasMap, pinotQuery);
validate(aliasMap, pinotQuery);
- return pinotQuery;
+ }
+
+ // This method converts a predicate expression to the what Pinot could
evaluate.
+ // For comparison expression, left operand could be any expression, but
right operand only
+ // supports literal.
+ // E.g. 'WHERE a > b' will be updated to 'WHERE a - b > 0'
+ private static Expression updateComparisonPredicate(Expression expression) {
+ Function functionCall = expression.getFunctionCall();
+ if (functionCall != null) {
+ SqlKind sqlKind = SqlKind.OTHER_FUNCTION;
+ try {
+ sqlKind = SqlKind.valueOf(functionCall.getOperator().toUpperCase());
+ } catch (Exception e) {
+ // Do nothing
+ }
+ switch (sqlKind) {
+ case EQUALS:
+ case NOT_EQUALS:
+ case GREATER_THAN:
+ case GREATER_THAN_OR_EQUAL:
+ case LESS_THAN:
+ case LESS_THAN_OR_EQUAL:
+ // Handle predicate like 'WHERE 10=a'
+ if (functionCall.getOperands().get(0).getLiteral() != null) {
+
functionCall.setOperator(getOppositeOperator(functionCall.getOperator()));
+ List<Expression> oldOperands = functionCall.getOperands();
+ Expression tempExpr = oldOperands.get(0);
+ oldOperands.set(0, oldOperands.get(1));
+ oldOperands.set(1, tempExpr);
+ }
+ if (functionCall.getOperands().get(1).getLiteral() != null) {
+ return expression;
+ }
+ Expression comparisonFunction =
RequestUtils.getFunctionExpression(functionCall.getOperator());
+ List<Expression> exprList = new ArrayList<>();
+ exprList.add(getLeftOperand(functionCall));
+ exprList.add(RequestUtils.getLiteralExpression(0));
+ comparisonFunction.getFunctionCall().setOperands(exprList);
+ return comparisonFunction;
+ default:
+ List<Expression> operands = functionCall.getOperands();
+ List<Expression> newOperands = new ArrayList<>();
+ for (int i = 0; i < operands.size(); i++) {
+ newOperands.add(updateComparisonPredicate(operands.get(i)));
+ }
+ functionCall.setOperands(newOperands);
+ }
+ }
+ return expression;
+ }
+
+ /**
+ * The purpose of this method is to convert expression "0 < columnA" to
"columnA > 0".
+ * The conversion would be:
+ * from ">" to "<",
+ * from "<" to ">",
+ * from ">=" to "<=",
+ * from "<=" to ">=".
+ *
+ * @param operator
+ * @return opposite operator
+ */
+ private static String getOppositeOperator(String operator) {
+ switch (operator.toUpperCase()) {
+ case "GREATER_THAN":
+ return "LESS_THAN";
+ case "GREATER_THAN_OR_EQUAL":
+ return "LESS_THAN_OR_EQUAL";
+ case "LESS_THAN":
+ return "GREATER_THAN";
+ case "LESS_THAN_OR_EQUAL":
+ return "GREATER_THAN_OR_EQUAL";
+ default:
+ // Do nothing
+ return operator;
+ }
+ }
+
+ private static Expression getLeftOperand(Function functionCall) {
+ Expression minusFunction =
RequestUtils.getFunctionExpression(SqlKind.MINUS.toString());
+ List<Expression> updatedOperands = new ArrayList<>();
+ for (Expression operand : functionCall.getOperands()) {
+ updatedOperands.add(updateComparisonPredicate(operand));
+ }
+ minusFunction.getFunctionCall().setOperands(updatedOperands);
+ return minusFunction;
}
private static void applyAlias(Map<Identifier, Expression> aliasMap,
PinotQuery pinotQuery) {
diff --git
a/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlCompilerTest.java
b/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlCompilerTest.java
index ffdefc4..c252142 100644
---
a/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlCompilerTest.java
+++
b/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlCompilerTest.java
@@ -60,14 +60,14 @@ public class CalciteSqlCompilerTest {
pinotQuery =
CalciteSqlParser.compileToPinotQuery("select * from vegetables where
origin = \"Martha\"\"s Vineyard\"");
- Assert
-
.assertEquals(pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(1).getIdentifier().getName(),
- "Martha\"s Vineyard");
+ Assert.assertEquals(
+
pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(1)
+ .getIdentifier().getName(), "Martha\"s Vineyard");
pinotQuery = CalciteSqlParser.compileToPinotQuery("select * from
vegetables where origin = \"Martha''s Vineyard\"");
- Assert
-
.assertEquals(pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(1).getIdentifier().getName(),
- "Martha''s Vineyard");
+ Assert.assertEquals(
+
pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(1)
+ .getIdentifier().getName(), "Martha''s Vineyard");
}
@Test
@@ -119,6 +119,87 @@ public class CalciteSqlCompilerTest {
}
@Test
+ public void testFilterCalusesWithRightExpression() {
+ PinotQuery pinotQuery = CalciteSqlParser.compileToPinotQuery("select *
from vegetables where a > b");
+ Function func = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(func.getOperator(), SqlKind.GREATER_THAN.name());
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperator(),
"MINUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"a");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"b");
+ Assert.assertEquals(func.getOperands().get(1).getLiteral().getLongValue(),
0L);
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("select * from
vegetables where 0 < a-b");
+ func = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(func.getOperator(), SqlKind.GREATER_THAN.name());
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperator(),
"MINUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"a");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"b");
+ Assert.assertEquals(func.getOperands().get(1).getLiteral().getLongValue(),
0L);
+
+
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("select * from
vegetables where b < 100 + c");
+ func = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(func.getOperator(), SqlKind.LESS_THAN.name());
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperator(),
"MINUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"b");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperator(),
"PLUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getLiteral().getLongValue(),
100L);
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"c");
+ Assert.assertEquals(func.getOperands().get(1).getLiteral().getLongValue(),
0L);
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("select * from
vegetables where b -(100+c)< 0");
+ func = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(func.getOperator(), SqlKind.LESS_THAN.name());
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperator(),
"MINUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"b");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperator(),
"PLUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getLiteral().getLongValue(),
100L);
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"c");
+ Assert.assertEquals(func.getOperands().get(1).getLiteral().getLongValue(),
0L);
+
+
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("select * from
vegetables where foo1(bar1(a-b)) <= foo2(bar2(c+d))");
+ func = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(func.getOperator(), SqlKind.LESS_THAN_OR_EQUAL.name());
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperator(),
"MINUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"foo1");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperator(),
"foo2");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"bar1");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"bar2");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"MINUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"PLUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"a");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"b");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"c");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"d");
+ Assert.assertEquals(func.getOperands().get(1).getLiteral().getLongValue(),
0L);
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("select * from
vegetables where foo1(bar1(a-b)) - foo2(bar2(c+d)) <= 0");
+ func = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(func.getOperator(), SqlKind.LESS_THAN_OR_EQUAL.name());
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperator(),
"MINUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"foo1");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperator(),
"foo2");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"bar1");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"bar2");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"MINUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"PLUS");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"a");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"b");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"c");
+
Assert.assertEquals(func.getOperands().get(0).getFunctionCall().getOperands().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"d");
+ Assert.assertEquals(func.getOperands().get(1).getLiteral().getLongValue(),
0L);
+
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("select * from
vegetables where c >= 10");
+ func = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(func.getOperator(),
SqlKind.GREATER_THAN_OR_EQUAL.name());
+ Assert.assertEquals(func.getOperands().get(0).getIdentifier().getName(),
"c");
+ Assert.assertEquals(func.getOperands().get(1).getLiteral().getLongValue(),
10L);
+ pinotQuery = CalciteSqlParser.compileToPinotQuery("select * from
vegetables where 10 <= c");
+ func = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(func.getOperator(),
SqlKind.GREATER_THAN_OR_EQUAL.name());
+ Assert.assertEquals(func.getOperands().get(0).getIdentifier().getName(),
"c");
+ Assert.assertEquals(func.getOperands().get(1).getLiteral().getLongValue(),
10L);
+ }
+
+ @Test
public void testBrokerConverter() {
PinotQuery pinotQuery =
CalciteSqlParser.compileToPinotQuery("select * from vegetables where g
IN (12, 13, 15.2, 17)");
@@ -1216,7 +1297,7 @@ public class CalciteSqlCompilerTest {
.getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"time");
Assert.assertEquals(pinotQuery.getGroupByList().get(0).getIdentifier().getName(),
"group");
}
-
+
@Test
public void testCastTransformation() {
PinotQuery pinotQuery = CalciteSqlParser.compileToPinotQuery("select
CAST(25.65 AS int) from myTable");
diff --git
a/pinot-integration-tests/src/test/resources/On_Time_On_Time_Performance_2014_100k_subset.test_queries_10K.sql
b/pinot-integration-tests/src/test/resources/On_Time_On_Time_Performance_2014_100k_subset.test_queries_10K.sql
index 6203277..bcb6033 100644
---
a/pinot-integration-tests/src/test/resources/On_Time_On_Time_Performance_2014_100k_subset.test_queries_10K.sql
+++
b/pinot-integration-tests/src/test/resources/On_Time_On_Time_Performance_2014_100k_subset.test_queries_10K.sql
@@ -9997,4 +9997,9 @@
{"sql":"SELECT COUNT(WeatherDelay) FROM mytable WHERE DestCityMarketID BETWEEN
33158 AND 30930 LIMIT 26"}
{"sql":"SELECT COUNT(OriginCityName), AVG(SecurityDelay) FROM mytable WHERE
DivAirportLandings BETWEEN 0 AND 9 OR OriginCityName <= 'Melbourne, FL' LIMIT
28"}
{"sql":"SELECT WheelsOff, DistanceGroup, OriginAirportID,
SUM(OriginAirportSeqID), MAX(LateAircraftDelay), MAX(Diverted) FROM mytable
WHERE TaxiOut <> 45 GROUP BY WheelsOff, DistanceGroup, OriginAirportID ORDER
BY WheelsOff, DistanceGroup, OriginAirportID LIMIT 6","hsqls":["SELECT
WheelsOff, DistanceGroup, OriginAirportID, SUM(OriginAirportSeqID),
MAX(LateAircraftDelay), MAX(Diverted) FROM mytable WHERE TaxiOut <> 45 GROUP BY
WheelsOff, DistanceGroup, OriginAirportID ORDER BY WheelsOff [...]
-{"sql":"SELECT DepDel15, OriginStateName, COUNT(*) FROM mytable WHERE DepTime
BETWEEN 1407 AND 1857 GROUP BY DepDel15, OriginStateName ORDER BY DepDel15,
OriginStateName LIMIT 24","hsqls":["SELECT DepDel15, OriginStateName, COUNT(*)
FROM mytable WHERE DepTime BETWEEN 1407 AND 1857 GROUP BY DepDel15,
OriginStateName ORDER BY DepDel15, OriginStateName LIMIT 24"]}
\ No newline at end of file
+{"sql":"SELECT DepDel15, OriginStateName, COUNT(*) FROM mytable WHERE DepTime
BETWEEN 1407 AND 1857 GROUP BY DepDel15, OriginStateName ORDER BY DepDel15,
OriginStateName LIMIT 24","hsqls":["SELECT DepDel15, OriginStateName, COUNT(*)
FROM mytable WHERE DepTime BETWEEN 1407 AND 1857 GROUP BY DepDel15,
OriginStateName ORDER BY DepDel15, OriginStateName LIMIT 24"]}
+{"sql":"SELECT COUNT(WeatherDelay) FROM mytable WHERE DestCityMarketID >=
33158 AND DestCityMarketID <= 30930 LIMIT 26"}
+{"sql":"SELECT COUNT(WeatherDelay) FROM mytable WHERE 33158 <=
DestCityMarketID AND 30930>= DestCityMarketID LIMIT 26"}
+{"sql":"SELECT COUNT(WeatherDelay) FROM mytable WHERE 33158 -1 <=
DestCityMarketID -1 AND 30930 + 1 >= DestCityMarketID + 1 LIMIT 26"}
+{"sql":"SELECT COUNT(WeatherDelay) FROM mytable WHERE ArrDelay > CarrierDelay
LIMIT 26"}
+{"sql":"SELECT COUNT(WeatherDelay) FROM mytable WHERE ArrDelay - CarrierDelay
> 0 LIMIT 26"}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
