duckdb-extension-lua-statcpp

A loadable DuckDB extension that exposes the statcpp C++17 statistics library as 203 SQL functions, plus a Lua layer for defining new SQL functions without recompiling anything.

Built against the DuckDB C Extension API (stable v1.2.0 baseline), not the C++ API. DuckDB itself is never built, linked, or modified: the build downloads two headers and produces a self-contained statcpp.duckdb_extension.

# The extension is unsigned, so the CLI must be started with -unsigned, and
# LOAD needs an absolute path (see "Loading" below).
EXT="$(pwd)/build/statcpp.duckdb_extension"
duckdb -unsigned -c "LOAD '$EXT';
  SELECT grp,
         stat_median(list(v))          AS median,
         stat_mad(list(v))             AS mad,
         stat_percentile(list(v), 0.9) AS p90
  FROM measurements
  GROUP BY grp;"

This is the C API port of the duckdb-lua-statcpp prototype, which did the same job as a standalone executable that statically linked DuckDB's C++ API.

Why the C API

Aspect	This project (C API)	The prototype (C++ API)
DuckDB build	not needed, 2 headers	full source build
Version coupling	loads into any DuckDB with C API >= v1.2.0	must be rebuilt per DuckDB version
Distribution	one .duckdb_extension file	an executable with DuckDB linked in
Non-deterministic UDFs	supported	not expressible

The cost is that vectors must be read and written through raw pointers and validity masks instead of duckdb::Value. All of that bookkeeping is confined to capi_util.hpp, so the ~210 registrations describe only what to compute.

Build

Requires CMake >= 3.20, a C++17 compiler, and network access on the first configure (statcpp, Lua and the DuckDB headers are downloaded into download/ and cached).

cmake -S . -B build -DCMAKE_BUILD_TYPE=Release
cmake --build build

The result is build/statcpp.duckdb_extension.

Loading

Two constraints apply, and both bite silently if missed:

