[Bug]: null_count() is not an integer dtype on the DuckDB backend, unlike count() and len()

Describe the bug

On the DuckDB backend, null_count() comes back as Decimal(38, 0) while count() and len() in the same query come back as Int64. dtype.is_integer() is False for a count. The values are right; only the dtype is off.

_duckdb/expr.py:233 implements it as a sum over a cast boolean:

def null_count(self) -> Self:
    return self._with_callable(lambda expr: F("sum", expr.isnull().cast("int")))

DuckDB widens an integer sum to HUGEINT, and Arrow has no 128-bit integer type, so it surfaces as decimal128(38, 0). len() on the line above uses count and stays BIGINT.

I know this looks like #4014, so to be clear about why I think it is a different thing. The three reasons given there were that collect_schema() correctly reports what the native relation holds, that the schema is not promised to survive collect() and you would rather not add silent casts to make it look that way, and that there would be no obvious type to cast to. None of those apply here:

The relation itself is what is wrong. collect_schema() reports Int128 for null_count and Int64 for count on the same frame, so the inconsistency is already visible before any collect().
The change removes a cast rather than adding one: .cast("int") goes away and the expression becomes a count, which is how len() and count() already do it.
A null count is unambiguously an integer, so there is no judgement call about the target type.
In [Bug]: integer sum() returns Decimal(38, 0) on the DuckDB backend, and arithmetic on the result raises #4014 the integer column was the user's own. Here the integer is one Narwhals creates itself, so it is ours to get right.

Switching to F("count", when(expr.isnull(), lit(1))) gives Int64 in select, group_by and over, and collect_schema() reports Int64 too. when and lit are already imported in that module. Things that do not work, for the record: count_if and sum(cast(... as int)) both return HUGEINT in DuckDB, and count(*) - count(x) fails to parse inside over().

The test suite does not catch this because tests/expr_and_series/null_count_test.py only compares values through assert_equal_data, and Decimal("2") == 2 holds.

I have the one-line patch and tests ready and would like to open the PR myself. Since CONTRIBUTING asks first-time contributors to keep one PR open at a time and my #4011 is still in review, I will open it once that one is resolved — unless you would rather it went in sooner, in which case say so and I will put it up now.

