| 1 | package main |
| 2 | |
| 3 | // cases.go is the case list and nothing else. |
| 4 | // |
| 5 | // Split out of main.go when that file reached the 300-line cap, and |
| 6 | // along the right seam: this file is DATA — the formulas the corpus |
| 7 | // asks LibreOffice about — and main.go is the program that asks. The |
| 8 | // list grows every time a function family lands, so it is the half that |
| 9 | // would otherwise push the cap again on the next slice. |
| 10 | |
| 11 | // cases is the full case list, in the same order and with the same cell |
| 12 | // references as testdata/corpus/formulas.json — so a run replaces the |
| 13 | // file's "want" values and nothing else, and the diff is purely a |
| 14 | // statement about what LibreOffice computed. |
| 15 | // |
| 16 | // The cell references matter. Ten cases read real cells |
| 17 | // (VLOOKUP/INDEX/MATCH, and the three reading a deliberately blank |
| 18 | // cell), and they use A1:B3 with C1 blank — exactly the table |
| 19 | // calc/corpus_test.go's corpusEnv and xlsx/oracle_excelize_test.go's |
| 20 | // newOracleWorkbook set up. That is why writeODS puts each row's own |
| 21 | // formula in column D rather than column A: column A is support data |
| 22 | // here, not case data. An earlier version of this list put the table at |
| 23 | // B1:C3 and the formulas in column A, which would have produced a corpus |
| 24 | // whose lookup entries no consuming test could evaluate — corpusEnv's |
| 25 | // B1:B3 are the English names, not the numbers, so every generated |
| 26 | // lookup row would have failed against the harness meant to read it. |
| 27 | // |
| 28 | // A prior version of this list also stopped at 41, silently dropping 9 |
| 29 | // plain scalar cases with no structural excuse plus the entire |
| 30 | // VLOOKUP/INDEX/MATCH family and three more cases that read a genuinely |
| 31 | // blank cell. Caught by code review, not by this tool running |
| 32 | // successfully, since nothing here checked its own output size against |
| 33 | // the file it was about to overwrite; main now does. |
| 34 | // |
| 35 | // Keep this list at parity with testdata/corpus/formulas.json whenever a |
| 36 | // new function family is added, so a real gencorpus run always produces |
| 37 | // a corpus at least as large as the one it replaces. |
| 38 | var cases = []corpusCase{ |
| 39 | {"SUM", "SUM(1,2,3)"}, |
| 40 | {"SUM", "SUM(1,2,3,4,5)"}, |
| 41 | {"SUM", "SUM(-5,5)"}, |
| 42 | {"SUM", "SUM(1.5,2.5)"}, |
| 43 | {"AVERAGE", "AVERAGE(2,4,6)"}, |
| 44 | {"AVERAGE", "AVERAGE(1,2,3,4)"}, |
| 45 | {"COUNT", `COUNT(1,2,"a",TRUE)`}, |
| 46 | {"COUNTA", `COUNTA(1,2,"a",TRUE)`}, |
| 47 | {"MIN", "MIN(4,2,9,1)"}, |
| 48 | {"MAX", "MAX(4,2,9,1)"}, |
| 49 | {"ROUND", "ROUND(3.14159,2)"}, |
| 50 | {"ROUND", "ROUND(2.5,0)"}, |
| 51 | {"ROUND", "ROUND(-2.5,0)"}, |
| 52 | {"ROUNDUP", "ROUNDUP(3.14159,2)"}, |
| 53 | {"ROUNDUP", "ROUNDUP(3.001,0)"}, |
| 54 | {"ROUNDDOWN", "ROUNDDOWN(3.99,0)"}, |
| 55 | {"ROUNDDOWN", "ROUNDDOWN(-3.99,0)"}, |
| 56 | {"ABS", "ABS(-7)"}, |
| 57 | {"ABS", "ABS(7)"}, |
| 58 | {"SQRT", "SQRT(16)"}, |
| 59 | {"SQRT", "SQRT(2)"}, |
| 60 | {"POWER", "POWER(2,10)"}, |
| 61 | {"POWER", "POWER(9,0.5)"}, |
| 62 | {"MOD", "MOD(10,3)"}, |
| 63 | {"MOD", "MOD(-10,3)"}, |
| 64 | {"MOD", "MOD(10,-3)"}, |
| 65 | {"IF", `IF(TRUE,"yes","no")`}, |
| 66 | {"IF", `IF(FALSE,"yes","no")`}, |
| 67 | {"IF", `IF(FALSE,1/0,5)`}, |
| 68 | {"IFS", "IFS(FALSE,1,TRUE,2,TRUE,3)"}, |
| 69 | {"AND", "AND(TRUE,TRUE,TRUE)"}, |
| 70 | {"AND", "AND(TRUE,FALSE)"}, |
| 71 | {"OR", "OR(FALSE,FALSE,TRUE)"}, |
| 72 | {"OR", "OR(FALSE,FALSE)"}, |
| 73 | {"NOT", "NOT(TRUE)"}, |
| 74 | {"NOT", "NOT(FALSE)"}, |
| 75 | {"IFERROR", `IFERROR(1/0,"fallback")`}, |
| 76 | {"IFERROR", `IFERROR(5,"fallback")`}, |
| 77 | {"CONCAT", `CONCAT("foo","bar")`}, |
| 78 | {"CONCAT", `CONCAT("a",1,"b")`}, |
| 79 | {"LEFT", `LEFT("hello",3)`}, |
| 80 | {"LEFT", `LEFT("hello")`}, |
| 81 | {"RIGHT", `RIGHT("hello",3)`}, |
| 82 | {"RIGHT", `RIGHT("hello")`}, |
| 83 | {"MID", `MID("hello",2,3)`}, |
| 84 | {"LEN", `LEN("hello")`}, |
| 85 | {"LEN", `LEN("")`}, |
| 86 | {"TRIM", `TRIM(" a b ")`}, |
| 87 | {"UPPER", `UPPER("MixedCase")`}, |
| 88 | {"LOWER", `LOWER("MixedCase")`}, |
| 89 | {"VLOOKUP", "VLOOKUP(20,A1:B3,2,FALSE)"}, |
| 90 | {"VLOOKUP", "VLOOKUP(25,A1:B3,2,TRUE)"}, |
| 91 | {"VLOOKUP", "VLOOKUP(99,A1:B3,2,FALSE)"}, |
| 92 | {"INDEX", "INDEX(A1:B3,2,2)"}, |
| 93 | {"INDEX", "INDEX(A1:B3,3,1)"}, |
| 94 | {"MATCH", "MATCH(20,A1:A3,0)"}, |
| 95 | {"MATCH", "MATCH(25,A1:A3,1)"}, |
| 96 | {"COUNT", "COUNT(A1,C1)"}, |
| 97 | {"COUNTA", "COUNTA(A1,C1)"}, |
| 98 | {"AVERAGE", "AVERAGE(A1,A2,A3)"}, |
| 99 | |
| 100 | // The conditional aggregates. A1:A3 hold 10/20/30 and B1:B3 their |
| 101 | // English names, so every case below is answered by the same small |
| 102 | // table the lookup family already uses — no new support data, and |
| 103 | // the cases stay readable next to their expected answers. |
| 104 | // |
| 105 | // The wildcard cases are back, and the story is worth keeping. |
| 106 | // |
| 107 | // They were removed on 2026-09-03 because LibreOffice answered 0 to |
| 108 | // every one of them, which was read as "its profile does not do |
| 109 | // Excel wildcards, so it cannot be the oracle here". The |
| 110 | // measurement was right and the diagnosis was wrong: wildcards are |
| 111 | // a per-DOCUMENT calculation setting, and ods.go was writing a file |
| 112 | // with no <table:calculation-settings> at all, so LibreOffice fell |
| 113 | // back to ODF's legacy default — regular expressions on, wildcards |
| 114 | // off — where "t*" is the regex t* and matches nothing in "ten". |
| 115 | // The generator was asking the wrong question and getting a correct |
| 116 | // answer to it. One element in ods.go, and the same binary gives |
| 117 | // Excel's answers. Caught by review, 2026-09-04. |
| 118 | // |
| 119 | // The lesson is the general one: when an oracle disagrees, check |
| 120 | // what you actually asked it before deciding it is unfit. |
| 121 | {"SUMIF", `SUMIF(A1:A3,">15")`}, |
| 122 | {"SUMIF", `SUMIF(A1:A3,">=20")`}, |
| 123 | {"SUMIF", `SUMIF(B1:B3,"ten",A1:A3)`}, |
| 124 | {"SUMIF", `SUMIF(B1:B3,"t*",A1:A3)`}, |
| 125 | {"COUNTIF", `COUNTIF(B1:B3,"t*")`}, |
| 126 | {"COUNTIF", `COUNTIF(B1:B3,"t??")`}, |
| 127 | {"COUNTIF", `COUNTIF(B1:B3,"t?n")`}, |
| 128 | {"COUNTIF", `COUNTIF(B1:B3,"*y")`}, |
| 129 | {"SUMIFS", `SUMIFS(A1:A3,B1:B3,"t*")`}, |
| 130 | {"COUNTIFS", `COUNTIFS(A1:A3,">=20",B1:B3,"t*")`}, |
| 131 | {"COUNTIF", `COUNTIF(A1:A3,">15")`}, |
| 132 | {"COUNTIF", `COUNTIF(A1:A3,"<>20")`}, |
| 133 | {"AVERAGEIF", `AVERAGEIF(A1:A3,">15")`}, |
| 134 | {"AVERAGEIF", `AVERAGEIF(B1:B3,"<>ten",A1:A3)`}, |
| 135 | {"SUMIFS", `SUMIFS(A1:A3,A1:A3,">10",A1:A3,"<30")`}, |
| 136 | {"COUNTIFS", `COUNTIFS(A1:A3,">100")`}, |
| 137 | {"AVERAGEIFS", `AVERAGEIFS(A1:A3,B1:B3,"<>ten")`}, |
| 138 | |
| 139 | // A numeric criterion over a range that is NOT all numbers. Added |
| 140 | // 2026-09-04 after review: the first version of the matcher said |
| 141 | // "only <> is true of a cell that cannot be compared numerically", |
| 142 | // which counts every blank as not-equal-to-20. C1:C3 are blank in |
| 143 | // this support table and B1:B3 are text, so A1:C3 is the range that |
| 144 | // separates the three cases, and every earlier case used A1:A3 |
| 145 | // where all three cells are numbers and nothing could disagree. |
| 146 | // |
| 147 | // excelize cannot settle these: asked the same question two ways it |
| 148 | // answered 5 for A1:C3 (text counted, blanks not) and 0 for B1:B3 |
| 149 | // (the same text, not counted). LibreOffice is the oracle here. |
| 150 | {"COUNTIF", `COUNTIF(A1:C3,"<>20")`}, |
| 151 | {"COUNTIF", `COUNTIF(B1:B3,"<>20")`}, |
| 152 | {"COUNTIF", `COUNTIF(A1:C3,">5")`}, |
| 153 | {"COUNTIF", `COUNTIF(A1:C3,"<>")`}, |
| 154 | {"COUNTIF", `COUNTIF(A1:C3,"=")`}, |
| 155 | {"SUMIF", `SUMIF(A1:C3,"<>20")`}, |
| 156 | {"AVERAGEIF", `AVERAGEIF(A1:C3,">5")`}, |
| 157 | {"COUNTIF", `COUNTIF(A1:C3,"ten")`}, |
| 158 | |
| 159 | // Mismatched range SHAPES are deliberately NOT here. A1:A3 is 3x1 |
| 160 | // and A1:C1 is 1x3, and LibreOffice answers Err:502 — a refusal, |
| 161 | // which is the right answer and which this generator cannot record |
| 162 | // (it treats an error as a sign the ODF translation is broken, and |
| 163 | // that check is worth more than this one case). Pinned instead as a |
| 164 | // direct assertion in calc/fn_cond_test.go, with excelize's #VALUE! |
| 165 | // as the cross-check. |
| 166 | |
| 167 | // The IS* predicates. C1 is deliberately blank in the support data, |
| 168 | // which is what makes the ISBLANK pair meaningful: one over a real |
| 169 | // blank and one over a cell holding a number. |
| 170 | {"ISBLANK", "ISBLANK(C1)"}, |
| 171 | {"ISBLANK", "ISBLANK(A1)"}, |
| 172 | {"ISNUMBER", "ISNUMBER(A1)"}, |
| 173 | {"ISNUMBER", "ISNUMBER(B1)"}, |
| 174 | {"ISTEXT", "ISTEXT(B1)"}, |
| 175 | {"ISTEXT", "ISTEXT(A1)"}, |
| 176 | {"ISERROR", "ISERROR(1/0)"}, |
| 177 | {"ISERROR", "ISERROR(A1)"}, |
| 178 | |
| 179 | // The date parts. Every case wraps its result in a plain arithmetic |
| 180 | // or comparison so LibreOffice exports a NUMBER rather than a |
| 181 | // formatted date string: DATE() alone comes back through |
| 182 | // `--convert-to csv` as whatever the locale's date format renders, |
| 183 | // which is a string this corpus cannot compare against a serial. |
| 184 | // YEAR/MONTH/DAY already return plain numbers. |
| 185 | {"YEAR", "YEAR(DATE(2026,9,3))"}, |
| 186 | {"MONTH", "MONTH(DATE(2026,9,3))"}, |
| 187 | {"DAY", "DAY(DATE(2026,9,3))"}, |
| 188 | {"MONTH", "MONTH(DATE(2026,13,1))"}, |
| 189 | {"YEAR", "YEAR(DATE(2026,13,1))"}, |
| 190 | {"DAY", "DAY(DATE(2026,3,0))"}, |
| 191 | {"MONTH", "MONTH(EOMONTH(DATE(2026,1,31),1))"}, |
| 192 | {"DAY", "DAY(EOMONTH(DATE(2026,1,31),1))"}, |
| 193 | {"DAY", "DAY(EOMONTH(DATE(2024,1,15),1))"}, |
| 194 | {"YEAR", "YEAR(EOMONTH(DATE(2026,12,1),1))"}, |
| 195 | |
| 196 | // VALUE, SUBSTITUTE, FIND and SEARCH. FIND and SEARCH differ in |
| 197 | // exactly two ways and both are easy to get backwards, so each has |
| 198 | // a case that separates them: "T" against "ten" is 1 for SEARCH |
| 199 | // (case-insensitive) and not found for FIND, and the wildcard case |
| 200 | // only SEARCH accepts. |
| 201 | {"VALUE", `VALUE("42")`}, |
| 202 | {"VALUE", `VALUE("3.5")`}, |
| 203 | {"VALUE", `VALUE("50%")`}, |
| 204 | {"SUBSTITUTE", `SUBSTITUTE("a-b-c","-","+")`}, |
| 205 | {"SUBSTITUTE", `SUBSTITUTE("a-b-c","-","+",2)`}, |
| 206 | {"SUBSTITUTE", `SUBSTITUTE("aaa","a","b",3)`}, |
| 207 | {"FIND", `FIND("n","ten")`}, |
| 208 | {"FIND", `FIND("e","seventeen",4)`}, |
| 209 | {"SEARCH", `SEARCH("T","ten")`}, |
| 210 | {"SEARCH", `SEARCH("e?e","seventeen")`}, |
| 211 | {"LEN", `LEN(SUBSTITUTE("a-b-c","-",""))`}, |
| 212 | } |
| 213 | |
amadan