Status Update
Comments
da...@google.com <da...@google.com> #2
This is indeed a long standing issue in Room and will likely be more relevant with the map / multimap return type. I think we can show a warning to start but we'll also take a deeper look to see how we can solve the duplicate column name issue.
As a workaround you can always use a column alias and a POJO class with matching field name.
el...@google.com <el...@google.com>
da...@google.com <da...@google.com>
ap...@google.com <ap...@google.com> #3
Branch: androidx-main
commit 894b0ff2c1b095a46803975f400a561eb974ea28
Author: Daniel Santiago Rivera <danysantiago@google.com>
Date: Fri Oct 29 10:56:31 2021
Duplicate column resolution heuristic algorithm
AmbiguousColumnResolver contains an algorithm to map query result columns to data objects (POJOs) columns. We call data object columns 'mapping' and in a multimap query where there might be multiple data objects we refer to the list of all their columns 'mappings'. The algorithm uses a grouping / neighboring strategy to assign the duplicate columns to their right data objects matching their name along with taking into account the the nearby columns since in a star-projected query all columns coming from a table will appear in the result before the next table. Room will generate code that uses the algorithm at runtime if the query has a star-projection, if not then Room will use the algorithm during compile-time since the columns result order is known and fixed.
The algorithm does not solve all cases, specifically those where one of the data objects has a single column which is the duplicate column. For those situation Room will warn the user so that they alias the duplicate column. For other odd cases, Room will behave as it used to, picking the first result column that matches with the data object column.
Bug: 201306012
Bug: 212279118
Test: AmbiguousColumnResolverTest
Relnote: Room will now attempt to resolve ambiguous columns in a multimap query. This allows for JOINs with tables containing same-name tables to be correctly mapped to a result data object.
Change-Id: I4b444b042245a334cc3f362f3239721ce0b6bd1e
M room/room-compiler/src/main/kotlin/androidx/room/solver/query/result/EntityRowAdapter.kt
M room/room-compiler/src/main/kotlin/androidx/room/solver/TypeAdapterStore.kt
A room/integration-tests/kotlintestapp/src/androidTest/java/androidx/room/integration/kotlintestapp/test/AmbiguousColumnResolverTest.kt
M room/room-compiler/src/main/kotlin/androidx/room/solver/query/result/PojoRowAdapter.kt
M room/room-common/api/current.txt
M room/room-compiler/src/main/kotlin/androidx/room/vo/Warning.kt
M room/room-runtime/api/restricted_current.txt
A room/room-common/src/main/java/androidx/room/AmbiguousColumnResolver.kt
M room/room-common/api/public_plus_experimental_current.txt
M room/room-common/build.gradle
M room/room-compiler/src/main/kotlin/androidx/room/processor/ProcessorErrors.kt
M room/room-common/src/main/java/androidx/room/RoomWarnings.kt
M room/room-compiler/src/main/kotlin/androidx/room/solver/query/result/MultimapQueryResultAdapter.kt
M room/room-compiler/src/main/kotlin/androidx/room/solver/query/result/GuavaImmutableMultimapQueryResultAdapter.kt
M room/room-runtime/src/main/java/androidx/room/util/CursorUtil.kt
M room/room-compiler/src/main/kotlin/androidx/room/ext/javapoet_ext.kt
A room/room-compiler/src/main/kotlin/androidx/room/solver/query/result/AmbiguousColumnIndexAdapter.kt
M room/room-compiler/src/test/kotlin/androidx/room/processor/QueryMethodProcessorTest.kt
M room/room-compiler/src/main/kotlin/androidx/room/solver/query/result/ImmutableMapQueryResultAdapter.kt
A room/room-common/src/test/java/androidx/room/AmbiguousColumnResolverTest.kt
M room/room-compiler/src/main/kotlin/androidx/room/solver/query/result/MapQueryResultAdapter.kt
M room/room-common/api/restricted_current.txt
da...@google.com <da...@google.com>
[Deleted User] <[Deleted User]> #4
@Entity
data class RoomSubTrip(
@PrimaryKey val id: String,
val startDate: Long,
val endDate: Long?,
val estimatedActivity: UserActivity,
val tripId: String,
)
@Entity
data class RoomSubTripSensorData(
@PrimaryKey(autoGenerate = true)
val id: Int = 0,
val tripId: String,
val subTripId: String,
val recording: String
)
@Transaction
@Query(
"SELECT * FROM RoomSubTrip JOIN RoomSubTripSensorData " +
"ON RoomSubTrip.id = RoomSubTripSensorData.subTripId "
)
fun getSubTripWithSensorRecordings(): Map<RoomSubTrip, List<RoomSubTripSensorData>>
This return map with RoomSubTrip with id from RoomSubTripSensorData.id
da...@google.com <da...@google.com> #5
Sorry, the fix for this issue is in Room 2.5.0 and not 2.4.x, we are working on getting a stable release of 2.5.0 soon.
th...@gmail.com <th...@gmail.com> #6
Need to know if I should wait for the update or refactor my code.
da...@google.com <da...@google.com> #7
Room 2.5.0-rc01 was recently released, the next release will be 2.5.0. Unfortunately the US holidays are around the corner and the next androidx release will be early next year.
ne...@gmail.com <ne...@gmail.com> #8
Room 2.5.1, this issue still exists when selecting using a join to filter data but omitting the joined data in the result. E.g. when Entity1
has column named id
and Entity2
also has id
, and I use a @Query
that returns a List<Entity1>
with a simple join in order to filter only those of Entity1
who have a relation to certain Entity2
. The resulting List<Entity1>
contains instances of Entity1
whose ids are wrongly assigned to those of their respective joined Entity2
. And that is regardless whether @RewriteQueriesToDropUnusedColumns
is being used or not.
yb...@google.com <yb...@google.com> #9
#8, can you provide a sample app that reproduces that error? Seeing the query + entity1/2 should be enough. thanks.
be...@gmail.com <be...@gmail.com> #10
I'm also still seeing this issue in Room 2.6.0. I'm able to produce it using the query from the "
@Query(
"SELECT * FROM book " +
"INNER JOIN loan ON loan.book_id = book.id " +
"INNER JOIN user ON user.id = loan.user_id " +
"WHERE user.name LIKE :userName"
)
fun findBooksBorrowedByNameSync(userName: String): List<Book>
I created these entities to go with it:
@Entity
data class Book(
@PrimaryKey(autoGenerate = true) val id: Int,
val title: String,
)
@Entity
data class Loan(
@PrimaryKey(autoGenerate = true) val id: Int,
@ColumnInfo(name = "book_id") val bookId: Int,
@ColumnInfo(name = "user_id") val userId: Int,
)
@Entity
data class User(
@PrimaryKey(autoGenerate = true) val id: Int,
val name: String,
)
When testing it with this
@Test
fun returnsCorrectIds() {
val users = (1..5).map { User(id = 0, name = "User $it") }
userDao.insert(users)
val books = (1..5).map { Book(id = 0, title = "Book $it") }
bookDao.insert(books)
val insertedBooks = bookDao.getAll()
assertThat(insertedBooks).contains(Book(id = 4, title = "Book 4")) // Book 4 has id 4
loanDao.insert(Loan(id = 0, bookId = 4, userId = 2))
val borrowedBooks = bookDao.findBooksBorrowedByNameSync("User 2")
assertThat(borrowedBooks).containsExactly(Book(id = 4, title = "Book 4"))
}
the last assertion here fails:
value of: iterable.onlyElement()
expected: Book(id=4, title=Book 4)
but was : Book(id=2, title=Book 4)
So the Books that are returned all have the User's ID in the id
field. (If Book
had name
instead of a title
it would also return the User's name
as the Book's name
.) If I change the order of the two joins, then the returned Books have the ID of the Loan.
Description
Component used: Room
Version used: 2.4.0-alpha04
Devices/Android versions reproduced on: Pixel 3a emulator
Description:
It's not uncommon to have an
id
column of every database entity. However, the code generated using the new JOIN feature introduced in Room 2.4.0 doesn't have the ability to distinguish betweenid
columns from different entities, which can produce completely wrong results.I've attached a minimal working AS project. In this I've created a
User
and aUserComment
Room entity and attempt to retrieve a list of all Users mapped to theirUserComment
s. But because both entities contain anid
-column, Room gets confused and actually returns a list ofUser
s with ids taken fromUserComment
.As I understand it, differentiating columns with the same name is difficult for Room, but this issue makes the new feature much less useful and downright dangerous given how prevalent the name
id
for the column is.If it's not possible to fix it, perhaps the Room plugin could generate a warning or error if it picks up duplicate column names. As it stands, Room will just silently create faulty data.