`count()` query is inconsistent with Polars

[asddfl]

What happens?

The count() query results of Duckdb SQL are inconsistent with those of Polars SQL and DataFrame API interfaces.

To Reproduce

import polars as pl
import duckdb

ctx = pl.SQLContext()

t0 = pl.LazyFrame(
    {
        "c0": [1, 1]
    }
)
ctx.register("t0", t0)

print("Polars SQL result: ")
polars_sql_result = ctx.execute("SELECT COUNT(1) FROM t0", eager=False)
print(polars_sql_result.collect())

print("Polars API result: ")
polars_api_result = t0.select(pl.lit(1).count())
print(polars_api_result.collect())

con = duckdb.connect()
con.execute(f"""
CREATE TABLE t0 AS SELECT * FROM VALUES (1), (1) AS t0(c0);
""")
t0 = con.table("t0")

print("Duckdb SQL result: ")
duckdb_result = con.sql("SELECT COUNT(1) FROM t0").pl()
print(duckdb_result)

con.close()

Polars SQL result: 
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ u32     │
╞═════════╡
│ 1       │
└─────────┘
Polars API result: 
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ u32     │
╞═════════╡
│ 1       │
└─────────┘
Duckdb SQL result: 
shape: (1, 1)
┌──────────┐
│ count(1) │
│ ---      │
│ i64      │
╞══════════╡
│ 2        │
└──────────┘

OS:

x86_64 Ubuntu 24.04 Linux-6.14.0-35-generic-x86_64-with-glibc2.39

DuckDB Version:

1.4.2

DuckDB Client:

Python

Hardware:

No response

Full Name:

asddfl

Affiliation:

xxx

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Yes

[paultiq]

In Polars, select count(constant) returns 1:

import polars as pl

with pl.SQLContext() as ctx:

    t0 = pl.LazyFrame(
        {
            "c0": [1, 1, 1, 1, 1, 1, 1, 1, 1]
        }
    )
    ctx.register("t0", t0)
    polars_sql_result = ctx.execute("SELECT COUNT(*) FROM t0", eager=False)
    print(polars_sql_result.collect())

    polars_sql_result = ctx.execute("SELECT COUNT(123456) FROM t0", eager=False)
    print(polars_sql_result.collect())

shape: (1, 1)
┌─────
│ len
│ ---
│ u32
╞═════
│ 9
└─────
shape: (1, 1)
┌─────────
│ literal
│ ---
│ u32
╞═════════
│ 1
└─────────

[evertlammerts]

Hey @asddfl, DuckDB and Polars indeed behave differently when an expression with only constants is given as argument to COUNT. Sorry for the verbosity, but some examples:

D select count(*) from values(0), (1), (2), (2), (NULL), (NULL), (NULL) as t(c0);
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│      7       │
└──────────────┘
D select count(c0=1) from values(0), (1), (2), (2), (NULL), (NULL), (NULL) as t(c0);
┌─────────────────┐
│ count((c0 = 1)) │
│      int64      │
├─────────────────┤
│        4        │
└─────────────────┘
D select count(c0=1 OR NULL) from values(0), (1), (2), (2), (NULL), (NULL), (NULL) as t(c0);
┌───────────────────────────┐
│ count(((c0 = 1) OR NULL)) │
│           int64           │
├───────────────────────────┤
│             1             │
└───────────────────────────┘
D select count(NULL) from values(0), (1), (2), (2), (NULL), (NULL), (NULL) as t(c0);
┌─────────────┐
│ count(NULL) │
│    int64    │
├─────────────┤
│      0      │
└─────────────┘
D select count(TRUE) from values(0), (1), (2), (2), (NULL), (NULL), (NULL) as t(c0);
┌─────────────────────────────┐
│ count(CAST('t' AS BOOLEAN)) │
│            int64            │
├─────────────────────────────┤
│              7              │
└─────────────────────────────┘
D select count(1=1) from values(0), (1), (2), (2), (NULL), (NULL), (NULL) as t(c0);
┌────────────────┐
│ count((1 = 1)) │
│     int64      │
├────────────────┤
│       7        │
└────────────────┘
D select count(1=2) from values(0), (1), (2), (2), (NULL), (NULL), (NULL) as t(c0);
┌────────────────┐
│ count((1 = 2)) │
│     int64      │
├────────────────┤
│       7        │
└────────────────┘
D select count(1=2 OR NULL) from values(0), (1), (2), (2), (NULL), (NULL), (NULL) as t(c0);
┌──────────────────────────┐
│ count(((1 = 2) OR NULL)) │
│          int64           │
├──────────────────────────┤
│            0             │
└──────────────────────────┘

import polars as pl
with pl.SQLContext() as ctx:
    t0 = pl.LazyFrame({"c0": [0, 1, 2, 2, None, None, None]})
    ctx.register("t0", t0)
    print(ctx.execute("select count(*) from t0", eager=False).collect())
    print(ctx.execute("select count(c0=1) from t0", eager=False).collect())
    print(ctx.execute("select count(c0=1 OR NULL) from t0", eager=False).collect())
    print(ctx.execute("select count(NULL) from t0", eager=False).collect())
    print(ctx.execute("select count(TRUE) from t0", eager=False).collect())
    print(ctx.execute("select count(1=1) from t0", eager=False).collect())
    print(ctx.execute("select count(1=2) from t0", eager=False).collect())
    print(ctx.execute("select count(1=2 OR NULL) from t0", eager=False).collect())

shape: (1, 1)
┌─────┐
│ len │
│ --- │
│ u32 │
╞═════╡
│ 7   │
└─────┘
shape: (1, 1)
┌─────┐
│ c0  │
│ --- │
│ u32 │
╞═════╡
│ 4   │
└─────┘
shape: (1, 1)
┌─────┐
│ c0  │
│ --- │
│ u32 │
╞═════╡
│ 1   │
└─────┘
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ u32     │
╞═════════╡
│ 0       │
└─────────┘
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ u32     │
╞═════════╡
│ 1       │
└─────────┘
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ u32     │
╞═════════╡
│ 1       │
└─────────┘
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ u32     │
╞═════════╡
│ 1       │
└─────────┘
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ u32     │
╞═════════╡
│ 0       │
└─────────┘

DuckDB counts all rows for which the expression evaluates to something other than NULL. Polars does this as well, but only if the expression involves a column name... Otherwise it returns 1, regardless of the amount of rows. I personally feel that DuckDB's behavior is more consistent here, wdyt? Does this somehow cause issues for you? If so, can you share more context?