MIN and MAX return data from WHERE clause on empty input

[tv42]

Describe the bug

Calling MIN or MAX on an empty input values (or one with all-NULL values) should return NULL.

Quoting https://www.postgresql.org/docs/16/functions-aggregate.html

It should be noted that except for count, these functions return a null value when no rows are selected.

To Reproduce

❯ CREATE TABLE mytable(col0 INTEGER);
0 rows in set. Query took 0.024 seconds.
❯ SELECT * FROM mytable;
0 rows in set. Query took 0.005 seconds.
❯ SELECT MIN(col0) FROM mytable WHERE col0=1;
+-------------------+
| MIN(mytable.col0) |
+-------------------+
| 1                 |
+-------------------+
1 row in set. Query took 0.013 seconds.

❯ SELECT MIN(col0) FROM mytable WHERE col0=2;
+-------------------+
| MIN(mytable.col0) |
+-------------------+
| 2                 |
+-------------------+
1 row in set. Query took 0.012 seconds.

❯ SELECT MIN(col0) FROM mytable WHERE col0=3;
+-------------------+
| MIN(mytable.col0) |
+-------------------+
| 3                 |
+-------------------+
1 row in set. Query took 0.013 seconds.

❯ SELECT MAX(col0) FROM mytable WHERE col0=1;
+-------------------+
| MAX(mytable.col0) |
+-------------------+
| 1                 |
+-------------------+
1 row in set. Query took 0.013 seconds.

❯ SELECT MAX(col0) FROM mytable WHERE col0=2;
+-------------------+
| MAX(mytable.col0) |
+-------------------+
| 2                 |
+-------------------+
1 row in set. Query took 0.005 seconds.

❯ SELECT MAX(col0) FROM mytable WHERE col0=3;
+-------------------+
| MAX(mytable.col0) |
+-------------------+
| 3                 |
+-------------------+
1 row in set. Query took 0.013 seconds.

Expected behavior

NULL returned.

Compare to SQLite:

sqlite> CREATE TABLE mytable(col0 INTEGER);
sqlite> SELECT MIN(col0) FROM mytable WHERE col0=1;
NULL
sqlite> SELECT MIN(col0) FROM mytable WHERE col0=2;
NULL
sqlite> SELECT MIN(col0) FROM mytable WHERE col0=3;
NULL
sqlite> SELECT MAX(col0) FROM mytable WHERE col0=1;
NULL
sqlite> SELECT MAX(col0) FROM mytable WHERE col0=2;
NULL
sqlite> SELECT MAX(col0) FROM mytable WHERE col0=3;
NULL

Compare to Postgres:

postgres=# \pset null 'NULL'
Null display is "NULL".
postgres=# CREATE TABLE mytable(col0 INTEGER);
CREATE TABLE
postgres=# SELECT MIN(col0) FROM mytable WHERE col0=1;
 min
------
 NULL
(1 row)

postgres=# SELECT MIN(col0) FROM mytable WHERE col0=2;
 min
------
 NULL
(1 row)

postgres=# SELECT MIN(col0) FROM mytable WHERE col0=3;
 min
------
 NULL
(1 row)

postgres=# SELECT MAX(col0) FROM mytable WHERE col0=1;
 max
------
 NULL
(1 row)

postgres=# SELECT MAX(col0) FROM mytable WHERE col0=2;
 max
------
 NULL
(1 row)

postgres=# SELECT MAX(col0) FROM mytable WHERE col0=3;
 max
------
 NULL
(1 row)

Additional context

No response

[alamb]

I agree this is a bug. Thanks for filing @tv42

[alamb]

Bam! Open/closed in ~ 24 hours 🚀