| 1 | package calc |
| 2 | |
| 3 | import "testing" |
| 4 | |
| 5 | // lookupTable is A1:B3 -> {10:"ten", 20:"twenty", 30:"thirty"}, the same |
| 6 | // small table testdata/corpus/formulas.json's VLOOKUP/INDEX/MATCH rows |
| 7 | // use, kept here so unit tests don't depend on the corpus file. |
| 8 | func lookupTable() mapEnv { |
| 9 | return mapEnv{ |
| 10 | {1, 1}: {Kind: KindNum, Num: 10}, {1, 2}: {Kind: KindStr, Str: "ten"}, |
| 11 | {2, 1}: {Kind: KindNum, Num: 20}, {2, 2}: {Kind: KindStr, Str: "twenty"}, |
| 12 | {3, 1}: {Kind: KindNum, Num: 30}, {3, 2}: {Kind: KindStr, Str: "thirty"}, |
| 13 | } |
| 14 | } |
| 15 | |
| 16 | func TestVLookupExact(t *testing.T) { |
| 17 | v := Eval(mustParse(t, "VLOOKUP(20,A1:B3,2,FALSE)"), lookupTable()) |
| 18 | if v.Kind != KindStr || v.Str != "twenty" { |
| 19 | t.Fatalf("VLOOKUP exact = %v, want \"twenty\"", v) |
| 20 | } |
| 21 | } |
| 22 | |
| 23 | func TestVLookupExactNoMatchIsNA(t *testing.T) { |
| 24 | v := Eval(mustParse(t, "VLOOKUP(99,A1:B3,2,FALSE)"), lookupTable()) |
| 25 | if v.Kind != KindErr || v.Err != ErrNA { |
| 26 | t.Fatalf("VLOOKUP no match = %v, want #N/A", v) |
| 27 | } |
| 28 | } |
| 29 | |
| 30 | func TestVLookupApproximate(t *testing.T) { |
| 31 | v := Eval(mustParse(t, "VLOOKUP(25,A1:B3,2,TRUE)"), lookupTable()) |
| 32 | if v.Kind != KindStr || v.Str != "twenty" { |
| 33 | t.Fatalf("VLOOKUP approx = %v, want \"twenty\" (largest <= 25)", v) |
| 34 | } |
| 35 | } |
| 36 | |
| 37 | func TestVLookupColIndexOutOfRangeIsRef(t *testing.T) { |
| 38 | v := Eval(mustParse(t, "VLOOKUP(20,A1:B3,3,FALSE)"), lookupTable()) |
| 39 | if v.Kind != KindErr || v.Err != ErrRef { |
| 40 | t.Fatalf("VLOOKUP col 3 of 2 = %v, want #REF!", v) |
| 41 | } |
| 42 | } |
| 43 | |
| 44 | func TestIndex(t *testing.T) { |
| 45 | v := Eval(mustParse(t, "INDEX(A1:B3,2,2)"), lookupTable()) |
| 46 | if v.Kind != KindStr || v.Str != "twenty" { |
| 47 | t.Fatalf("INDEX(A1:B3,2,2) = %v, want \"twenty\"", v) |
| 48 | } |
| 49 | v = Eval(mustParse(t, "INDEX(A1:B3,3,1)"), lookupTable()) |
| 50 | if v.Kind != KindNum || v.Num != 30 { |
| 51 | t.Fatalf("INDEX(A1:B3,3,1) = %v, want 30", v) |
| 52 | } |
| 53 | } |
| 54 | |
| 55 | func TestIndexOutOfRangeIsRef(t *testing.T) { |
| 56 | v := Eval(mustParse(t, "INDEX(A1:B3,4,1)"), lookupTable()) |
| 57 | if v.Kind != KindErr || v.Err != ErrRef { |
| 58 | t.Fatalf("INDEX row 4 of 3 = %v, want #REF!", v) |
| 59 | } |
| 60 | } |
| 61 | |
| 62 | func TestMatchExact(t *testing.T) { |
| 63 | v := Eval(mustParse(t, "MATCH(20,A1:A3,0)"), lookupTable()) |
| 64 | if v.Kind != KindNum || v.Num != 2 { |
| 65 | t.Fatalf("MATCH exact = %v, want 2", v) |
| 66 | } |
| 67 | } |
| 68 | |
| 69 | func TestMatchNearestAscending(t *testing.T) { |
| 70 | v := Eval(mustParse(t, "MATCH(25,A1:A3,1)"), lookupTable()) |
| 71 | if v.Kind != KindNum || v.Num != 2 { |
| 72 | t.Fatalf("MATCH nearest = %v, want 2", v) |
| 73 | } |
| 74 | } |
| 75 | |
| 76 | func TestMatchTwoDimensionalArrayIsNA(t *testing.T) { |
| 77 | v := Eval(mustParse(t, "MATCH(20,A1:B3,0)"), lookupTable()) |
| 78 | if v.Kind != KindErr || v.Err != ErrNA { |
| 79 | t.Fatalf("MATCH over a 2D range = %v, want #N/A", v) |
| 80 | } |
| 81 | } |
| 82 | |
| 83 | func TestVLookupTableArrayMustBeRange(t *testing.T) { |
| 84 | v := Eval(mustParse(t, "VLOOKUP(1,2,1,FALSE)"), emptyEnv{}) |
| 85 | if v.Kind != KindErr || v.Err != ErrValue { |
| 86 | t.Fatalf("VLOOKUP with scalar table_array = %v, want #VALUE!", v) |
| 87 | } |
| 88 | } |
| 89 | |
| 90 | // range_lookup defaults to TRUE (approximate match) when omitted — one |
| 91 | // of the two "classic trap" defaults a self-authored corpus can't |
| 92 | // validate (nothing forces a corpus author to remember to omit an |
| 93 | // argument), so it needs a direct test rather than relying on a corpus |
| 94 | // row. |
| 95 | func TestVLookupOmittedRangeLookupDefaultsToApproximate(t *testing.T) { |
| 96 | v := Eval(mustParse(t, "VLOOKUP(25,A1:B3,2)"), lookupTable()) |
| 97 | if v.Kind != KindStr || v.Str != "twenty" { |
| 98 | t.Fatalf("VLOOKUP with range_lookup omitted = %v, want \"twenty\" (approximate match by default)", v) |
| 99 | } |
| 100 | } |
| 101 | |
| 102 | // match_type defaults to 1 (largest value <= lookup, ascending) when |
| 103 | // omitted — the second classic trap default. |
| 104 | func TestMatchOmittedMatchTypeDefaultsToNearestAscending(t *testing.T) { |
| 105 | v := Eval(mustParse(t, "MATCH(25,A1:A3)"), lookupTable()) |
| 106 | if v.Kind != KindNum || v.Num != 2 { |
| 107 | t.Fatalf("MATCH with match_type omitted = %v, want 2 (nearest-ascending by default)", v) |
| 108 | } |
| 109 | } |
| 110 | |
| 111 | // pathologicalRange is a range far past maxRangeCells — decodeRef accepts |
| 112 | // any int row, so nothing upstream stops a formula from naming one. The |
| 113 | // lookup family must reject it as #REF! the same way evalCallArg and |
| 114 | // evalAggArgs do for every other function, not iterate r0..r1 raw: that |
| 115 | // raw iteration is both an unbounded-hang risk (the failure mode the |
| 116 | // cycle-detection tests exist to prevent, reached through a different |
| 117 | // door) and a stale-cache hole (collectRefs leaves an over-cap range |
| 118 | // untracked as a precedent, so a formula that reads it directly reads |
| 119 | // cells the graph never wired and won't be dirtied when they change). |
| 120 | func pathologicalRange() *Range { |
| 121 | return &Range{From: &Ref{RowPos: 0, ColPos: 0}, To: &Ref{RowPos: 1 << 40, ColPos: 0}} |
| 122 | } |
| 123 | |
| 124 | // TestVLookupOverCapRangeIsRefNotHang must return #REF! promptly rather |
| 125 | // than hang. It carries the same 2-second timeout the cycle-detection |
| 126 | // tests use (graph_cycle_test.go's evalWithTimeout) so a regression here |
| 127 | // fails the test instead of wedging the whole test run. |
| 128 | func TestVLookupOverCapRangeIsRefNotHang(t *testing.T) { |
| 129 | call := &Call{Name: "VLOOKUP", Args: []Node{&Num{V: 1}, pathologicalRange(), &Num{V: 1}, &Bool{V: false}}} |
| 130 | v := evalWithTimeout(t, func() Value { return Eval(call, emptyEnv{}) }) |
| 131 | if v.Kind != KindErr || v.Err != ErrRef { |
| 132 | t.Fatalf("VLOOKUP over-cap range = %v, want #REF!", v) |
| 133 | } |
| 134 | } |
| 135 | |
| 136 | func TestIndexOverCapRangeIsRefNotHang(t *testing.T) { |
| 137 | call := &Call{Name: "INDEX", Args: []Node{pathologicalRange(), &Num{V: 1}, &Num{V: 1}}} |
| 138 | v := evalWithTimeout(t, func() Value { return Eval(call, emptyEnv{}) }) |
| 139 | if v.Kind != KindErr || v.Err != ErrRef { |
| 140 | t.Fatalf("INDEX over-cap range = %v, want #REF!", v) |
| 141 | } |
| 142 | } |
| 143 | |
| 144 | func TestMatchExactOverCapRangeIsRefNotHang(t *testing.T) { |
| 145 | call := &Call{Name: "MATCH", Args: []Node{&Num{V: 1}, pathologicalRange(), &Num{V: 0}}} |
| 146 | v := evalWithTimeout(t, func() Value { return Eval(call, emptyEnv{}) }) |
| 147 | if v.Kind != KindErr || v.Err != ErrRef { |
| 148 | t.Fatalf("MATCH(match_type=0) over-cap range = %v, want #REF!", v) |
| 149 | } |
| 150 | } |
| 151 | |
| 152 | func TestMatchNearestOverCapRangeIsRefNotHang(t *testing.T) { |
| 153 | call := &Call{Name: "MATCH", Args: []Node{&Num{V: 1}, pathologicalRange(), &Num{V: 1}}} |
| 154 | v := evalWithTimeout(t, func() Value { return Eval(call, emptyEnv{}) }) |
| 155 | if v.Kind != KindErr || v.Err != ErrRef { |
| 156 | t.Fatalf("MATCH(match_type=1) over-cap range = %v, want #REF!", v) |
| 157 | } |
| 158 | } |
| 159 | |
amadan