One of the (many) optimizations in SQLite is that if an outer query has a WHERE clause and the body of the query is a subquery (or view) then the WHERE clause is "pushed down" into the sub-query so that the query planner can perhaps make use of that WHERE clause when optimizing. Except, if the subquery is an aggregate then the WHERE clause must be converted into a HAVING clause as it is pushed down in order to keep its meaning.

This conversion of a WHERE clause in the outer query into a HAVING clause in the subquery is why there is a "HAVING" in your error message.

Your bug report is actually an enhancement request. SQLite has historically not allowed a HAVING clause unless there is also a GROUP BY clause. But a better approach is to allow a HAVING clause on any aggregate query. That's what PostgreSQL does. And, it turns out, it isn't very difficult to enhance SQLite to do the same.

In other words, this SQL works in PostgreSQL:

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1),(2),(3);
SELECT sum(a) FROM t1 HAVING sum(a)>0;

The query above has historically given an error in SQLite, complaining about a HAVING clause without a GROUP BY. But beginning with check-in 9322a7c21f1c22ba it now also works for SQLite. That change also resolves the issue raised by this forum thread.

Thanks!

The query should work previously, not always give this error. I also tried it on v3.31.1 and no error is given.

Just for fun I did try the query on 3.31.1 pre-compiled windows binary CLI, and for me it did indeed throw the error, so I'm not sure why your experience was different.

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t1(a INT);
sqlite> INSERT INTO t1 VALUES(1),(2),(3);
sqlite> SELECT sum(a) FROM t1 HAVING sum(a)>0;
Error: a GROUP BY clause is required before HAVING

As an aside, thanks for finding obscure problems, Jinsheng Ba.

Please try my original test case.

I am not able to observe the error on 3.31.1 for the rewritten test case as well.

Attached my commands to reproduce it on Linux:

wget https://github.com/sqlite/sqlite/archive/refs/tags/version-3.31.1.tar.gz
tar -zxvf version-3.31.1.tar.gz
cd sqlite-version-3.31.1
./configure --enable-all --enable-debug
make
 ./sqlite3
sqlite> CREATE VIEW v0(c0, c1) AS SELECT RANK() OVER (PARTITION BY 0), SUM(0);
sqlite> SELECT * FROM v0 WHERE 1;

You observe that it works on version 3.31.1 and does not work on 3.38.0. So bisect to find out where it changed from working to non-working. I did this in about 5 minutes and got this bisect result.

You can see that the problem was introduced on 2021-02-23, which by consulting the version log you can see is version 3.35.0. The check-in that bisect landed on is one that introduced the push-down optimization that is necessary for your particular script to fail. The addition of that optimization is notated as item 8g of the 3.35.0 change log.

I see. Thanks for detailed explanation!