I found an assertion failure while SQLite (latest, eb94ae13206762a4) executes the following queries.

CREATE TABLE v0 (c1 UNIQUE, c);
INSERT INTO v0 (c) VALUES (0);
CREATE VIEW v1 AS SELECT c1 COLLATE NOCASE FROM v0;
SELECT 0 FROM v0 LEFT JOIN v1 AS a0 GROUP BY 1 HAVING (SELECT substr(a0.c1, lag(0) OVER (PARTITION BY 0)));

Here are the outputs:

sqlite3: sqlite3.c:93444: sqlite3VdbeExec: Assertion `pC!=0' failed.
[1]    867448 abort      ./sqlite3 < poc

We discussed this problem (forum post: 0b53708c95), here is the patch 4c5a3c5fb351cc1c. It seems the patch is incomplete and there is another case that can trigger this assertion failure.

Here is the result of bisecting:

  5 BAD     2022-07-25 16:06:14 b52393ac28debe98 CURRENT
  3 GOOD    2022-07-25 11:04:13 836fa097060dadeb

The assertion failure may be caused by the optimizations. SQLite crashes by default with all optimizations enabled. If I disable the SQLITE_CoverIdxScan optimization, SQLite works well.

.testctrl optimizations 0x00000020;

My compilation flags:

    export CFLAGS="-g -O0 -DSQLITE_DEBUG 
                -DSQLITE_ENABLE_TREETRACE 
                -DSQLITE_ENABLE_WHERETRACE
                -DSQLITE_ENABLE_CURSOR_HINTS 
                -DSQLITE_COUNTOFVIEW_OPTIMIZATION 
                -DSQLITE_ENABLE_STAT4"