This is an automated email from the ASF dual-hosted git repository.
yashmayya 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 30233a1c6dd Automatically rewrite MIN / MAX on string col to MINSTRING
/ MAXSTRING (#16980)
30233a1c6dd is described below
commit 30233a1c6dd8edcd781652c47c53fe6dc7f979f9
Author: Yash Mayya <[email protected]>
AuthorDate: Fri Oct 10 14:20:04 2025 -0700
Automatically rewrite MIN / MAX on string col to MINSTRING / MAXSTRING
(#16980)
---
.../pinot/core/query/optimizer/QueryOptimizer.java | 7 +-
.../AggregateFunctionRewriteOptimizer.java | 98 ++++++++++++++++++
.../tests/BaseClusterIntegrationTestSet.java | 4 +
.../tests/ErrorCodesIntegrationTest.java | 8 --
.../tests/OfflineClusterIntegrationTest.java | 1 +
.../rules/PinotAggregateFunctionRewriteRule.java | 114 +++++++++++++++++++++
.../calcite/rel/rules/PinotQueryRuleSets.java | 3 +
.../src/test/resources/queries/AggregatePlans.json | 48 +++++++++
.../src/test/resources/queries/Aggregates.json | 31 +++---
.../apache/pinot/spi/utils/CommonConstants.java | 1 +
10 files changed, 284 insertions(+), 31 deletions(-)
diff --git
a/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/QueryOptimizer.java
b/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/QueryOptimizer.java
index a465249ec56..70151b7fec8 100644
---
a/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/QueryOptimizer.java
+++
b/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/QueryOptimizer.java
@@ -18,8 +18,6 @@
*/
package org.apache.pinot.core.query.optimizer;
-import java.util.Arrays;
-import java.util.Collections;
import java.util.List;
import javax.annotation.Nullable;
import org.apache.pinot.common.request.Expression;
@@ -32,6 +30,7 @@ import
org.apache.pinot.core.query.optimizer.filter.MergeRangeFilterOptimizer;
import org.apache.pinot.core.query.optimizer.filter.NumericalFilterOptimizer;
import org.apache.pinot.core.query.optimizer.filter.TextMatchFilterOptimizer;
import
org.apache.pinot.core.query.optimizer.filter.TimePredicateFilterOptimizer;
+import
org.apache.pinot.core.query.optimizer.statement.AggregateFunctionRewriteOptimizer;
import org.apache.pinot.core.query.optimizer.statement.StatementOptimizer;
import
org.apache.pinot.core.query.optimizer.statement.StringPredicateFilterOptimizer;
import org.apache.pinot.spi.data.Schema;
@@ -45,12 +44,12 @@ public class QueryOptimizer {
// - TimePredicateFilterOptimizer and MergeRangeFilterOptimizer relies on
NumericalFilterOptimizer to convert the
// values to the proper format so that they can be properly parsed
private static final List<FilterOptimizer> FILTER_OPTIMIZERS =
- Arrays.asList(new FlattenAndOrFilterOptimizer(), new
IdenticalPredicateFilterOptimizer(),
+ List.of(new FlattenAndOrFilterOptimizer(), new
IdenticalPredicateFilterOptimizer(),
new MergeEqInFilterOptimizer(), new NumericalFilterOptimizer(), new
TimePredicateFilterOptimizer(),
new MergeRangeFilterOptimizer(), new TextMatchFilterOptimizer());
private static final List<StatementOptimizer> STATEMENT_OPTIMIZERS =
- Collections.singletonList(new StringPredicateFilterOptimizer());
+ List.of(new StringPredicateFilterOptimizer(), new
AggregateFunctionRewriteOptimizer());
/**
* Optimizes the given query.
diff --git
a/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizer.java
b/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizer.java
new file mode 100644
index 00000000000..e023c820135
--- /dev/null
+++
b/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizer.java
@@ -0,0 +1,98 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.pinot.core.query.optimizer.statement;
+
+import java.util.List;
+import javax.annotation.Nullable;
+import org.apache.pinot.common.request.Expression;
+import org.apache.pinot.common.request.Function;
+import org.apache.pinot.common.request.PinotQuery;
+import org.apache.pinot.segment.spi.AggregationFunctionType;
+import org.apache.pinot.spi.data.FieldSpec;
+import org.apache.pinot.spi.data.Schema;
+
+/**
+ * Rewrites aggregate functions to type-specific versions in order to support
polymorphic functions.
+ */
+public class AggregateFunctionRewriteOptimizer implements StatementOptimizer {
+
+ @Override
+ public void optimize(PinotQuery pinotQuery, @Nullable Schema schema) {
+ if (schema == null) {
+ return;
+ }
+
+ List<Expression> selectList = pinotQuery.getSelectList();
+ if (selectList != null) {
+ for (Expression expression : selectList) {
+ maybeRewriteAggregateFunction(expression, schema);
+ }
+ }
+
+ List<Expression> groupByList = pinotQuery.getGroupByList();
+ if (groupByList != null) {
+ for (Expression expression : groupByList) {
+ maybeRewriteAggregateFunction(expression, schema);
+ }
+ }
+
+ List<Expression> orderByList = pinotQuery.getOrderByList();
+ if (orderByList != null) {
+ for (Expression expression : orderByList) {
+ maybeRewriteAggregateFunction(expression, schema);
+ }
+ }
+
+ maybeRewriteAggregateFunction(pinotQuery.getFilterExpression(), schema);
+ maybeRewriteAggregateFunction(pinotQuery.getHavingExpression(), schema);
+ }
+
+ private void maybeRewriteAggregateFunction(@Nullable Expression expression,
Schema schema) {
+ if (expression == null || !expression.isSetFunctionCall()) {
+ return;
+ }
+
+ Function function = expression.getFunctionCall();
+ String functionName = function.getOperator();
+ if (!AggregationFunctionType.isAggregationFunction(functionName)) {
+ return;
+ }
+
+ // Rewrite MIN(stringCol) and MAX(stringCol) to MINSTRING / MAXSTRING
+ if ((functionName.equals(AggregationFunctionType.MIN.getName())
+ || functionName.equals(AggregationFunctionType.MAX.getName()))
+ && function.getOperandsSize() == 1) {
+ Expression operand = function.getOperands().get(0);
+ // TODO: Handle more complex expressions (e.g. MIN(trim(stringCol)) )
+ if (operand.isSetIdentifier()) {
+ String columnName = operand.getIdentifier().getName();
+ if (schema != null) {
+ FieldSpec fieldSpec = schema.getFieldSpecFor(columnName);
+ if (fieldSpec != null && fieldSpec.getDataType().getStoredType() ==
FieldSpec.DataType.STRING) {
+ String newFunctionName =
+ functionName.equals(AggregationFunctionType.MIN.getName())
+ ? AggregationFunctionType.MINSTRING.name().toLowerCase()
+ : AggregationFunctionType.MAXSTRING.name().toLowerCase();
+ function.setOperator(newFunctionName);
+ }
+ }
+ }
+ }
+ }
+}
diff --git
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/BaseClusterIntegrationTestSet.java
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/BaseClusterIntegrationTestSet.java
index 4de86eb3c6c..0a774238f83 100644
---
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/BaseClusterIntegrationTestSet.java
+++
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/BaseClusterIntegrationTestSet.java
@@ -326,6 +326,10 @@ public abstract class BaseClusterIntegrationTestSet
extends BaseClusterIntegrati
+ "'DL' ORDER BY ArrTime DESC";
testQuery(query, h2Query);
+ // Test MIN / MAX on STRING columns (automatically rewritten to MINSTRING
/ MAXSTRING internally)
+ query = "SELECT MIN(OriginCityName), MAX(OriginCityName) FROM mytable";
+ testQuery(query);
+
// Test orderedPreferredPools option which will fallbacks to non preferred
Pools
// when non of preferred Pools is available
query = "SELECT count(*) FROM mytable WHERE OriginState LIKE 'A_'
option(orderedPreferredPools=0|1)";
diff --git
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/ErrorCodesIntegrationTest.java
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/ErrorCodesIntegrationTest.java
index 069e3fa382d..1fdc45330b2 100644
---
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/ErrorCodesIntegrationTest.java
+++
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/ErrorCodesIntegrationTest.java
@@ -141,14 +141,6 @@ public abstract class ErrorCodesIntegrationTest extends
BaseClusterIntegrationTe
useMultiStageQueryEngine() ? QueryErrorCode.QUERY_PLANNING :
QueryErrorCode.QUERY_VALIDATION);
}
- @Test
- public void testInvalidAggregationArg()
- throws Exception {
- // Cannot use numeric aggregate function for string column
- testQueryException("SELECT MAX(OriginState) FROM mytable where ArrTime >
5",
- QueryErrorCode.QUERY_VALIDATION);
- }
-
private void testQueryException(@Language("sql") String query,
QueryErrorCode errorCode)
throws Exception {
QueryAssert queryAssert;
diff --git
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/OfflineClusterIntegrationTest.java
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/OfflineClusterIntegrationTest.java
index 10331c51cc8..0018a5ea10b 100644
---
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/OfflineClusterIntegrationTest.java
+++
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/OfflineClusterIntegrationTest.java
@@ -3510,6 +3510,7 @@ public class OfflineClusterIntegrationTest extends
BaseClusterIntegrationTestSet
assertEquals(response1Json.get("rows").get(0).get(2).asText(), "Rule
Execution Times\n"
+ "Rule: SortRemove -> Time:*\n"
+ "Rule: AggregateProjectMerge -> Time:*\n"
+ + "Rule: AggregateFunctionRewrite -> Time:*\n"
+ "Rule: EvaluateProjectLiteral -> Time:*\n"
+ "Rule: AggregateRemove -> Time:*\n");
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateFunctionRewriteRule.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateFunctionRewriteRule.java
new file mode 100644
index 00000000000..9b68104daa9
--- /dev/null
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateFunctionRewriteRule.java
@@ -0,0 +1,114 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.pinot.calcite.rel.rules;
+
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Aggregate;
+import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.logical.LogicalAggregate;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeField;
+import org.apache.calcite.sql.SqlAggFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.tools.RelBuilderFactory;
+import org.apache.pinot.common.function.sql.PinotSqlAggFunction;
+
+
+/**
+ * Rewrites certain aggregation functions based on operand types to support
polymorphic aggregations.
+ *
+ * Currently supported rewrites:
+ * - MIN(STRING) -> MINSTRING
+ * - MAX(STRING) -> MAXSTRING
+ */
+public class PinotAggregateFunctionRewriteRule extends RelOptRule {
+ public static final PinotAggregateFunctionRewriteRule INSTANCE =
+ new PinotAggregateFunctionRewriteRule(PinotRuleUtils.PINOT_REL_FACTORY,
null);
+
+ public static PinotAggregateFunctionRewriteRule
instanceWithDescription(String description) {
+ return new
PinotAggregateFunctionRewriteRule(PinotRuleUtils.PINOT_REL_FACTORY,
description);
+ }
+
+ private PinotAggregateFunctionRewriteRule(RelBuilderFactory factory, String
description) {
+ super(operand(LogicalAggregate.class, any()), factory, description);
+ }
+
+ @Override
+ public void onMatch(RelOptRuleCall call) {
+ Aggregate aggRel = call.rel(0);
+ RelNode input = aggRel.getInput();
+ List<AggregateCall> originalCalls = aggRel.getAggCallList();
+
+ boolean changed = false;
+ List<AggregateCall> rewrittenCalls = new ArrayList<>(originalCalls.size());
+ for (AggregateCall aggCall : originalCalls) {
+ AggregateCall newCall = maybeRewriteMinMaxOnString(aggCall, input,
aggRel.getGroupCount());
+ if (newCall != aggCall) {
+ changed = true;
+ }
+ rewrittenCalls.add(newCall);
+ }
+
+ if (!changed) {
+ return;
+ }
+
+ call.transformTo(aggRel.copy(aggRel.getTraitSet(), input,
aggRel.getGroupSet(), aggRel.getGroupSets(),
+ rewrittenCalls));
+ }
+
+ /**
+ * If the call is MIN or MAX over a STRING input, rewrite it to
MINSTRING/MAXSTRING.
+ */
+ private static AggregateCall maybeRewriteMinMaxOnString(AggregateCall call,
RelNode input, int numGroups) {
+ SqlAggFunction aggFunction = call.getAggregation();
+ SqlKind kind = aggFunction.getKind();
+ if (kind != SqlKind.MIN && kind != SqlKind.MAX) {
+ return call;
+ }
+
+ List<Integer> argList = call.getArgList();
+ if (argList.isEmpty()) {
+ return call;
+ }
+
+ int argIndex = argList.get(0);
+ RelDataTypeField field = input.getRowType().getFieldList().get(argIndex);
+ RelDataType fieldType = field.getType();
+ SqlTypeName sqlTypeName = fieldType.getSqlTypeName();
+ if (!SqlTypeName.STRING_TYPES.contains(sqlTypeName)) {
+ return call;
+ }
+
+ String targetName = (kind == SqlKind.MIN) ? "MINSTRING" : "MAXSTRING";
+ SqlAggFunction stringAgg =
+ new PinotSqlAggFunction(targetName, SqlKind.OTHER_FUNCTION,
ReturnTypes.explicit(call.getType()),
+ aggFunction.getOperandTypeChecker(),
SqlFunctionCategory.USER_DEFINED_FUNCTION);
+
+ return AggregateCall.create(stringAgg, call.isDistinct(),
call.isApproximate(), call.ignoreNulls(), argList,
+ call.filterArg, call.distinctKeys, call.getCollation(), numGroups,
input, call.getType(), call.getName());
+ }
+}
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 b9d2604b83e..f64b39404c6 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
@@ -147,6 +147,9 @@ public class PinotQueryRuleSets {
PinotAggregateReduceFunctionsRule
.instanceWithDescription(PlannerRuleNames.AGGREGATE_REDUCE_FUNCTIONS),
+ PinotAggregateFunctionRewriteRule
+
.instanceWithDescription(PlannerRuleNames.AGGREGATE_FUNCTION_REWRITE),
+
// convert CASE-style filtered aggregates into true filtered aggregates
// put it after AGGREGATE_REDUCE_FUNCTIONS where SUM is converted to SUM0
AggregateCaseToFilterRule.Config.DEFAULT
diff --git a/pinot-query-planner/src/test/resources/queries/AggregatePlans.json
b/pinot-query-planner/src/test/resources/queries/AggregatePlans.json
index 1fd3b3cd334..8dbcb446242 100644
--- a/pinot-query-planner/src/test/resources/queries/AggregatePlans.json
+++ b/pinot-query-planner/src/test/resources/queries/AggregatePlans.json
@@ -157,6 +157,54 @@
"\n PinotLogicalTableScan(table=[[default, a]])",
"\n"
]
+ },
+ {
+ "description": "MIN on STRING column rewritten to MINSTRING",
+ "sql": "EXPLAIN PLAN FOR SELECT MIN(a.col1) FROM a",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[MINSTRING($0)],
aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[MINSTRING($0)],
aggType=[LEAF])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "MIN on INT column not rewritten",
+ "sql": "EXPLAIN PLAN FOR SELECT MIN(a.col3) FROM a",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[MIN($0)],
aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[MIN($2)],
aggType=[LEAF])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "MAX on STRING column rewritten to MAXSTRING",
+ "sql": "EXPLAIN PLAN FOR SELECT MAX(a.col2) FROM a",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[MAXSTRING($0)],
aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[MAXSTRING($1)],
aggType=[LEAF])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "MAX on INT column not rewritten",
+ "sql": "EXPLAIN PLAN FOR SELECT MAX(a.col3) FROM a",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[MAX($0)],
aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[MAX($2)],
aggType=[LEAF])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
}
]
}
diff --git a/pinot-query-runtime/src/test/resources/queries/Aggregates.json
b/pinot-query-runtime/src/test/resources/queries/Aggregates.json
index c12b7a8b94d..23b2f2f8182 100644
--- a/pinot-query-runtime/src/test/resources/queries/Aggregates.json
+++ b/pinot-query-runtime/src/test/resources/queries/Aggregates.json
@@ -44,7 +44,7 @@
{
"psql": "4.2.7",
"description": "aggregations on string column",
- "sql": "SELECT count(string_col), count(distinct(string_col)),
count(*) FROM {tbl}"
+ "sql": "SELECT min(string_col), max(string_col), count(string_col),
count(distinct(string_col)), count(*) FROM {tbl}"
},
{
"psql": "4.2.7",
@@ -153,17 +153,17 @@
{
"psql": "4.2.7",
"description": "aggregations on string column with filters",
- "sql": "SELECT count(string_col), count(distinct(string_col)),
count(*) FROM {tbl} WHERE string_col='b'"
+ "sql": "SELECT count(string_col), count(distinct(string_col)),
count(*), min(string_col), max(string_col) FROM {tbl} WHERE string_col='b'"
},
{
"psql": "4.2.7",
"description": "aggregations on string column with filters",
- "sql": "SELECT count(string_col), count(distinct(string_col)),
count(*) FROM {tbl} WHERE int_col > 100"
+ "sql": "SELECT count(string_col), count(distinct(string_col)),
count(*), min(string_col), max(string_col) FROM {tbl} WHERE int_col > 100"
},
{
"psql": "4.2.7",
"description": "aggregations on string column with filters",
- "sql": "SELECT count(string_col), count(distinct(string_col)),
count(*) FROM {tbl} WHERE int_col > 100 and double_col = 1.75"
+ "sql": "SELECT count(string_col), count(distinct(string_col)),
count(*), min(string_col), max(string_col) FROM {tbl} WHERE int_col > 100 and
double_col = 1.75"
}
]
},
@@ -226,12 +226,12 @@
{
"psql": "4.2.7",
"description": "group by with aggregations on string column",
- "sql": "SELECT bool_col, count(string_col),
count(distinct(string_col)), count(*) FROM {tbl} GROUP BY bool_col"
+ "sql": "SELECT bool_col, count(string_col),
count(distinct(string_col)), count(*), min(string_col), max(string_col) FROM
{tbl} GROUP BY bool_col"
},
{
"psql": "4.2.7",
"description": "group by with aggregations on string column and order
by",
- "sql": "SELECT bool_col, count(string_col),
count(distinct(string_col)), count(*) FROM {tbl} GROUP BY bool_col ORDER BY
bool_col"
+ "sql": "SELECT bool_col, count(string_col),
count(distinct(string_col)), count(*), min(string_col), max(string_col) FROM
{tbl} GROUP BY bool_col ORDER BY bool_col"
},
{
"psql": "4.2.7",
@@ -324,17 +324,17 @@
{
"psql": "4.2.7",
"description": "aggregations on string column with filters",
- "sql": "SELECT double_col, count(string_col),
count(distinct(string_col)), count(*) FROM {tbl} WHERE string_col='b' GROUP BY
double_col"
+ "sql": "SELECT double_col, count(string_col),
count(distinct(string_col)), count(*), min(string_col), max(string_col) FROM
{tbl} WHERE string_col='b' GROUP BY double_col"
},
{
"psql": "4.2.7",
"description": "aggregations on string column with filters",
- "sql": "SELECT double_col, int_col, count(string_col),
count(distinct(string_col)), count(*) FROM {tbl} WHERE int_col > 100 GROUP BY
double_col, int_col"
+ "sql": "SELECT double_col, int_col, count(string_col),
count(distinct(string_col)), count(*), min(string_col), max(string_col) FROM
{tbl} WHERE int_col > 100 GROUP BY double_col, int_col"
},
{
"psql": "4.2.7",
"description": "aggregations on string column with filters",
- "sql": "SELECT double_col, int_col, bool_col, count(string_col),
count(distinct(string_col)), count(*) FROM {tbl} WHERE int_col > 100 and
double_col = 1.75 GROUP BY double_col, int_col, bool_col"
+ "sql": "SELECT double_col, int_col, bool_col, count(string_col),
count(distinct(string_col)), count(*), min(string_col), max(string_col) FROM
{tbl} WHERE int_col > 100 and double_col = 1.75 GROUP BY double_col, int_col,
bool_col"
}
]
},
@@ -438,7 +438,7 @@
{
"psql": "9.21.0",
"description": "JOIN with simple aggregations on string columns",
- "sql": "SELECT count(string_col), count(string_col2), count(distinct
string_col), count(distinct string_col2) from {tbl1} JOIN {tbl2} ON int_col =
int_col2"
+ "sql": "SELECT count(string_col), count(string_col2), count(distinct
string_col), count(distinct string_col2), min(string_col), max(string_col) from
{tbl1} JOIN {tbl2} ON int_col = int_col2"
},
{
"psql": "9.21.0",
@@ -463,12 +463,12 @@
{
"psql": "9.21.0",
"description": "JOIN with aggregations on string columns and group by
on double",
- "sql": "SELECT double_col, count(string_col), count(string_col2),
count(distinct string_col), count(distinct string_col2) from {tbl1} JOIN {tbl2}
ON int_col = int_col2 GROUP BY double_col"
+ "sql": "SELECT double_col, count(string_col), count(string_col2),
count(distinct string_col), count(distinct string_col2), min(string_col2),
max(string_col2) from {tbl1} JOIN {tbl2} ON int_col = int_col2 GROUP BY
double_col"
},
{
"psql": "9.21.0",
"description": "JOIN with aggregations on string columns and group by
on double, bool and int",
- "sql": "SELECT double_col, bool_col, int_col, count(string_col),
count(string_col2), count(distinct string_col), count(distinct string_col2)
from {tbl1} JOIN {tbl2} ON int_col = int_col2 GROUP BY double_col, bool_col,
int_col"
+ "sql": "SELECT double_col, bool_col, int_col, count(string_col),
count(string_col2), count(distinct string_col), count(distinct string_col2),
min(string_col2), max(string_col2) from {tbl1} JOIN {tbl2} ON int_col =
int_col2 GROUP BY double_col, bool_col, int_col"
},
{
"psql": "9.21.0",
@@ -659,13 +659,6 @@
"description": "aggregate double column followed with order by",
"sql": "SELECT min(double_col ORDER BY int_col) FROM {tbl}"
},
- {
- "psql": "4.2.7",
- "ignored": true,
- "comments": "NumberFormatException: For input string: \"a\"",
- "description": "aggregate string column",
- "sql": "SELECT min(string_col) FROM {tbl}"
- },
{
"psql": "4.2.7",
"description": "aggregate boolean column",
diff --git
a/pinot-spi/src/main/java/org/apache/pinot/spi/utils/CommonConstants.java
b/pinot-spi/src/main/java/org/apache/pinot/spi/utils/CommonConstants.java
index 820bf3a1052..00102c90a28 100644
--- a/pinot-spi/src/main/java/org/apache/pinot/spi/utils/CommonConstants.java
+++ b/pinot-spi/src/main/java/org/apache/pinot/spi/utils/CommonConstants.java
@@ -856,6 +856,7 @@ public class CommonConstants {
public static final String AGGREGATE_JOIN_TRANSPOSE =
"AggregateJoinTranspose";
public static final String AGGREGATE_UNION_AGGREGATE =
"AggregateUnionAggregate";
public static final String AGGREGATE_REDUCE_FUNCTIONS =
"AggregateReduceFunctions";
+ public static final String AGGREGATE_FUNCTION_REWRITE =
"AggregateFunctionRewrite";
public static final String AGGREGATE_CASE_TO_FILTER =
"AggregateCaseToFilter";
public static final String PROJECT_FILTER_TRANSPOSE =
"ProjectFilterTranspose";
public static final String PROJECT_MERGE = "ProjectMerge";
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
