Android Room @RewriteQueriesToDropUnusedColumns not working

I have a Dao query with table joins:

@Query("SELECT * FROM t_work t1 INNER JOIN (SELECT *, max(gmt_create) FROM t_work_log GROUP BY work_id) t2 ON t1.id = t2.work_id;")
  fun getWorkAndLog(): Flow<Map<Work, WorkLog>>

Unable to complete the build, it reports the following error:

E:\AndroidProjects\MyApplication\app\build\tmp\kapt3\stubs\release\com\freedom\android\work\dao\IWorkDao.java:15:
The query returns some columns [max(gmt_create)] which are not used by any of [`Work`,`WorkLog`]

Different solutions were then prompted:

1. You can use @ColumnInfo annotation on the fields to specify the mapping.
2. You can annotate the method with @RewriteQueriesToDropUnusedColumns to direct Room to rewrite your query to avoid fetching unused columns.
3. You can suppress this warning by annotating the method with @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH).

I don't think any of the three solutions are ideal for me, but I'd like to try the second one and see what happens with the so-called rewrite.

@RewriteQueriesToDropUnusedColumns
  @Query("SELECT * FROM t_work t1 INNER JOIN (SELECT *, max(gmt_create) FROM t_work_log GROUP BY work_id) t2 ON t1.id = t2.work_id;")
  fun getWorkAndLog(): Flow<Map<Work, WorkLog>>

But it doesn't work, I still can't build, it still reports The query returns some columns, What I did wrong?

About Room's dependencies:

def room_version = "2.5.0"
implementation "androidx.room:room-runtime:$room_version"
annotationProcessor "androidx.room:room-compiler:$room_version"
// To use Kotlin annotation processing tool (kapt)
kapt "androidx.room:room-compiler:$room_version"
// To use Coroutine features, you must add `ktx` artifact from Room as a dependency. androidx.room:room-ktx:<version>
implementation "androidx.room:room-ktx:$room_version"
// optional - Paging 3 Integration
implementation "androidx.room:room-paging:$room_version"

Is it the best option to ignore this warning for this situation?

I think max(gmt_create) exists only for this function and it should not be added to the persistence class. Leave it to Room to rewrite the query sql, although I haven't seen the results yet, I think this rewrite might change my intentions.

First Edit:

After I close Android Studio and open it again, this compilation error disappears. So this could be a problem with Android Studio or Gradle? But this error will appear again from time to time.

It's tricky, and I can't fully reproduce the problem, which only comes up occasionally.

> The query returns some columns [max(gmt_create)] which are not used by any of [Work,WorkLog]

and

> max(gmt_create) is required, in a sense it is a query condition

It is not required in the output columns though AND as the warning says Room does not need it and ignores it.

• Perhaps the warning message should be along the lines of "Why are you telling me to work out this value for you to simply throw the value in the bin"

Now if instead of using the max function, you were to use the sum/total function then the result would not be as expected and would highlight the underlying flaw.

To demonstrate a Work object has the fields/columns

• id
• name
• other
• etc

and the WorkFlow object has the fields(columns)

• id
• work_id
• gmt_create
• another
• blah

So the following will create the 2 tables and load some data into them, using an SQLite tool (rather than writing all the code in a demo App)

CREATE TABLE IF NOT EXISTS t_work (id INTEGER PRIMARY KEY, name TEXT, other TEXT, etc TEXT);
CREATE TABLE IF NOT EXISTS t_work_log (id INTEGER PRIMARY KEY, work_id INTEGER, gmt_create INTEGER, another TEXT, blah TEXT);

INSERT INTO t_work VALUES 
	(1,'w1','otherw1','etcw1'),
	(2,'w2','otherw2','etcw2'),
	(3,'w3','otherw3','etcw3')
;
INSERT INTO t_work_log (work_id,gmt_create,another,blah) VALUES
	(1,10,900,'blah for w1-10-900')
	,(1,11,901,'blah for w1-11-901')
	,(1,8,902,'blah for w1-8-902')
	,(1,7,903,'blah for w1-7-903')
	,(1,12,904,'blah for w1-12-904')
	,(1,1,905,'blah for w1-1-905')
	,(1,21,906,'blah for w1-21-906')
	,(1,6,907,'blah for w1-6-907')
	,(2,111,801,'blah for w2-111-801')
	,(2,18,802,'blah for w2-18-802')
	,(2,17,803,'blah for w2-17-803')
	,(2,112,804,'blah for w2-112-804')
	,(2,11,805,'blah for w2-11-805')
	,(2,121,806,'blah for w2-121-806')
	,(2,16,807,'blah for w2-16-807')
	,(3,211,701,'blah for w3-211-701')
	,(3,28,702,'blah for w3-28-702')
	,(3,27,703,'blah for w3-27-703')
	,(3,212,704,'blah for w3-27-704')
	,(3,21,705,'blah for w3-21-705')
	,(3,221,706,'blah for w3-221-706')
	,(3,26,707,'blah for w3-26-707')
