package main // cases.go is the case list and nothing else. // // Split out of main.go when that file reached the 300-line cap, and // along the right seam: this file is DATA — the formulas the corpus // asks LibreOffice about — and main.go is the program that asks. The // list grows every time a function family lands, so it is the half that // would otherwise push the cap again on the next slice. // cases is the full case list, in the same order and with the same cell // references as testdata/corpus/formulas.json — so a run replaces the // file's "want" values and nothing else, and the diff is purely a // statement about what LibreOffice computed. // // The cell references matter. Ten cases read real cells // (VLOOKUP/INDEX/MATCH, and the three reading a deliberately blank // cell), and they use A1:B3 with C1 blank — exactly the table // calc/corpus_test.go's corpusEnv and xlsx/oracle_excelize_test.go's // newOracleWorkbook set up. That is why writeODS puts each row's own // formula in column D rather than column A: column A is support data // here, not case data. An earlier version of this list put the table at // B1:C3 and the formulas in column A, which would have produced a corpus // whose lookup entries no consuming test could evaluate — corpusEnv's // B1:B3 are the English names, not the numbers, so every generated // lookup row would have failed against the harness meant to read it. // // A prior version of this list also stopped at 41, silently dropping 9 // plain scalar cases with no structural excuse plus the entire // VLOOKUP/INDEX/MATCH family and three more cases that read a genuinely // blank cell. Caught by code review, not by this tool running // successfully, since nothing here checked its own output size against // the file it was about to overwrite; main now does. // // Keep this list at parity with testdata/corpus/formulas.json whenever a // new function family is added, so a real gencorpus run always produces // a corpus at least as large as the one it replaces. var cases = []corpusCase{ {"SUM", "SUM(1,2,3)"}, {"SUM", "SUM(1,2,3,4,5)"}, {"SUM", "SUM(-5,5)"}, {"SUM", "SUM(1.5,2.5)"}, {"AVERAGE", "AVERAGE(2,4,6)"}, {"AVERAGE", "AVERAGE(1,2,3,4)"}, {"COUNT", `COUNT(1,2,"a",TRUE)`}, {"COUNTA", `COUNTA(1,2,"a",TRUE)`}, {"MIN", "MIN(4,2,9,1)"}, {"MAX", "MAX(4,2,9,1)"}, {"ROUND", "ROUND(3.14159,2)"}, {"ROUND", "ROUND(2.5,0)"}, {"ROUND", "ROUND(-2.5,0)"}, {"ROUNDUP", "ROUNDUP(3.14159,2)"}, {"ROUNDUP", "ROUNDUP(3.001,0)"}, {"ROUNDDOWN", "ROUNDDOWN(3.99,0)"}, {"ROUNDDOWN", "ROUNDDOWN(-3.99,0)"}, {"ABS", "ABS(-7)"}, {"ABS", "ABS(7)"}, {"SQRT", "SQRT(16)"}, {"SQRT", "SQRT(2)"}, {"POWER", "POWER(2,10)"}, {"POWER", "POWER(9,0.5)"}, {"MOD", "MOD(10,3)"}, {"MOD", "MOD(-10,3)"}, {"MOD", "MOD(10,-3)"}, {"IF", `IF(TRUE,"yes","no")`}, {"IF", `IF(FALSE,"yes","no")`}, {"IF", `IF(FALSE,1/0,5)`}, {"IFS", "IFS(FALSE,1,TRUE,2,TRUE,3)"}, {"AND", "AND(TRUE,TRUE,TRUE)"}, {"AND", "AND(TRUE,FALSE)"}, {"OR", "OR(FALSE,FALSE,TRUE)"}, {"OR", "OR(FALSE,FALSE)"}, {"NOT", "NOT(TRUE)"}, {"NOT", "NOT(FALSE)"}, {"IFERROR", `IFERROR(1/0,"fallback")`}, {"IFERROR", `IFERROR(5,"fallback")`}, {"CONCAT", `CONCAT("foo","bar")`}, {"CONCAT", `CONCAT("a",1,"b")`}, {"LEFT", `LEFT("hello",3)`}, {"LEFT", `LEFT("hello")`}, {"RIGHT", `RIGHT("hello",3)`}, {"RIGHT", `RIGHT("hello")`}, {"MID", `MID("hello",2,3)`}, {"LEN", `LEN("hello")`}, {"LEN", `LEN("")`}, {"TRIM", `TRIM(" a b ")`}, {"UPPER", `UPPER("MixedCase")`}, {"LOWER", `LOWER("MixedCase")`}, {"VLOOKUP", "VLOOKUP(20,A1:B3,2,FALSE)"}, {"VLOOKUP", "VLOOKUP(25,A1:B3,2,TRUE)"}, {"VLOOKUP", "VLOOKUP(99,A1:B3,2,FALSE)"}, {"INDEX", "INDEX(A1:B3,2,2)"}, {"INDEX", "INDEX(A1:B3,3,1)"}, {"MATCH", "MATCH(20,A1:A3,0)"}, {"MATCH", "MATCH(25,A1:A3,1)"}, {"COUNT", "COUNT(A1,C1)"}, {"COUNTA", "COUNTA(A1,C1)"}, {"AVERAGE", "AVERAGE(A1,A2,A3)"}, // The conditional aggregates. A1:A3 hold 10/20/30 and B1:B3 their // English names, so every case below is answered by the same small // table the lookup family already uses — no new support data, and // the cases stay readable next to their expected answers. // // The wildcard cases are back, and the story is worth keeping. // // They were removed on 2026-09-03 because LibreOffice answered 0 to // every one of them, which was read as "its profile does not do // Excel wildcards, so it cannot be the oracle here". The // measurement was right and the diagnosis was wrong: wildcards are // a per-DOCUMENT calculation setting, and ods.go was writing a file // with no at all, so LibreOffice fell // back to ODF's legacy default — regular expressions on, wildcards // off — where "t*" is the regex t* and matches nothing in "ten". // The generator was asking the wrong question and getting a correct // answer to it. One element in ods.go, and the same binary gives // Excel's answers. Caught by review, 2026-09-04. // // The lesson is the general one: when an oracle disagrees, check // what you actually asked it before deciding it is unfit. {"SUMIF", `SUMIF(A1:A3,">15")`}, {"SUMIF", `SUMIF(A1:A3,">=20")`}, {"SUMIF", `SUMIF(B1:B3,"ten",A1:A3)`}, {"SUMIF", `SUMIF(B1:B3,"t*",A1:A3)`}, {"COUNTIF", `COUNTIF(B1:B3,"t*")`}, {"COUNTIF", `COUNTIF(B1:B3,"t??")`}, {"COUNTIF", `COUNTIF(B1:B3,"t?n")`}, {"COUNTIF", `COUNTIF(B1:B3,"*y")`}, {"SUMIFS", `SUMIFS(A1:A3,B1:B3,"t*")`}, {"COUNTIFS", `COUNTIFS(A1:A3,">=20",B1:B3,"t*")`}, {"COUNTIF", `COUNTIF(A1:A3,">15")`}, {"COUNTIF", `COUNTIF(A1:A3,"<>20")`}, {"AVERAGEIF", `AVERAGEIF(A1:A3,">15")`}, {"AVERAGEIF", `AVERAGEIF(B1:B3,"<>ten",A1:A3)`}, {"SUMIFS", `SUMIFS(A1:A3,A1:A3,">10",A1:A3,"<30")`}, {"COUNTIFS", `COUNTIFS(A1:A3,">100")`}, {"AVERAGEIFS", `AVERAGEIFS(A1:A3,B1:B3,"<>ten")`}, // A numeric criterion over a range that is NOT all numbers. Added // 2026-09-04 after review: the first version of the matcher said // "only <> is true of a cell that cannot be compared numerically", // which counts every blank as not-equal-to-20. C1:C3 are blank in // this support table and B1:B3 are text, so A1:C3 is the range that // separates the three cases, and every earlier case used A1:A3 // where all three cells are numbers and nothing could disagree. // // excelize cannot settle these: asked the same question two ways it // answered 5 for A1:C3 (text counted, blanks not) and 0 for B1:B3 // (the same text, not counted). LibreOffice is the oracle here. {"COUNTIF", `COUNTIF(A1:C3,"<>20")`}, {"COUNTIF", `COUNTIF(B1:B3,"<>20")`}, {"COUNTIF", `COUNTIF(A1:C3,">5")`}, {"COUNTIF", `COUNTIF(A1:C3,"<>")`}, {"COUNTIF", `COUNTIF(A1:C3,"=")`}, {"SUMIF", `SUMIF(A1:C3,"<>20")`}, {"AVERAGEIF", `AVERAGEIF(A1:C3,">5")`}, {"COUNTIF", `COUNTIF(A1:C3,"ten")`}, // Mismatched range SHAPES are deliberately NOT here. A1:A3 is 3x1 // and A1:C1 is 1x3, and LibreOffice answers Err:502 — a refusal, // which is the right answer and which this generator cannot record // (it treats an error as a sign the ODF translation is broken, and // that check is worth more than this one case). Pinned instead as a // direct assertion in calc/fn_cond_test.go, with excelize's #VALUE! // as the cross-check. // The IS* predicates. C1 is deliberately blank in the support data, // which is what makes the ISBLANK pair meaningful: one over a real // blank and one over a cell holding a number. {"ISBLANK", "ISBLANK(C1)"}, {"ISBLANK", "ISBLANK(A1)"}, {"ISNUMBER", "ISNUMBER(A1)"}, {"ISNUMBER", "ISNUMBER(B1)"}, {"ISTEXT", "ISTEXT(B1)"}, {"ISTEXT", "ISTEXT(A1)"}, {"ISERROR", "ISERROR(1/0)"}, {"ISERROR", "ISERROR(A1)"}, // The date parts. Every case wraps its result in a plain arithmetic // or comparison so LibreOffice exports a NUMBER rather than a // formatted date string: DATE() alone comes back through // `--convert-to csv` as whatever the locale's date format renders, // which is a string this corpus cannot compare against a serial. // YEAR/MONTH/DAY already return plain numbers. {"YEAR", "YEAR(DATE(2026,9,3))"}, {"MONTH", "MONTH(DATE(2026,9,3))"}, {"DAY", "DAY(DATE(2026,9,3))"}, {"MONTH", "MONTH(DATE(2026,13,1))"}, {"YEAR", "YEAR(DATE(2026,13,1))"}, {"DAY", "DAY(DATE(2026,3,0))"}, {"MONTH", "MONTH(EOMONTH(DATE(2026,1,31),1))"}, {"DAY", "DAY(EOMONTH(DATE(2026,1,31),1))"}, {"DAY", "DAY(EOMONTH(DATE(2024,1,15),1))"}, {"YEAR", "YEAR(EOMONTH(DATE(2026,12,1),1))"}, // VALUE, SUBSTITUTE, FIND and SEARCH. FIND and SEARCH differ in // exactly two ways and both are easy to get backwards, so each has // a case that separates them: "T" against "ten" is 1 for SEARCH // (case-insensitive) and not found for FIND, and the wildcard case // only SEARCH accepts. {"VALUE", `VALUE("42")`}, {"VALUE", `VALUE("3.5")`}, {"VALUE", `VALUE("50%")`}, {"SUBSTITUTE", `SUBSTITUTE("a-b-c","-","+")`}, {"SUBSTITUTE", `SUBSTITUTE("a-b-c","-","+",2)`}, {"SUBSTITUTE", `SUBSTITUTE("aaa","a","b",3)`}, {"FIND", `FIND("n","ten")`}, {"FIND", `FIND("e","seventeen",4)`}, {"SEARCH", `SEARCH("T","ten")`}, {"SEARCH", `SEARCH("e?e","seventeen")`}, {"LEN", `LEN(SUBSTITUTE("a-b-c","-",""))`}, }