Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetData/Tests/SheetDataTests/EngineTests.swift10// Module : SheetDataTests11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import SheetData9192@Suite struct EngineTests {93 @Test func linksVendoredDuckDB() throws {94 #expect(DataEngine.duckDBVersion == "v2.0.1")95 }9697 @Test func typedColumns() throws {98 let e = try DataEngine()99 let t = try e.query("""100 SELECT true AS b, 42::TINYINT AS i8, 9007199254740993::BIGINT AS i64, 1.5::FLOAT AS f, 2.25::DOUBLE AS d,101 123.4500::DECIMAL(18,4) AS dec, -0.05::DECIMAL(4,2) AS negdec, 'héllo 日本' AS s, DATE '2024-02-29' AS dt,102 TIMESTAMP '2024-01-31 13:45:10.5' AS ts, TIME '08:30:00' AS tm, 'ab'::BLOB AS bl, NULL AS n,103 18446744073709551615::UBIGINT AS u64, sum(7) AS huge, [1, 2] AS list, {'a': 1} AS st,104 12345678901234567890123.456::DECIMAL(30,3) AS bigdec, 'x'::ENUM('x', 'y') AS en,105 TIMESTAMP_MS '2024-01-01 00:00:01' AS tms, TIMESTAMP_S '2024-01-01 00:00:01' AS tss106 """)107 func v(_ n: String) -> DataValue { t.value(row: 0, column: n)! }108 #expect(v("b") == .bool(true))109 #expect(v("i8") == .int(42))110 #expect(v("i64") == .int(9007199254740993))111 #expect(v("f") == .double(1.5))112 #expect(v("d") == .double(2.25))113 #expect(v("dec") == .decimal("123.4500"))114 #expect(t.column("dec")!.double(0) == 123.45)115 #expect(v("negdec") == .decimal("-0.05"))116 #expect(v("s") == .string("héllo 日本"))117 #expect(v("dt") == .date(year: 2024, month: 2, day: 29))118 #expect(v("dt").text == "2024-02-29")119 #expect(v("ts").text == "2024-01-31 13:45:10.500000")120 #expect(v("tm").text == "08:30:00")121 #expect(v("bl") == .blob(Data("ab".utf8)))122 #expect(v("n") == .null)123 #expect(v("u64") == .decimal("18446744073709551615"))124 #expect(v("huge") == .int(7))125 #expect(v("list") == .string("[1, 2]"))126 #expect(v("st") == .string("{'a': 1}"))127 #expect(v("bigdec") == .decimal("12345678901234567890123.456"))128 #expect(v("en") == .string("x"))129 #expect(v("tms").text == "2024-01-01 00:00:01")130 #expect(v("tss").text == "2024-01-01 00:00:01")131 #expect(t.column("dec")!.type == .decimal)132 #expect(t.column("dec")!.declaredType == "DECIMAL(18,4)")133 #expect(t.column("i8")!.declaredType == "TINYINT")134 }135136 @Test func nullMasksAndColumnAccess() throws {137 let e = try DataEngine()138 let t = try e.query("SELECT i, CASE WHEN i % 3 = 0 THEN NULL ELSE i * 1.5 END AS x, CASE WHEN i % 2 = 0 THEN 's' || i END AS s FROM range(10) t(i) ORDER BY i")139 #expect(t.rowCount == 10)140 #expect(t.column("i")!.valid == nil)141 #expect(t.column("x")!.nullCount == 4)142 #expect(t.column("s")!.nullCount == 5)143 #expect(t.column("x")!.double(3) == nil)144 #expect(t.column("x")!.double(4) == 6.0)145 #expect(t.value(row: 2, column: "s") == .string("s2"))146 #expect(t.value(row: 1, column: "s") == .null)147 }148149 @Test func parametersBindEveryType() throws {150 let e = try DataEngine()151 let t = try e.query("SELECT ?::BOOLEAN, ?::BIGINT + 1, ?::DOUBLE * 2, ? || '!', ?::DATE + 1, ?::TIMESTAMP, ?::TIME, octet_length(?::BLOB), ? IS NULL, ?::DECIMAL(10,2)",152 parameters: [.bool(true), .int(41), .double(1.25), .string("O'Brien; DROP TABLE x;--"), .date(year: 2024, month: 12, day: 31),153 .timestamp(1_704_067_200_000_000), .time(3_600_000_000), .blob(Data([0, 1, 2])), .null, .decimal("12.34")])154 #expect(t.row(0) == [.bool(true), .int(42), .double(2.5), .string("O'Brien; DROP TABLE x;--!"), .date(year: 2025, month: 1, day: 1),155 .timestamp(1_704_067_200_000_000), .time(3_600_000_000), .int(3), .bool(true), .decimal("12.34")])156 #expect(throws: DataError.self) { _ = try e.query("SELECT ?", parameters: []) }157 }158159 @Test func limitStopsFetchingAndFlagsTruncation() throws {160 let e = try DataEngine()161 let t = try e.query("SELECT * FROM range(1000000)", limit: 2500)162 #expect(t.rowCount == 2500)163 #expect(t.truncated)164 let full = try e.query("SELECT * FROM range(10)", limit: 10)165 #expect(full.rowCount == 10 && !full.truncated)166 }167168 @Test func streamDeliversChunksAndCanStop() throws {169 let e = try DataEngine()170 var total = 0, sum: Int64 = 0, chunks = 0171 try e.stream("SELECT i FROM range(300000) t(i)", chunkRows: 50_000) { chunk in172 chunks += 1173 total += chunk.rowCount174 if case .int64(let a) = chunk.columns[0].storage { sum += a.reduce(0, +) }175 return true176 }177 #expect(total == 300_000)178 #expect(sum == Int64(299_999) * 300_000 / 2)179 #expect(chunks >= 5)180 var seen = 0181 try e.stream("SELECT i FROM range(300000) t(i)", chunkRows: 10_000) { seen += $0.rowCount; return false }182 #expect(seen < 300_000 && seen > 0)183 #expect(try e.query("SELECT 1").rowCount == 1) // the connection is usable after an early stop184 }185186 @Test func multiStatementScripts() throws {187 let e = try DataEngine()188 try e.execute("CREATE TABLE t (a INTEGER); INSERT INTO t VALUES (1), (2), (3);")189 let t = try e.query("INSERT INTO t VALUES (4); SELECT sum(a) FROM t")190 #expect(t.value(row: 0, column: 0) == .int(10))191 #expect(throws: DataError.self) { try e.execute("SELECT 1; SELEC 2") }192 }193194 @Test func errorsCarryDuckDBMessage() throws {195 let e = try DataEngine()196 do { _ = try e.query("SELECT * FROM missing_table"); Issue.record("expected an error") } catch let DataError.sql(message, _) {197 #expect(message.contains("missing_table"))198 }199 }200201 @Test func interruptCancelsALongQuery() async throws {202 let e = try DataEngine()203 let started = Date()204 let canceller = Task.detached {205 try await Task.sleep(nanoseconds: 300_000_000)206 e.interrupt()207 }208 var interrupted = false209 do { _ = try e.query("SELECT count(*) FROM range(200000000000) t(i) WHERE i % 7 = 3") } catch DataError.interrupted { interrupted = true }210 _ = await canceller.result211 #expect(interrupted)212 #expect(Date().timeIntervalSince(started) < 20)213 // the engine keeps working, and an interrupt while idle does not poison the next query214 e.interrupt()215 #expect(try e.query("SELECT 41 + 1").value(row: 0, column: 0) == .int(42))216 }217218 @Test func progressIsReportedDuringALongStatement() throws {219 let e = try DataEngine()220 let box = ProgressBox()221 try e.withProgress({ box.add($0) }) {222 try e.execute("CREATE TABLE big AS SELECT i, hash(i) AS h FROM range(60000000) t(i)")223 }224 #expect(box.values.last == 1)225 #expect(box.values.allSatisfy { $0 >= 0 && $0 <= 1 })226 }227228 @Test func concurrentCallersAreSerialized() async throws {229 let e = try DataEngine()230 try e.execute("CREATE TABLE c (v BIGINT)")231 await withTaskGroup(of: Void.self) { group in232 for i in 0..<16 {233 group.addTask {234 try? e.execute("INSERT INTO c VALUES (?)", parameters: [.int(Int64(i))])235 _ = try? e.query("SELECT count(*) FROM c")236 _ = try? e.datasets()237 }238 }239 }240 #expect(try e.query("SELECT count(*), sum(v) FROM c").row(0) == [.int(16), .int(120)])241 }242243 @Test func describeReturnsSchemaWithoutRunning() throws {244 let e = try DataEngine()245 let s = try e.describe("SELECT 1::BIGINT AS a, 'x' AS b, DATE '2024-01-01' AS c, 1.5::DECIMAL(9,2) AS d FROM range(100000000000)")246 #expect(s.map(\.name) == ["a", "b", "c", "d"])247 #expect(s.map(\.type) == [.integer, .string, .date, .decimal])248 }249250 @Test func diskBackedOperationsUnderASmallMemoryLimit() throws {251 // 40M rows sorted with a 256 MiB limit: the sort must spill to the temp directory instead of failing.252 let e = try DataEngine(options: DataEngineOptions(memoryLimitBytes: 256 << 20, threads: 4))253 try e.execute("CREATE TABLE s AS SELECT hash(i) AS h, i FROM range(40000000) t(i)")254 let t = try e.query("SELECT h FROM s ORDER BY h LIMIT 1 OFFSET 39999999")255 #expect(t.rowCount == 1)256 }257258 @Test func persistentDatabaseReopens() throws {259 let dir = Fixtures.temporaryDirectory()260 defer { try? FileManager.default.removeItem(at: dir) }261 let path = dir.appendingPathComponent("book.duckdb")262 do {263 let e = try DataEngine(path: path)264 try e.importFile(Fixtures.url("customers.csv"), as: "customers")265 try e.registerView(Fixtures.url("sales.parquet"), as: "sales_view")266 }267 let e = try DataEngine(path: path)268 let names = try e.datasets().map(\.name)269 #expect(names == ["customers", "sales_view"])270 #expect(try e.rowCount(of: "sales_view") == 2000) // the staged file lives next to the database271 #expect(try e.dataset("customers").sourceURL?.lastPathComponent == "customers.csv")272 }273}274275/// Polls a directory from a background thread and remembers the largest total file size seen.276final class SpillObserver: @unchecked Sendable {277 private let directory: URL278 private let lock = NSLock()279 private var peak: Int64 = 0280 private var running = false281 private let done = DispatchSemaphore(value: 0)282 init(directory: URL) { self.directory = directory }283284 private func sample() {285 let fm = FileManager.default286 var total: Int64 = 0287 if let walker = fm.enumerator(at: directory, includingPropertiesForKeys: [.fileSizeKey]) {288 for case let url as URL in walker { total += Int64((try? url.resourceValues(forKeys: [.fileSizeKey]).fileSize) ?? 0) }289 }290 lock.lock(); peak = max(peak, total); lock.unlock()291 }292293 func start() {294 lock.lock(); running = true; lock.unlock()295 Thread.detachNewThread { [self] in296 while true {297 lock.lock(); let go = running; lock.unlock()298 if !go { break }299 sample()300 usleep(2_000)301 }302 done.signal()303 }304 }305306 func stop() -> Int64 {307 lock.lock(); running = false; lock.unlock()308 done.wait()309 lock.lock(); defer { lock.unlock() }310 return peak311 }312}313314final class ProgressBox: @unchecked Sendable {315 private let lock = NSLock()316 private var storage: [Double] = []317 func add(_ v: Double) { lock.lock(); storage.append(v); lock.unlock() }318 var values: [Double] { lock.lock(); defer { lock.unlock() }; return storage }319}320321@Suite struct SecurityTests {322 @Test func noFileAccessOutsideTheStagingDirectory() throws {323 let e = try DataEngine()324 let secret = Fixtures.temporaryDirectory().appendingPathComponent("secret.csv")325 try "a,b\n1,2\n".write(to: secret, atomically: true, encoding: .utf8)326 let attempts = [327 "SELECT * FROM read_csv(\(SQL.string(secret.path)))",328 "SELECT * FROM \(SQL.string(secret.path))",329 "SELECT * FROM read_text('/etc/hosts')",330 "SELECT * FROM read_blob('/etc/hosts')",331 "SELECT * FROM glob('/etc/*')",332 "COPY (SELECT 1) TO \(SQL.string(secret.deletingLastPathComponent().appendingPathComponent("out.csv").path))",333 "COPY (SELECT 1) TO '/tmp/orix-evil.parquet' (FORMAT parquet)",334 "EXPORT DATABASE '/tmp/orix-evil-export'",335 "ATTACH '/tmp/orix-evil.duckdb' AS evil",336 "CREATE TABLE x AS SELECT * FROM read_json('/etc/hosts')",337 "SELECT * FROM read_csv(\(SQL.string(e.stagingDirectory.path + "/../../secret.csv")))",338 ]339 for sql in attempts {340 #expect(throws: DataError.self, "\(sql)") { _ = try e.query(sql) }341 }342 #expect(!FileManager.default.fileExists(atPath: "/tmp/orix-evil.parquet"))343 #expect(!FileManager.default.fileExists(atPath: "/tmp/orix-evil.duckdb"))344 }345346 @Test func noNetworkNoExtensions() throws {347 let e = try DataEngine()348 for sql in ["INSTALL httpfs", "LOAD httpfs", "INSTALL sqlite_scanner", "LOAD sqlite_scanner", "FORCE INSTALL spatial",349 "SELECT * FROM read_csv('https://example.com/data.csv')", "SELECT * FROM 's3://bucket/file.parquet'",350 "SELECT * FROM sqlite_scan('x.db', 't')", "INSTALL h3 FROM community", "UPDATE EXTENSIONS"] {351 #expect(throws: DataError.self, "\(sql)") { _ = try e.query(sql) }352 }353 // nothing was written to the (empty, private) extension directory354 let extDir = e.spillDirectory.deletingLastPathComponent().appendingPathComponent("extensions-disabled")355 #expect((try FileManager.default.contentsOfDirectory(atPath: extDir.path)).isEmpty)356 }357358 /// The engine sets `allowed_directories = [stagingDirectory]`; DuckDB adds its own `temp_directory` (the spill359 /// directory) when external access is switched off. The effective allow-list is therefore exactly360 /// {staging, spill}: larger-than-memory operators work under `enable_external_access = false`, and the spill361 /// directory is, like the staging directory, reachable from SQL. Both are private to the engine.362 @Test func spillingWorksAndTheAllowListIsStagingPlusSpill() throws {363 let e = try DataEngine(options: DataEngineOptions(memoryLimitBytes: 64 << 20, threads: 2))364 let allowed = try e.query("SELECT value FROM duckdb_settings() WHERE name = 'allowed_directories'").rows[0][0].text365 let entries = allowed.trimmingCharacters(in: CharacterSet(charactersIn: "[]")).split(separator: ",")366 .map { $0.trimmingCharacters(in: .whitespaces) }.map { $0.hasSuffix("/") ? String($0.dropLast()) : $0 }367 #expect(Set(entries) == [e.stagingDirectory.path, e.spillDirectory.path])368 #expect(try e.query("SELECT value FROM duckdb_settings() WHERE name = 'enable_external_access'").rows[0][0].text == "false")369370 // ~190 MiB of sort keys against a 64 MiB limit: the sort has to go to disk.371 try e.execute("CREATE TABLE s AS SELECT hash(i) AS h, hash(i + 1) AS g, i FROM range(8000000) t(i)")372 let observer = SpillObserver(directory: e.spillDirectory)373 observer.start()374 let t = try e.query("SELECT h FROM s ORDER BY h, g LIMIT 1 OFFSET 7999999")375 let peak = observer.stop()376 #expect(t.rowCount == 1)377 #expect(peak > 0, "the sort did not write anything to the spill directory")378379 // The spill directory is inside the allow-list; its sibling (the disabled extension directory) is not.380 let planted = e.spillDirectory.appendingPathComponent("planted.csv")381 try "a,b\n1,2\n".write(to: planted, atomically: true, encoding: .utf8)382 #expect(try e.query("SELECT a + b FROM read_csv(\(SQL.string(planted.path)))").rows[0][0].text == "3")383 let outside = e.spillDirectory.deletingLastPathComponent().appendingPathComponent("extensions-disabled/planted.csv")384 try "a,b\n1,2\n".write(to: outside, atomically: true, encoding: .utf8)385 #expect(throws: DataError.self) { _ = try e.query("SELECT * FROM read_csv(\(SQL.string(outside.path)))") }386 try FileManager.default.removeItem(at: outside)387 }388389 @Test func configurationIsLocked() throws {390 let e = try DataEngine()391 for sql in ["SET enable_external_access = true", "SET allowed_directories = ['/']", "SET allowed_paths = ['/etc/hosts']",392 "SET autoinstall_known_extensions = true", "SET autoload_known_extensions = true", "SET lock_configuration = false",393 "RESET enable_external_access", "SET GLOBAL enable_external_access = true", "PRAGMA enable_external_access = true",394 "SET extension_directory = '/tmp'", "SET temp_directory = '/tmp'"] {395 #expect(throws: DataError.self, "\(sql)") { try e.execute(sql) }396 }397 let t = try e.query("SELECT name, value FROM duckdb_settings() WHERE name IN ('enable_external_access', 'lock_configuration', 'autoinstall_known_extensions', 'autoload_known_extensions', 'allow_community_extensions', 'allow_unsigned_extensions') ORDER BY name")398 let settings = Dictionary(uniqueKeysWithValues: t.rows.map { ($0[0].text, $0[1].text) })399 #expect(settings["enable_external_access"] == "false")400 #expect(settings["lock_configuration"] == "true")401 #expect(settings["autoinstall_known_extensions"] == "false")402 #expect(settings["autoload_known_extensions"] == "false")403 #expect(settings["allow_community_extensions"] == "false")404 #expect(settings["allow_unsigned_extensions"] == "false")405 // the reader connection obeys the same (database-wide) policy406 #expect(throws: DataError.self) { _ = try e.reading { try $0.queryTable("SELECT * FROM read_text('/etc/hosts')") } }407 }408409 @Test func identifiersWithSpacesQuotesUnicodeAndSQL() throws {410 let e = try DataEngine()411 let info = try e.importFile(Fixtures.url("odd names.csv"), as: "odd \"names\"; DROP TABLE x;--")412 let names = ["Unit Price ($)", "naïve \"quoted\" col", "日本語", "select", "x; DROP TABLE sales;--"]413 #expect(info.columns.map(\.name) == names)414 #expect(info.rowCount == 3)415 let dataset = info.name416 let p = try e.preview(dataset, offset: 0, limit: 10, orderBy: [SortSpec("日本語", ascending: false)],417 filter: FilterGroup(.all, [FilterCondition("naïve \"quoted\" col", .notEqual, "b"), FilterCondition("x; DROP TABLE sales;--", .greaterOrEqual, 1)]),418 columns: ["select", "Unit Price ($)"])419 #expect(p.rows == [[.string("z"), .double(4.0)], [.string("x"), .double(1.5)]])420 let prof = try e.profile(dataset)421 #expect(prof.map(\.name) == names)422 #expect(prof[0].max == .double(4.0))423 // every transform step builds SQL from these names424 let pipeline = TransformPipeline(name: "odd", source: .dataset(dataset), steps: [425 .renameColumn(from: "select", to: "from \"where\""),426 .calculatedColumn(name: "total; --", expression: "\"Unit Price ($)\" * \"日本語\""),427 .groupBy(keys: ["from \"where\""], aggregations: [Aggregation(.sum, "total; --", as: "Σ")]),428 .sort([SortSpec("Σ")]),429 ])430 let out = try TransformRunner.run(pipeline, engine: e)431 #expect(out.columnNames == ["from \"where\"", "Σ"])432 #expect(out.column("Σ")!.double(2) == 120)433 // pivot over the same names434 let spec = PivotSpec(source: .dataset(dataset), rows: [PivotField("select")], columns: [PivotField("naïve \"quoted\" col")],435 values: [PivotValue("x; DROP TABLE sales;--", .sum)])436 let r = try PivotEngine.compute(spec, workbook: nil, data: e)437 #expect(r.value(row: r.rowIndex(keys: [])!, column: r.dataColumnCount - 1) == 6)438 try e.dropDataset(dataset)439 #expect(try e.datasets().isEmpty)440 }441442 @Test func injectionThroughValuesAndNames() throws {443 let e = try Fixtures.engine()444 let evil = "x'; DROP TABLE sales; --"445 // filter constants446 let none = try e.preview("sales", offset: 0, limit: 5, filter: FilterGroup(FilterCondition("region", .equal, .string(evil))))447 #expect(none.rowCount == 0)448 _ = try e.preview("sales", offset: 0, limit: 5, filter: FilterGroup(.any, [449 FilterCondition("product", .contains, .string(evil)), FilterCondition("product", .isIn, [.string(evil), .string("a\"b")]),450 FilterCondition("product", .startsWith, .string("') OR 1=1 --")), FilterCondition("product", .matchesRegex, .string("'(x)'; DROP TABLE sales;--")),451 ]))452 // unknown / hostile column names are rejected before any SQL runs453 #expect(throws: DataError.self) { _ = try e.preview("sales", offset: 0, limit: 5, orderBy: [SortSpec("1; DROP TABLE sales")]) }454 #expect(throws: DataError.self) { _ = try e.preview("sales\"; DROP TABLE sales; --", offset: 0, limit: 5) }455 #expect(throws: DataError.self) { _ = try e.rowCount(of: "sales WHERE 1=1; DROP TABLE sales") }456 // hostile dataset names are just names457 try e.createTable(evil, from: "SELECT 1 AS a")458 #expect(try e.rowCount(of: evil) == 1)459 // replace / fill / pivot values460 let p = TransformPipeline(name: "inj", source: .dataset("sales"), steps: [461 .replaceValues(column: "region", replacements: [Replacement("North", .string(evil))], matchSubstring: false),462 .fillMissing(columns: ["note"], strategy: .constant(.string("'); DROP TABLE sales; --"))),463 .mergeColumns(columns: ["region", "category"], separator: "', (SELECT 1)); DROP TABLE sales; --", into: "m", keepOriginals: true),464 .pivot(rowKeys: ["category"], column: "region", value: "quantity", aggregation: .sum),465 ])466 let out = try TransformRunner.run(p, engine: e)467 #expect(out.columnNames.contains(evil))468 // calculated expressions cannot escape their parentheses469 for expression in ["1); DROP TABLE sales; --", "1) AS x, (SELECT 2", "quantity /* c */", "1 -- c", "1, 2", "'unterminated",470 "$$a$$", "E'\\'' ) FROM sales; DROP TABLE sales; SELECT (E'\\''", "quantity; DELETE FROM sales", ""] {471 let bad = TransformPipeline(name: "bad", source: .dataset("sales"), steps: [.calculatedColumn(name: "c", expression: expression)])472 #expect(throws: DataError.self, "\(expression)") { _ = try TransformCompiler.sql(for: bad, engine: e) }473 let diag = TransformCompiler.validate(bad, engine: e)474 #expect(diag.last?.status == .error)475 }476 // export and createTable accept exactly one SELECT477 let out2 = Fixtures.temporaryDirectory().appendingPathComponent("o.csv")478 #expect(throws: DataError.self) { _ = try e.exportQuery("SELECT 1; DROP TABLE sales", to: out2, format: .csv) }479 #expect(throws: DataError.self) { _ = try e.exportQuery("DROP TABLE sales", to: out2, format: .csv) }480 #expect(throws: DataError.self) { _ = try e.createTable("t2", from: "SELECT 1); DROP TABLE sales; --") }481 #expect(try e.rowCount(of: "sales") == 2000)482 }483484 @Test func expressionScannerAcceptsOrdinarySQL() {485 for ok in ["quantity * unit_price", "CASE WHEN a > 1 THEN 'x;y' ELSE \"weird, name\" END", "round(a / nullif(b, 0), 2)",486 "coalesce(note, '--')", "list_extract([1, 2, 3], 2)", "regexp_extract(code, '(\\d+)-(\\w)', 1)", "date_trunc('month', d)"] {487 #expect(SQL.expressionProblem(ok) == nil, "\(ok)")488 }489 }490}491