Android Studio - Room query different result than expected

Question

I'm having a difficult time understanding why does the same query on the same database is correct on my local testing environment (my computer) and is wrong over my device/emulator.

The database is literally the same (copied from emulator to computer).

SELECT * FROM (
    SELECT name, max(date_col) as date_col, value FROM tbl
    WHERE date_col <= '2021-06-30'
    GROUP BY name
    UNION 
    SELECT name, min(date_col) as date_col, value FROM tbl
    GROUP BY name
    ORDER BY date_col DESC
)
GROUP BY name

The schema for the table is (id, date_col, name, value).

What I'm trying to do is select all the rows with the nearest date to the supplied date. I'm not quite sure that this is the best way of doing it so any suggestions are welcomed

When trying this query in my computer (SQLITE v3.27.2) it works as intended and when testing on a device/emulator (Tried multiple API levels such as 23, 27, 28) they all failed.

Edit: Table data:

|name |date_col  |value|
|-----|----------|-----|
|NAME6|2021-06-29|71   |
|NAME7|2021-06-29|80   |
|NAME1|2021-06-29|2925 |
|NAME4|2021-06-29|182.0|
|NAME2|2021-06-29|365  |
|NAME3|2021-06-29|81.0 |
|NAME5|2021-06-29|0.25 |
|NAME7|2021-06-27|81.0 |
|NAME1|2021-06-27|3000 |
|NAME5|2021-06-01|0.35 |
|NAME6|2021-06-01|68.0 |
|NAME5|2021-06-28|0.15 |

Results on device (for date 2021-06-28):

|name |date_col  |value|
|-----|----------|-----|
|NAME1|2021-06-27|3000 |
|NAME2|2021-06-29|365.0|
|NAME3|2021-06-29|81.0 |
|NAME4|2021-06-29|182.0|
|NAME5|2021-06-01|0.35 |
|NAME6|2021-06-01|68.0 |
|NAME7|2021-06-27|81.0 |

Results on computer (for date 2021-06-28):

|name |date_col  |value|
|-----|----------|-----|
|NAME1|2021-06-27|3000 |
|NAME2|2021-06-29|365.0|
|NAME3|2021-06-29|81.0 |
|NAME4|2021-06-29|182.0|
|NAME5|2021-06-28|0.15 |
|NAME6|2021-06-01|68.0 |
|NAME7|2021-06-27|81.0 |

As you can see, in this example the difference is with name5, it should be 0.15 but on device for some reason its 0.35.

What could be the reason for these differences?

Thank you very much!

Answer 1

SQLite allows statements with SELECT * and GROUP BY, but the resulting rows are arbitrary.

If you want to use this in lower API levels, you can't use window functions which would easily solve the problem like this:

SELECT name, date_col, value 
FROM (
  SELECT *, ROW_NUMBER() OVER (
              PARTITION BY name 
              ORDER BY date_col < '2021-06-28' DESC,
                       abs(strftime('%s', date_col) - strftime('%s', '2021-06-28'))
                       
            ) rn
  FROM tbl
)
WHERE rn = 1
ORDER BY name;

You can use a correlated subquery:

SELECT t1.name, t1.date_col, t1.value 
FROM tbl t1
WHERE t1.date_col = (
  SELECT t2.date_col 
  FROM tbl t2
  WHERE t2.name = t1.name 
  ORDER BY date_col < '2021-06-28' DESC,
           abs(strftime('%s', t2.date_col) - strftime('%s', '2021-06-28'))
  LIMIT 1
)
ORDER BY name;

See the demo.