Repository Public
1import Foundation2import Testing3import SheetCore4@testable import FunctionsLookup56@Suite struct DynamicArrayEdgeCaseTests {7 func sample() -> Book {8 let b = Book()9 b.column("A1", ["pear", "Apple", "fig", "apple", "kiwi"])10 b.column("B1", [3, 1, 2, 1, 2])11 b.column("C1", [true, false, true, true, false])12 return b13 }1415 @Test func emptyResultsAreCalcErrors() {16 let b = sample()17 #expect(b.eval("=FILTER(A1:A5,B1:B5>9)") == .error(.calc))18 #expect(b.eval("=FILTER(A1:A5,B1:B5>9,\"nothing\")") == "nothing")19 #expect(b.eval("=FILTER(A1:A5,B1:B5>9,NA())") == NA)20 #expect(b.eval("=UNIQUE({1;1;2;2},,TRUE)") == .error(.calc))21 #expect(b.eval("=TAKE(A1:B5,0)") == .error(.calc))22 #expect(b.eval("=TAKE(A1:B5,2,0)") == .error(.calc))23 #expect(b.eval("=DROP(A1:B5,5)") == .error(.calc))24 #expect(b.eval("=DROP(A1:B5,-7)") == .error(.calc))25 #expect(b.eval("=DROP(A1:B5,1,2)") == .error(.calc))26 #expect(b.eval("=SEQUENCE(0)") == .error(.calc))27 #expect(b.eval("=SEQUENCE(3,0)") == .error(.calc))28 #expect(b.eval("=TOCOL(Y1:Z3,1)") == .error(.calc)) // only blanks, all ignored29 #expect(b.eval("=TOROW({#N/A,#DIV/0!},2)") == .error(.calc))30 }3132 @Test func argumentErrors() {33 let b = sample()34 #expect(b.eval("=FILTER(A1:B5,B1:B4>0)") == .error(.value)) // include of the wrong length35 #expect(b.eval("=FILTER(A1:B5,A1:B5)") == .error(.value)) // include must be a vector36 #expect(b.eval("=FILTER(A1:A5,A1:A5)") == .error(.value)) // text is not a condition37 #expect(b.eval("=FILTER(A1:A3,{1;#N/A;1})") == NA) // an error in include propagates38 #expect(b.eval("=FILTER(1/0,TRUE)") == .error(.div0))39 #expect(b.eval("=SORT(A1:B5,3)") == .error(.value))40 #expect(b.eval("=SORT(A1:B5,1,0)") == .error(.value))41 #expect(b.eval("=SORTBY(A1:A5,B1:B4)") == .error(.value))42 #expect(b.eval("=SORTBY(A1:B5,B1:C5)") == .error(.value))43 #expect(b.eval("=SEQUENCE(-1)") == .error(.value))44 #expect(b.eval("=SEQUENCE(\"x\")") == .error(.value))45 #expect(b.eval("=CHOOSEROWS(A1:B5,0)") == .error(.value))46 #expect(b.eval("=CHOOSEROWS(A1:B5,6)") == .error(.value))47 #expect(b.eval("=CHOOSECOLS(A1:B5,-3)") == .error(.value))48 #expect(b.eval("=EXPAND(A1:B5,4)") == .error(.value))49 #expect(b.eval("=EXPAND(A1:B5,5,1)") == .error(.value))50 #expect(b.eval("=WRAPROWS(A1:B5,2)") == .error(.value))51 #expect(b.eval("=WRAPCOLS(A1:A5,0)") == .error(.num))52 #expect(b.eval("=TOCOL(A1:B5,4)") == .error(.value))53 #expect(b.eval("=MAKEARRAY(0,1,LAMBDA(r,c,1))") == .error(.value))54 #expect(b.eval("=MAP({1,2},5)") == .error(.value)) // last argument must be a LAMBDA55 #expect(b.eval("=BYROW(A1:B5,LAMBDA(a,b,1))") == .error(.value)) // wrong number of parameters56 #expect(b.eval("=REDUCE(0,{1,2},LAMBDA(a,a))") == .error(.value))57 #expect(b.eval("=SORT(NA())") == NA)58 #expect(b.eval("=UNIQUE(1/0)") == .error(.div0))59 }6061 @Test func mixedTypeOrdering() {62 let b = Book()63 b.column("A1", [true, "b", 2, nil, "A", .error(.div0), false, -1, "10", 10])64 // ascending: numbers, text (case-insensitive), FALSE, TRUE, errors, empty cells last65 #expect(b.eval("=SORT(A1:A10)") == grid([[-1], [2], [10], ["10"], ["A"], ["b"], [false], [true], [.error(.div0)], [.blank]]))66 // descending reverses the values but empty cells stay last67 #expect(b.eval("=SORT(A1:A10,1,-1)") == grid([[.error(.div0)], [true], [false], ["b"], ["A"], ["10"], [10], [2], [-1], [.blank]]))68 #expect(b.eval("=SORTBY({\"x\";\"y\";\"z\"},{2;\"a\";1})") == grid([["z"], ["x"], ["y"]]))69 }7071 @Test func sortingIsStable() {72 let b = sample()73 // equal keys keep their original order, in both directions74 #expect(b.eval("=SORT(A1:B5,2)") == grid([["Apple", 1], ["apple", 1], ["fig", 2], ["kiwi", 2], ["pear", 3]]))75 #expect(b.eval("=SORT(A1:B5,2,-1)") == grid([["pear", 3], ["fig", 2], ["kiwi", 2], ["Apple", 1], ["apple", 1]]))76 #expect(b.eval("=SORT(A1:A5)") == grid([["Apple"], ["apple"], ["fig"], ["kiwi"], ["pear"]]))77 #expect(b.eval("=SORTBY(A1:A5,C1:C5,1,B1:B5,-1)") == grid([["kiwi"], ["Apple"], ["pear"], ["fig"], ["apple"]]))78 #expect(b.eval("=SORTBY(A1:A5,B1:B5,-1)") == grid([["pear"], ["fig"], ["kiwi"], ["Apple"], ["apple"]]))79 // 1 000 rows with 3 distinct keys: ties must stay in input order80 b.engine.transaction("fill") {81 for i in 0..<1000 {82 b.engine.set(sheet: 0, row: i, col: 5, .value(.number(Double(i % 3))))83 b.engine.set(sheet: 0, row: i, col: 6, .value(.number(Double(i))))84 }85 }86 guard case .array(let sorted) = b.eval("=SORT(F1:G1000,1)") else { Issue.record("no array"); return }87 var previous = (-1.0, -1.0)88 for r in 0..<1000 {89 let key = sorted[r, 0].numberValue!, seq = sorted[r, 1].numberValue!90 #expect(key > previous.0 || (key == previous.0 && seq > previous.1))91 previous = (key, seq)92 }93 }9495 @Test func uniqueSemantics() {96 let b = sample()97 #expect(b.eval("=UNIQUE(A1:A5)") == grid([["pear"], ["Apple"], ["fig"], ["kiwi"]])) // case-insensitive, first spelling kept98 #expect(b.eval("=UNIQUE(A1:A5,,TRUE)") == grid([["pear"], ["fig"], ["kiwi"]]))99 #expect(b.eval("=UNIQUE(B1:C5)") == grid([[3, true], [1, false], [2, true], [1, true], [2, false]]))100 #expect(b.eval("=UNIQUE({1,\"1\",1,TRUE,\"true\"},TRUE)") == grid([[1, "1", true, "true"]])) // types are distinct101 #expect(b.eval("=UNIQUE({1,2;1,2;3,4})") == grid([[1, 2], [3, 4]]))102 #expect(b.eval("=UNIQUE({1,1,2;3,3,4},TRUE)") == grid([[1, 2], [3, 4]]))103 #expect(b.eval("=UNIQUE({#N/A;1;#N/A})") == grid([[NA], [1]]))104 #expect(b.eval("=UNIQUE(5)") == 5)105 }106107 @Test func spillAndDependencies() {108 let b = sample()109 b.set("E1", "=SORT(FILTER(A1:B5,B1:B5<3),2,-1)")110 #expect(b.get("E1") == "fig")111 #expect(b.get("F1") == 2)112 #expect(b.get("E4") == "apple")113 #expect(b.get("E5") == .blank)114 b.set("B1", 0) // pear now passes the filter and sorts last115 #expect(b.get("E5") == "pear")116 b.set("H1", "=SUM(E1#)") // spill reference: text ignored, numbers 2+2+1+1+0117 #expect(b.get("H1") == 6)118 b.set("J1", "=SEQUENCE(2,2)")119 b.set("K2", "x") // blocks the spill120 #expect(b.get("J1") == .error(.spill))121 b.set("M1", "=TAKE(Y1:Z2,1)") // empty cells come out as 0, as in Excel122 #expect(b.get("M1") == 0)123 #expect(b.get("N1") == 0)124 }125126 @Test func shapingDetails() {127 let b = sample()128 #expect(b.eval("=TAKE({1,2,3;4,5,6},1)") == grid([[1, 2, 3]]))129 #expect(b.eval("=TAKE({1,2,3;4,5,6},-1,-2)") == grid([[5, 6]]))130 #expect(b.eval("=TAKE({1,2,3;4,5,6},9,9)") == grid([[1, 2, 3], [4, 5, 6]]))131 #expect(b.eval("=TAKE({1,2,3;4,5,6},1,1)") == 1)132 #expect(b.eval("=DROP({1,2,3;4,5,6},1,-1)") == grid([[4, 5]]))133 #expect(b.eval("=CHOOSECOLS({1,2,3;4,5,6},3,1,3)") == grid([[3, 1, 3], [6, 4, 6]]))134 #expect(b.eval("=CHOOSEROWS({1,2;3,4;5,6},-1,{1,2})") == grid([[5, 6], [1, 2], [3, 4]]))135 #expect(b.eval("=EXPAND({1,2},2,3)") == grid([[1, 2, NA], [NA, NA, NA]]))136 #expect(b.eval("=EXPAND({1,2},2,3,\"\")") == grid([[1, 2, ""], ["", "", ""]]))137 #expect(b.eval("=HSTACK({1;2},{3;4;5})") == grid([[1, 3], [2, 4], [NA, 5]]))138 #expect(b.eval("=VSTACK({1,2},{3})") == grid([[1, 2], [3, NA]]))139 #expect(b.eval("=VSTACK(B1:B2,\"x\",#DIV/0!)") == grid([[3], [1], ["x"], [.error(.div0)]]))140 #expect(b.eval("=IFERROR(VSTACK({1,2},3),0)") == grid([[1, 2], [3, 0]]))141 #expect(b.eval("=TOCOL({1,2;3,4})") == grid([[1], [2], [3], [4]]))142 #expect(b.eval("=TOCOL({1,2;3,4},0,TRUE)") == grid([[1], [3], [2], [4]]))143 #expect(b.eval("=TOROW({1,#N/A;3,4},2)") == grid([[1, 3, 4]]))144 #expect(b.eval("=WRAPROWS({1,2,3,4,5},2)") == grid([[1, 2], [3, 4], [5, NA]]))145 #expect(b.eval("=WRAPCOLS({1,2,3,4,5},2,0)") == grid([[1, 3, 5], [2, 4, 0]]))146 #expect(b.eval("=WRAPROWS({1;2;3},5)") == grid([[1, 2, 3, NA, NA]]))147 #expect(b.eval("=SEQUENCE(2,3,0,0.5)") == grid([[0, 0.5, 1], [1.5, 2, 2.5]]))148 #expect(b.eval("=SEQUENCE(3)") == grid([[1], [2], [3]]))149 #expect(b.eval("=SUM(SEQUENCE(1000,1000))") == 500_000_500_000)150 #expect(b.eval("=FILTER({1,2,3;4,5,6},{0,1,1})") == grid([[2, 3], [5, 6]]))151 #expect(b.eval("=FILTER(B1:B5,C1:C5)") == grid([[3], [2], [1]]))152 #expect(b.eval("=FILTER(B1:B5,{1;0;2;-1;0})") == grid([[3], [2], [1]])) // any non-zero number keeps the row153 }154155 @Test func lambdaHelpers() {156 let b = sample()157 #expect(b.eval("=MAP(B1:B5,LAMBDA(x,x*10))") == grid([[30], [10], [20], [10], [20]]))158 #expect(b.eval("=MAP(A1:A2,B1:B2,LAMBDA(a,n,a&n))") == grid([["pear3"], ["Apple1"]]))159 #expect(b.eval("=MAP({1,2,3},LAMBDA(x,IF(x=2,NA(),x)))") == grid([[1, NA, 3]]))160 #expect(b.eval("=MAP({1,2},LAMBDA(x,{1,2}))") == grid([[.error(.calc), .error(.calc)]]))161 #expect(b.eval("=REDUCE(0,B1:B5,LAMBDA(a,v,a+v))") == 9)162 #expect(b.eval("=REDUCE(,{5,6,7},LAMBDA(a,v,a+v))") == 18)163 #expect(b.eval("=REDUCE(\"\",A1:A3,LAMBDA(a,v,v&a))") == "figApplepear")164 #expect(b.eval("=REDUCE({0},{1,2,3},LAMBDA(a,v,HSTACK(a,v*v)))") == grid([[0, 1, 4, 9]]))165 #expect(b.eval("=REDUCE(0,{1,2,3},LAMBDA(a,v,a+1/(v-2)))") == .error(.div0))166 #expect(b.eval("=SCAN(0,B1:B5,LAMBDA(a,v,a+v))") == grid([[3], [4], [6], [7], [9]]))167 #expect(b.eval("=SCAN(,{2,3,4},LAMBDA(a,v,a*v))") == grid([[2, 6, 24]]))168 #expect(b.eval("=SCAN(0,{1,2;3,4},LAMBDA(a,v,a+v))") == grid([[1, 3], [6, 10]]))169 #expect(b.eval("=BYROW({1,2;3,4},LAMBDA(r,SUM(r)))") == grid([[3], [7]]))170 #expect(b.eval("=BYCOL({1,2;3,4},LAMBDA(c,MAX(c)))") == grid([[3, 4]]))171 #expect(b.eval("=BYROW(B1:C5,LAMBDA(r,COUNT(r)))") == grid([[1], [1], [1], [1], [1]])) // rows arrive as references: TRUE/FALSE not counted172 #expect(b.eval("=BYROW(B1:B5,LAMBDA(r,ROW(r)))") == grid([[1], [2], [3], [4], [5]]))173 #expect(b.eval("=BYCOL(A1:B5,LAMBDA(c,COUNTA(c)))") == grid([[5, 5]]))174 #expect(b.eval("=BYROW({1,2;3,4},LAMBDA(r,r))") == grid([[.error(.calc)], [.error(.calc)]]))175 #expect(b.eval("=MAKEARRAY(2,3,LAMBDA(r,c,r*c))") == grid([[1, 2, 3], [2, 4, 6]]))176 #expect(b.eval("=MAKEARRAY(2,2,LAMBDA(r,c,INDEX(B1:C5,r,c)))") == grid([[3, true], [1, false]]))177 #expect(b.eval("=LET(sq,LAMBDA(x,x*x),SUM(MAP(SEQUENCE(4),sq)))") == 30)178 #expect(b.eval("=LET(k,10,MAP({1,2},LAMBDA(x,x+k)))") == grid([[11, 12]])) // closures capture LET names179 b.set("E1", "=BYROW(B1:C5,LAMBDA(r,SUM(r)))")180 #expect(b.get("E3") == 2)181 }182}183