SQLite Forum

Bug: unexpected result from an empty table
Login
The "t1.c1" value is a "[bare column][1]".  Among SQL database engines, SQLite
is the only one that allows bare columns in aggregate queries.  It has always
been implied, but never explicitly stated, that the value of a bare column on
an aggregate query that has no input rows is arbitrary.  The fix for this problem
is to update the documentation to actually say that, which I have down
[here][2].

Think about it.  The value of a bare column will be one of the values that
that column takes on for the duration of the aggregate.  But if the aggregate
has no input rows, what value does it take?

Usually the value of a bare column in an aggregate with no inputs will be
NULL.  But in this specific example, the query optimizer recognizes that
the value of "t1.c1" will always be the constant "1", so it just hard-codes
a "1" in that spot.


[1]: https://sqlite.org/lang_select.html#bareagg
[2]: https://sqlite.org/docsrc/info/cf8be0cd71d24e5e