KazukiKandaKK opened a new issue, #19603:
URL: https://github.com/apache/pinot/issues/19603
**Description**
Running standard SQL `MIN(column)` / `MAX(column)` on a STRING-typed column
(e.g. a `dateTimeFieldSpec` with `SIMPLE_DATE_FORMAT`) fails with a
`NumberFormatException`, even though the column is correctly declared per the
official docs.
```
NonScanBasedAggregationOperator: For input string: "2013-07-01"
```
Example:
```sql
SELECT MIN(EventDate), MAX(EventDate) FROM hits
```
**Steps to reproduce**
1. Pinot 1.5.1 (official Docker image `apachepinot/pinot:1.5.1`)
2. Create a minimal table with one STRING column (`yyyy-MM-dd` format)
3. Run `SELECT MIN(col), MAX(col) FROM table`
4. Fails within seconds with `NumberFormatException` (reproduced locally on
Docker)
**Root cause**
`AggregationFunctionType` (in `pinot-segment-spi`) contains:
```java
// TODO: min/max only supports NUMERIC in Pinot, where Calcite supports
COMPARABLE_ORDERED
MIN("min", SqlTypeName.DOUBLE, SqlTypeName.DOUBLE),
MAX("max", SqlTypeName.DOUBLE, SqlTypeName.DOUBLE),
MINSTRING("minString", SqlTypeName.VARCHAR, SqlTypeName.VARCHAR),
MAXSTRING("maxString", SqlTypeName.VARCHAR, SqlTypeName.VARCHAR),
```
Standard `MIN`/`MAX` always compiles to the numeric-only function regardless
of column type — a limitation the Pinot team has already flagged with a TODO.
`NonScanBasedAggregationOperator`'s fast aggregation path unconditionally calls
`toDouble()` on the dictionary/metadata value, which throws when the value is a
non-numeric string.
**Existing but under-documented workaround**
PR #16980 (merged 2025-10-10) added an `AggregateFunctionRewriteOptimizer`
that rewrites `MIN`/`MAX` on STRING columns to `MINSTRING`/`MAXSTRING`; at that
point the rewrite was unconditional. A later PR #17058 (merged 2025-10-30,
alongside LONG-type support) gated this behind a new
`autoRewriteAggregationType` query option, defaulting to `false`.
```sql
SELECT MIN(EventDate), MAX(EventDate) FROM hits
OPTION(autoRewriteAggregationType=true)
```
Verified locally (Docker, Pinot 1.5.1, minimal 3-row STRING table):
- Without the option: fails with `NumberFormatException: For input string:
"2013-07-01"`
- With `OPTION(autoRewriteAggregationType=true)`: returns correct values via
`minstring`/`maxstring`
The official "Query Options" page
(docs.pinot.apache.org/build-with-pinot/querying-and-sql/query-execution-controls/query-options)
does not mention `autoRewriteAggregationType`, `MINSTRING`, or `MAXSTRING`.
The error message itself gives no hint that they exist.
Related but distinct: #19145 ("Metadata/dictionary based aggregation ignores
column-type preconditions for MINLONG/MAXLONG and MINSTRING/MAXSTRING") — a
type-checking gap, not the same issue as this one.
**Suggestion**
1. Improve the error message to point users to `MINSTRING`/`MAXSTRING` or
`autoRewriteAggregationType=true` when `MIN`/`MAX` is called on a non-numeric
column
2. Document this behavior and the workaround on the `MIN`/`MAX` reference
page
3. (Optional, larger change) Consider defaulting
`autoRewriteAggregationType` to `true` in a future release
Options 1 and 2 introduce no breaking changes. Option 3 would need separate
discussion.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
