tv42 opened a new issue, #8911: URL: https://github.com/apache/arrow-datafusion/issues/8911
### 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 ```console ❯ 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: ```console 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: ```console 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_ -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
