| 1 | package calc |
| 2 | |
| 3 | import "testing" |
| 4 | |
| 5 | // condEnv is corpus_test.go's table: A1:A3 hold 10/20/30, B1:B3 their |
| 6 | // English names, C1:C3 blank. |
| 7 | var condEnv = mapEnv{ |
| 8 | {1, 1}: {Kind: KindNum, Num: 10}, {1, 2}: {Kind: KindStr, Str: "ten"}, |
| 9 | {2, 1}: {Kind: KindNum, Num: 20}, {2, 2}: {Kind: KindStr, Str: "twenty"}, |
| 10 | {3, 1}: {Kind: KindNum, Num: 30}, {3, 2}: {Kind: KindStr, Str: "thirty"}, |
| 11 | } |
| 12 | |
| 13 | // TestMismatchedRangeShapesAreRefused. |
| 14 | // |
| 15 | // A1:A3 is three rows by one column; A1:C1 is one row by three columns. |
| 16 | // They have the same CELL COUNT, and the first version of readPairs |
| 17 | // compared exactly that — so the pair was accepted and offsetRect |
| 18 | // quietly reshaped one onto the other, reading cells nobody named and |
| 19 | // returning a plausible number. |
| 20 | // |
| 21 | // Not a corpus case, and that is not an omission: LibreOffice answers |
| 22 | // Err:502 — a refusal, which is the right answer — and gencorpus |
| 23 | // treats an error as a sign its own ODF translation is broken rather |
| 24 | // than as a value to record. That check is worth more than this one |
| 25 | // case, so this is pinned here instead. excelize agrees with the |
| 26 | // refusal (#VALUE!), which is the independent half. |
| 27 | func TestMismatchedRangeShapesAreRefused(t *testing.T) { |
| 28 | for _, expr := range []string{ |
| 29 | `COUNTIFS(A1:A3,">5",A1:C1,"<>")`, |
| 30 | `SUMIFS(A1:A3,A1:A3,">5",A1:C1,"<>")`, |
| 31 | `AVERAGEIFS(A1:A3,A1:A3,">5",A1:C1,"<>")`, |
| 32 | } { |
| 33 | n, err := Parse(expr) |
| 34 | if err != nil { |
| 35 | t.Fatalf("%s: %v", expr, err) |
| 36 | } |
| 37 | got := Eval(n, condEnv) |
| 38 | if got.Kind != KindErr || got.Err != ErrValue { |
| 39 | t.Errorf("%s = %+v, want #VALUE! — two ranges of the same area and "+ |
| 40 | "different shapes must be refused, not silently reshaped onto "+ |
| 41 | "each other", expr, got) |
| 42 | } |
| 43 | } |
| 44 | } |
| 45 | |
| 46 | // TestMatchingShapesAreStillAccepted is the other side, and the reason |
| 47 | // the check is width-and-height rather than "the two ranges must be |
| 48 | // written identically": Excel pairs by OFFSET, so ranges of the same |
| 49 | // shape at different origins are the ordinary case. |
| 50 | func TestMatchingShapesAreStillAccepted(t *testing.T) { |
| 51 | n, err := Parse(`COUNTIFS(A1:A3,">5",B1:B3,"t*")`) |
| 52 | if err != nil { |
| 53 | t.Fatal(err) |
| 54 | } |
| 55 | got := Eval(n, condEnv) |
| 56 | if got.Kind != KindNum || got.Num != 3 { |
| 57 | t.Fatalf("got %+v, want 3", got) |
| 58 | } |
| 59 | } |
| 60 | |
| 61 | // errRangeEnv is condEnv with A2 replaced by a #DIV/0!. |
| 62 | var errRangeEnv = mapEnv{ |
| 63 | {1, 1}: {Kind: KindNum, Num: 10}, |
| 64 | {2, 1}: {Kind: KindErr, Err: ErrDiv0}, |
| 65 | {3, 1}: {Kind: KindNum, Num: 30}, |
| 66 | } |
| 67 | |
| 68 | // TestAConditionalSumAgreesWithSumAboutErrors. |
| 69 | // |
| 70 | // SUM over a range holding a #DIV/0! answers #DIV/0!. SUMIF over the |
| 71 | // same range answered 40 — it skipped the error along with the text and |
| 72 | // the blanks — which contradicted fn_cond_apply.go's own stated |
| 73 | // invariant that a conditional sum must not disagree with an |
| 74 | // unconditional one. Found by review. |
| 75 | // |
| 76 | // SUM's own behaviour is the reference on purpose: it is corpus- |
| 77 | // validated, and neither oracle can settle this one. excelize does not |
| 78 | // propagate at all (it answers 40 to SUM over this range, disagreeing |
| 79 | // with our validated SUM before SUMIF is reached), and gencorpus |
| 80 | // refuses to record an error as a value. |
| 81 | func TestAConditionalSumAgreesWithSumAboutErrors(t *testing.T) { |
| 82 | base := evalIn(t, `SUM(A1:A3)`, errRangeEnv) |
| 83 | if base.Kind != KindErr { |
| 84 | t.Fatalf("the premise is gone: SUM over a range holding an error = %+v, "+ |
| 85 | "so there is nothing for the conditional forms to agree WITH", base) |
| 86 | } |
| 87 | for _, expr := range []string{ |
| 88 | `SUMIF(A1:A3,">5")`, |
| 89 | `SUMIFS(A1:A3,A1:A3,">5")`, |
| 90 | `AVERAGEIF(A1:A3,">5")`, |
| 91 | `AVERAGEIFS(A1:A3,A1:A3,">5")`, |
| 92 | } { |
| 93 | got := evalIn(t, expr, errRangeEnv) |
| 94 | if got.Kind != KindErr || got.Err != base.Err { |
| 95 | t.Errorf("%s = %+v, want %v — SUM over the same range says %v, and a "+ |
| 96 | "conditional sum that skips an error reports a total nobody can "+ |
| 97 | "tell is missing a row", expr, got, base.Err, base.Err) |
| 98 | } |
| 99 | } |
| 100 | } |
| 101 | |
| 102 | // TestCountingStillCounts is the boundary: COUNTIF counts CELLS, not |
| 103 | // values, so an error in the range is a cell that does not match rather |
| 104 | // than an answer. Without this, the obvious fix for the test above — |
| 105 | // propagate everywhere — would silently change counting too. |
| 106 | func TestCountingStillCounts(t *testing.T) { |
| 107 | got := evalIn(t, `COUNTIF(A1:A3,">5")`, errRangeEnv) |
| 108 | if got.Kind != KindNum || got.Num != 2 { |
| 109 | t.Errorf("COUNTIF over a range holding an error = %+v, want 2", got) |
| 110 | } |
| 111 | } |
| 112 | |
| 113 | func evalIn(t *testing.T, expr string, env Env) Value { |
| 114 | t.Helper() |
| 115 | n, err := Parse(expr) |
| 116 | if err != nil { |
| 117 | t.Fatalf("%s: %v", expr, err) |
| 118 | } |
| 119 | return Eval(n, env) |
| 120 | } |
| 121 | |
| 122 | // fault lines the plain support table cannot reach: an error cell, a |
| 123 | // blank criterion, and a sum range of the wrong shape. |
| 124 | var edgeEnv = mapEnv{ |
| 125 | {1, 1}: {Kind: KindNum, Num: 10}, |
| 126 | {2, 1}: {Kind: KindNum, Num: 20}, |
| 127 | {3, 1}: {Kind: KindErr, Err: ErrDiv0}, |
| 128 | {1, 2}: {Kind: KindStr, Str: "ten"}, |
| 129 | {2, 2}: {Kind: KindStr, Str: "twenty"}, |
| 130 | {3, 2}: {Kind: KindStr, Str: "thirty"}, |
| 131 | // D3 (col 4, row 3) is deliberately absent: a blank criterion cell. |
| 132 | } |
| 133 | |
| 134 | // TestTheCriteriaMatcherOnItsEdges pins three answers the review |
| 135 | // measured against LibreOffice 26.8 and this engine got wrong. |
| 136 | // |
| 137 | // Direct assertions rather than corpus cases, because each needs a |
| 138 | // support cell the shared corpus table does not have — an error, and a |
| 139 | // blank used as a CRITERION rather than as data. Extending that table |
| 140 | // changes the fixture every other case is written against, which is a |
| 141 | // bigger change than these three fixes and is named in the spec's |
| 142 | // deferred table instead. |
| 143 | func TestTheCriteriaMatcherOnItsEdges(t *testing.T) { |
| 144 | for _, tc := range []struct { |
| 145 | expr string |
| 146 | want float64 |
| 147 | why string |
| 148 | }{ |
| 149 | {`COUNTIF(A1:A3,"<>")`, 3, |
| 150 | "an error cell is not blank, so it counts for <> — LibreOffice and Excel agree, " + |
| 151 | "and the first version refused every error for every criterion"}, |
| 152 | {`COUNTIF(A1:A3,"<>20")`, 2, |
| 153 | "10 and the error; 20 is excluded"}, |
| 154 | {`COUNTIF(A1:A3,D3)`, 0, |
| 155 | "a BLANK criterion cell matches nothing, and is not the same request as " + |
| 156 | `COUNTIF(range,"")`}, |
| 157 | } { |
| 158 | got := evalIn(t, tc.expr, edgeEnv) |
| 159 | if got.Kind != KindNum || got.Num != tc.want { |
| 160 | t.Errorf("%s = %+v, want %v — %s", tc.expr, got, tc.want, tc.why) |
| 161 | } |
| 162 | } |
| 163 | } |
| 164 | |
| 165 | // TestASumRangeOfTheWrongShapeIsRefused: SUMIFS(A1:A2,B1:B3,"t*") |
| 166 | // answered 60 by reshaping A1:A2 up to the criteria range's height and |
| 167 | // reading A3 — a cell the formula never named. LibreOffice: Err:502. |
| 168 | // |
| 169 | // The SINGULAR form is asserted alongside it precisely because it must |
| 170 | // NOT be refused: Excel's SUMIF reads its third argument for its origin |
| 171 | // only, and a fix that made both strict would break the documented |
| 172 | // asymmetry while looking tidier. |
| 173 | func TestASumRangeOfTheWrongShapeIsRefused(t *testing.T) { |
| 174 | got := evalIn(t, `SUMIFS(A1:A2,B1:B3,"t*")`, condEnv) |
| 175 | if got.Kind != KindErr || got.Err != ErrValue { |
| 176 | t.Errorf("SUMIFS with a short sum range = %+v, want #VALUE!", got) |
| 177 | } |
| 178 | if got := evalIn(t, `SUMIF(B1:B3,"t*",A1)`, condEnv); got.Kind != KindNum || got.Num != 60 { |
| 179 | t.Errorf("SUMIF(B1:B3,\"t*\",A1) = %+v, want 60 — the singular form reads its "+ |
| 180 | "third argument for its ORIGIN, which is Excel's documented asymmetry", got) |
| 181 | } |
| 182 | } |
| 183 | |
amadan