You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "jonahgao (via GitHub)" <gi...@apache.org> on 2024/07/06 11:43:10 UTC
[I] Review the behavior of `count` with multiple arguments [datafusion]
jonahgao opened a new issue, #11303:
URL: https://github.com/apache/datafusion/issues/11303
### Is your feature request related to a problem or challenge?
Datafusion supports this type of syntax, such as `count(a, b)` and `count(distinct a, b)`.
However, its behavior may not be well-defined. A counterintuitive behavior is that `count(distinct a, b)` returns more rows than `count(a, b)`.
```sh
create table t(a int, b int) as values(1, NULL), (NULL, 10), (NULL, NULL);
> select count(distinct a,b) from t;
+-------------------------+
| count(DISTINCT t.a,t.b) |
+-------------------------+
| 1 |
+-------------------------+
> select count(a,b) from t;
+----------------+
| count(t.a,t.b) |
+----------------+
| 0 |
+----------------+
```
Additionally, PostgreSQL does not support this type of syntax. MySQL only supports `count(distinct a, b)`, but its result is different from DataFusion.
```sh
Server version: 8.3.0 Homebrew
mysql> create table t(a int, b int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values(1, NULL), (NULL, 10), (NULL, NULL);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select count(distinct a,b) from t;
+---------------------+
| count(distinct a,b) |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
```
### Describe the solution you'd like
_No response_
### Describe alternatives you've considered
_No response_
### 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@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Review the behavior of `count` with multiple arguments [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #11303:
URL: https://github.com/apache/datafusion/issues/11303#issuecomment-2211795107
Related to this, we could maybe support zero-arg `count()`.
For 2+ args for count, I as a user would prefer to used filtered aggregation. It's then obvious whether I am counting rows when all args are not null, or when at least one is not null:
```
count() FILTER (WHERE a IS NOT NULL ( AND | OR ) b IS NOT NULL)
```
(definitely more verbose than `count(a, b)`, but is `count(a, b)` established & consistent across query engines?)
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Review the behavior of `count` with multiple arguments [datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #11303:
URL: https://github.com/apache/datafusion/issues/11303#issuecomment-2212431032
This feature was introduced by #5908. Spark also supports it, and its behavior seems to be consistent with MySQL. So I think we can follow [Spark](https://spark.apache.org/docs/latest/api/sql/index.html#count).
> count(expr[, expr...]) - Returns the number of rows for which the supplied expression(s) are all non-null.
> count(DISTINCT expr[, expr...]) - Returns the number of rows for which the supplied expression(s) are unique and non-null.
We need to correct `count(distinct a, b)`.
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Review the behavior of `count` with multiple arguments [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #11303:
URL: https://github.com/apache/datafusion/issues/11303#issuecomment-2212053813
> Related to this, we could maybe support zero-arg `count()`.
>
> For 2+ args for count, I as a user would prefer to used filtered aggregation. It's then obvious whether I am counting rows when all args are not null, or when at least one is not null:
>
> ```
> count() FILTER (WHERE a IS NOT NULL ( AND | OR ) b IS NOT NULL)
> ```
>
> (definitely more verbose than `count(a, b)`, but is `count(a, b)` established & consistent across query engines?)
`count()` is WIP #11229 , it is equivalent to `count(*)` and `count(1)`
> count(a, b) established & consistent across query engines
I don't think so, therefore I prefer not to support it and returns error instead.
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Review the behavior of `count` with multiple arguments [datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao closed issue #11303: Review the behavior of `count` with multiple arguments
URL: https://github.com/apache/datafusion/issues/11303
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org