FrankChen021 opened a new issue, #20785:
URL: https://github.com/apache/druid/issues/20785
### Motivation
AI agents and users increasingly analyze cluster state through SQL over
system tables, for example:
```sql
SELECT datasource, MIN("start"), MAX("end") FROM sys.segments GROUP BY
datasource
```
`MIN`/`MAX` over string columns is standard SQL and widely supported:
| Database | `MIN`/`MAX` on strings | How strings are ordered |
|---|---|---|
| PostgreSQL | Yes | By the column's collation (locale-aware, e.g. `en_US`
vs `C`) |
| MySQL | Yes | By collation (the default `utf8mb4_0900_ai_ci` ignores case
and accents) |
| SQL Server / Oracle | Yes | By collation (Oracle: `NLS_SORT`) |
| Snowflake | Yes | Binary by default; collation-aware if the column has a
collation |
| BigQuery | Yes | By code point |
| Trino / Presto | Yes | Byte order of the UTF-8 value |
| Spark SQL | Yes | Binary (UTF-8 bytes) |
| ClickHouse | Yes (also `argMin`/`argMax`) | Byte-wise lexicographic |
| StarRocks / Doris | Yes | Byte-wise |
| **Druid** | **No** | n/a |
This has been requested repeatedly: #6433 (2018), #16331
(`stringMin`/`stringMax` aggregations) and #16956 (`MIN()`/`MAX()` on string
columns). The latter two were closed as stale, not rejected.
In Druid it fails:
- On system tables, as reported in #11659: system tables are executed by
Calcite's interpreter, which only implements `MIN`/`MAX` for numeric and
boolean types, so string columns fail with an unclear
`InvocationTargetException`.
- On datasources, the native engine has no string `MIN`/`MAX` aggregator;
`MIN`/`MAX` are documented as numeric-only.
### Proposal
#20183 adds native-engine execution for system tables. Once system tables go
through the native engine, a native string `MIN`/`MAX` aggregator fixes both
cases at once, without relying on Calcite's interpreter.
Proposed semantics:
- `MIN(expr)` / `MAX(expr)` on `VARCHAR` map to new `stringMin` /
`stringMax` aggregators.
- Nulls are ignored; the result is null only when there are no non-null
values.
- Ordering is UTF-8 byte order, the same order Druid's string dictionaries
use.
- The result is a plain `STRING`, so it can be filtered and combined without
finalization (per the discussion in #16331).
- An optional `maxBytesPerValue` argument, as in `EARLIEST`/`LATEST`.
### Prior work: #17192
#17192 already implements `stringMin`/`stringMax` (heap, buffer and vector
aggregators, plus SQL wiring) but was closed without review. It's a good
starting point. Gaps found while reviewing it:
1. **Vectorization:**
- The vector aggregator decodes each row with `lookupName`, then reads,
decodes and re-encodes the buffered value for every row. This allocates per row
and is slower than the non-vectorized path.
- For dictionary-encoded columns, the sorted dictionary means it can
instead track the min/max **dictionary id** per group (integer comparison) and
look up the string once in `get`.
- Expression inputs are vectorized by passing the expression text as a
column name to `makeSingleValueDimensionSelector`, which won't resolve. They
need an expression-based vector object selector, or `canVectorize` should
return false for them.
2. **Null handling:** the factory extends
`NullableNumericAggregatorFactory`, which applies numeric null semantics. A
dedicated string base class (like the first/last string aggregators) is safer.
3. **Ordering:** it uses `String.compareTo` (UTF-16 order), which differs
from UTF-8 byte order for supplementary characters. It should compare UTF-8
bytes to match dictionary order.
4. **Truncation:**
- The heap aggregator checks `length()` (characters) against
`maxStringBytes` (bytes).
- The buffer and vector paths cut at a byte boundary, which can split a
multi-byte character.
5. **Multi-value strings:** with `aggregateMultipleValues`, rows are
stringified as `"[a, b]"` and compared as a whole. Taking min/max across
individual values is probably more useful.
6. **Ingestion:** not covered. Supporting these at ingestion time (rollup)
needs a string metric column type.
7. **Tests:** no vector-aggregator tests, and no SQL tests
(`CalciteQueryTest`) for `MIN`/`MAX` on `VARCHAR`, nulls, multi-value columns
or expressions.
8. **Rebase:** the PR is from 2024 and conflicts with current master.
--
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]
