Repository navigation
MIN and MAX return data from WHERE clause on empty input #8911
Description
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;
NULLCompare 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
Activity
alamb commentedon Jan 19, 2024
I agree this is a bug. Thanks for filing @tv42
alamb commentedon Jan 21, 2024
Bam! Open/closed in ~ 24 hours 🚀