# Min() and max() functions return null values even if the field contains data

**URL:** <https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036>\
**Category:** CrateDB\
**Tags:** sql, fundamentals\
**Created:** [June 9, 2025, 8:26am UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036 "2025-06-09T08:26:30Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [June 9, 2025, 8:26am UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036/1 "2025-06-09T08:26:30Z")

</div>

Hi Team, we have a table metrics that store prometheus data in `cratedb 5.10.7`, while both the `min()` and `max()` functions return null values, even if the field `day__generated` itself contains data.

```auto
cr> select version();
+----------------------------------------------------------------------------------------------------+
| pg_catalog.version() |
+----------------------------------------------------------------------------------------------------+
| CrateDB 5.10.7 (built 67ecbaa/NA, Linux 6.8.0-54-generic amd64, OpenJDK 64-Bit Server VM 23.0.2+7) |
+----------------------------------------------------------------------------------------------------+
SELECT 1 row in set (0.002 sec)

cr> select * from metrics where day__generated is null limit 1;
+-----------+-------------+--------+-------+----------+----------------+
| timestamp | labels_hash | labels | value | valueRaw | day__generated |
+-----------+-------------+--------+-------+----------+----------------+
+-----------+-------------+--------+-------+----------+----------------+
SELECT 0 rows in set (0.014 sec)

cr> select min(day__generated),max(day__generated) from metrics;
+---------------------+---------------------+
| min(day__generated) | max(day__generated) |
+---------------------+---------------------+
| NULL | NULL |
+---------------------+---------------------+
SELECT 1 row in set (64.968 sec)

```

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [June 9, 2025, 9:00am UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036/2 "2025-06-09T09:00:15Z")

</div>

Hi, could you share the table definition?

```sql
cr > show create table metrics

```

---

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [June 9, 2025, 9:04am UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036/3 "2025-06-09T09:04:25Z")

</div>

```auto
cr> show create table metrics;
+--------------------------------------------------------------------------------------------------------------+
| SHOW CREATE TABLE doc.metrics |
+--------------------------------------------------------------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "doc"."metrics" ( |
| "timestamp" TIMESTAMP WITHOUT TIME ZONE NOT NULL, |
| "labels_hash" TEXT NOT NULL, |
| "labels" OBJECT(DYNAMIC) AS ( |
| "productline" TEXT, |
| "product" TEXT, |
| "address" TEXT, |
| "instance" TEXT, |
| "role" TEXT, |
| "datacenter" TEXT, |
| "env" TEXT, |
| "type" TEXT, |
| "devicetype" TEXT, |
| "metrics_path" TEXT, |
| "target" TEXT, |
| "hostname" TEXT, |
| " __name__" TEXT, |
| "monitorpoint" TEXT, |
| "location" TEXT, |
| "share" TEXT, |
| "job" TEXT, |
| "exported_type" TEXT, |
| "port" TEXT, |
| "server" TEXT, |
| "version" TEXT, |
| "desc" TEXT, |
| "id" TEXT, |
| "scheme" TEXT, |
| "replicaset" TEXT, |
| "master" TEXT, |
| "bucket" TEXT, |
| "log" TEXT, |
| "time" TEXT, |
| "icmphost" TEXT, |
| "unit" TEXT, |
| "os" TEXT, |
| "release" TEXT, |
| "version_id" TEXT, |
| "pretty_name" TEXT, |
| "name" TEXT, |
| "id_like" TEXT, |
| "version_codename" TEXT, |
| "variant_id" TEXT, |
| "variant" TEXT, |
| "facenv" TEXT, |
| "cpu" TEXT, |
| "mode" TEXT, |
| "mountpoint" TEXT, |
| "device" TEXT, |
| "fstype" TEXT, |
| "exported_product" TEXT, |
| "minor_version" TEXT, |
| "major_version" TEXT, |
| "build_number" TEXT, |
| "revision" TEXT, |
| "core" TEXT, |
| "volume" TEXT, |
| "nic" TEXT, |
| "af" TEXT, |
| "token" TEXT, |
| "readonly" TEXT, |
| "label" TEXT, |
| "bi_ignore" TEXT |
| ), |
| "value" DOUBLE PRECISION, |
| "valueRaw" BIGINT, |
| "day__generated" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS date_trunc('day', "timestamp") NOT NULL, |
| PRIMARY KEY ("timestamp", "labels_hash", "day__generated") |
| ) |
| CLUSTERED INTO 8 SHARDS |
| PARTITIONED BY ("day__generated") |
| WITH ( |
| column_policy = 'strict', |
| number_of_replicas = '0-1', |
| refresh_interval = 1000 |
| ) |
+--------------------------------------------------------------------------------------------------------------+

```

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [June 9, 2025, 9:13am UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036/4 "2025-06-09T09:13:30Z")

</div>

I could reproduce the issue, I will open an issue, thanks!

---

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [June 9, 2025, 9:28am UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036/5 "2025-06-09T09:28:35Z")

</div>

Thanks a lot @surister .

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [June 9, 2025, 9:36am UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036/6 "2025-06-09T09:36:30Z")

</div>

Thanks again for the report, you can follow the issue at [Aggregations on generated columns return null when the table is partitioned by it · Issue #17980 · crate/crate · GitHub](https://github.com/crate/crate/issues/17980)

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [June 13, 2025, 1:18pm UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036/7 "2025-06-13T13:18:27Z")

</div>

The issue is now fixed and will be available in a `5.10.9` hotfix release soon. This is the PR if you are interested in more details, [Fix aggregation on PARTITION BY cols by matriv · Pull Request #18009 · crate/crate · GitHub](https://github.com/crate/crate/pull/18009) again thanks for the report!