(Disclosure: I investigated this with Claude's help. The reproduction and the test run below were done in a Linux container rather than on my own machine; I will post a local run in the PR as CONTRIBUTING asks.)

Steps or code to reproduce the bug

Against main at e5af71cf88485f7b23ebc562fa356d331f66d290, installed with pip install -e . into a clean venv.

import duckdb
import polars as pl

import narwhals as nw

data = {"x": [1.0, None, None, 2.0], "g": [1, 1, 2, 2]}
df_pl = pl.DataFrame(data)
rel = duckdb.sql("select * from df_pl")

q = nw.from_native(rel).select(
    nc=nw.col("x").null_count(), c=nw.col("x").count(), n=nw.len()
).lazy()

print(dict(q.collect_schema()))
print(q.collect().schema)
print(q.collect().schema["nc"].is_integer())

group_by and over behave the same way:

nw.from_native(rel).group_by("g").agg(nc=nw.col("x").null_count()).lazy().collect().schema
# Schema([('g', Int64), ('nc', Decimal(precision=38, scale=0))])

nw.from_native(rel).with_columns(nc=nw.col("x").null_count().over("g")).lazy().collect().schema["nc"]
# Decimal(precision=38, scale=0)

The DuckDB types underneath:

select typeof(sum(cast(x is null as int))),      -- HUGEINT  (current)
       typeof(count_if(x is null)),              -- HUGEINT
       typeof(count(case when x is null then 1 end))  -- BIGINT
from (select unnest([1, null]) as x);

Expected results

null_count() should be an integer dtype, and the same integer dtype that count() and len() return on the same backend:

{'nc': Int64, 'c': Int64, 'n': Int64}
Schema([('nc', Int64), ('c', Int64), ('n', Int64)])
True

For comparison, the other backends are each self-consistent: Polars gives UInt32 for all three, pandas and PyArrow give Int64 for all three.

Actual results

{'nc': Int128, 'c': Int64, 'n': Int64}
Schema([('nc', Decimal(precision=38, scale=0)), ('c', Int64), ('n', Int64)])
False

All three return the right numbers (2, 2, 4) — it is only null_count's dtype that differs, and only on DuckDB.

Please run narwhals.show_versions() and enter the output below.

System:
    python: 3.11.15 (main, Mar  3 2026, 09:26:23) [GCC 13.3.0]
executable: .../.venv/bin/python
   machine: Linux-6.18.44-fc-v64-x86_64-with-glibc2.39

Python dependencies:
     narwhals: 2.26.0
        numpy: 2.4.6
       pandas: 3.0.6
        modin:
         cudf:
      pyarrow: 25.0.1
      pyspark:
       polars: 1.44.2
         dask:
       duckdb: 1.5.6
         ibis:
     sqlframe:

Relevant log output

No traceback: the operation succeeds and returns the correct count, just not as an integer dtype.

@lucaluo925

Hey @lucaluo925 thanks for reporting this. I agree here we should keep duckdb do its own thing with count

What I would like to ask you before submitting a PR:

Can you check what would happen for a relation that has enough null rows that the count need to be Int128 (or UInt128)?
Can you benchmark performance differences in runtime? I wonder if F("count", when(expr.isnull(), lit(True))) would lower the memory footprint as well?

Thanks for the quick reply. I looked into both questions — the numbers below come from a Linux container rather than my own machine.

On the first question — the Int128 case

I don't think such a relation can be constructed, and the current behaviour isn't an on-demand switch to Int128 either. DuckDB's return types here are static, independent of row count:

expression	n=1	n=1e6	n=5e7
`count(*)`	BIGINT	BIGINT	BIGINT
`count(x)`	BIGINT	BIGINT	BIGINT
`count(case when x is null then 1 end)`	BIGINT	BIGINT	BIGINT
`sum(cast(x is null as int))` (current)	HUGEINT	HUGEINT	HUGEINT

A null count is bounded by the row count, which count(*) reports as BIGINT (max 9223372036854775807). DuckDB's own row generator refuses anything past that:

range(0, 9223372036854775807)   -> ok
range(0, 9223372036854775808)   -> BinderException: No function matches the given name
                                   and argument types 'range(INTEGER, HUGEINT)'

BIGINT overflow in DuckDB raises rather than wrapping silently: OutOfRangeException: Overflow in addition of INT64.

On the second question — benchmarks

50M rows, ~50% null, 1000 groups, pragma threads=2, median of 7 runs (3 for over):

expression	type	select	group_by	over
A `sum(cast(x is null as int))` (current)	HUGEINT	0.064s (1.00x)	0.150s (1.00x)	37.4s (1.00x)
B `count(case when x is null then 1 end)`	BIGINT	0.125s (1.94x)	0.209s (1.40x)	10.4s (0.28x)
C `cast(sum(cast(x is null as int)) as bigint)`	BIGINT	0.064s (0.99x)	0.160s (1.07x)	10.7s (0.29x)
D `count(*) - count(x)`	BIGINT	0.037s (0.57x)	0.140s (0.94x)	needs two aggregates wrapped separately

B — the expression I suggested in the issue — turns out to be about twice as slow in aggregate contexts, so I would propose C instead: aggregates stay level with the current implementation, and over is 3.4x faster. The over speedup comes from the current implementation having to materialize 50 million HUGEINT values inside the window.

C follows the window_func pattern already used by any() in _sql/expr.py — only the aggregate goes inside window_expression, the cast stays outside:

def null_count(self) -> Self:
    def _total(expr: Expression) -> Expression:
        return F("sum", expr.isnull().cast("int"))

    def f(expr: Expression) -> Expression:
        return _total(expr).cast("bigint")

    def window_f(
        df: DuckDBLazyFrame, inputs: WindowInputs[Expression]
    ) -> list[Expression]:
        return [
            window_expression(_total(expr), inputs.partition_by).cast("bigint")
            for expr in self(df)
        ]

    return self._with_callable(f, window_f)

Int64 in select, group_by and over, and collect_schema() agrees. No new imports. Full suite: 10944 passed, with only the pre-existing read_scan_test.py sqlframe import failures, which also fail on a clean checkout.

B is one line, C is around fifteen — would you rather keep it concise or take the performance? I'll hold off opening the PR until #4011 lands, to keep to one open PR at a time.

@lucaluo925

Hey @lucaluo925 I found a discrepancy also for null_count on an empty dataframe for all sql-like backends. I opened a PR which is still in draft that addresses both issues. I want to make sure that there are not big performance regressions on that one, so it might take a bit longer before we merge it

Navigation Menu

Repository navigation

[Bug]: null_count() is not an integer dtype on the DuckDB backend, unlike count() and len() #4024

Describe the bug

Steps or code to reproduce the bug

Expected results

Actual results

Please run narwhals.show_versions() and enter the output below.

Relevant log output

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[Bug]: null_count() is not an integer dtype on the DuckDB backend, unlike count() and len() #4024

Description

Describe the bug

Steps or code to reproduce the bug

Expected results

Actual results

Please run narwhals.show_versions() and enter the output below.

Relevant log output

Activity

FBruzzesi commented on Oct 4, 2026

lucaluo925 commented on Oct 4, 2026

FBruzzesi commented on Oct 5, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions