This is an automated email from the ASF dual-hosted git repository.
gortiz 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 bc71221f71 Apply filter reduce expressions Calcite rule at the end to
prevent literal-only filter pushdown to leaf stage (#14448)
bc71221f71 is described below
commit bc71221f718f5e08e08a10cb4ca5078bbf98aea1
Author: Yash Mayya <[email protected]>
AuthorDate: Tue Nov 19 17:44:07 2024 +0700
Apply filter reduce expressions Calcite rule at the end to prevent
literal-only filter pushdown to leaf stage (#14448)
---
.../tests/MultiStageEngineIntegrationTest.java | 29 ++++++---
.../calcite/rel/rules/PinotQueryRuleSets.java | 6 +-
.../query/planner/logical/RexExpressionUtils.java | 40 ++++++------
.../src/test/resources/queries/JoinPlans.json | 18 +++---
.../resources/queries/LiteralEvaluationPlans.json | 49 +++++++++++++++
.../resources/queries/WindowFunctionPlans.json | 72 +++++++++++-----------
.../test/resources/queries/LiteralExpressions.json | 33 ++++++++++
7 files changed, 172 insertions(+), 75 deletions(-)
diff --git
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/MultiStageEngineIntegrationTest.java
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/MultiStageEngineIntegrationTest.java
index 22b515eefa..40ae6920e2 100644
---
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/MultiStageEngineIntegrationTest.java
+++
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/MultiStageEngineIntegrationTest.java
@@ -915,6 +915,20 @@ public class MultiStageEngineIntegrationTest extends
BaseClusterIntegrationTestS
assertNoError(jsonNode);
}
+ @Test
+ public void testLiteralFilterReduce() throws Exception {
+ String sqlQuery = "SELECT * FROM (SELECT CASE WHEN AirTime > 0 THEN
'positive' ELSE 'negative' END AS AirTime "
+ + "FROM mytable) WHERE AirTime IN ('positive', 'negative')";
+ JsonNode jsonNode = postQuery(sqlQuery);
+ assertNoError(jsonNode);
+ assertEquals(jsonNode.get("resultTable").get("rows").size(),
getCountStarResult());
+
+ String explainQuery = "EXPLAIN PLAN FOR " + sqlQuery;
+ jsonNode = postQuery(explainQuery);
+
assertTrue(jsonNode.get("resultTable").get("rows").get(0).get(1).asText().contains("LogicalProject"));
+
assertFalse(jsonNode.get("resultTable").get("rows").get(0).get(1).asText().contains("LogicalFilter"));
+ }
+
@Test
public void testBetween()
throws Exception {
@@ -927,10 +941,9 @@ public class MultiStageEngineIntegrationTest extends
BaseClusterIntegrationTestS
jsonNode = postQuery(explainQuery);
assertNoError(jsonNode);
String plan =
jsonNode.get("resultTable").get("rows").get(0).get(1).asText();
- // Ensure that the BETWEEN filter predicate was converted to >= and <=
+ // Ensure that the BETWEEN filter predicate was converted
Assert.assertFalse(plan.contains("BETWEEN"));
- Assert.assertTrue(plan.contains(">="));
- Assert.assertTrue(plan.contains("<="));
+ Assert.assertTrue(plan.contains("Sarg[[10..50]]"));
// No rows should be returned since lower bound is greater than upper bound
sqlQuery = "SELECT COUNT(*) FROM mytable WHERE ARRAY_TO_MV(RandomAirports)
BETWEEN 'SUN' AND 'GTR'";
@@ -942,10 +955,11 @@ public class MultiStageEngineIntegrationTest extends
BaseClusterIntegrationTestS
jsonNode = postQuery(explainQuery);
assertNoError(jsonNode);
plan = jsonNode.get("resultTable").get("rows").get(0).get(1).asText();
- // Ensure that the BETWEEN filter predicate was not converted to >= and <=
+ // Ensure that the BETWEEN filter predicate was not converted
Assert.assertTrue(plan.contains("BETWEEN"));
Assert.assertFalse(plan.contains(">="));
Assert.assertFalse(plan.contains("<="));
+ Assert.assertFalse(plan.contains("Sarg"));
// Expect a non-zero result this time since we're using BETWEEN SYMMETRIC
sqlQuery = "SELECT COUNT(*) FROM mytable WHERE ARRAY_TO_MV(RandomAirports)
BETWEEN SYMMETRIC 'SUN' AND 'GTR'";
@@ -957,10 +971,11 @@ public class MultiStageEngineIntegrationTest extends
BaseClusterIntegrationTestS
jsonNode = postQuery(explainQuery);
assertNoError(jsonNode);
plan = jsonNode.get("resultTable").get("rows").get(0).get(1).asText();
- // Ensure that the BETWEEN filter predicate was not converted to >= and <=
Assert.assertTrue(plan.contains("BETWEEN"));
+ // Ensure that the BETWEEN filter predicate was not converted
Assert.assertFalse(plan.contains(">="));
Assert.assertFalse(plan.contains("<="));
+ Assert.assertFalse(plan.contains("Sarg"));
// Test NOT BETWEEN
sqlQuery = "SELECT COUNT(*) FROM mytable WHERE ARRAY_TO_MV(RandomAirports)
NOT BETWEEN 'GTR' AND 'SUN'";
@@ -974,12 +989,12 @@ public class MultiStageEngineIntegrationTest extends
BaseClusterIntegrationTestS
jsonNode = postQuery(explainQuery);
assertNoError(jsonNode);
plan = jsonNode.get("resultTable").get("rows").get(0).get(1).asText();
- // Ensure that the BETWEEN filter predicate was not converted to >= and
<=. Also ensure that the NOT filter is
- // added.
+ // Ensure that the BETWEEN filter predicate was not converted. Also ensure
that the NOT filter is added.
Assert.assertTrue(plan.contains("BETWEEN"));
Assert.assertTrue(plan.contains("FilterNot"));
Assert.assertFalse(plan.contains(">="));
Assert.assertFalse(plan.contains("<="));
+ Assert.assertFalse(plan.contains("Sarg"));
}
@Test
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotQueryRuleSets.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotQueryRuleSets.java
index 5bc55835e7..d2aaf4dbdd 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotQueryRuleSets.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotQueryRuleSets.java
@@ -127,8 +127,6 @@ public class PinotQueryRuleSets {
// Pinot specific rules that should be run AFTER all other rules
public static final List<RelOptRule> PINOT_POST_RULES = List.of(
- // Evaluate the Literal filter nodes
- CoreRules.FILTER_REDUCE_EXPRESSIONS,
// TODO: Merge the following 2 rules into a single rule
// add an extra exchange for sort
PinotSortExchangeNodeInsertRule.INSTANCE,
@@ -152,7 +150,9 @@ public class PinotQueryRuleSets {
// NOTE: Keep this rule at the end because it can potentially create a
lot of predicates joined by OR/AND for IN/
// NOT IN clause, which can be expensive to process in other rules.
// TODO: Consider removing this rule and directly handle SEARCH in
RexExpressionUtils.
- PinotFilterExpandSearchRule.INSTANCE
+ PinotFilterExpandSearchRule.INSTANCE,
+ // Evaluate the Literal filter nodes
+ CoreRules.FILTER_REDUCE_EXPRESSIONS
);
//@formatter:on
}
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/logical/RexExpressionUtils.java
b/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/logical/RexExpressionUtils.java
index d9b4af5fca..bf92e28f8e 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/logical/RexExpressionUtils.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/logical/RexExpressionUtils.java
@@ -316,32 +316,32 @@ public class RexExpressionUtils {
private static RexExpression handleSearch(RexCall rexCall) {
assert rexCall.operands.size() == 2;
- RexInputRef rexInputRef = (RexInputRef) rexCall.operands.get(0);
- RexLiteral rexLiteral = (RexLiteral) rexCall.operands.get(1);
- ColumnDataType dataType =
RelToPlanNodeConverter.convertToColumnDataType(rexLiteral.getType());
- Sarg sarg = rexLiteral.getValueAs(Sarg.class);
+ RexNode leftOperand = rexCall.operands.get(0);
+ RexLiteral searchArgument = (RexLiteral) rexCall.operands.get(1);
+ ColumnDataType dataType =
RelToPlanNodeConverter.convertToColumnDataType(searchArgument.getType());
+ Sarg sarg = searchArgument.getValueAs(Sarg.class);
assert sarg != null;
if (sarg.isPoints()) {
return new RexExpression.FunctionCall(ColumnDataType.BOOLEAN,
SqlKind.IN.name(),
- toFunctionOperands(rexInputRef, sarg.rangeSet.asRanges(), dataType));
+ toSearchFunctionOperands(leftOperand, sarg.rangeSet.asRanges(),
dataType));
} else if (sarg.isComplementedPoints()) {
return new RexExpression.FunctionCall(ColumnDataType.BOOLEAN,
SqlKind.NOT_IN.name(),
- toFunctionOperands(rexInputRef,
sarg.rangeSet.complement().asRanges(), dataType));
+ toSearchFunctionOperands(leftOperand,
sarg.rangeSet.complement().asRanges(), dataType));
} else {
Set<Range> ranges = sarg.rangeSet.asRanges();
- return convertRangesToOr(dataType, rexInputRef, ranges);
+ return convertRangesToOr(dataType, leftOperand, ranges);
}
}
- private static RexExpression convertRangesToOr(ColumnDataType dataType,
RexInputRef rexInputRef, Set<Range> ranges) {
+ private static RexExpression convertRangesToOr(ColumnDataType dataType,
RexNode leftOperand, Set<Range> ranges) {
int numRanges = ranges.size();
if (numRanges == 0) {
return RexExpression.Literal.FALSE;
}
- RexExpression.InputRef rexInput = fromRexInputRef(rexInputRef);
+ RexExpression convertedLeftOperand = fromRexNode(leftOperand);
List<RexExpression> operands = new ArrayList<>(numRanges);
for (Range range : ranges) {
- RexExpression operand = convertRange(rexInput, dataType, range);
+ RexExpression operand = convertRange(convertedLeftOperand, dataType,
range);
if (operand == RexExpression.Literal.TRUE) {
return operand;
}
@@ -359,43 +359,43 @@ public class RexExpressionUtils {
}
}
- private static RexExpression convertRange(RexExpression.InputRef rexInput,
ColumnDataType dataType, Range range) {
+ private static RexExpression convertRange(RexExpression leftOperand,
ColumnDataType dataType, Range range) {
if (range.isEmpty()) {
return RexExpression.Literal.FALSE;
}
if (!range.hasLowerBound()) {
- return !range.hasUpperBound() ? RexExpression.Literal.TRUE :
convertUpperBound(rexInput, dataType, range);
+ return !range.hasUpperBound() ? RexExpression.Literal.TRUE :
convertUpperBound(leftOperand, dataType, range);
}
if (!range.hasUpperBound()) {
- return convertLowerBound(rexInput, dataType, range);
+ return convertLowerBound(leftOperand, dataType, range);
}
return new RexExpression.FunctionCall(ColumnDataType.BOOLEAN,
SqlKind.AND.name(),
- List.of(convertLowerBound(rexInput, dataType, range),
convertUpperBound(rexInput, dataType, range)));
+ List.of(convertLowerBound(leftOperand, dataType, range),
convertUpperBound(leftOperand, dataType, range)));
}
- private static RexExpression convertLowerBound(RexExpression.InputRef
inputRef, ColumnDataType dataType,
+ private static RexExpression convertLowerBound(RexExpression leftOperand,
ColumnDataType dataType,
Range range) {
assert range.hasLowerBound();
SqlKind sqlKind = range.lowerBoundType() == BoundType.OPEN ?
SqlKind.GREATER_THAN : SqlKind.GREATER_THAN_OR_EQUAL;
return new RexExpression.FunctionCall(ColumnDataType.BOOLEAN,
sqlKind.name(),
- List.of(inputRef, fromRexLiteralValue(dataType,
range.lowerEndpoint())));
+ List.of(leftOperand, fromRexLiteralValue(dataType,
range.lowerEndpoint())));
}
- private static RexExpression convertUpperBound(RexExpression.InputRef
inputRef, ColumnDataType dataType,
+ private static RexExpression convertUpperBound(RexExpression leftOperand,
ColumnDataType dataType,
Range range) {
assert range.hasUpperBound();
SqlKind sqlKind = range.upperBoundType() == BoundType.OPEN ?
SqlKind.LESS_THAN : SqlKind.LESS_THAN_OR_EQUAL;
return new RexExpression.FunctionCall(ColumnDataType.BOOLEAN,
sqlKind.name(),
- List.of(inputRef, fromRexLiteralValue(dataType,
range.upperEndpoint())));
+ List.of(leftOperand, fromRexLiteralValue(dataType,
range.upperEndpoint())));
}
/**
* Transforms a set of <b>point based</b> ranges into a list of expressions.
*/
- private static List<RexExpression> toFunctionOperands(RexInputRef
rexInputRef, Set<Range> ranges,
+ private static List<RexExpression> toSearchFunctionOperands(RexNode
leftOperand, Set<Range> ranges,
ColumnDataType dataType) {
List<RexExpression> operands = new ArrayList<>(1 + ranges.size());
- operands.add(fromRexInputRef(rexInputRef));
+ operands.add(fromRexNode(leftOperand));
for (Range range : ranges) {
operands.add(fromRexLiteralValue(dataType, range.lowerEndpoint()));
}
diff --git a/pinot-query-planner/src/test/resources/queries/JoinPlans.json
b/pinot-query-planner/src/test/resources/queries/JoinPlans.json
index cd08713dfe..456618bbf4 100644
--- a/pinot-query-planner/src/test/resources/queries/JoinPlans.json
+++ b/pinot-query-planner/src/test/resources/queries/JoinPlans.json
@@ -193,11 +193,11 @@
"\n LogicalJoin(condition=[=($0, $1)], joinType=[inner])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0])",
- "\n LogicalFilter(condition=[OR(=($1, _UTF-8'bar'), =($1,
_UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1, Sarg[_UTF-8'bar',
_UTF-8'foo']:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0], col2=[$1])",
- "\n LogicalFilter(condition=[AND(<>($1,
_UTF-8'alice':VARCHAR(7) CHARACTER SET \"UTF-8\"), <>($1,
_UTF-8'charlie':VARCHAR(7) CHARACTER SET \"UTF-8\"))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'alice':VARCHAR(7) CHARACTER SET \"UTF-8\"),
(_UTF-8'alice':VARCHAR(7) CHARACTER SET \"UTF-8\".._UTF-8'charlie':VARCHAR(7)
CHARACTER SET \"UTF-8\"), (_UTF-8'charlie':VARCHAR(7) CHARACTER SET
\"UTF-8\"..+∞)]:VARCHAR(7) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, b]])",
"\n"
]
@@ -211,11 +211,11 @@
"\n LogicalJoin(condition=[=($0, $1)], joinType=[inner])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0])",
- "\n LogicalFilter(condition=[OR(=($1, _UTF-8'bar'), =($1,
_UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1, Sarg[_UTF-8'bar',
_UTF-8'foo']:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0], col2=[$1])",
- "\n LogicalFilter(condition=[AND(<>($1,
_UTF-8'alice':VARCHAR(7) CHARACTER SET \"UTF-8\"), <>($1,
_UTF-8'charlie':VARCHAR(7) CHARACTER SET \"UTF-8\"))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'alice':VARCHAR(7) CHARACTER SET \"UTF-8\"),
(_UTF-8'alice':VARCHAR(7) CHARACTER SET \"UTF-8\".._UTF-8'charlie':VARCHAR(7)
CHARACTER SET \"UTF-8\"), (_UTF-8'charlie':VARCHAR(7) CHARACTER SET
\"UTF-8\"..+∞)]:VARCHAR(7) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, b]])",
"\n"
]
@@ -437,7 +437,7 @@
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[broadcast],
relExchangeType=[PIPELINE_BREAKER])",
"\n LogicalProject(col1=[$0])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'),
<>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[broadcast],
relExchangeType=[PIPELINE_BREAKER])",
"\n LogicalProject(col1=[$0])",
@@ -465,7 +465,7 @@
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[broadcast],
relExchangeType=[PIPELINE_BREAKER])",
"\n LogicalProject(col1=[$0])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'),
<>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[broadcast],
relExchangeType=[PIPELINE_BREAKER])",
"\n LogicalProject(col3=[$2])",
@@ -491,7 +491,7 @@
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[broadcast],
relExchangeType=[PIPELINE_BREAKER])",
"\n LogicalProject(col1=[$0])",
- "\n LogicalFilter(condition=[AND(<>($1,
_UTF-8'bar'), <>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[broadcast],
relExchangeType=[PIPELINE_BREAKER])",
"\n LogicalProject(col3=[$2])",
@@ -511,11 +511,11 @@
"\n LogicalTableScan(table=[[default, b]])",
"\n PinotLogicalExchange(distribution=[broadcast],
relExchangeType=[PIPELINE_BREAKER])",
"\n LogicalProject(col1=[$0])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[broadcast],
relExchangeType=[PIPELINE_BREAKER])",
"\n LogicalProject(col3=[$2])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1, Sarg[(-∞.._UTF-8'bar'),
(_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET
\"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
diff --git
a/pinot-query-planner/src/test/resources/queries/LiteralEvaluationPlans.json
b/pinot-query-planner/src/test/resources/queries/LiteralEvaluationPlans.json
index 8e513b76fa..d9ba67e9a4 100644
--- a/pinot-query-planner/src/test/resources/queries/LiteralEvaluationPlans.json
+++ b/pinot-query-planner/src/test/resources/queries/LiteralEvaluationPlans.json
@@ -257,6 +257,55 @@
"description": "select non-exist literal function",
"sql": "EXPLAIN PLAN FOR Select nonExistFun(1,2) FROM a",
"expectedException": ".*No match found for function signature
nonExistFun.*"
+ },
+ {
+ "description": "Literal only filter on subquery is simplified",
+ "sql": "EXPLAIN PLAN FOR SELECT * FROM (SELECT CASE WHEN col1 =
'abc123' THEN 'val1' ELSE 'val2' END as val FROM a) WHERE val in ('val1',
'val2')",
+ "output": [
+ "Execution Plan",
+ "\nLogicalProject(val=[CASE(=($0, _UTF-8'abc123'), _UTF-8'val1',
_UTF-8'val2')])",
+ "\n LogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ }
+ ]
+ },
+ "literal_planning_cte_tests": {
+ "comment": "Tests for CTEs involving literal only filters. The SQL parser
cannot get rid of expressions that cross CTEs, so this is useful to check that
the expressions are simplified in the logical plan.",
+ "queries": [
+ {
+ "description": "Simple filter on constants is simplified",
+ "sql": "EXPLAIN PLAN FOR WITH CTE_B AS ( SELECT 'a' AS val FROM a
GROUP BY val ) SELECT 1 FROM CTE_B WHERE val >= 'b'",
+ "output": [
+ "Execution Plan",
+ "\nLogicalValues(tuples=[[]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "AND filter on constants is simplified",
+ "sql": "EXPLAIN PLAN FOR WITH CTE_B AS (SELECT 1692057600000 AS __ts
FROM a GROUP BY __ts) SELECT 1692057600000 AS __ts FROM CTE_B WHERE __ts >=
1692057600000 AND __ts < 1693267200000 GROUP BY __ts",
+ "output": [
+ "Execution Plan",
+ "\nLogicalProject(__ts=[1692057600000:BIGINT])",
+ "\n PinotLogicalAggregate(group=[{0}])",
+ "\n PinotLogicalExchange(distribution=[hash[0]])",
+ "\n PinotLogicalAggregate(group=[{0}])",
+ "\n LogicalProject(__ts=[1692057600000:BIGINT])",
+ "\n LogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "Search + OR filter on constants is simplified",
+ "sql": "EXPLAIN PLAN FOR WITH tmp2 AS (SELECT CASE WHEN col2 = 'VAL1'
THEN 'A' ELSE col2 END AS cased FROM a) SELECT 1 FROM tmp2 WHERE ((cased = 'B')
OR (cased = 'A'))",
+ "output": [
+ "Execution Plan",
+ "\nLogicalProject(EXPR$0=[1])",
+ "\n LogicalFilter(condition=[SEARCH($1, Sarg[_UTF-8'A':VARCHAR
CHARACTER SET \"UTF-8\", _UTF-8'B':VARCHAR CHARACTER SET \"UTF-8\",
_UTF-8'VAL1':VARCHAR CHARACTER SET \"UTF-8\"]:VARCHAR CHARACTER SET
\"UTF-8\")])",
+ "\n LogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
}
]
}
diff --git
a/pinot-query-planner/src/test/resources/queries/WindowFunctionPlans.json
b/pinot-query-planner/src/test/resources/queries/WindowFunctionPlans.json
index ea68837d32..6fd3cbb193 100644
--- a/pinot-query-planner/src/test/resources/queries/WindowFunctionPlans.json
+++ b/pinot-query-planner/src/test/resources/queries/WindowFunctionPlans.json
@@ -218,7 +218,7 @@
"\n LogicalWindow(window#0=[window(aggs [MIN($0)])])",
"\n PinotLogicalExchange(distribution=[hash])",
"\n LogicalProject(col3=[$2], $1=[CONCAT($0, _UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(=($0, _UTF-8'bar'), =($0,
_UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($0, Sarg[_UTF-8'bar',
_UTF-8'foo']:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -457,7 +457,7 @@
"\n LogicalWindow(window#0=[window(aggs [MIN($0), MAX($0)])])",
"\n PinotLogicalExchange(distribution=[hash])",
"\n LogicalProject(col3=[$2], $1=[LENGTH(CONCAT($0, _UTF-8' ',
$1))])",
- "\n LogicalFilter(condition=[AND(<>($0, _UTF-8'bar'), <>($0,
_UTF-8'baz'), <>($0, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -472,7 +472,7 @@
"\n LogicalWindow(window#0=[window(rows between UNBOUNDED PRECEDING
and CURRENT ROW aggs [ROW_NUMBER()])])",
"\n PinotLogicalExchange(distribution=[hash])",
"\n LogicalProject($0=[LENGTH(CONCAT($0, _UTF-8' ', $1))])",
- "\n LogicalFilter(condition=[AND(<>($0, _UTF-8'bar'), <>($0,
_UTF-8'baz'), <>($0, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -748,7 +748,7 @@
"\n LogicalWindow(window#0=[window(partition {0} aggs [SUM($1),
COUNT($1)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1], col3=[$2])",
- "\n LogicalFilter(condition=[AND(>($2, 10), <=($2, 500))])",
+ "\n LogicalFilter(condition=[SEARCH($2, Sarg[(10..500]])])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -762,7 +762,7 @@
"\n LogicalWindow(window#0=[window(partition {0} aggs [SUM($1),
COUNT($1)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1], col3=[$2], $2=[CONCAT($0,
_UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1099,7 +1099,7 @@
"\n LogicalWindow(window#0=[window(partition {0} aggs [SUM($1),
COUNT($1), COUNT($0)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0], col3=[$2])",
- "\n LogicalFilter(condition=[AND(>($2, 42), OR(=($0,
_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'yoda':VARCHAR(9) CHARACTER SET \"UTF-8\")))])",
+ "\n LogicalFilter(condition=[AND(>($2, 42), SEARCH($0,
Sarg[_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\",
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\", _UTF-8'yoda':VARCHAR(9)
CHARACTER SET \"UTF-8\"]:VARCHAR(9) CHARACTER SET \"UTF-8\"))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1113,7 +1113,7 @@
"\n LogicalWindow(window#0=[window(partition {0} aggs [MIN($1),
MAX($1)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0], col3=[$2], $2=[REVERSE(CONCAT($0,
_UTF-8' ', $1))])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'baz'), <>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1578,7 +1578,7 @@
"\n LogicalWindow(window#0=[window(order by [0] aggs [SUM($1),
COUNT($1)])])",
"\n PinotLogicalSortExchange(distribution=[hash],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col2=[$1], col3=[$2])",
- "\n LogicalFilter(condition=[AND(>($2, 10), <=($2, 500))])",
+ "\n LogicalFilter(condition=[SEARCH($2, Sarg[(10..500]])])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1591,7 +1591,7 @@
"\nLogicalWindow(window#0=[window(order by [0] aggs
[DENSE_RANK()])])",
"\n PinotLogicalSortExchange(distribution=[hash], collation=[[0]],
isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col2=[$1])",
- "\n LogicalFilter(condition=[AND(>($2, 10), <=($2, 500))])",
+ "\n LogicalFilter(condition=[SEARCH($2, Sarg[(10..500]])])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1605,7 +1605,7 @@
"\n LogicalWindow(window#0=[window(order by [0] aggs [SUM($1),
COUNT($1)])])",
"\n PinotLogicalSortExchange(distribution=[hash],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col2=[$1], col3=[$2], $2=[CONCAT($0,
_UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1619,7 +1619,7 @@
"\n LogicalWindow(window#0=[window(order by [0] rows between
UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])",
"\n PinotLogicalSortExchange(distribution=[hash],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col2=[$1], $1=[CONCAT($0, _UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1633,7 +1633,7 @@
"\n LogicalWindow(window#0=[window(order by [0] aggs [RANK()])])",
"\n PinotLogicalSortExchange(distribution=[hash],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col2=[$1], $1=[CONCAT($0, _UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1922,7 +1922,7 @@
"\n LogicalWindow(window#0=[window(order by [0] aggs [SUM($1),
COUNT($1), COUNT($0)])])",
"\n PinotLogicalSortExchange(distribution=[hash],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col3=[$2])",
- "\n LogicalFilter(condition=[AND(>($2, 42), OR(=($0,
_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'yoda':VARCHAR(9) CHARACTER SET \"UTF-8\")))])",
+ "\n LogicalFilter(condition=[AND(>($2, 42), SEARCH($0,
Sarg[_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\",
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\", _UTF-8'yoda':VARCHAR(9)
CHARACTER SET \"UTF-8\"]:VARCHAR(9) CHARACTER SET \"UTF-8\"))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1936,7 +1936,7 @@
"\n LogicalWindow(window#0=[window(order by [0] aggs [MIN($1),
MAX($1)])])",
"\n PinotLogicalSortExchange(distribution=[hash],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col3=[$2], $2=[REVERSE(CONCAT($0,
_UTF-8' ', $1))])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'baz'), <>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -1950,7 +1950,7 @@
"\n LogicalWindow(window#0=[window(order by [0] aggs [RANK(),
DENSE_RANK()])])",
"\n PinotLogicalSortExchange(distribution=[hash],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], $1=[REVERSE(CONCAT($0, _UTF-8' ',
$1))])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'baz'), <>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2230,7 +2230,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [0] aggs
[SUM($1), COUNT($1)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1], col3=[$2])",
- "\n LogicalFilter(condition=[AND(>($2, 10), <=($2, 500))])",
+ "\n LogicalFilter(condition=[SEARCH($2, Sarg[(10..500]])])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2243,7 +2243,7 @@
"\nLogicalWindow(window#0=[window(partition {0} order by [0] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1])",
- "\n LogicalFilter(condition=[AND(>($2, 10), <=($2, 500))])",
+ "\n LogicalFilter(condition=[SEARCH($2, Sarg[(10..500]])])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2256,7 +2256,7 @@
"\nLogicalWindow(window#0=[window(partition {0} order by [0] aggs
[DENSE_RANK()])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1])",
- "\n LogicalFilter(condition=[AND(>($2, 10), <=($2, 500))])",
+ "\n LogicalFilter(condition=[SEARCH($2, Sarg[(10..500]])])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2270,7 +2270,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [0] aggs
[SUM($1), COUNT($1)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1], col3=[$2], $2=[CONCAT($0,
_UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2284,7 +2284,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [0] aggs
[RANK()])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1], $1=[CONCAT($0, _UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2571,7 +2571,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [0] aggs
[SUM($1), COUNT($1), COUNT($0)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0], col3=[$2])",
- "\n LogicalFilter(condition=[AND(>($2, 42), OR(=($0,
_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'yoda':VARCHAR(9) CHARACTER SET \"UTF-8\")))])",
+ "\n LogicalFilter(condition=[AND(>($2, 42), SEARCH($0,
Sarg[_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\",
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\", _UTF-8'yoda':VARCHAR(9)
CHARACTER SET \"UTF-8\"]:VARCHAR(9) CHARACTER SET \"UTF-8\"))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2584,7 +2584,7 @@
"\nLogicalWindow(window#0=[window(partition {0} order by [0] aggs
[RANK(), DENSE_RANK()])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0])",
- "\n LogicalFilter(condition=[AND(>($2, 42), OR(=($0,
_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'yoda':VARCHAR(9) CHARACTER SET \"UTF-8\")))])",
+ "\n LogicalFilter(condition=[AND(>($2, 42), SEARCH($0,
Sarg[_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\",
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\", _UTF-8'yoda':VARCHAR(9)
CHARACTER SET \"UTF-8\"]:VARCHAR(9) CHARACTER SET \"UTF-8\"))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2598,7 +2598,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [0] aggs
[MIN($1), MAX($1)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0], col3=[$2], $2=[REVERSE(CONCAT($0,
_UTF-8' ', $1))])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'baz'), <>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2612,7 +2612,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [0] aggs
[DENSE_RANK(), MAX($1)])])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col1=[$0], col3=[$2], $2=[REVERSE(CONCAT($0,
_UTF-8' ', $1))])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'baz'), <>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2928,7 +2928,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [1] aggs
[SUM($2), COUNT($2)])])",
"\n PinotLogicalSortExchange(distribution=[hash[0]],
collation=[[1]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1], col3=[$2])",
- "\n LogicalFilter(condition=[AND(>($2, 10), <=($2, 500))])",
+ "\n LogicalFilter(condition=[SEARCH($2, Sarg[(10..500]])])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2942,7 +2942,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [1] aggs
[RANK()])])",
"\n PinotLogicalSortExchange(distribution=[hash[0]],
collation=[[1]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1])",
- "\n LogicalFilter(condition=[AND(>($2, 10), <=($2, 500))])",
+ "\n LogicalFilter(condition=[SEARCH($2, Sarg[(10..500]])])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2956,7 +2956,7 @@
"\n LogicalWindow(window#0=[window(partition {1} order by [0] aggs
[SUM($2), COUNT($2)])])",
"\n PinotLogicalSortExchange(distribution=[hash[1]],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1], col3=[$2],
$3=[CONCAT($0, _UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2970,7 +2970,7 @@
"\n LogicalWindow(window#0=[window(partition {1} order by [0] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])",
"\n PinotLogicalSortExchange(distribution=[hash[1]],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1], $2=[CONCAT($0,
_UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -2984,7 +2984,7 @@
"\n LogicalWindow(window#0=[window(partition {1} order by [0] aggs
[RANK()])])",
"\n PinotLogicalSortExchange(distribution=[hash[1]],
collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1], $2=[CONCAT($0,
_UTF-8'-', $1)])",
- "\n LogicalFilter(condition=[OR(AND(<>($0, _UTF-8'bar'),
<>($0, _UTF-8'foo')), >=($2, 42))])",
+ "\n LogicalFilter(condition=[OR(SEARCH($0,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'foo'), (_UTF-8'foo'..+∞)]:CHAR(3)
CHARACTER SET \"UTF-8\"), >=($2, 42))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -3233,7 +3233,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [1] aggs
[SUM($2), COUNT($2), COUNT($0)])])",
"\n PinotLogicalSortExchange(distribution=[hash[0]],
collation=[[1]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1], col3=[$2])",
- "\n LogicalFilter(condition=[AND(>($2, 42), OR(=($0,
_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'yoda':VARCHAR(9) CHARACTER SET \"UTF-8\")))])",
+ "\n LogicalFilter(condition=[AND(>($2, 42), SEARCH($0,
Sarg[_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\",
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\", _UTF-8'yoda':VARCHAR(9)
CHARACTER SET \"UTF-8\"]:VARCHAR(9) CHARACTER SET \"UTF-8\"))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -3247,7 +3247,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [1] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])",
"\n PinotLogicalSortExchange(distribution=[hash[0]],
collation=[[1]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1])",
- "\n LogicalFilter(condition=[AND(>($2, 42), OR(=($0,
_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'yoda':VARCHAR(9) CHARACTER SET \"UTF-8\")))])",
+ "\n LogicalFilter(condition=[AND(>($2, 42), SEARCH($0,
Sarg[_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\",
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\", _UTF-8'yoda':VARCHAR(9)
CHARACTER SET \"UTF-8\"]:VARCHAR(9) CHARACTER SET \"UTF-8\"))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -3261,7 +3261,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [1] aggs
[RANK(), DENSE_RANK()])])",
"\n PinotLogicalSortExchange(distribution=[hash[0]],
collation=[[1]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1])",
- "\n LogicalFilter(condition=[AND(>($2, 42), OR(=($0,
_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\"), =($0,
_UTF-8'yoda':VARCHAR(9) CHARACTER SET \"UTF-8\")))])",
+ "\n LogicalFilter(condition=[AND(>($2, 42), SEARCH($0,
Sarg[_UTF-8'chewbacca':VARCHAR(9) CHARACTER SET \"UTF-8\",
_UTF-8'vader':VARCHAR(9) CHARACTER SET \"UTF-8\", _UTF-8'yoda':VARCHAR(9)
CHARACTER SET \"UTF-8\"]:VARCHAR(9) CHARACTER SET \"UTF-8\"))])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -3275,7 +3275,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [1] aggs
[MIN($2), MAX($2)])])",
"\n PinotLogicalSortExchange(distribution=[hash[0]],
collation=[[1]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1], col3=[$2],
$3=[REVERSE(CONCAT($0, _UTF-8' ', $1))])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'baz'), <>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -3289,7 +3289,7 @@
"\n LogicalWindow(window#0=[window(partition {0} order by [1] aggs
[DENSE_RANK(), RANK()])])",
"\n PinotLogicalSortExchange(distribution=[hash[0]],
collation=[[1]], isSortOnSender=[false], isSortOnReceiver=[true])",
"\n LogicalProject(col1=[$0], col2=[$1], $2=[REVERSE(CONCAT($0,
_UTF-8' ', $1))])",
- "\n LogicalFilter(condition=[AND(<>($1, _UTF-8'bar'), <>($1,
_UTF-8'baz'), <>($1, _UTF-8'foo'))])",
+ "\n LogicalFilter(condition=[SEARCH($1,
Sarg[(-∞.._UTF-8'bar'), (_UTF-8'bar'.._UTF-8'baz'), (_UTF-8'baz'.._UTF-8'foo'),
(_UTF-8'foo'..+∞)]:CHAR(3) CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, a]])",
"\n"
]
@@ -3473,7 +3473,7 @@
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1])",
- "\n LogicalFilter(condition=[OR(=($0, _UTF-8'brandon
sanderson':VARCHAR(17) CHARACTER SET \"UTF-8\"), =($0, _UTF-8'douglas
adams':VARCHAR(17) CHARACTER SET \"UTF-8\"))])",
+ "\n LogicalFilter(condition=[SEARCH($0,
Sarg[_UTF-8'brandon sanderson':VARCHAR(17) CHARACTER SET \"UTF-8\",
_UTF-8'douglas adams':VARCHAR(17) CHARACTER SET \"UTF-8\"]:VARCHAR(17)
CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, b]])",
"\n"
]
@@ -3495,7 +3495,7 @@
"\n LogicalTableScan(table=[[default, a]])",
"\n PinotLogicalExchange(distribution=[hash[0]])",
"\n LogicalProject(col2=[$1])",
- "\n LogicalFilter(condition=[OR(=($0, _UTF-8'brandon
sanderson':VARCHAR(17) CHARACTER SET \"UTF-8\"), =($0, _UTF-8'douglas
adams':VARCHAR(17) CHARACTER SET \"UTF-8\"))])",
+ "\n LogicalFilter(condition=[SEARCH($0,
Sarg[_UTF-8'brandon sanderson':VARCHAR(17) CHARACTER SET \"UTF-8\",
_UTF-8'douglas adams':VARCHAR(17) CHARACTER SET \"UTF-8\"]:VARCHAR(17)
CHARACTER SET \"UTF-8\")])",
"\n LogicalTableScan(table=[[default, b]])",
"\n"
]
diff --git
a/pinot-query-runtime/src/test/resources/queries/LiteralExpressions.json
b/pinot-query-runtime/src/test/resources/queries/LiteralExpressions.json
new file mode 100644
index 0000000000..77992571a5
--- /dev/null
+++ b/pinot-query-runtime/src/test/resources/queries/LiteralExpressions.json
@@ -0,0 +1,33 @@
+{
+ "literal_expressions_test": {
+ "tables": {
+ "mytable": {
+ "schema": [
+ {"name": "intCol", "type": "INT"},
+ {"name": "strCol", "type": "STRING"}
+ ],
+ "inputs": [
+ [1, "foo"],
+ [2, "bar"]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "CTE with literal selection and filter",
+ "sql": "WITH CTE_1 AS (SELECT 'a' AS val FROM {mytable} GROUP BY val)
SELECT 1 FROM CTE_1 WHERE val >= 'a' AND val < 'b'",
+ "outputs": [
+ [1]
+ ]
+ },
+ {
+ "description": "Literal SEARCH filter on subquery with CASE WHEN",
+ "sql": "SELECT * FROM (SELECT CASE WHEN strCol = 'foo' THEN 'a' ELSE
'b' END AS val FROM {mytable}) WHERE val IN ('a', 'b')",
+ "outputs": [
+ ["a"],
+ ["b"]
+ ]
+ }
+ ]
+ }
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
