I am having a “No such column: code 1 SQLITE_ERROR” but it does exists. On Room Database using SimpleSQLiteQuery in Kotlin.

Using this query shows no error:

select SubRoom.location as 'Sub Room Location', SubRoom.subroomname as 'Sub Room Name', SubRoom.subroomtype as 'Sub Room Type' from SubRoom

Using this query gives me [no such column] error:

select House.addressline1 as 'Address Line 1', House.addressline2 as 'Address Line 2', House.city as 'City', House.country as 'Country', House.geolocation as 'Geolocation', House.name as 'House Nickname', House.use as 'House Use', House.state as 'State', House.zipcode as 'Zip Code', SubRoom.location as 'Sub Room Location', SubRoom.subroomname as 'Sub Room Name', SubRoom.subroomtype as 'Sub Room Type' from House, Floor, Room Where House.houseId = Floor.houseId AND Floor.floorId = Room.floorId AND Room.roomId = SubRoom.roomId

The code use to generate the above query is to complex to post it here. I can assure the query is exactly that because I copied the query string from the variable in Debug mode.

These are my relevant Table Entities:

@Entity(tableName = "House",
indices = [
Index(value = ["name"], unique = true),
Index(name = "address", value = ["country","addressline1","addressline2","city","state","zipcode","addmarker"], unique = true)
]
)
data class HouseEntity(
@PrimaryKey(autoGenerate = true)
val houseId: Long,

@ColumnInfo(name = "name", collate = ColumnInfo.NOCASE)
val name: String,
val use: String,

@ColumnInfo(typeAffinity = ColumnInfo.BLOB)
var foto: ByteArray? = null,

var geolocation: String? = null,

@ColumnInfo(name = "country", collate = ColumnInfo.NOCASE)
var country:String = "",
@ColumnInfo(name = "addressline1", collate = ColumnInfo.NOCASE)
var addressline1:String = "",
@ColumnInfo(name = "addressline2", collate = ColumnInfo.NOCASE)
var addressline2:String = "",
@ColumnInfo(name = "city", collate = ColumnInfo.NOCASE)
var city:String = "",
@ColumnInfo(name = "state", collate = ColumnInfo.NOCASE)
var state:String = "",
@ColumnInfo(name = "zipcode", collate = ColumnInfo.NOCASE)
var zipcode:String = "",
@ColumnInfo(name = "addmarker")
var addmarker:Date?

)

@Entity(tableName = "SubRoom",
foreignKeys = [ForeignKey(entity = RoomEntity::class,
    parentColumns = ["roomId"],
    childColumns = ["roomId"],
    onDelete = ForeignKey.CASCADE)],
indices = [Index(value = ["roomId"], unique = false),
        Index(value = ["subroomname"], unique = false),
        Index(value = ["roomId","subroomname"], unique = true),
]
)
data class SubRoomEntity(
@PrimaryKey(autoGenerate = true)
val subroomId: Long,

@ColumnInfo(name = "roomId")
val roomId: Long,

@ColumnInfo(name = "subroomname", collate = ColumnInfo.NOCASE)
val subroomname: String,
val subroomtype: String, //walking closet, wall segment, inner floor, inner ceiling, sub area[bathroom,balcon,kitchen,etc]

val location: String?
)

////////// Just in case you need to see the other Tables that are refered in the query

@Entity(tableName = "Floor",
    foreignKeys = [ForeignKey(entity = HouseEntity::class,
        parentColumns = ["houseId"],
        childColumns = ["houseId"],
        onDelete = ForeignKey.CASCADE)],
    indices = [Index(value = ["houseId"], unique = false),
            Index(value = ["floorname"], unique = false),
            Index(value = ["houseId","floorname"], unique = true),
    ]
)
data class FloorEntity(
@PrimaryKey(autoGenerate = true)
val floorId: Long,

@ColumnInfo(name = "houseId")
val houseId: Long,

@ColumnInfo(name = "floorname", collate = ColumnInfo.NOCASE)
val floorname: String?,

val location: String?
)

@Entity(tableName = "Room",
foreignKeys = [ForeignKey(entity = FloorEntity::class,
    parentColumns = ["floorId"],
    childColumns = ["floorId"],
    onDelete = ForeignKey.CASCADE)],
indices = [Index(value = ["floorId"], unique = false),
        Index(value = ["roomname"], unique = false),
        Index(value = ["floorId","roomname"], unique = true),
]
)
data class RoomEntity(
@PrimaryKey(autoGenerate = true)
val roomId: Long,

@ColumnInfo(name = "floorId")
val floorId: Long,

@ColumnInfo(name = "roomname", collate = ColumnInfo.NOCASE)
val roomname: String,
val roomtype: String, //Dorm, Kitchen, bathroom, Living, etc.

val location: String?
)

Clearly the field does exist and there is not a typo error.