COUNT(expr) always returns the COUNT(colname)

[rtyler]

Describe the bug

The COUNT() function appears to accept expressions but kind of disregards user input and then just drives into walls.

Imagine for example a table which has id[int], status[int], url[utf8] and has 50 rows with status == 200 and 50 rows with status == 500

SELECT COUNT(status) FROM source

Will return 100

SELECT COUNT(status > 200) FROM source

will also return 100 😕

It appears that this the count function is just taking the first argument in the expression and calling it a day, so the invocation syntax is valid (no errors) but produces incorrect results.

To Reproduce

No response

Expected behavior

Ideally I would like the predicate expression in COUNT() to work as it does in Spark SQL, which would result in SELECT COUNT(status == 500) as errors, COUNT(status == 200) as oks FROM source producing two columns:

+-----------+-------+
|   errors  |  oks  |
+-----------+-------+
|     50    |  50   |
+-----------+-------+

Additional context

No response

[rtyler]

Related to:

• Count() and Count(Distinct )should accept multiple exprs #5619
• Count agg support multiple expressions #5908
• Review the behavior of count with multiple arguments #11303

[devanbenz]

take

[jayzhan211]

I check the behaviour from Duckdb and Postgres, it is not the same as what Spark SQL is

D create table t(a int);
D insert into t values(1);
D insert into t values(2);
D select count(a < 0) from t;
┌────────────────┐
│ count((a < 0)) │
│     int64      │
├────────────────┤
│              2 │
└────────────────┘

postgres=# create table t(a int);
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1
postgres=# insert into t values (2);
INSERT 0 1
postgres=# select count(a > 10) from t;
 count 
-------
     2
(1 row)

Given the behaviour is consistent with both Duckdb and Postgres. I suggest we leave the function alone, but instead

1. Write the spark-compatible count function downstream
2. Bring spark-compatible functions to datafusion
3. Configure count function so the user could choose what dialect to use (Postgres, DuckDB or Spark or others)

[rtyler]

Perhaps just implementing count_if would do the trick?

[jayzhan211]

Perhaps just implementing count_if would do the trick?

It really make sense. DuckDB has count_if function too.

[alamb]

Another classic trick if you are trying to find the number of rows where a > 0 is to use SUM/CASE

Something like like

SELECT 
  SUM(CASE WHEN a > 10 THEN 1 ELSE 0 END)
FROM 
  table

[alamb]

I think we have concluded this is working as expected, so closing this ticket. Please let me know if that is not correct