;

Now your query SELECT * FROM t_work t1 INNER JOIN (SELECT *, max(gmt_create) FROM t_work_log GROUP BY work_id) t2 ON t1.id = t2.work_id; results in :-

Room will do it's stuff, IGNORING, the max(gmt_create) column, and using the apparently correct value from the gmt_create column to assign a value to the gmt_create field. Other than the warning nothing untoward happens.

Now if the query were SELECT * FROM t_work t1 INNER JOIN (SELECT *, sum(gmt_create) FROM t_work_log GROUP BY work_id) t2 ON t1.id = t2.work_id; the result would be:-

The gmt_create column and the sum(gmt_create) differ, Room will assign the gmt_create column's value to the gmt_create field and the flaw will be noticable.

The options to avoid the warning AND to correctly get the derived value should be to either

• a) Use a POJO that has a field that Room will assigned the derived value to (e.g. a field name max(gmt_create)).
  • in which case your query would be fine.
• b) Have the query manipulate the values to produce output with suitable columns and values that will allow Room to assign appropriate values according to the requirements.
  • in which case the query based upon the demo could be SELECT t1.*,t2.id,t2.work_id,t2.[max(gmt_create)] AS gmt_create,t2.another,t2.blah FROM t_work t1 INNER JOIN (SELECT *, max(gmt_create) FROM t_work_log GROUP BY work_id) t2 ON t1.id = t2.work_id;

The latter query would output:-

If using sum instead of max then it would output:-

i.e. it is a) clear that the derived values have been placed into the gmt_create column ADN that the extraneous column has not been output, thus avoiding the warning and any need to suppress the warning.

• But note that then another factor is highlighted and that is that arbitrary values are assigned to other columns in a group.

  • see https://www.sqlite.org/lang_select.html#generation_of_the_set_of_result_rows

Additional

re:-

> After reading your answer, the best option in my case would have been to simply ignore the warning. But I'm curious about the @RewriteQueriesToDropUnusedColumns documentation that says annotation will cause Room to rewrite your Query methods, how is it rewritten? Does it just remove max(gmt_create) from my sql statement? This will result in incorrect query results.

1. Simplest is to ignore the warning.

2. According to a simple testRegarding the rest does it re-write the query BUT by embedding the original query as a subquery.

Consider:-

@Transaction
@Query("SELECT *, 'useless data' AS useless_column FROM job")
fun getAllJobsEtcWithUselessData(): List<JobWithWorkersAndPosterWithPosterWorker>

and thus:-

warning: The query returns some columns [useless_column] which are not used by a.a.so75656155kotlinroomrelationships.JobWithWorkersAndPosterWithPosterWorker. You can use @ColumnInfo annotation on the fields to specify the mapping. You can annotate the method with @RewriteQueriesToDropUnusedColumns to direct Room to rewrite your query to avoid fetching unused columns.  You can suppress this warning by annotating the method with @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH). Columns returned by the query: job_id, jobName, useless_column.
public abstract java.util.List<a.a.so75656155kotlinroomrelationships.JobWithWorkersAndPosterWithPosterWorker> getAllJobsEtcWithUselessData();

The generated java (in the java(generated) from Android View) includes:-

  @Override
  public List<JobWithWorkersAndPosterWithPosterWorker> getAllJobsEtcWithUselessData() {
    final String _sql = "SELECT *, 'useless data' AS useless_column FROM job";
    final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 0);
  ....

Change to add the @Rewrite.... :-

@Transaction
@Query("SELECT *, 'useless data' AS useless_column FROM job")
@RewriteQueriesToDropUnusedColumns
fun getAllJobsEtcWithUselessData(): List<JobWithWorkersAndPosterWithPosterWorker>

The warning is gone and the generated java is now :-

  @Override
  public List<JobWithWorkersAndPosterWithPosterWorker> getAllJobsEtcWithUselessData() {
    final String _sql = "SELECT `job_id`, `jobName` FROM (SELECT *, 'useless data' AS useless_column FROM job)";
    final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 0);
  ....

Actually quite a nice little trick. HOWEVER, as explained re sum v max the actual derived value is not available/used.

Now if you still have concerns, then you can do away with the @RewriteQueriesToDropUnusedColumns by:-

1. Include the annotation appropriately.
2. Compile.
3. Go to the java (generated)
4. Find the class that is the same name as the respective @Dao annotated interface/abstract class but suffixed with Impl
5. search for the function, the adjusted SQL can then be used to do what Room does and that is embed the original query as a subquery, and have the overall query only extract the required columns.

I This query

SELECT * FROM t_work t1 INNER JOIN (SELECT *, max(gmt_create) FROM t_work_log GROUP BY work_id) t2 ON t1.id = t2.work_id;

have a syntax error.
Try it

SELECT * FROM t_work t1 INNER JOIN 
( SELECT work_id, MAX(gmt_create) Amax_gmt_create FROM t_work_logGROUP BY work_id)
t2 ON t1.id = t2.work_id;