SQL `max()`, `min()` fail for `VARCHAR` (`string`) columns
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 1d 19h
- Merged PRs (30d)
- 209
Description
### Affected Version
Version: 0.21.1
### Description
The SQL functions `min()` and `max()` are typically defined to operate over any type with an ordering. However, Druid fails when they are used with a `VARCHAR` (Druid `string`) column. Example:
```sql
SELECT
min("start"), max("end")
FROM sys.segments
```
Results:
```text
Error: Unknown exception
java.lang.reflect.InvocationTargetException
java.lang.RuntimeException
```
But, the following does work:
```sql
SELECT
min("size"), max("size")
FROM sys.segments
```
When run against the sample Wikipedia data, we get a more detailed error message:
```sql
SELECT min("user")
FROM "wikipedia"
```
```text
Error: Unknown exception
Error while applying rule DruidQueryRule(AGGREGATE), args [rel#1235:LogicalAggregate.NONE.[]
(input=RelSubset#1230,group={},EXPR$0=MIN($19)), rel#1244:DruidQueryRel.NONE.[](query=
{"queryType":"scan","dataSource":{"type":"table","name":"wikiticker-2015-09-12-sampled"},"intervals":
{"type":"intervals","intervals":["2021-09-03T23:04:08.000Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":
[],"resultFormat":"compactedList","batchSize":20480,"order":"none","filter":null,"columns":
["__time","added","channel","cityName","comment","countryIsoCode","countryName","deleted","delta","
isAnonymous","isMinor","isNew","isRobot","isUnpatrolled","metroCode","namespace","page","
regionIsoCode","regionName","user"],"legacy":false,"context":{"sqlOuterLimit":100,
"sqlQueryId":"531a8bef-ce4b-45a7-96f4-c153d98a0951"},"descending":false,"
granularity":{"type":"all"}},signature={__time:LONG, added:LONG, channel:STRING,
cityName:STRING, comment:STRING, countryIsoCode:STRING, countryName:STRING,
deleted:LONG, delta:LONG, isAnonymous:STRING, isMinor:STRING, isNew:STRING,
isRobot:STRING, isUnpatrolled:STRING, metroCode:STRING, namespace:STRING,
page:STRING, regionIsoCode:STRING, regionName:STRING, user:STRING})]
java.lang.RuntimeException
```
### Workaround
Tried converting the `start` value to a `TIMESTAMP` with `TIME_PARSE()`, but that [failed](https://github.com/apache/druid/issues/11660).
We can, however, issue a separate query for each value, and use `ORDER BY` to do the ordering. For the minimum:
```sql
SELECT "start"
FROM sys.segments
ORDER BY "start" ASC
LIMIT 1
```
This approach does not scale if we need to work with many columns, or if we need the answer from a single query.
Contributor guide
Research direction
No source file or test is named. Start by reproducing the min() and max() queries against sys.segments and the sample Wikipedia data, comparing VARCHAR and numeric columns; done means both functions work for string columns without regressing numeric aggregation.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100