SPB Git

Repository Public

DynamicArrayTests.swift 11.0 KB 183 lines swift

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
Files main
Menu

Theme