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

[Jun_Zhou]

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.

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)

[surister]

Hi, could you share the table definition?

cr > show create table metrics

[Jun_Zhou]

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                                                                                   |
| )                                                                                                            |
+--------------------------------------------------------------------------------------------------------------+

[surister]

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

[Jun_Zhou]

Thanks a lot @surister .

[surister]

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