nil conversion panic when aggregating with stats extension over a window without the current row
#232
-
|
I managed to successfully calculate a moving average (with the default 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 stddevwhich results in the following panic: 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 stddevI'm not sure if this is a bug, or if there's a mistake on my end. I'd appreciate any help 🙏 |
Beta Was this translation helpful? Give feedback.
Answered by
ncruces
Feb 26, 2025
Replies: 1 comment
-
|
Any panic caused by me is a bug. 🥲 |
Beta Was this translation helpful? Give feedback.
0 replies
Answer selected by
ncruces
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Any panic caused by me is a bug. 🥲
See issue #233.