In the title I was meant to write 1 new column, not 2 new columns.

Curious enough, if I just rename that newColumn to anything else, the auto migration correctly creates a database SQL entry for that, like this:

database.execSQL("ALTER TABLE 'SubMain' ADD COLUMN 'newColumnRENAMED' INTEGER NOT NULL DEFAULT 0");

Which made me think if maybe the table somehow already had that column from before, so to confirm this I renamed the column to the same original name, cleaned the app, ran the build, and then I completely removed the column from the entity, but the auto migration file did not add any execSQL entry to remove that column. It's like the column is being arbitrarily completely ignored from the auto migration logic.

I tried to check the auto migration builder logic, but I cannot find it, nor do I know how to inspect such a thing since it does not run in the app, but inside the IDE. Why in the world would this specific column be causing this problem?

I think I found the problem, but I cannot check why it is happening because I don't know how to check the automigration logic builder, I can only confirm the result of the bug, but not the origin of it in the source code of the auto migration implementation builder.

Besides the entity I described previously, where I add a newColumn to it, I also happened to have another entity that also has an exact same column being added to it, same name, and same type, just on a completely different entity.

It seems that when the auto migration builder logic starts reading the changes it is ignoring the newColumn in SubMainEntity because it already detected that another entity, let's call it FirstMainEntity, also has a new column named newColumn, and so when it generates the auto migration schema this is the result

class Database_AutoMigration_1_2_Impl extends Migration {
  public Database_AutoMigration_1_2_Impl() {
    super(1, 2);
  }

  @Override
  public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE `FirstMain` ADD COLUMN `newColumn` INTEGER NOT NULL DEFAULT 0");
  }
}

However, if I rename the newColumn in FirstMainEntity to something different like this:

    @ColumnInfo(name = "newColumnTest", defaultValue = "0")
    var newColumnTest = false

And run the build, this is what the auto migration generates now:

class Database_AutoMigration_1_2_Impl extends Migration {
  public Database_AutoMigration_1_2_Impl() {
    super(1, 2);
  }

  @Override
  public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE `SubMain` ADD COLUMN `newColumn` INTEGER NOT NULL DEFAULT 0");
    database.execSQL("ALTER TABLE `FirstMain` ADD COLUMN `newColumnTest` INTEGER NOT NULL DEFAULT 0");
  }
}

And now the migration runs without an issue.

I can only assume that this is a bug in the auto migration logic, perhaps the new columns are being cached as keys, and being used per database, instead of per table, causing any subsequent table that happens to have a new column with the same @ColumnInfo(name =... - and I have to emphasize name here because even when I changed its variable name, or type it made no difference - to not be recognized as having a new column added, except for the first table that was detected.

Great detective work! You are totally right in that we have a 'key' bug. Specifically we are incorrectly keeping track of 'added columns without complex changes' using the column name, but forgot to also include the table as part of the key.

See: https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:room/room-compiler/src/main/kotlin/androidx/room/util/SchemaDiffer.kt;l=98

I'll fix this soon.