AndroidでSQLiteがバックエンドのJetpack Roomを使っていた時の話題。
SELECT column_a, column_b, column_c, MAX(column_d) FROM foo GROUP BY column_a
このようなSQLにおいて GROUP BY に使われる column_a と、集計関数（この場合は MAX ）に使われる column_d 以外の column_b, column_c の2つのカラムは bare columns と呼ばれる。SQLiteでは集計関数が MAX か MIN の場合には、これらbare columnsの値が、その集計関数で返される行に由来することが保証されている（いくつかの例外はある）。
Special processing occurs when the aggregate function is either min() or max(). Example:
SELECT a, b, max(c) FROM tab1 GROUP BY a;If there is exactly one min() or max() aggregate in the query, then all bare columns in the result set take values from an input row which also contains the minimum or maximum. So in the query above, the value of the "b" column in the output will be the value of the "b" column in the input row that has the largest "c" value. There are limitations on this special behavior of min() and max():
1. If the same minimum or maximum value occurs on two or more rows, then bare values might be selected from any of those rows. The choice is arbitrary. There is no way to predict from which row the bare values will be choosen. The choice might be different for different bare columns within the same query. 2. If there are two or more min() or max() aggregates in the query, then bare column values will be taken from one of the rows on which one of the aggregates has their minimum or maximum value. The choice of which min() or max() aggregate determines the selection of bare column values is arbitrary. The choice might be different for different bare columns within the same query. 3. This special processing for min() or max() aggregates only works for the built-in implementation of those aggregates. If an application overrides the built-in min() or max() aggregates with application-defined alternatives, then the values selected for bare columns will be taken from an arbitrary row.
SQLite以外のRDBではこのような動作は保証されないのでポータブルではなく避けた方がよさそうではあるが、AndroidのRoomの場合はSQLiteを前提として使ってもいいんじゃなかろうか。
