Custom function cannot return Blob/Data

[tylerbrandt]

Issues are used to track bugs and feature requests. Need help or have a general question? Ask on Stack Overflow (tag sqlite.swift).

Build Information

• Include the SQLite.swift version, commit or branch experiencing the issue.

0.14.1

• Mention Xcode and OS X versions affected.

Xcode 14.3.1 Ventura 13.5

• How do do you integrate SQLite.swift in your project?
  • manual
  • CocoaPods
  • Carthage
  • Swift Package manager

SPM

General guidelines

• Be as descriptive as possible.
• Provide as much information needed to reliably reproduce the issue.
• Attach screenshots if possible.
• Better yet: attach GIFs or link to video.
• Even better: link to a sample project exhibiting the issue.

When implementing a function that returns Data (or any type bridged to Blob), the results don't seem to be propagating to the database correctly. The stored value has the correct data length but the values are all 0. See example playground code:

import Foundation
import SQLite

let db = try Connection(.inMemory)
db.trace { print("\($0);") }

// A Swift function to convert a string into Data (for testing purposes)
func data_literal(_ message: String) -> Data {
  message.data(using: .utf8)!
}

// A SQL function that returns the same data
let data_func = try db.createFunction("data_func", { message in
  data_literal(message)
})

try db.run(Table("blob").create { tbl in
  tbl.column(Expression<Blob>("blob_func"), primaryKey: true)
  tbl.column(Expression<Blob>("blob_literal"))
})

try db.run(Table("blob").insert(Expression<Data>("blob_func") <- data_func(Expression<String>("hello world")), Expression<Data>("blob_literal") <- data_literal("hello world")))
let blobresults = try db.prepare(Table("blob"))
for result in blobresults {
  // Prints blob_func x'0000000000000000000000'
  print("blob_func", result[Expression<Blob>("blob_func")])
  // Prints blob_literal x'68656c6c6f20776f726c64'
  print("blob_literal", result[Expression<Blob>("blob_literal")])
}

// Doesn't crash because of different data length, but results are still wrong
try db.run(Table("blob").insert(Expression<Data>("blob_func") <- data_func(Expression<String>("foobar")), Expression<Data>("blob_literal") <- data_literal("foobar")))
let blobresults2 = try db.prepare(Table("blob"))
for result in blobresults2 {
  // Prints blob_func x'000000000000'
  print("blob_func", result[Expression<Blob>("blob_func")])
  // Prints blob_literal x'666f6f626172'
  print("blob_literal", result[Expression<Blob>("blob_literal")])
}

// Crashes here due to uniqueness constraint on blob_func (same length as hello world)
try db.run(Table("blob").insert(Expression<Data>("blob_func") <- data_func(Expression<String>("hello earth")), Expression<Data>("blob_literal") <- data_literal("hello earth")))
let blobresults3 = try db.prepare(Table("blob"))
for result in blobresults3 {
  print("blob_func", result[Expression<Blob>("blob_func")])
  print("blob_literal", result[Expression<Blob>("blob_literal")])
}

Prints

CREATE TABLE "blob" ("blob_func" BLOB PRIMARY KEY NOT NULL, "blob_literal" BLOB NOT NULL);
INSERT INTO "blob" ("blob_func", "blob_literal") VALUES ("data_func"("hello world"), x'68656c6c6f20776f726c64');
SELECT * FROM "blob";
blob_func x'0000000000000000000000'
blob_literal x'68656c6c6f20776f726c64'
INSERT INTO "blob" ("blob_func", "blob_literal") VALUES ("data_func"("foobar"), x'666f6f626172');
SELECT * FROM "blob";
blob_func x'0000000000000000000000'
blob_literal x'68656c6c6f20776f726c64'
blob_func x'000000000000'
blob_literal x'666f6f626172'
INSERT INTO "blob" ("blob_func", "blob_literal") VALUES ("data_func"("hello earth"), x'68656c6c6f206561727468');
Swift/ErrorType.swift:200: Fatal error: Error raised at top level: UNIQUE constraint failed: blob.blob_func (code: 19)
2023-07-14 12:37:44.059336-0700 SQLiteXidDemo[46862:21639780] Swift/ErrorType.swift:200: Fatal error: Error raised at top level: UNIQUE constraint failed: blob.blob_func (code: 19)

This came up for me when trying to use createFunction to emit new Id values from https://github.com/uditha-atukorala/swift-xid; after conforming the type to Value everything compiled correctly, but as soon as I tried to set the second row, I hit the uniqueness constraint, and upon inspection saw that the value inserted was 12 bytes of 0.