MIN/MAX on STRING columns fail with NumberFormatException instead of guiding users to MINSTRING/MAXSTRING or autoRewriteAggregationType #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:

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:

// 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.

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.