The CLI must be started with -unsigned. The extension is unsigned, and allow_unsigned_extensions cannot be turned on after the database is running: SET allow_unsigned_extensions = true inside a session fails with "Cannot change allow_unsigned_extensions setting while database is running". -unsigned sets it before startup, which is the only way in the CLI. Client libraries set it in the connection config at open time instead (for example DuckDB's Python client accepts config={'allow_unsigned_extensions': 'true'} in duckdb.connect).

LOAD needs an absolute path (at least on macOS). The duckdb CLI is a hardened program, and macOS refuses dlopen on a relative path from one — a relative LOAD fails with "relative path not allowed in hardened program". Resolve the path first:

EXT="$(pwd)/build/statcpp.duckdb_extension"
duckdb -unsigned -c "LOAD '$EXT'; SELECT stat_mean([1, 2, 3]);"

Testing

./test/run_tests.sh

36 assertions covering registration, a call to every one of the 203 functions, known numerical values, NULL and boundary handling, LIST output, volatility, the Lua layer, and correctness under 8 threads.

Function families

Holistic statistics need the whole sample at once, which does not fit DuckDB's fixed-size aggregate-state model. So a column is aggregated into a LIST with list() and passed to a scalar function:

The 89 LIST-based functions split into these families:

Family	Signature	Example
Aggregate	LIST to DOUBLE	stat_median(list(v))
With parameter	(LIST, DOUBLE) to DOUBLE	stat_percentile(list(v), 0.9)
Two-sample	(LIST, LIST) to DOUBLE	stat_pearson_r(list(x), list(y))
Transform	LIST to LIST	unnest(stat_rank(list(v)))
Window	(LIST, DOUBLE) to LIST	unnest(stat_rolling_mean(list(v), 3))

The remaining 114 are plain scalars, (DOUBLE, ...) to DOUBLE or VARCHAR, such as stat_normal_quantile(0.975, 0, 1).

List them all with:

SELECT function_name, parameter_types, return_type
FROM duckdb_functions() WHERE function_name LIKE 'stat\_%' ESCAPE '\'
ORDER BY function_name;

Structure-returning statistics (regression, ANOVA, hypothesis tests, clustering) are out of scope; use statcpp directly for those.

Conventions

• DuckDB NULL and the statcpp missing value (NaN) map to each other in both directions, including inside LIST elements.
• Missing values are dropped before a statistic is computed, pairwise for two-sample inputs.
• Invalid input yields SQL NULL rather than a query error. statcpp exceptions are absorbed at the boundary, so nothing propagates into the engine.
• stat_stdev is the population standard deviation (ddof = 0). The sample variants are stat_sample_variance and stat_var.
• Rolling statistics return one value per complete window, so the output is shorter than the input by window - 1.

The Lua layer

stats.lua declares which of its functions become SQL functions:

function lua_midrange(data)
    local mn, mx
    for _, v in ipairs(data) do
        if v == v then                      -- v ~= v means NaN, i.e. missing
            if mn == nil or v < mn then mn = v end
            if mx == nil or v > mx then mx = v end
        end
    end
    if mn == nil then return nil end
    return (mn + mx) / 2.0
end

exports.midrange = "scalar"

Each entry name = kind registers the Lua global lua_<name> as the SQL function lua_stat_<name>. All take one LIST<DOUBLE> argument; kind picks the return type: "scalar" for DOUBLE, "list" for LIST<DOUBLE>, "text" for VARCHAR.

EXT="$(pwd)/build/statcpp.duckdb_extension"
STATCPP_LUA_SCRIPT=./mystats.lua duckdb -unsigned \
  -c "LOAD '$EXT'; SELECT lua_stat_midrange([1, 2, 9]);"

No rebuild, no C++ change. The prototype could only change the bodies of a fixed list of seven functions this way; adding one meant editing C++.

Script resolution

First hit wins:

1. $STATCPP_LUA_SCRIPT
2. ~/.duckdb/statcpp/stats.lua
3. the copy embedded in the binary at build time

The embedded copy only guarantees the extension is useful out of the box; an external script always takes precedence. SELECT lua_stat_script_origin(); reports which one is in effect, which is the first thing to check when an edit appears to have no effect.

Threading

lua_State is not thread-safe and DuckDB runs scalar functions on many threads. A pool in lua_udf.hpp hands each thread its own state for the duration of a chunk. The prototype shared one state across all UDFs, which was a latent data race that stayed quiet only because its demo was single-threaded.

Known limitations

Loading is unsigned and needs an absolute path

The extension is unsigned, so the CLI must be started with -unsigned; the setting cannot be changed at runtime. LOAD also needs an absolute path on macOS, because the CLI is a hardened program. Both are covered in Loading above.

An all-NULL list written as a literal returns wrong values

SELECT stat_count([NULL, NULL]::DOUBLE[]);   -- returns 1.0, should be 0.0
SELECT stat_mean([NULL, NULL]::DOUBLE[]);    -- returns 0.0, should be NULL

Cause. Vector::Flatten flattens the child of a constant ARRAY or STRUCT vector but, for PhysicalType::LIST, only repeats the list_entry_t values and leaves the child untouched (duckdb/src/common/types/vector.cpp). A literal like [NULL, NULL] is constant-folded at bind time, so the extension receives a constant child whose validity mask is only meaningful at index 0. Elements past the first read back as non-NULL.

Why it is not worked around here. The stable C API exposes no way to flatten a vector or even to ask what kind of vector it is, and the corrupted state is bit-for-bit identical to a legitimate [NULL, 0.0], so it cannot be detected from inside the extension either. Marking the LIST functions volatile does avoid it, by suppressing bind-time folding, but that makes DuckDB treat them as having side effects: referencing their alias in the same SELECT list then fails with Binder Error: Alias "m" referenced - but the expression has side effects, and common-subexpression elimination and filter pushdown are disabled. Breaking SELECT stat_mean(list(v)) AS m, m * 2 outright is a worse trade than this literal-only defect.

Scope. Only literals written directly as an argument are affected. Verified correct:

SELECT stat_count(x) FROM (SELECT [NULL,NULL]::DOUBLE[] AS x);  -- 0.0, via a projection
SELECT stat_count(list(v)) FROM t;                              -- 0.0, via list()
SELECT stat_count(x) FROM stored_table;                         -- 0.0, from a column

A literal with at least one non-NULL element is also unaffected. test/run_tests.sh section 6 pins the current behaviour, so the assertions fail if DuckDB changes this.

Layout

CMakeLists.txt              extension build; derives the DuckDB platform string
cmake/
  duckdb_capi.cmake         downloads duckdb.h and duckdb_extension.h
  extension_metadata.cmake  appends the 534-byte loadable-extension footer
  statcpp.cmake             downloads the statcpp headers
  lua.cmake                 builds Lua as a static PIC library
  embed_lua.cmake           embeds stats.lua into a generated header
src/
  statcpp_extension.cpp     the only C ABI symbol: statcpp_init_c_api
  include/
    capi_util.hpp           C API readers/writers, registration, exception boundary
    statcpp_compute.hpp     pure computation, no DuckDB types
    statcpp_udf.hpp         LIST-based registration (89 functions)
    statcpp_scalar.hpp      scalar registration (114 functions)
    lua_statcpp_bindings.hpp  the statcpp Lua module
    lua_udf.hpp             script resolution, state pool, exports discovery
  lua/stats.lua             default script
test/run_tests.sh           test suite

License

See LICENSE.md. statcpp, Lua and the DuckDB headers are MIT licensed.