I don't (yet) know why your query is not working. But thanks for sharing your
sample database. I don't think I've ever seen a schema that is quite that
intense, with deeply nested views that make extensive use of
recursive common table expressions and window functions.
This one query:
>
EXPLAIN QUERY PLAN
SELECT * FROM _viewNetworkNodesPredSuccCompletness_;
Results in 15179 lines of query plan explanation output! The corresponding
prepared statement contains 314,248 byte-code instructions and
uses 11,231 distinct b-tree cursors.
For years, I've been giving talks that encourage developers to embrace the
power of the SQL query language - to move beyond simple SELECT and INSERT
statements mixed with a pile of procedural code and instead write complex
queries that give the answer directly. I often use some of the queries generated
by Fossil as examples, pointing out how a few lines (or a few dozen lines)
of SQL can replace thousands and thousands of lines of procedural code. Doing
so reduces the number of bugs (which are roughly proportional to the number
of lines of code) and also often result in faster solutions as well.
But you, sir, have taken this idea to a whole new level.
I don't know if we'll be able to fix your problem or not. But I can promise
that we will keep your schema around to use as a stress-test for SQLite's
parser and code generator.