Search code examples
androidsqliteandroid-sqlite

SQLite Group By results order behaviour change 3.28.0 onwards

I have noticed an issue with the order of results using "GROUP BY" with later versions of SQLite that am struggling to solve. I first noticed it when Android 11 came out as it uses SQLite 3.28.0. I have now pinned it down to a behaviour change in SQLite itself. The issue is the the order of results is changed depending on the version. I'm not sure if it's a bug or intended behaviour or an error on my part (albeit an error that has been working fine for 8 years with older SQLite versions).

Example table:

id|year|command |code
1 |2005|TV      |A
2 |2005|TV-CD   |B
3 |2005|CD      |B
4 |2010|TV      |B
5 |2015|TV      |C

If I run the following command

SELECT * FROM myTable  GROUP BY command ORDER BY _id

With in SQLite 3.22 (Android 10) or 3.23.1 I get:

2|2005|TV-CD|B
3|2005|CD   |B
5|2015|TV   |C

Which is what I want...

If I run the same command in SQLite 3.28 (Android 11) or higher I get

1|2005|TV   |A
2|2005|TV-CD|B
3|2005|CD   |B

Here's a quick sample of the table if you want to try it for yourself

CREATE TABLE 'myTable' ('_id' integer PRIMARY KEY  ,'year' NUMERIC ,'command' TEXT, 'code' TEXT);
INSERT INTO myTable VALUES ("1","2005","TV","A");
INSERT INTO myTable VALUES ("2","2005","TV-CD","B");
INSERT INTO myTable VALUES ("3","2005","CD","B");
INSERT INTO myTable VALUES ("4","2010","TV","B");
INSERT INTO myTable VALUES ("5","2015","TV","C");
SELECT * FROM myTable  GROUP BY command ORDER BY _id

https://www.jdoodle.com/execute-sql-online/ was useful for testing as it allows you to change the SQLite version on the fly.

Solution

  • As I already mentioned in my comment, the result of a query like:

    SELECT * FROM myTable  GROUP BY command

    is unpredictable, because GROUP BY should be used only for aggregation, something like:

    SELECT command, MAX(year) AS max_year 
FROM myTable  
GROUP BY command

    which will return the max year for each command.

    So if the behavior of GROUP BY with SELECT * ... has changed in newer versionsshould not be a problem if you did not use such queries.

    From what I read in your question, you are expecting in the results for each command the row with the max id.

    In standard sql and without window functions, which are still not supported in most versions of android SQLite, you could aggregate first and then join to the table.

    But, SQLite has a documented feature to use so that you can use bare columns.
    This is valid:

    SELECT MAX(_id) AS _id, year, command, code
FROM myTable  
GROUP BY command
ORDER BY _id

    You will get the row with the max _id for each command.