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 <fx19880...@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-unsubscr...@pinot.apache.org For additional commands, e-mail: commits-h...@pinot.apache.org