During Android's Room migration, it sets the value to 1 instead of using the default value of 0

I run some migrating tests. All I want now is to create a new table with only one column set to a default value of 0:

@Entity
data class FirstLoad (
    @ColumnInfo("isFirstCreated", defaultValue = "0")
    @PrimaryKey(autoGenerate = false)
    val isFirstCreated: Int = 0,
)

However when I run the test (shown below), the logs says: 'expected: 0 but was : 1'

The test itself:

@Test
    fun migration5To6(){

        var db = helper.createDatabase(DB_NAME,5).apply {
            execSQL("INSERT INTO user VALUES('testValue','testValue2','0','0')")
            close()
        }

        db = helper.runMigrationsAndValidate(DB_NAME,6, true, UserDatabase.migration5To6)
        db.execSQL("INSERT INTO FirstLoad DEFAULT VALUES")

        val resultCursor = db.query("SELECT * FROM FirstLoad") 
        assertThat(resultCursor.moveToFirst()).isTrue()
        val isFirstCreatedIndex = resultCursor.getColumnIndex("isFirstCreated")
        assertThat(isFirstCreatedIndex).isNotNull()
        assertThat(isFirstCreatedIndex).isEqualTo(0)
        val value = resultCursor.getInt(isFirstCreatedIndex)
        assertThat(value).isEqualTo(0)
        resultCursor.close()
}

So, each line passes the test except assertThat(value).isEqualTo(0) even though migration5To6 looks like this:


        val migration5To6 = object: Migration(5,6){
          override fun migrate(database: SupportSQLiteDatabase) {
             database.execSQL(
             "CREATE TABLE IF NOT EXISTS FirstLoad (isFirstCreated INTEGER NOT NULL PRIMARY KEY DEFAULT 0)"
              )
           }
        }

What I did that helped:

I manually provided values for the isFirstCreated column

db.execSQL("INSERT INTO FirstLoad VALUES('0')")

Furthermore, I asked chat gpt many times and it confirmed that my code is correct. There are no erros that would result in the creation of a value 1 for isFirstCreated .

Solution

All I want now is to create a new table with only one column set to a default value of 0:

A solution is to use INSERT INTO firstload VALUES(0);

However

The insertion of 1 (actually an SQLite generated value) is how SQLite works when a row is inserted using INSERT DEFAULT VALUES AND the COLUMN is defined using INETGER PRIMARY KEY.

This is because a column that is defined as INTEGER PRIMARY KEY (i.e. an integer type annotated with @PrimaryKey when using Room) is a special handling situation where the column is in fact an alias of the normally hidden rowid column and in the absence of a value being provided will be 1 greater than the highest existing value of the column. In the case of no value existing then the generated value is 1.

It should be noted that autoGenerate=false does not turn off this feature. Rather it just turns off the inclusion of the AUTOINCREMENT keyword, a constraint(rule) that restricts the generated value to being a value that MUST be greater than any value ever used (even if rows have been deleted/updated).

Perhaps consider the following:-

DROP TABLE IF EXISTS firstload;
CREATE TABLE IF NOT EXISTS `FirstLoad` (`isFirstCreated` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`isFirstCreated`));
INSERT INTO firstload DEFAULT VALUES;
INSERT INTO firstload DEFAULT VALUES;
INSERT INTO firstload DEFAULT VALUES;
INSERT INTO firstload VALUES(0);
SELECT rowid,* FROM firstload;
DROP TABLE IF EXISTS secondload;
CREATE TABLE IF NOT EXISTS `Secondload` (`isFirstCreated` INTEGER NOT NULL DEFAULT 0);
INSERT INTO secondload DEFAULT VALUES;
INSERT INTO secondload DEFAULT VALUES;
INSERT INTO secondload DEFAULT VALUES;
INSERT INTO secondload VALUES(0);
SELECT rowid,* FROM secondload;
/* Cleanup */
DROP TABLE IF EXISTS firstload;
DROP TABLE IF EXISTS secondload;

The first SELECT on the Firstload table results in:-

isFirstCreated  isFirstCreated(1)
0               0
1               1
2               2
3               3

As can be seen the rowid column exists and the values exactly match the isFirstCreated column. They are in fact different ways of referencing that same (_rowid and also oid)

However, with secondload, where the isFirstCreated column is not defined using INTEGER PRIMARY then the results are:-

rowid   isFirstCreated
1       0
2       0
3       0
4       0

As can be seen, as the isFirstCreated column is not defined as INTEGER PRIMARY KEY, then the default value of 0 has been set. However, the rowid is a generated value.

It makes little sense to specify a default value on a PRIMARY KEY, the value MUST be a UNIQUE value so there is a very good chance that utilising the default value will result in a UNIQUE conflict (unless of course if that column is also an INTEGER type).