The initial value of `lastInsertedRowID`

[rakuyoMo]

What did you do?

After the application starts, I initialize a DatabaseQueue object, and then I use this object to insert data into Table A and Table B respectively. Before the insert operation, both Table A and Table B are blank tables.

My code is roughly as follows:

// It can be simplified to understand that it is initialized in AppDelegate and applied elsewhere in the App.
database = try DatabaseQueue(path: filePath, configuration: config)

// In One.swift
try database.writeWithoutTransaction {
    try modelA.insert($0)
}

// In Other.swift
try database.writeWithoutTransaction {
    for key in keys {
        let modelB = createModelB(id: $0.lastInsertedRowID)
        try modelB.insert($0)
    }
}

What did you expect to happen?

I expect the above statement to execute successfully

What happened instead?

However, I found that when I inserted data into table A and prepared to insert data into table B, lastInsertedRowID returned 1 for the first time instead of the expected 0. And it also returned 1 for the second time.

I found that it was written in the comment that If no row has ever been inserted using this database connection.

According to my sample code, does this mean that I used the same database connection? So even if Table B is a blank table, the initial value of lastInsertedRowID is 1 instead of 0?

Environment

GRDB flavor(s): GRDB GRDB version: 6.27.0 Installation method: Tuist with SPM Xcode version: 15.3 Swift version: 5.10 Platform(s) running GRDB: iOS macOS version running Xcode: 14.5

[rakuyoMo]

I found that when I initialized the database and first got the value of lastInsertedRowID, it returned 1 instead of 0.

https://github.com/groue/GRDB.swift/blob/78448cf8f4ed39b259bd6718d074a57ae3b2e8ba/Documentation/DemoApps/GRDBDemoiOS/GRDBDemoiOS/AppDatabase.swift#L175

If you breakpoint in this line of the demo, when the demo is first run on the simulator, lastInsertedRowID still returns 1. At this time, the data table is empty.

[groue]

Hello @rakuyoMo,

does this mean that I used the same database connection? So even if Table B is a blank table, the initial value of lastInsertedRowID is 1 instead of 0?

Yes. The last inserted rowID is global for all tables in a given database connection. See the SQLite reference for more information.

You might have to update your application logic.

[groue]

Side note: your usage of writeWithoutTransaction instead of write is concerning, because nothing in your sample code shows that you need to explicitly avoid a transaction. Do as you wish of course, but the plain write should be your default. See Transactions and Savepoints for more information about the caveats of writeWithoutTransaction.

[rakuyoMo]

@groue

Yes. The last inserted rowID is global for all tables in a given database connection. See the SQLite reference for more information.

You might have to update your application logic.

There may be some time difference here, can you look at the description I mentioned above to reproduce in the demo?

I think that is using the database connection to get the lastInsertedRowID when all the data tables are blank, but it is still 1.

Side note: your usage of writeWithoutTransaction instead of write is concerning, because nothing in your sample code shows that you need to explicitly avoid a transaction. Do as you wish of course, but the plain write should be your default. See Transactions and Savepoints for more information about the caveats of writeWithoutTransaction.

Thanks for the heads up, but please keep it in mind, I have a safe "rollback" logic somewhere else.

[groue]

There may be some time difference here, can you look at the description I mentioned above to reproduce in the demo?

I think that is using the database connection to get the lastInsertedRowID when all the data tables are blank, but it is still 1.

There's nothing I can do here. GRDB outputs the raw result of SQLite's last inserted row id. Adapt your assumptions to SQLite, because SQLite won't adapt the other way around.

[rakuyoMo]

There's nothing I can do here. GRDB outputs the raw result of SQLite's last inserted row id. Adapt your assumptions to SQLite, because SQLite won't adapt the other way around.

https://github.com/groue/GRDB.swift/blob/78448cf8f4ed39b259bd6718d074a57ae3b2e8ba/GRDB/Core/Database.swift#L171

Do the comments here need to be modified, because in fact it does not return 0

[groue]

This comment reproduces exactly the SQLite documentation (go read it, this is interesting, and I gave you the link above).

Another insert was probably performed before: see Tracing SQL Statements to figure it out.

I would be surprised if you found a bug in SQLite - in which case this repository would not be the correct place to discuss this.

[rakuyoMo]

This comment reproduces exactly the SQLite documentation (go read it, this is interesting, and I gave you the link above).

I made sure I read the docs carefully before asking, but they didn't solve my problem.

Another insert was probably performed before: see Tracing SQL Statements to figure it out.

db.trace is a good idea, and I may have found the problem:

I guess it is because of this INSERT that after the App is installed for the first time, when the first INSERT statement in the business code is executed, lastInsertedRowID is 1 instead of 0.

This should be a reasonable behavior according to the documentation. But it is a bit unexpected.

For any users who are skeptical about this issue, the above should be the answer.