Android Room inner join returns columns which are not used

Question

I have a table for orders and customers. Customers contains an order ID and email. I wish to search for orders by customer email.

This is what I have come up with:

@RewriteQueriesToDropUnusedColumns
@Query(
    "SELECT * FROM orders INNER JOIN order_customers ON order_customers.order_id = orders.id " +
    "WHERE (order_customers.email LIKE '%' || :email || '%') ORDER BY orders.collection_time DESC"
)
protected abstract fun loadByEmail(email: String): LiveData<List<Order>>

I get a cursor mismatch warning "the query returns some columns which are not used" that I am loading all of the customer columns, when really all I want is orders. Adding the suggested @RewriteQueriesToDropUnusedColumns does not do anything. So what is the best syntax here? Should I just be embedding customers into the order DAO, or is there a simple solution without remodeling and migrating the data?

I can see that instead of saying SELECT * I can individually specify every order column but that is supposed to be the whole reason for Room...

Answer 1

You can use SELECT orders.* FROM orders INNER JOIN ....

Adding the suggested @RewriteQueriesToDropUnusedColumns does not do anything.

This could be because of the caveat:-

Note that Room will not rewrite the query if it has multiple columns that have the same name as it does not yet have a way to distinguish which one is necessary.

I'd suggest always using unique column names, doing so can avoid issues with ambiguities

Note that it appears that when there are duplicate column names, then the value of last column with the duplicated name is used by room. As can be seen in this example