count() query is inconsistent with Polars

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

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
└─────────

@asddfl

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?

Navigation Menu

Repository navigation

`count()` query is inconsistent with Polars #179

What happens?

To Reproduce

OS:

DuckDB Version:

DuckDB Client:

Hardware:

Full Name:

Affiliation:

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

Did you include all code required to reproduce the issue?

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

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

count() query is inconsistent with Polars #179

Description

What happens?

To Reproduce

OS:

DuckDB Version:

DuckDB Client:

Hardware:

Full Name:

Affiliation:

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

Did you include all code required to reproduce the issue?

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

Activity

paultiq commented on Nov 14, 2025

evertlammerts commented on Nov 14, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

`count()` query is inconsistent with Polars #179