order by date is not working in android 11 sqllite

Question

I am getting a weird problem. with SQL lite query in android 11 devices only.

@Query("SELECT *, SUM(case when isReadMessage = 0 then 1 else 0 end) as countMessage from chat_message_history\n" +
            " where messageUser is not null GROUP BY messageUser ORDER by messageDate desc")
    LiveData<List<MessagesData>> getAllLiveUniqueUsers();

The DAO class

@Entity(tableName = "chat_message_history", indices = {
    @Index(value = "mesId")})
public class MessagesData {


@PrimaryKey(autoGenerate = true)
private int mesId;
private String message;
@TypeConverters(DateConverter.class)
private Date messageDate;
private boolean isReadMessage;
private int countMessage;
private String messageUser;
...}

Date Converter class

public class DateConverter {

@TypeConverter
public static Date toDate(Long timestamp) {
    return timestamp == null ? null : new Date(timestamp);
}

@TypeConverter
public static Long toTimestamp(Date date) {
    return date == null ? null : date.getTime();
}
}

The output should be order by date which is working in android 6 - android 10. In android 11 its not working. Please help me to solve this problem.

Answer 1

Maybe your query worked coincidentally in previous versions, but actually it is wrong.

It is an aggregation query that counts the number of unread messages of each user of the table chat_message_history.

The flaw in your query is that you want to sort by the date of the message, but which date?
For each user exist many messages.
Which of these dates do you want to be used in the final sorting?
Maybe you want the first or maybe the last.

In this case, SQLite will chose arbitrarily a date (usually it picks the first but this is not guaranteed).

Use an aggregation function like MIN() or MAX() in the ORDER BY clause and also simplify the CASE expression:

SELECT *, SUM(isReadMessage = 0) AS countMessage 
FROM chat_message_history
WHERE messageUser IS NOT NULL 
GROUP BY messageUser 
ORDER by MAX(messageDate) DESC;

Everything I mentioned above about SQLite picking arbitrary values for messageDate also apply the values of the columns returned by SELECT *.

It would be better if you return only aggregated columns like:

SELECT messageUser,
       MAX(messageDate) last_date, 
       SUM(isReadMessage = 0) AS countMessage 
FROM chat_message_history
WHERE messageUser IS NOT NULL 
GROUP BY messageUser 
ORDER by last_date DESC;