You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by xi...@apache.org on 2020/05/28 09:27:58 UTC
[incubator-pinot] 01/02: Adding case-when-else sql parsing statement
This is an automated email from the ASF dual-hosted git repository.
xiangfu pushed a commit to branch support_case_when_statement
in repository https://gitbox.apache.org/repos/asf/incubator-pinot.git
commit 5bf635da66507bfbc0a226553a67fd3d6054916b
Author: Xiang Fu <fx...@gmail.com>
AuthorDate: Tue May 26 16:17:32 2020 -0700
Adding case-when-else sql parsing statement
---
.../apache/pinot/sql/parsers/CalciteSqlParser.java | 20 +++++++
.../pinot/sql/parsers/CalciteSqlCompilerTest.java | 64 ++++++++++++++++++++++
2 files changed, 84 insertions(+)
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 2aec8ec..5e0fe8d 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
@@ -39,6 +39,7 @@ import org.apache.calcite.sql.SqlNumericLiteral;
import org.apache.calcite.sql.SqlOrderBy;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlSelectKeyword;
+import org.apache.calcite.sql.fun.SqlCase;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.babel.SqlBabelParserImpl;
@@ -610,6 +611,25 @@ public class CalciteSqlParser {
}
asFuncExpr.getFunctionCall().addToOperands(RequestUtils.getIdentifierExpression(aliasName));
return asFuncExpr;
+ case CASE:
+ // CASE WHEN Statement is model as a function with variable length parameters.
+ // Assume N is number of WHEN Statements, total number of parameters is (2 * N + 1).
+ // - N: Convert each WHEN Statement into a function Expression;
+ // - N: Convert each THEN Statement into an Expression;
+ // - 1: Convert ELSE Statement into an Expression.
+ SqlCase caseSqlNode = (SqlCase) node;
+ SqlNodeList whenOperands = caseSqlNode.getWhenOperands();
+ SqlNodeList thenOperands = caseSqlNode.getThenOperands();
+ SqlNode elseOperand = caseSqlNode.getElseOperand();
+ Expression caseFuncExpr = RequestUtils.getFunctionExpression(SqlKind.CASE.name());
+ for (SqlNode whenSqlNode : whenOperands.getList()) {
+ caseFuncExpr.getFunctionCall().addToOperands(toExpression(whenSqlNode));
+ }
+ for (SqlNode thenSqlNode : thenOperands.getList()) {
+ caseFuncExpr.getFunctionCall().addToOperands(toExpression(thenSqlNode));
+ }
+ caseFuncExpr.getFunctionCall().addToOperands(toExpression(elseOperand));
+ return caseFuncExpr;
case OTHER:
if (node instanceof SqlDataTypeSpec) {
// This is to handle expression like: CAST(col AS INT)
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 aec7917..80ef72b 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
@@ -50,6 +50,70 @@ import org.testng.annotations.Test;
public class CalciteSqlCompilerTest {
@Test
+ public void testCaseWhenStatements() {
+ PinotQuery pinotQuery = CalciteSqlParser.compileToPinotQuery(
+ "SELECT OrderID, Quantity,\n"
+ + "CASE\n"
+ + " WHEN Quantity > 30 THEN 'The quantity is greater than 30'\n"
+ + " WHEN Quantity = 30 THEN 'The quantity is 30'\n"
+ + " ELSE 'The quantity is under 30'\n"
+ + "END AS QuantityText\n"
+ + "FROM OrderDetails");
+ Assert.assertEquals(pinotQuery.getSelectList().get(0).getIdentifier().getName(), "OrderID");
+ Assert.assertEquals(pinotQuery.getSelectList().get(1).getIdentifier().getName(), "Quantity");
+ Function asFunc = pinotQuery.getSelectList().get(2).getFunctionCall();
+ Assert.assertEquals(asFunc.getOperator(), SqlKind.AS.name());
+ Function caseFunc = asFunc.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(caseFunc.getOperator(), SqlKind.CASE.name());
+ Assert.assertEquals(caseFunc.getOperandsSize(), 5);
+ Function greatThanFunc = caseFunc.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(greatThanFunc.getOperator(), SqlKind.GREATER_THAN.name());
+ Assert.assertEquals(greatThanFunc.getOperands().get(0).getIdentifier().getName(), "Quantity");
+ Assert.assertEquals(greatThanFunc.getOperands().get(1).getLiteral().getFieldValue(), 30L);
+ Function equalsFunc = caseFunc.getOperands().get(1).getFunctionCall();
+ Assert.assertEquals(equalsFunc.getOperator(), SqlKind.EQUALS.name());
+ Assert.assertEquals(equalsFunc.getOperands().get(0).getIdentifier().getName(), "Quantity");
+ Assert.assertEquals(equalsFunc.getOperands().get(1).getLiteral().getFieldValue(), 30L);
+ Assert.assertEquals(caseFunc.getOperands().get(2).getLiteral().getFieldValue(), "The quantity is greater than 30");
+ Assert.assertEquals(caseFunc.getOperands().get(3).getLiteral().getFieldValue(), "The quantity is 30");
+ Assert.assertEquals(caseFunc.getOperands().get(4).getLiteral().getFieldValue(), "The quantity is under 30");
+
+ pinotQuery = CalciteSqlParser.compileToPinotQuery(
+ "SELECT Quantity,\n"
+ + "SUM(CASE\n"
+ + " WHEN Quantity > 30 THEN 3\n"
+ + " WHEN Quantity > 20 THEN 2\n"
+ + " WHEN Quantity > 10 THEN 1\n"
+ + " ELSE 0\n"
+ + "END) AS new_sum_quant\n"
+ + "FROM OrderDetails");
+ Assert.assertEquals(pinotQuery.getSelectList().get(0).getIdentifier().getName(), "Quantity");
+ asFunc = pinotQuery.getSelectList().get(1).getFunctionCall();
+ Assert.assertEquals(asFunc.getOperator(), SqlKind.AS.name());
+ Function sumFunc = asFunc.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(sumFunc.getOperator(), SqlKind.SUM.name());
+ caseFunc = sumFunc.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(caseFunc.getOperator(), SqlKind.CASE.name());
+ Assert.assertEquals(caseFunc.getOperandsSize(), 7);
+ greatThanFunc = caseFunc.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(greatThanFunc.getOperator(), SqlKind.GREATER_THAN.name());
+ Assert.assertEquals(greatThanFunc.getOperands().get(0).getIdentifier().getName(), "Quantity");
+ Assert.assertEquals(greatThanFunc.getOperands().get(1).getLiteral().getFieldValue(), 30L);
+ greatThanFunc = caseFunc.getOperands().get(1).getFunctionCall();
+ Assert.assertEquals(greatThanFunc.getOperator(), SqlKind.GREATER_THAN.name());
+ Assert.assertEquals(greatThanFunc.getOperands().get(0).getIdentifier().getName(), "Quantity");
+ Assert.assertEquals(greatThanFunc.getOperands().get(1).getLiteral().getFieldValue(), 20L);
+ greatThanFunc = caseFunc.getOperands().get(2).getFunctionCall();
+ Assert.assertEquals(greatThanFunc.getOperator(), SqlKind.GREATER_THAN.name());
+ Assert.assertEquals(greatThanFunc.getOperands().get(0).getIdentifier().getName(), "Quantity");
+ Assert.assertEquals(greatThanFunc.getOperands().get(1).getLiteral().getFieldValue(), 10L);
+ Assert.assertEquals(caseFunc.getOperands().get(3).getLiteral().getFieldValue(), 3L);
+ Assert.assertEquals(caseFunc.getOperands().get(4).getLiteral().getFieldValue(), 2L);
+ Assert.assertEquals(caseFunc.getOperands().get(5).getLiteral().getFieldValue(), 1L);
+ Assert.assertEquals(caseFunc.getOperands().get(6).getLiteral().getFieldValue(), 0L);
+ }
+
+ @Test
public void testQuotedStrings() {
PinotQuery pinotQuery =
CalciteSqlParser.compileToPinotQuery("select * from vegetables where origin = 'Martha''s Vineyard'");
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org