nil conversion panic when aggregating with stats extension over a window without the current row

[ncruces]

Discussed in #232

^{Originally posted by chdorner February 26, 2025}
I managed to successfully calculate a moving average (with the default AVG() function) and standard deviation (with the go-sqlite3's STDDEV_POP function from the stats extension) if that window includes the current row. However, if I try to shift the window back by one row, the builtin AVG continues to work, but the same change on the STDDEV_POP function results in a panic.

For a minimal reproduction, I generated the following series of data:

CREATE TABLE vals (dt TEXT UNIQUE NOT NULL, val INT NOT NULL);
INSERT INTO vals
SELECT
    date('now', '-' || value || ' day') as dt,
    ABS(RANDOM() % 100) as val
FROM
    generate_series(0, 100);

and this code:

package main

import (
	"fmt"

	"github.com/ncruces/go-sqlite3"
	"github.com/ncruces/go-sqlite3/driver"
	_ "github.com/ncruces/go-sqlite3/embed"
	"github.com/ncruces/go-sqlite3/ext/stats"
)

func main() {
	sqlite3.AutoExtension(stats.Register)
	db, err := driver.Open("file:vals.db?mode=ro")
	if err != nil {
		panic(err)
	}
	rows, err := db.Query(`
		WITH inner AS (
			SELECT
				date('now', '-' || dates.value || ' day') as dt
			FROM generate_series(0, 15) as dates
		)
		SELECT
			vals.dt,
			vals.val,
			AVG(vals.val) OVER (ORDER BY vals.dt ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as mvg_avg,
			STDDEV_POP(vals.val) OVER (ORDER BY vals.dt ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as stddev
		FROM inner
		JOIN vals ON vals.dt = inner.dt
		ORDER BY vals.dt DESC
		LIMIT 7
	`)
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	var dt string
	var val int
	var mvavg float64
	var stddev float64
	for rows.Next() {
		err := rows.Scan(&dt, &val, &mvavg, &stddev)
		if err != nil {
			panic(err)
		}
		fmt.Println(dt, val, mvavg, stddev)
	}
}

This correctly calculates the moving average over the last 7 days of values including today, and the standard deviation over the same period.

If I now want to calculate the moving averages and standard deviation over the last 7 days excluding today I change those two columns in the query to:

AVG(vals.val) OVER (ORDER BY vals.dt ASC ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) as mvg_avg,
STDDEV_POP(vals.val) OVER (ORDER BY vals.dt ASC ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) as stddev

which results in the following panic:

panic: interface conversion: interface is nil, not sqlite3.AggregateFunction (recovered by wazero)
        wasm stack trace:
                env.go_value(i32,i32)
                sqlite3.wasm.go_value_wrapper(i32)
                sqlite3.wasm.sqlite3VdbeExec(i32) i32
                sqlite3.wasm.sqlite3_step(i32) i32

        Go runtime stack trace:
        goroutine 1 [running]:
        runtime/debug.Stack()
                /opt/homebrew/Cellar/go/1.24.0/libexec/src/runtime/debug/stack.go:26 +0x64
        github.com/tetratelabs/wazero/internal/wasmdebug.(*stackTrace).FromRecovered(0x140034cb088?, {0x10292ca00, 0x1400184f9e0})
                /Users/chdorner/go/pkg/mod/github.com/tetratelabs/wazero@v1.9.0/internal/wasmdebug/debug.go:142 +0x140
        github.com/tetratelabs/wazero/internal/engine/wazevo.(*callEngine).callWithStack.func1()
                /Users/chdorner/go/pkg/mod/github.com/tetratelabs/wazero@v1.9.0/internal/engine/wazevo/call_engine.go:255 +0x334
        panic({0x10292ca00?, 0x1400184f9e0?})
                /opt/homebrew/Cellar/go/1.24.0/libexec/src/runtime/panic.go:787 +0x124
        github.com/ncruces/go-sqlite3.valueCallback({0x102987c70?, 0x1400184f8f0?}, {0x496d80298d2e0?, 0x14000001000?}, 0xfe50, 0x0)
                /Users/chdorner/go/pkg/mod/github.com/ncruces/go-sqlite3@v0.23.3/func.go:196 +0x150
        github.com/ncruces/go-sqlite3/internal/util.funcVII[...].Call(...)
                /Users/chdorner/go/pkg/mod/github.com/ncruces/go-sqlite3@v0.23.3/internal/util/func.go:27
        github.com/tetratelabs/wazero/internal/engine/wazevo.(*callEngine).callWithStack.func4(0x0, 0x102929700?, {0x102985d48?, 0x1029833d8?}, {0x102987c70?, 0x1400184f8f0?}, 0x14002b4fb88?)
                /Users/chdorner/go/pkg/mod/github.com/tetratelabs/wazero@v1.9.0/internal/engine/wazevo/call_engine.go:369 +0xb0
        github.com/tetratelabs/wazero/internal/engine/wazevo.(*callEngine).callWithStack(0x140034cb088, {0x102987c70, 0x1400184f8f0}, {0x14002d53830, 0x9, 0x1026fd138?})
                /Users/chdorner/go/pkg/mod/github.com/tetratelabs/wazero@v1.9.0/internal/engine/wazevo/call_engine.go:370 +0x3b8
        github.com/tetratelabs/wazero/internal/engine/wazevo.(*callEngine).CallWithStack(0x14002b4fc08?, {0x102987c70?, 0x1400184f8f0?}, {0x14002d53830?, 0x14002b4fc00?, 0x1400000f138?})
                /Users/chdorner/go/pkg/mod/github.com/tetratelabs/wazero@v1.9.0/internal/engine/wazevo/call_engine.go:191 +0x98
        github.com/ncruces/go-sqlite3.(*sqlite).call(0x14002d53508, {0x10272af1f, 0xc?}, {0x14002b4fc50?, 0x14000170000?, 0x152034d00?})
                /Users/chdorner/go/pkg/mod/github.com/ncruces/go-sqlite3@v0.23.3/sqlite.go:183 +0x9c
        github.com/ncruces/go-sqlite3.(*Stmt).Step(0x1400184f980)
                /Users/chdorner/go/pkg/mod/github.com/ncruces/go-sqlite3@v0.23.3/stmt.go:110 +0x5c
        github.com/ncruces/go-sqlite3/driver.(*rows).Next(0x140032eef80, {0x140034d8040, 0x4, 0x0?})
                /Users/chdorner/go/pkg/mod/github.com/ncruces/go-sqlite3@v0.23.3/driver/driver.go:743 +0x94
        database/sql.(*Rows).nextLocked(0x14000110280)
                /opt/homebrew/Cellar/go/1.24.0/libexec/src/database/sql/sql.go:3066 +0x144
        database/sql.(*Rows).Next.func1()
                /opt/homebrew/Cellar/go/1.24.0/libexec/src/database/sql/sql.go:3041 +0x30
        database/sql.withLock({0x1029872e8, 0x140001102b8}, 0x14002b4fe08)
                /opt/homebrew/Cellar/go/1.24.0/libexec/src/database/sql/sql.go:3574 +0x74
        database/sql.(*Rows).Next(0x14000110280)
                /opt/homebrew/Cellar/go/1.24.0/libexec/src/database/sql/sql.go:3040 +0x74
        main.main()
                /private/tmp/repro/main.go:43 +0x190

Note, that if I only change the moving average to be the last 7 days excluding today, there is no panic:

AVG(vals.val) OVER (ORDER BY vals.dt ASC ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) as 
STDDEV_POP(vals.val) OVER (ORDER BY vals.dt ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as stddev

I'm not sure if this is a bug, or if there's a mistake on my end. I'd appreciate any help 🙏

[ncruces]

Can you test e9db0d8, @chdorner?

Or, alternatively, is this the expected output?

2025-02-27 7 45.714285714285715 35.15156686495247
2025-02-26 68 49.142857142857146 38.19659136410911
2025-02-25 87 41.714285714285715 35.03758914453482
2025-02-24 26 40.42857142857143 35.74855498720849
2025-02-23 98 37.714285714285715 31.774717198760786
2025-02-22 2 38 31.45972481943041
2025-02-21 24 35.42857142857143 33.18716250452938

[chdorner]

This works, thank you for the quick fix!

[ncruces]

OK, great. This updates the Wasm binary, and I'm having an issue with reproducible builds that prevents attestations to work across platforms (basically the Wasm built on Linux does not match the one built on macOS). I need to fix that before I make a release.

[ncruces]

The build reproducibility issue is fixed. Will tag shortly.

The issue was Value being called without Step, which can happen for an initially empty window.

SQLite documents the behavior explicitly for empty aggregations, but not empty windows (although it makes perfect sense). At the C level these are different callbacks, hence the issue.

[chdorner]

Thanks for the quick fix!