| 1 | package xlsx_test |
| 2 | |
| 3 | // oracleDivergence records one reviewed disagreement between this |
| 4 | // package's own formula engine (internal/sheets/calc) and excelize's |
| 5 | // independent one, for one exact corpus expression. This is NOT a |
| 6 | // filter — TestAgainstExcelizeOracle (oracle_test.go) does not consult |
| 7 | // this list to decide whether to run a comparison, only to decide |
| 8 | // whether an already-found disagreement is one a human has already |
| 9 | // looked at. A new disagreement not listed here fails the test; nothing |
| 10 | // here can silently widen to cover one nobody has actually reviewed. |
| 11 | // |
| 12 | // OurAnswer and OracleAnswer are the exact strings the two engines |
| 13 | // produced at the time this entry was written (calc's Value rendered by |
| 14 | // renderCalcValue, excelize's raw CalcCellValue string) — the test |
| 15 | // re-checks both engines' CURRENT answers against these on every run |
| 16 | // (see the "stale divergence record" branch in TestAgainstExcelizeOracle) |
| 17 | // so a later change to either engine that resolves — or changes the |
| 18 | // shape of — a listed divergence cannot pass silently: either the |
| 19 | // disagreement is gone (fix the entry by deleting it) or it has |
| 20 | // changed shape (fix the entry to match, and re-review Note). |
| 21 | type oracleDivergence struct { |
| 22 | Expr string |
| 23 | OurAnswer string |
| 24 | OracleAnswer string |
| 25 | Note string // one line: which engine we believe, and why |
| 26 | } |
| 27 | |
| 28 | // oracleDivergences is the full, currently-reviewed list. As of this |
| 29 | // harness's first run against all 60 corpus entries (30-ish functions), |
| 30 | // exactly one disagreement was found — see the entry below for the |
| 31 | // investigation. That is a real finding, not a placeholder: 59 of 60 |
| 32 | // entries have excelize, an entirely independent formula engine, |
| 33 | // agreeing with ours. |
| 34 | // |
| 35 | // One disagreement deliberately NOT listed here, because it is not |
| 36 | // about a corpus entry: SUM(1,2,"a",TRUE). Ours returns #VALUE!, |
| 37 | // LibreOffice 26.8.0.3 returns #VALUE!, and excelize returns 4 — |
| 38 | // excelize silently skips a text constant SUM cannot coerce, where |
| 39 | // Excel treats a literal text argument passed DIRECTLY to SUM as a type |
| 40 | // error (text reached through a range reference is the case that is |
| 41 | // ignored). Measured 2026-08-31, not assumed. It is absent from the |
| 42 | // corpus, so this harness never compares it; adding it as a case would |
| 43 | // make it a listed divergence with excelize on the wrong side. |
| 44 | var oracleDivergences = []oracleDivergence{ |
| 45 | { |
| 46 | Expr: `COUNTIF(A1:C3,"<>20")`, |
| 47 | OurAnswer: "8", |
| 48 | OracleAnswer: "5", |
| 49 | // Investigated 2026-09-04, and excelize is the one that is |
| 50 | // wrong — provably, because it contradicts ITSELF. A1:C3 holds |
| 51 | // three numbers (10, 20, 30), three text cells and three |
| 52 | // blanks. excelize answers 5 here, which is three text plus two |
| 53 | // numbers: it counted the text and skipped the blanks. Asked |
| 54 | // about the SAME three text cells alone, as |
| 55 | // COUNTIF(B1:B3,"<>20") — the entry below — it answers 0. Text |
| 56 | // cannot both satisfy "<>20" and not satisfy it. |
| 57 | // |
| 58 | // LibreOffice 26.8.0.3 answers 8 here and 3 there, which is one |
| 59 | // consistent rule: a cell not equal to 20 is counted, and a |
| 60 | // blank is not equal to 20. Ours agrees, and the corpus pins it. |
| 61 | // |
| 62 | // Raised by the branch review as "a numeric <> criterion matches |
| 63 | // blanks and all text". That is what the code does and what the |
| 64 | // oracle we trust says it should do, so it is recorded rather |
| 65 | // than changed — changing it would make the corpus red in order |
| 66 | // to make excelize green. |
| 67 | Note: "excelize contradicts itself on text under a numeric <>; LibreOffice and we are consistent", |
| 68 | }, |
| 69 | { |
| 70 | Expr: `COUNTIF(B1:B3,"<>20")`, |
| 71 | OurAnswer: "3", |
| 72 | OracleAnswer: "0", |
| 73 | // The other half of the pair above, and the half that shows |
| 74 | // which engine is inconsistent: three text cells, none equal to |
| 75 | // 20. LibreOffice says 3; excelize says 0 while counting those |
| 76 | // same three cells inside A1:C3. |
| 77 | Note: "the same three text cells excelize counts inside A1:C3; see the entry above", |
| 78 | }, |
| 79 | { |
| 80 | Expr: `TRIM(" a b ")`, |
| 81 | OurAnswer: "a b", |
| 82 | OracleAnswer: "a b", |
| 83 | // Investigated 2026-08-30: real Excel's TRIM "removes all spaces |
| 84 | // from text except for single spaces between words" (Microsoft's |
| 85 | // own documented behavior) -- i.e. it collapses interior runs of |
| 86 | // whitespace to one space, not just leading/trailing. Our fnTrim |
| 87 | // (fn_text.go) implements exactly that via strings.Fields, which |
| 88 | // is what produces "a b" here. |
| 89 | // |
| 90 | // excelize v2.11.0's TRIM (calc.go, the TRIM function) is |
| 91 | // `newStringFormulaArg(strings.TrimSpace(...))` -- leading/ |
| 92 | // trailing only, no interior collapse -- even though its OWN doc |
| 93 | // comment two lines above claims the correct behavior ("removes |
| 94 | // extra spaces ... except for single spaces between words"). Read |
| 95 | // directly from |
| 96 | // $(go env GOMODCACHE)/github.com/xuri/excelize/v2@v2.11.0/calc.go. |
| 97 | // This is excelize's implementation contradicting its own |
| 98 | // documented contract, not a legitimate alternate reading of |
| 99 | // TRIM's semantics. |
| 100 | // |
| 101 | // Corroborated 2026-08-31 by a THIRD engine: the corpus is now |
| 102 | // generated from a real LibreOffice 26.8.0.3 headless run, and |
| 103 | // Calc returns "a b" for this same expression -- the same answer |
| 104 | // ours gives. So two independent implementations and Microsoft's |
| 105 | // own documentation agree, and excelize alone does not. |
| 106 | // |
| 107 | // We believe OUR engine here: it matches real Excel's documented |
| 108 | // and observable behavior; excelize's TRIM does not match its own |
| 109 | // doc comment, let alone Excel's. |
| 110 | Note: "excelize v2.11.0's TRIM is strings.TrimSpace only (does not collapse interior whitespace runs), contradicting both its own doc comment and real Excel's documented TRIM behavior. Ours is correct, and LibreOffice 26.8.0.3 independently agrees with ours.", |
| 111 | }, |
| 112 | } |
| 113 | |
amadan