Grdb.swift: GRDB 5 and SQLite views

[gverdouw]

What did you do?

After updating to GRDB5, one of my TableRecord structs no longer loads (throws an error: SQLite error 1: no such table: View_Item). This one is unique in my app in that View_Item is a view and not a table. View_Item definitely exists in the database.

The relevant part of the struct definition:

public struct Item: TableRecord, FetchableRecord, Decodable, Hashable {
    public static var databaseTableName: String = "View_Item"

What did you expect to happen?

Loads as before. Has something changed in the way queries are handled that means this is no longer supported?

What happened instead?

SQLite error 1: no such table: View_Item

Environment

GRDB flavor(s): GRDB
GRDB version: 5.0.0-beta8
Installation method: SPM
Xcode version: 12 beta
Swift version: 5.3
Platform(s) running GRDB: iOS
macOS version running Xcode: macOS 11

[groue]

Hello @gverdouw,

Thanks for reporting this issue with GRDB 5 beta! Let's fix it.

I can't reproduce the problem with the sample code below. Will you please extend it until it produces your error?

try DatabaseQueue().write { db in
    try db.execute(sql: """
        CREATE TABLE base (id INTEGER PRIMARY KEY, name TEXT);
        CREATE VIEW View_Item AS SELECT * FROM base;
        """)

    struct Item: TableRecord, FetchableRecord, Decodable, Hashable {
        static let databaseTableName = "View_Item"
        var id: Int64
        var name: String
    }
    let items = try Item.fetchAll(db)
}

[gverdouw]

I've poked around a bit and it seems as soon as I add a .filter to the query (and there is data in the table), that is when it falls over.

For example:

try DatabaseQueue().write { db in
    try db.execute(sql: """
        CREATE TABLE base (id INTEGER PRIMARY KEY, name TEXT);
        CREATE VIEW View_Item AS SELECT * FROM base;
        """)

    try db.execute(sql: "INSERT INTO base (id, name) VALUES (1, 'test');")

    struct Item: TableRecord, FetchableRecord, Decodable, Hashable {
        static let databaseTableName = "View_Item"
        var id: Int64
        var name: String
    }

    let item = try Item.filter(Column("id") == 1).fetchOne(db)
}

[groue]

Thank you @gverdouw 👍

I can reproduce the issue, and I'll be able to fix it.

The cause is that GRDB likes to generate "nice looking" SQL. In particular it attempts at avoiding appending LIMIT 1 to SQL queries when it can prove that someRequest.fetchOne(db) can not return more than one row. GRDB 5 has changed the heuristic for this test, and introduced a bug with views. This is the bug that needs a fix!

[groue]

Closing in favor of #814

[groue]

The fix has shipped in v5.0.0-beta.9

[gverdouw]

v5.0.0-beta9 fixed my issues, thanks again!