apache / apache/druid

SQL `max()`, `min()` fail for `VARCHAR` (`string`) columns

Open
#11,659 8 comments 0 reactions 0 assignees View on GitHub
Area - SQL
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.