| 1 | // Cross-checking against excelize — why this lives here, not in calc. |
| 2 | // |
| 3 | // calc/testdata/corpus/formulas.json (internal/sheets/calc) is generated |
| 4 | // by driving a real LibreOffice headless (26.8.0.3, as of 2026-08-31), |
| 5 | // so the formula engine is checked against an independent implementation |
| 6 | // rather than its own author's reasoning. It was hand-authored for a |
| 7 | // while, and that corpus proved self-consistency, not conformance: it |
| 8 | // shipped, for a while, with COUNT(1,2,"a",TRUE) expecting 2 (a wrong |
| 9 | // implementation's answer) until a human reviewer noticed real |
| 10 | // spreadsheets return 3 — a mistake the corpus, checking only itself, |
| 11 | // could not have caught. |
| 12 | // |
| 13 | // A LibreOffice-generated corpus closes that gap but opens a narrower |
| 14 | // one: LibreOffice Calc is not Excel. Measured directly, Calc returns |
| 15 | // #VALUE! for SUM(1,2,"3") where Excel (and this engine, and excelize) |
| 16 | // return 6. So conformance to Calc is not conformance to Excel, and a |
| 17 | // second independent engine is still worth having — which is what this |
| 18 | // file is. |
| 19 | // |
| 20 | // excelize (github.com/xuri/excelize/v2, already in go.mod for XLSX |
| 21 | // import/export) ships its own formula engine, CalcCellValue — a |
| 22 | // different codebase by different authors, independent of ours by |
| 23 | // construction. TestAgainstExcelizeOracle below drives it against every |
| 24 | // non-volatile corpus entry and cross-checks its answer against calc's |
| 25 | // live Eval — not against the corpus's "want" field, which is what |
| 26 | // TestAgainstReferenceCorpus (calc package) already checks and is |
| 27 | // exactly the self-consistency this harness exists to get past. |
| 28 | // |
| 29 | // This lives in internal/sheets/xlsx, not internal/sheets/calc, for one |
| 30 | // mechanical reason and one architectural one: |
| 31 | // - internal/guardstest/excelize_confinement_test.go fails any file outside |
| 32 | // internal/sheets/xlsx (any depth) that imports excelize, with no |
| 33 | // exemption for _test.go files. A file in calc that imported excelize |
| 34 | // — even test-only — would fail that guard. |
| 35 | // - xlsx already imports both excelize and calc (import.go, export.go) |
| 36 | // for exactly the same reason this harness needs both: it is the one |
| 37 | // package whose whole job is bridging excelize's world and ours |
| 38 | // (import.go's package doc: "excelize is confined to this package |
| 39 | // deliberately ... letting any other package import it would couple |
| 40 | // our storage to theirs"). A cross-check between the two engines is |
| 41 | // that same bridging job, just for verification instead of import. |
| 42 | // |
| 43 | // A brand-new internal/oracle package was the other option considered: |
| 44 | // rejected because it would need excelizeHome widened in the guard to |
| 45 | // cover it, for no benefit over reusing the tree that already exists for |
| 46 | // exactly this pairing of dependencies. |
| 47 | package xlsx_test |
| 48 | |
| 49 | import ( |
| 50 | "math" |
| 51 | "strconv" |
| 52 | "testing" |
| 53 | "time" |
| 54 | |
| 55 | "amadan.net/oficina/sheets/internal/sheets/calc" |
| 56 | ) |
| 57 | |
| 58 | // oracleEnv is calc.Env backed by the identical fixed lookup table |
| 59 | // computeOracleAnswer's excelize workbook sets up (oracle_excelize_test.go): |
| 60 | // A1:A3 hold 10/20/30, B1:B3 their English names, C1 deliberately unset. |
| 61 | // Now is pinned rather than wired to the real clock because NOW/TODAY are |
| 62 | // excluded from this comparison anyway (loadOracleExclusions) — nothing |
| 63 | // here ever reads it for a real answer, but calc.Env requires it. |
| 64 | type oracleEnv map[calc.CellKey]calc.Value |
| 65 | |
| 66 | func (e oracleEnv) CellValue(k calc.CellKey) calc.Value { return e[k] } |
| 67 | func (e oracleEnv) Now() time.Time { return time.Date(2026, 8, 30, 12, 0, 0, 0, time.UTC) } |
| 68 | |
| 69 | var oracleCellEnv = oracleEnv{ |
| 70 | {RowID: 1, ColID: 1}: {Kind: calc.KindNum, Num: 10}, |
| 71 | {RowID: 1, ColID: 2}: {Kind: calc.KindStr, Str: "ten"}, |
| 72 | {RowID: 2, ColID: 1}: {Kind: calc.KindNum, Num: 20}, |
| 73 | {RowID: 2, ColID: 2}: {Kind: calc.KindStr, Str: "twenty"}, |
| 74 | {RowID: 3, ColID: 1}: {Kind: calc.KindNum, Num: 30}, |
| 75 | {RowID: 3, ColID: 2}: {Kind: calc.KindStr, Str: "thirty"}, |
| 76 | } |
| 77 | |
| 78 | // renderCalcValue renders a calc.Value the same way an excelize |
| 79 | // CalcCellValue answer would read as text, so the two sides of the |
| 80 | // comparison — and oracleDivergences' recorded strings — are directly |
| 81 | // comparable and directly printable. |
| 82 | func renderCalcValue(v calc.Value) string { |
| 83 | switch v.Kind { |
| 84 | case calc.KindNum: |
| 85 | return strconv.FormatFloat(v.Num, 'g', -1, 64) |
| 86 | case calc.KindStr: |
| 87 | return v.Str |
| 88 | case calc.KindBool: |
| 89 | if v.Bool { |
| 90 | return "TRUE" |
| 91 | } |
| 92 | return "FALSE" |
| 93 | case calc.KindErr: |
| 94 | return v.Err.String() |
| 95 | default: // calc.KindEmpty |
| 96 | return "" |
| 97 | } |
| 98 | } |
| 99 | |
| 100 | // agreesWithOracle compares our engine's Value against excelize's raw |
| 101 | // answer string. Numeric comparison tolerates a small epsilon — matching |
| 102 | // calc's own corpus_test.go valuesMatch — because excelize's |
| 103 | // CalcCellValue renders floats to fewer significant digits than Go's |
| 104 | // full round-trip precision (observed directly: SQRT(2) comes back as |
| 105 | // "1.4142135623731", 13 significant digits, not |
| 106 | // "1.4142135623730951"), which is a rendering difference, not a |
| 107 | // disagreement about the value. |
| 108 | func agreesWithOracle(our calc.Value, oracleVal string) bool { |
| 109 | switch our.Kind { |
| 110 | case calc.KindNum: |
| 111 | f, err := strconv.ParseFloat(oracleVal, 64) |
| 112 | if err != nil { |
| 113 | return false |
| 114 | } |
| 115 | return f == our.Num || math.Abs(f-our.Num) < 1e-9 |
| 116 | case calc.KindBool: |
| 117 | want := "FALSE" |
| 118 | if our.Bool { |
| 119 | want = "TRUE" |
| 120 | } |
| 121 | return oracleVal == want |
| 122 | case calc.KindErr: |
| 123 | return oracleVal == our.Err.String() |
| 124 | case calc.KindStr: |
| 125 | return oracleVal == our.Str |
| 126 | default: // calc.KindEmpty |
| 127 | return oracleVal == "" |
| 128 | } |
| 129 | } |
| 130 | |
| 131 | // mustParseOracle parses expr with calc.Parse, failing the test on a |
| 132 | // parse error — every corpus expression is already known-parseable |
| 133 | // (TestAgainstReferenceCorpus parses the same file), so a failure here |
| 134 | // means the corpus and this harness have drifted apart. |
| 135 | func mustParseOracle(t *testing.T, expr string) calc.Node { |
| 136 | t.Helper() |
| 137 | n, err := calc.Parse(expr) |
| 138 | if err != nil { |
| 139 | t.Fatalf("mustParseOracle(%q): %v", expr, err) |
| 140 | } |
| 141 | return n |
| 142 | } |
| 143 | |
| 144 | // TestAgainstExcelizeOracle is the cross-check itself. For every |
| 145 | // non-volatile corpus entry it evaluates the SAME expression with calc's |
| 146 | // own engine and with excelize's, and requires them to agree UNLESS the |
| 147 | // exact disagreement is already reviewed in oracleDivergences. |
| 148 | // |
| 149 | // Three outcomes per entry, all counted and none silently dropped: |
| 150 | // - agree: the common case. |
| 151 | // - known divergence: logged with its reasoned note, not a failure — |
| 152 | // but the recorded strings are checked against what both engines |
| 153 | // currently say, so the record cannot rot out from under a later |
| 154 | // change to either engine. |
| 155 | // - unavailable: excelize could not produce a real answer at all |
| 156 | // (unimplemented function, parse rejection) — logged explicitly as |
| 157 | // its own category, never merged into "agree" or silently skipped. |
| 158 | // |
| 159 | // Anything else — a disagreement with no matching entry in |
| 160 | // oracleDivergences — fails the test outright. |
| 161 | func TestAgainstExcelizeOracle(t *testing.T) { |
| 162 | entries := loadOracleCorpus(t) |
| 163 | excluded := loadOracleExclusions(t) |
| 164 | known := indexOracleDivergences(t) |
| 165 | |
| 166 | var agree, unavailable, knownDivergent, compared int |
| 167 | for _, e := range entries { |
| 168 | if excluded[e.Function] { |
| 169 | continue |
| 170 | } |
| 171 | compared++ |
| 172 | |
| 173 | ourVal := calc.Eval(mustParseOracle(t, e.Expr), oracleCellEnv) |
| 174 | ourStr := renderCalcValue(ourVal) |
| 175 | |
| 176 | oa := computeOracleAnswer(t, e.Expr) |
| 177 | if !oa.Available { |
| 178 | unavailable++ |
| 179 | t.Logf("ORACLE UNAVAILABLE: %s %q: %s", e.Function, e.Expr, oa.Reason) |
| 180 | continue |
| 181 | } |
| 182 | |
| 183 | if agreesWithOracle(ourVal, oa.Value) { |
| 184 | agree++ |
| 185 | continue |
| 186 | } |
| 187 | |
| 188 | d, isKnown := known[e.Expr] |
| 189 | if !isKnown { |
| 190 | t.Errorf("NEW DISAGREEMENT: %s %q: ours=%q excelize=%q — not in oracleDivergences (oracle_divergences_test.go); investigate before adding it there, do not assume we are wrong", e.Function, e.Expr, ourStr, oa.Value) |
| 191 | continue |
| 192 | } |
| 193 | if d.OurAnswer != ourStr || d.OracleAnswer != oa.Value { |
| 194 | t.Errorf("STALE DIVERGENCE RECORD: %s %q: recorded ours=%q/excelize=%q, now ours=%q/excelize=%q — update oracle_divergences_test.go (either the disagreement is gone, or it changed shape and needs re-review)", e.Function, e.Expr, d.OurAnswer, d.OracleAnswer, ourStr, oa.Value) |
| 195 | continue |
| 196 | } |
| 197 | knownDivergent++ |
| 198 | t.Logf("KNOWN DIVERGENCE: %s %q: ours=%q excelize=%q — %s", e.Function, e.Expr, ourStr, oa.Value, d.Note) |
| 199 | } |
| 200 | |
| 201 | t.Logf("oracle cross-check: %d entries compared (%d volatile excluded), agree=%d known-divergence=%d unavailable=%d", |
| 202 | compared, len(entries)-compared, agree, knownDivergent, unavailable) |
| 203 | } |
| 204 | |
| 205 | // indexOracleDivergences keys oracleDivergences by Expr and fails fast |
| 206 | // on a duplicate key — two records for the same expression is itself a |
| 207 | // list-rot bug (which one is authoritative?), not a valid state. |
| 208 | func indexOracleDivergences(t *testing.T) map[string]oracleDivergence { |
| 209 | t.Helper() |
| 210 | out := make(map[string]oracleDivergence, len(oracleDivergences)) |
| 211 | for _, d := range oracleDivergences { |
| 212 | if _, dup := out[d.Expr]; dup { |
| 213 | t.Fatalf("oracleDivergences: duplicate entry for expr %q", d.Expr) |
| 214 | } |
| 215 | out[d.Expr] = d |
| 216 | } |
| 217 | return out |
| 218 | } |
| 219 | |
| 220 | // TestOracleDivergencesMatchCorpus fails on an oracleDivergences entry |
| 221 | // whose Expr does not match any current corpus entry — an orphaned |
| 222 | // divergence record (the corpus changed under it, or it was mistyped) is |
| 223 | // the same kind of silent rot TestExclusionsAreRealFunctions |
| 224 | // (calc/corpus_test.go) guards against for the exclusion list. |
| 225 | func TestOracleDivergencesMatchCorpus(t *testing.T) { |
| 226 | entries := loadOracleCorpus(t) |
| 227 | exprs := make(map[string]bool, len(entries)) |
| 228 | for _, e := range entries { |
| 229 | exprs[e.Expr] = true |
| 230 | } |
| 231 | for _, d := range oracleDivergences { |
| 232 | if !exprs[d.Expr] { |
| 233 | t.Errorf("oracleDivergences: %q does not match any corpus entry — stale record", d.Expr) |
| 234 | } |
| 235 | } |
| 236 | } |
| 237 | |
amadan