Search code examples
androidsqliteandroid-sqliteandroid-room

Room / SQLite query with min(...) without returning any result if nothing found

Currently stuck at a SQL roadblock and I am having no luck in finding any answers for my issue due to how generic the relating words are.

I have two queries that appear to be the exact same thing, but yield a different outcome.

For the sake of these examples assume that there is NO entry with id 123 and MyTable has the following columns: id, time, and data.

One query is for an entry that does not exist with all the columns

SELECT id, data, time FROM MyTable WHERE id = 123

which returns no results.

The other query is also for an entry that does not exist with all the columns

SELECT id, data, min(time) FROM MyTable WHERE id = 123

which returns one result where every column has the value null

Example of this scenario from Android Studio:

returns no results, as desired enter image description here

returns one result with all columns as null enter image description here

same issue happens even if I name the column with the same name enter image description here

I am trying to achieve the same behavior in the second query as the first one; if there are no results then return no results, instead of returning one result with all columns with null for values.

I understand I can work around this by doing a sub-query and querying that, but I would prefer to use a single query as simple as the ones above.

Is this not possible or is there a way that I can achieve that in Android Room Library / SQLite?

EDIT: The question was initially incorrect, thanks to users in the comments I realized the issue happens whenever using min(...) in the query so I updated the question to reflect the correct problem.

Solution

  • This:

    SELECT id, data, min(time) FROM MyTable WHERE id = 123

    is an aggregate query without a GROUP BY clause and as such it will always return exactly 1 row.

    If you want it to return nothing in case the condition in the WHERE clause fails, use EXCEPT:

    SELECT id, data, min(time) FROM MyTable WHERE id = 123
EXCEPT
SELECT null, null, null; -- as many null columns as the main selection

    or, use a non-standard SQL feature of SQL with a HAVING clause in an aggregate query without a GROUP BY clause (which will not work in other databases):

    SELECT id, data, min(time) FROM MyTable WHERE id = 123
HAVING id IS NOT NULL;

    or, with a GROUP BY clause:

    SELECT id, data, min(time) FROM MyTable WHERE id = 123
GROUP BY id
HAVING id IS NOT NULL;

    See the demo.