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 622e00e Support alias replacement in PinotQuery (#5016)
622e00e is described below
commit 622e00e2c31c7e509fb4f9352cb9c7e57d773ddb
Author: Xiang Fu <[email protected]>
AuthorDate: Tue Jan 28 01:48:27 2020 -0800
Support alias replacement in PinotQuery (#5016)
* Make toExpression by default converts SqlNode to function
* Support alias replacement in PinotQuery
* Adding validation for selection alias
---
.../pinot/common/utils/request/RequestUtils.java | 2 +-
.../apache/pinot/sql/parsers/CalciteSqlParser.java | 123 ++++++++++++++++-----
.../pinot/sql/parsers/CalciteSqlCompilerTest.java | 120 ++++++++++++++++++++
3 files changed, 216 insertions(+), 29 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 0a3a71d..a82eb6a 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
@@ -338,12 +338,12 @@ public class RequestUtils {
String res = expression.getFunctionCall().getOperator() + "(";
boolean isFirstParam = true;
for (Expression operand : expression.getFunctionCall().getOperands()) {
- res += prettyPrint(operand);
if (!isFirstParam) {
res += ", ";
} else {
isFirstParam = false;
}
+ res += prettyPrint(operand);
}
res += ")";
return res;
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 cec5752..a907dc2 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
@@ -47,6 +47,7 @@ import org.apache.pinot.common.request.DataSource;
import org.apache.pinot.common.request.Expression;
import org.apache.pinot.common.request.ExpressionType;
import org.apache.pinot.common.request.Function;
+import org.apache.pinot.common.request.Identifier;
import org.apache.pinot.common.request.PinotQuery;
import org.apache.pinot.common.utils.request.RequestUtils;
import org.apache.pinot.pql.parsers.Pql2Compiler;
@@ -94,11 +95,43 @@ public class CalciteSqlParser {
return pinotQuery;
}
- static void validate(PinotQuery pinotQuery)
+ static void validate(Map<Identifier, Expression> aliasMap, PinotQuery
pinotQuery)
throws SqlCompilationException {
+ validateSelectionClause(aliasMap, pinotQuery);
validateGroupByClause(pinotQuery);
}
+ private static void validateSelectionClause(Map<Identifier, Expression>
aliasMap, PinotQuery pinotQuery)
+ throws SqlCompilationException {
+ // Sanity check on selection expression shouldn't use alias reference.
+ Set<String> aliasKeys = new HashSet<>();
+ for (Identifier identifier : aliasMap.keySet()) {
+ aliasKeys.add(identifier.getName().toLowerCase());
+ }
+ for (Expression selectExpr : pinotQuery.getSelectList()) {
+ matchIdentifierInAliasMap(selectExpr, aliasKeys);
+ }
+ }
+
+ private static void matchIdentifierInAliasMap(Expression selectExpr,
Set<String> aliasKeys)
+ throws SqlCompilationException {
+ if (selectExpr.getFunctionCall() != null) {
+ if (selectExpr.getFunctionCall().getOperator().equalsIgnoreCase("AS")) {
+
matchIdentifierInAliasMap(selectExpr.getFunctionCall().getOperands().get(0),
aliasKeys);
+ } else {
+ for (Expression operand : selectExpr.getFunctionCall().getOperands()) {
+ matchIdentifierInAliasMap(operand, aliasKeys);
+ }
+ }
+ }
+ if (selectExpr.getIdentifier() != null) {
+ if
(aliasKeys.contains(selectExpr.getIdentifier().getName().toLowerCase())) {
+ throw new SqlCompilationException(
+ "Alias " + selectExpr.getIdentifier().getName() + " cannot be
referred in SELECT Clause");
+ }
+ }
+ }
+
private static void validateGroupByClause(PinotQuery pinotQuery)
throws SqlCompilationException {
if (pinotQuery.getGroupByList() == null) {
@@ -108,14 +141,21 @@ public class CalciteSqlParser {
for (Expression selectExpression : pinotQuery.getSelectList()) {
if (!isAggregateExpression(selectExpression)) {
boolean foundInGroupByClause = false;
+ Expression selectionToCheck;
+ if (selectExpression.getFunctionCall() != null &&
selectExpression.getFunctionCall().getOperator()
+ .equalsIgnoreCase("AS")) {
+ selectionToCheck =
selectExpression.getFunctionCall().getOperands().get(0);
+ } else {
+ selectionToCheck = selectExpression;
+ }
for (Expression groupByExpression : pinotQuery.getGroupByList()) {
- if (groupByExpression.equals(selectExpression)) {
+ if (groupByExpression.equals(selectionToCheck)) {
foundInGroupByClause = true;
}
}
if (!foundInGroupByClause) {
throw new SqlCompilationException(
- "'" + RequestUtils.prettyPrint(selectExpression) + "' should
appear in GROUP BY clause.");
+ "'" + RequestUtils.prettyPrint(selectionToCheck) + "' should
appear in GROUP BY clause.");
}
}
}
@@ -239,10 +279,60 @@ public class CalciteSqlParser {
throw new RuntimeException(
"Unable to convert SqlNode: " + sqlNode + " to PinotQuery. Unknown
node type: " + sqlNode.getKind());
}
- validate(pinotQuery);
+ Map<Identifier, Expression> aliasMap =
extractAlias(pinotQuery.getSelectList());
+ applyAlias(aliasMap, pinotQuery);
+ validate(aliasMap, pinotQuery);
return pinotQuery;
}
+ private static void applyAlias(Map<Identifier, Expression> aliasMap,
PinotQuery pinotQuery) {
+ if (pinotQuery.isSetFilterExpression()) {
+ applyAlias(aliasMap, pinotQuery.getFilterExpression());
+ }
+ if (pinotQuery.isSetGroupByList()) {
+ for (Expression groupByExpr : pinotQuery.getGroupByList()) {
+ applyAlias(aliasMap, groupByExpr);
+ }
+ }
+ if (pinotQuery.isSetOrderByList()) {
+ for (Expression orderByExpr : pinotQuery.getOrderByList()) {
+ applyAlias(aliasMap, orderByExpr);
+ }
+ }
+ }
+
+ private static void applyAlias(Map<Identifier, Expression> aliasMap,
Expression expression) {
+ if (expression == null) {
+ return;
+ }
+ Identifier identifierKey = expression.getIdentifier();
+ if ((identifierKey != null) && (aliasMap.containsKey(identifierKey))) {
+ Expression aliasExpression = aliasMap.get(identifierKey);
+
expression.setType(aliasExpression.getType()).setIdentifier(aliasExpression.getIdentifier())
+
.setFunctionCall(aliasExpression.getFunctionCall()).setLiteral(aliasExpression.getLiteral());
+ }
+ if (expression.getFunctionCall() != null) {
+ for (Expression operand : expression.getFunctionCall().getOperands()) {
+ applyAlias(aliasMap, operand);
+ }
+ }
+ }
+
+ private static Map<Identifier, Expression> extractAlias(List<Expression>
expressions) {
+ Map<Identifier, Expression> aliasMap = new HashMap<>();
+ for (Expression expression : expressions) {
+ Function functionCall = expression.getFunctionCall();
+ if (functionCall == null) {
+ continue;
+ }
+ if (functionCall.getOperator().equalsIgnoreCase("AS")) {
+ Expression identifierExpr = functionCall.getOperands().get(1);
+ aliasMap.put(identifierExpr.getIdentifier(),
functionCall.getOperands().get(0));
+ }
+ }
+ return aliasMap;
+ }
+
private static List<String> extractOptionsFromSql(String sql) {
List<String> results = new ArrayList<>();
Matcher matcher = OPTIONS_REGEX_PATTEN.matcher(sql);
@@ -350,28 +440,7 @@ public class CalciteSqlParser {
return RequestUtils.getIdentifierExpression(node.toString());
case LITERAL:
return RequestUtils.getLiteralExpression((SqlLiteral) node);
- case AS:
- // Aggregation
- case COUNT:
- case SUM:
- case AVG:
- case MAX:
- case MIN:
- case HOP:
- case OTHER_FUNCTION:
- // Filtering
- case OR:
- case AND:
- case EQUALS:
- case NOT_EQUALS:
- case BETWEEN:
- case GREATER_THAN:
- case GREATER_THAN_OR_EQUAL:
- case LESS_THAN:
- case LESS_THAN_OR_EQUAL:
- case IN:
- case NOT_IN:
- case LIKE:
+ default:
SqlBasicCall funcSqlNode = (SqlBasicCall) node;
String funcName = funcSqlNode.getOperator().getKind().name();
if (funcSqlNode.getOperator().getKind() == SqlKind.OTHER_FUNCTION) {
@@ -390,8 +459,6 @@ public class CalciteSqlParser {
}
}
return funcExpr;
- default:
- throw new RuntimeException("Unknown node type: " + node.getKind());
}
}
}
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 6ae54e3..2957d81 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
@@ -898,4 +898,124 @@ public class CalciteSqlCompilerTest {
Assert.assertTrue(e.getMessage().contains("is not allowed in GROUP BY
clause."));
}
}
+
+ @Test
+ public void testAliasQuery() {
+ String sql;
+ PinotQuery pinotQuery;
+ // Valid alias in query.
+ sql =
+ "select secondsSinceEpoch, sum(rsvp_count) as sum_rsvp_count, count(*)
as cnt from meetupRsvp group by secondsSinceEpoch order by cnt, sum_rsvp_count
DESC limit 50";
+ pinotQuery = CalciteSqlParser.compileToPinotQuery(sql);
+ Assert.assertEquals(pinotQuery.getSelectListSize(), 3);
+ Assert.assertEquals(pinotQuery.getGroupByListSize(), 1);
+ Assert.assertEquals(pinotQuery.getOrderByListSize(), 2);
+
Assert.assertEquals(pinotQuery.getOrderByList().get(0).getFunctionCall().getOperator(),
"ASC");
+ Assert.assertEquals(
+
pinotQuery.getOrderByList().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
+ "COUNT");
+ Assert.assertEquals(
+
pinotQuery.getOrderByList().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0)
+ .getIdentifier().getName(), "*");
+
Assert.assertEquals(pinotQuery.getOrderByList().get(1).getFunctionCall().getOperator(),
"DESC");
+ Assert.assertEquals(
+
pinotQuery.getOrderByList().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
+ "SUM");
+ Assert.assertEquals(
+
pinotQuery.getOrderByList().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0)
+ .getIdentifier().getName(), "rsvp_count");
+
+ // Valid mixed alias expressions in query.
+ sql =
+ "select secondsSinceEpoch, sum(rsvp_count), count(*) as cnt from
meetupRsvp group by secondsSinceEpoch order by cnt, sum(rsvp_count) DESC limit
50";
+ pinotQuery = CalciteSqlParser.compileToPinotQuery(sql);
+ Assert.assertEquals(pinotQuery.getSelectListSize(), 3);
+ Assert.assertEquals(pinotQuery.getGroupByListSize(), 1);
+ Assert.assertEquals(pinotQuery.getOrderByListSize(), 2);
+
Assert.assertEquals(pinotQuery.getOrderByList().get(0).getFunctionCall().getOperator(),
"ASC");
+ Assert.assertEquals(
+
pinotQuery.getOrderByList().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
+ "COUNT");
+ Assert.assertEquals(
+
pinotQuery.getOrderByList().get(0).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0)
+ .getIdentifier().getName(), "*");
+
Assert.assertEquals(pinotQuery.getOrderByList().get(1).getFunctionCall().getOperator(),
"DESC");
+ Assert.assertEquals(
+
pinotQuery.getOrderByList().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
+ "SUM");
+ Assert.assertEquals(
+
pinotQuery.getOrderByList().get(1).getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0)
+ .getIdentifier().getName(), "rsvp_count");
+
+ sql =
+ "select secondsSinceEpoch/86400 AS daysSinceEpoch, sum(rsvp_count) as
sum_rsvp_count, count(*) as cnt from meetupRsvp where daysSinceEpoch = 18523
group by daysSinceEpoch order by cnt, sum_rsvp_count DESC limit 50";
+ pinotQuery = CalciteSqlParser.compileToPinotQuery(sql);
+ Assert.assertEquals(pinotQuery.getSelectListSize(), 3);
+
Assert.assertEquals(pinotQuery.getFilterExpression().getFunctionCall().getOperator(),
"EQUALS");
+ Assert.assertEquals(
+
pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
+ "DIVIDE");
+ Assert.assertEquals(
+
pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(0)
+ .getIdentifier().getName(), "secondsSinceEpoch");
+ Assert.assertEquals(
+
pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(0).getFunctionCall().getOperands().get(1)
+ .getLiteral().getLongValue(), 86400);
+ Assert.assertEquals(
+
pinotQuery.getFilterExpression().getFunctionCall().getOperands().get(1).getLiteral().getLongValue(),
18523);
+ Assert.assertEquals(pinotQuery.getGroupByListSize(), 1);
+
Assert.assertEquals(pinotQuery.getGroupByList().get(0).getFunctionCall().getOperator(),
"DIVIDE");
+ Assert.assertEquals(
+
pinotQuery.getGroupByList().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
+ "secondsSinceEpoch");
+ Assert.assertEquals(
+
pinotQuery.getGroupByList().get(0).getFunctionCall().getOperands().get(1).getLiteral().getLongValue(),
86400);
+ Assert.assertEquals(pinotQuery.getOrderByListSize(), 2);
+
+ // Invalid groupBy clause shouldn't contain aggregate expression, like
sum(rsvp_count), count(*).
+ try {
+ sql = "select sum(rsvp_count), count(*) as cnt from meetupRsvp group by
group_country, cnt limit 50";
+ CalciteSqlParser.compileToPinotQuery(sql);
+ Assert.fail("Query should have failed compilation");
+ } catch (Exception e) {
+ Assert.assertTrue(e instanceof SqlCompilationException);
+ Assert.assertTrue(e.getMessage().contains("is not allowed in GROUP BY
clause."));
+ }
+ }
+
+ @Test
+ public void testAliasInSelection() {
+ String sql;
+ PinotQuery pinotQuery;
+ sql = "SELECT C1 AS ALIAS_C1, C2 AS ALIAS_C2, ADD(C1, C2) FROM Foo";
+ pinotQuery = CalciteSqlParser.compileToPinotQuery(sql);
+ Assert.assertEquals(pinotQuery.getSelectListSize(), 3);
+
Assert.assertEquals(pinotQuery.getSelectList().get(0).getFunctionCall().getOperator(),
"AS");
+ Assert.assertEquals(
+
pinotQuery.getSelectList().get(0).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"C1");
+ Assert.assertEquals(
+
pinotQuery.getSelectList().get(0).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"ALIAS_C1");
+
+
Assert.assertEquals(pinotQuery.getSelectList().get(1).getFunctionCall().getOperator(),
"AS");
+ Assert.assertEquals(
+
pinotQuery.getSelectList().get(1).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"C2");
+ Assert.assertEquals(
+
pinotQuery.getSelectList().get(1).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"ALIAS_C2");
+
+
Assert.assertEquals(pinotQuery.getSelectList().get(2).getFunctionCall().getOperator(),
"ADD");
+ Assert.assertEquals(
+
pinotQuery.getSelectList().get(2).getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"C1");
+ Assert.assertEquals(
+
pinotQuery.getSelectList().get(2).getFunctionCall().getOperands().get(1).getIdentifier().getName(),
"C2");
+
+ // Invalid groupBy clause shouldn't contain aggregate expression, like
sum(rsvp_count), count(*).
+ try {
+ sql = "SELECT C1 AS ALIAS_C1, C2 AS ALIAS_C2, ADD(alias_c1, alias_c2)
FROM Foo";
+ CalciteSqlParser.compileToPinotQuery(sql);
+ Assert.fail("Query should have failed compilation");
+ } catch (Exception e) {
+ Assert.assertTrue(e instanceof SqlCompilationException);
+ Assert.assertTrue(e.getMessage().contains("cannot be referred in SELECT
Clause"));
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
