GRDB seems to be treating empty arrays as null when inserting records

[mallman]

What did you do?

I created a table named selectedFolders with the following schema:

t.column("id", .integer).primaryKey()
t.column("folderIds", .text).notNull()

This table is intended to have a single row, with folderIds a text field consisting of a (possibly empty) JSON-array of integers.

I created a struct

import GRDB

struct SelectedFoldersRecord: Codable, PersistableRecord, FetchableRecord {
  typealias Id = Int64

  static let id: Id = 0

  let id: Id = SelectedFoldersRecord.id
  let folderIds: [FolderRecord.Id]
}

(BTW, FolderRecord.Id is a type alias for Int64.)

I then ran

try dbQueue.write { db in
  try db.execute("delete from selectedFolders")
  try SelectedFoldersRecord(folderIds: []).insert(db)
}

What did you expect to happen?

I expected GRDB to run the equivalent of the following SQL statements:

delete from selectedFolders
insert into selectedFolders (id, folderIds) values (0, '[]')

What happened instead?

GRDB (attempted to) run the following SQL:

delete from selectedFolders
INSERT INTO "selectedFolders" ("id") VALUES (0)

Subsequently, SQLite rolled-back the transaction because the insert violated the non-null constraint on the folderIds column. (Although that's incidental to my problem with GRDB's behavior here. The real problem for me is that it isn't inserting '[]' into the folderIds column when the value of the SelectedFoldersRecord.folderIds property is [].)

Environment

GRDB flavor(s): (GRDB, GRDBCipher, GRDBCustom?)
GRDB built from source. I think it's linked against macOS SQLite.
GRDB version:
Built from git clone, HEAD detached at v3.5.0
Installation method: (CocoaPods, SPM, manual?)
Manual sub-project of my app
Xcode version:
Version 10.1 (10B61)
Swift version:
Apple Swift version 4.2.1 (swiftlang-1000.11.42 clang-1000.11.45.1)
Platform(s) running GRDB: (iOS, macOS, watchOS?)
macOS
macOS version running Xcode:
Version 10.14.1 (18B75)

Demo Project

I don't have something immediately available. LMK if it would be valuable to you for me to supply you with one.

Thanks for helping out!

[groue]

Hello @mallman,

You definitely found a bug, thank you!

Empty arrays are not encoded (the same problem affects empty dictionaries).

#449 brings a fix, and the missing tests. It will be soon be merged in GRDB 3.6.0, but I can't announce any release date. Meanwhile, you can use the feature/fixEmptyContainersJSONEncoding branch.

[mallman]

Hi @groue. Thanks for being so responsive! I've been using a rather simple workaround. I have a special case in my code that runs custom SQL when I want to store an empty array. It's not a big deal but will be nice to eliminate once your bug fix has been merged into a release.

As an aside, I've been using GRDB for over a year now and have found it to be rock solid. I think this really is the first and only bug I've encountered. Conversely, I'm continually impressed by how capable it is. In my 15+ years programming, I would have to say that GRDB is easily in the top 5% if not top 1% in my experience in terms of documentation, project direction, execution, dependability and code quality. I don't know how you pull it off. Perhaps you should write a book on the secrets of success in open source development??? 😄

Cheers.

[groue]

Wow, thank you very, very much! 😊

I don't know the secret of success, but it looks like I know one secret or two that make the life of fellow developers easier ;-) And I'm glad you already had a workaround.

[groue]

#449 has been merged. The fix is now available in the development branch. Let's keep this issue open until the next release.

[groue]

The fix has shipped in v3.6.0! Thanks for your report, and your patience, @mallman!