You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "tv42 (via GitHub)" <gi...@apache.org> on 2024/01/19 19:48:13 UTC
[I] MIN and MAX return data from WHERE clause on empty input [arrow-datafusion]
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: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] MIN and MAX return data from WHERE clause on empty input [arrow-datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya closed issue #8911: MIN and MAX return data from WHERE clause on empty input
URL: https://github.com/apache/arrow-datafusion/issues/8911
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] MIN and MAX return data from WHERE clause on empty input [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #8911:
URL: https://github.com/apache/arrow-datafusion/issues/8911#issuecomment-1901062428
I agree this is a bug. Thanks for filing @tv42
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] MIN and MAX return data from WHERE clause on empty input [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #8911:
URL: https://github.com/apache/arrow-datafusion/issues/8911#issuecomment-1902623558
Bam! Open/closed in ~ 24 hours 🚀
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org