1package paged23import (4 "context"5 "database/sql"6 "path/filepath"7 "strings"8 "testing"910 "src.rest/cat/012/internal/sheet"11)1213// orderFormulas are order statistics over the source as {T}, each the14// same as over the table ref holding the same rows, though the source15// holds more cells than the budget: its column is sorted on disk.16var orderFormulas = []string{17 "=MEDIAN({T}[amount])", "=MEDIAN({T}[id])", "=MEDIAN({T}[day])", "=MEDIAN({T}[cat])",18 "=MODE({T}[amount])", "=MODE.SNGL({T}[day])", "=MODE({T}[id])",19 "=PERCENTILE({T}[amount],0.3)", "=PERCENTILE.INC({T}[amount],0.999)", "=PERCENTILE({T}[amount],2)",20 "=PERCENTILE.EXC({T}[amount],0.1)", "=PERCENTILE.EXC({T}[amount],0.0001)",21 "=QUARTILE({T}[amount],1)", "=QUARTILE({T}[amount],3)", "=QUARTILE.INC({T}[id],4)",22 "=QUARTILE.EXC({T}[amount],2)", "=QUARTILE.EXC({T}[amount],4)",23 "=LARGE({T}[amount],10)", "=SMALL({T}[amount],1)", "=LARGE({T}[id],1)", "=SMALL({T}[amount],100000)",24 "=RANK(50.5,{T}[amount])", "=RANK.EQ(17,{T}[amount],1)", "=RANK(0.25,{T}[amount])",25 "=MEDIAN({T}[[id]:[amount]])",26}2728// checkSame sets each of formulas over the source sales and over the29// table ref, and checks the two agree.30func checkSame(t *testing.T, w *sheet.Workbook, h *Host, formulas []string) {31 t.Helper()32 s := w.Sheet(0)33 for i, f := range formulas {34 s.Set(sheet.Addr{Col: 10, Row: i}, strings.ReplaceAll(f, "{T}", "sales"))35 s.Set(sheet.Addr{Col: 11, Row: i}, strings.ReplaceAll(f, "{T}", "ref"))36 }37 h.Settle(context.Background(), w, nil)38 for i, f := range formulas {39 got, want := s.Value(sheet.Addr{Col: 10, Row: i}), s.Value(sheet.Addr{Col: 11, Row: i})40 if testing.Verbose() {41 t.Logf("%s: %v", f, got)42 }43 if got != want {44 t.Errorf("%s: %v over the source, %v over the sheet (%s)", f, got, want, s.ExplainError(sheet.Addr{Col: 10, Row: i}))45 }46 }47}4849func TestOrderOverAParquetSource(t *testing.T) {50 name := filepath.Join(t.TempDir(), "sales.parquet")51 writeSales(t, name, 5000, 0)52 w, h := linked(t, name, 1000)53 checkSame(t, w, h, orderFormulas)54}5556func TestOrderOverASQLiteSource(t *testing.T) {57 name := filepath.Join(t.TempDir(), "sales.sqlite")58 db, err := sql.Open("sqlite", name)59 if err != nil {60 t.Fatal(err)61 }62 if _, err := db.Exec("CREATE TABLE sales (id INTEGER, cat TEXT, amount REAL, day DATE)"); err != nil {63 t.Fatal(err)64 }65 for i := range 3000 {66 var amount any67 if i%11 != 7 {68 amount = float64((i*37)%101) - 20.569 }70 _, err := db.Exec("INSERT INTO sales VALUES (?, ?, ?, ?)", i, cats[i%len(cats)], amount,71 "2026-01-"+string(rune('1'+i%9))+"0")72 if err != nil {73 t.Fatal(err)74 }75 }76 db.Close()77 w, h := linked(t, name, 1000)78 checkSame(t, w, h, orderFormulas)79}8081// TestOrderAnswersKept checks an order statistic is answered once, then82// kept as the other answers are.83func TestOrderAnswersKept(t *testing.T) {84 name := filepath.Join(t.TempDir(), "sales.parquet")85 writeSales(t, name, 500, 0)86 w, h := linked(t, name, 100)87 s := w.Sheet(0)88 s.Set(sheet.Addr{Col: 10}, "=MEDIAN(sales[amount])")89 h.Settle(context.Background(), w, nil)90 s.Set(sheet.Addr{Col: 10, Row: 1}, "=MEDIAN(sales[amount])+1")91 if jobs := h.Jobs(); len(jobs) != 0 {92 t.Errorf("asked again: %v", jobs)93 }94 if v := s.Value(sheet.Addr{Col: 10, Row: 1}); v.Kind != sheet.Number {95 t.Errorf("the kept answer: %v", v)96 }97}