SQLite Forum

order of INSERT affects the output
Not a bug.

SQLite allows columns in an aggregate query that are not arguments to
an aggregate function nor arguments to the GROUP BY clause.  This is
not standard SQL, and is sometimes criticized.  But it does have its uses.

Here is a quote from the [relevant documentation][1]:

The usual case is that all column names in an aggregate query are either arguments to aggregate functions or else appear in the GROUP BY clause. A result column which contains a column name that is not within an aggregate function and that does not appear in the GROUP BY clause (if one exists) is called a "bare" column. Example:
    SELECT a, b, sum(c) FROM tab1 GROUP BY a;
In the query above, the "a" column is part of the GROUP BY clause and so each row of the output contains one of the distinct values for "a". The "c" column is contained within the sum() aggregate function and so that output column is the sum of all "c" values in rows that have the same value for "a". But what is the result of the bare column "b"? The answer is that the "b" result will be the value for "b" in one of the input rows that form the aggregate. The problem is that you usually do not know which input row is used to compute "b", and so in many cases the value for "b" is undefined.

In your case, the c0 column in the HAVING clause is a bare column.  It can
take on any value within its group.  Sometimes it takes on the value '-1'
which is considered TRUE, and other times it takes on the value 'a' which is
considered FALSE.

## Why Does SQLite Allow This Kind Of Confusing Behavior

When I was first writing SQLite, I attempted to follow [Postel's law][2].
This means that I wrote SQLite so that it would accept unusual inputs
(such as your example) and do the best it could with them rather than raise an
error.  You could make an argument that this was a bad choice, and that I should
have made SQLite more intolerant of goofy inputs and more likely to raise errors
when it sees something unusual.  However, there are now so many millions of applications
in the wild that use SQLite that I am reluctant to make it more strict for fear
of breaking legacy.  So this potentially confusing situation will containue
to exist.

[1]: https://www.sqlite.org/lang_select.html
[2]: https://en.wikipedia.org/wiki/Robustness_principle