You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by ja...@apache.org on 2023/07/31 15:54:51 UTC
[pinot] branch master updated: Fail the query if a filter's rhs contains NULL. (#11188)
This is an automated email from the ASF dual-hosted git repository.
jackie pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/pinot.git
The following commit(s) were added to refs/heads/master by this push:
new 214feadc9a Fail the query if a filter's rhs contains NULL. (#11188)
214feadc9a is described below
commit 214feadc9a8dd7c79f133b1ca6c3a5a4a76fb0e7
Author: Shen Yu <sh...@startree.ai>
AuthorDate: Mon Jul 31 08:54:45 2023 -0700
Fail the query if a filter's rhs contains NULL. (#11188)
---
.../apache/pinot/sql/parsers/CalciteSqlParser.java | 31 +++++++++++
.../pinot/sql/parsers/CalciteSqlCompilerTest.java | 65 ++++++++++++++++++++++
2 files changed, 96 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 5d7581874f..890048c593 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
@@ -201,6 +201,9 @@ public class CalciteSqlParser {
throws SqlCompilationException {
validateGroupByClause(pinotQuery);
validateDistinctQuery(pinotQuery);
+ if (pinotQuery.isSetFilterExpression()) {
+ validateFilter(pinotQuery.getFilterExpression());
+ }
}
private static void validateGroupByClause(PinotQuery pinotQuery)
@@ -267,6 +270,34 @@ public class CalciteSqlParser {
}
}
+ /*
+ * Throws an exception if the filter's rhs has NULL because:
+ * - Predicate evaluator and pruning do not have NULL support.
+ * - It is not useful to have NULL in the filter's rhs.
+ * - For most of the filters (e.g. EQUALS, GREATER_THAN, LIKE), the rhs being NULL leads to no record matched.
+ * - For IN, adding NULL to the rhs list does not change the matched records.
+ * - For NOT IN, adding NULL to the rhs list leads to no record matched.
+ */
+ private static void validateFilter(Expression filterExpression) {
+ if (!filterExpression.isSetFunctionCall()) {
+ return;
+ }
+ String operator = filterExpression.getFunctionCall().getOperator();
+ if (operator.equals(FilterKind.AND.name()) || operator.equals(FilterKind.OR.name()) || operator.equals(
+ FilterKind.NOT.name())) {
+ for (Expression filter : filterExpression.getFunctionCall().getOperands()) {
+ validateFilter(filter);
+ }
+ } else {
+ List<Expression> operands = filterExpression.getFunctionCall().getOperands();
+ for (int i = 1; i < operands.size(); i++) {
+ if (operands.get(i).getLiteral().isSetNullValue()) {
+ throw new IllegalStateException(String.format("Using NULL in %s filter is not supported", operator));
+ }
+ }
+ }
+ }
+
private static List<Expression> getAliasLeftExpressionsFromDistinctExpression(Function function) {
List<Expression> operands = function.getOperands();
List<Expression> expressions = new ArrayList<>(operands.size());
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 b951b737de..6b5a515858 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
@@ -3124,4 +3124,69 @@ public class CalciteSqlCompilerTest {
CalciteSqlParser.compileToPinotQuery("SELECT key FROM T1"));
Assert.assertEquals(join.getCondition(), CalciteSqlParser.compileToExpression("T1.key = self.key"));
}
+
+ @Test
+ public void testInPredicateWithOutNullPasses() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 IN (1, 2) AND column2 = 1");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in IN "
+ + "filter is not supported")
+ public void testSingleInPredicateWithNullFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 IN (1, 2, NULL)");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in NOT_IN "
+ + "filter is not supported")
+ public void testSingleNotInPredicateWithNullFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 NOT IN (1, 2, NULL)");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in IN "
+ + "filter is not supported")
+ public void testAndFilterWithNullFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 IN (1, 2, NULL) AND column2 = 1");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in NOT_IN "
+ + "filter is not supported")
+ public void testOrFilterWithNullFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 NOT IN (1, 2, NULL) OR column2 = 1");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in IN "
+ + "filter is not supported")
+ public void testNotFilterWithNullFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE NOT(column1 IN (NULL, 1, 2))");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in "
+ + "GREATER_THAN filter is not supported")
+ public void testGreaterThanNullFilterFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 > null");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in "
+ + "LESS_THAN_OR_EQUAL filter is not supported")
+ public void testLessThanOrEqualNullFilterFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 <= null");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in LIKE "
+ + "filter is not supported")
+ public void testLikeFilterWithNullFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 LIKE null");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in EQUALS "
+ + "filter is not supported")
+ public void testEqualFilterWithNullFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 = null");
+ }
+
+ @Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "Using NULL in "
+ + "NOT_EQUALS filter is not supported")
+ public void testInEqualFilterWithNullFails() {
+ CalciteSqlParser.compileToPinotQuery("SELECT * FROM testTable WHERE column1 != null");
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org