Sqlite: Why does 'select min(time) from asdftable' return a blank row when DB is empty?

Question

This is an oddity. I have an empty SQLite DB.

When I execute select min(time) from asdftable I get a blank row back. When I execute select time from asdftable I get nothing back (which is proper).

Any ideas why this is?

Answer 1

Aggregate functions all return something even if nothing is found, simply because they are implicitly grouping all (any) rows in your table to get you an aggregate value. Take count for example:

sqlite> create table foo (a int not null);
sqlite> select count(a) from foo;
0

null is to min as 0 is to count where no rows are returned.

This can be a useful property. Consider the following example:

sqlite> select ifnull(min(a), "I'm null") from foo;
I'm null

If you want to filter this case so no records are returned, you can use a having clause (and make the grouping explicit):

sqlite> select min(a) as min_a from foo group by a having min_a not null;