3 ms·
SQLite requires that the self-reference be in the top-level FROM clause of the recursive part of a recursive CTE. PG apparently allows the self-reference to be
by SQLite 6y ago
SQLite requires that the self-reference be in the top-level FROM clause of the recursive part of a recursive CTE. PG apparently allows the self-reference to be down inside of subqueries, as long as there is only one reference.
I have make a copy of the Collatz Conjecture CTE that you linked to and was going to see if I could get it to work in SQLite for the next release cycle. I don't (yet) see any reason why it shouldn't work to have the recursive reference down inside a subquery, as long as there is only one recursive reference. No promises. We'll see how it goes.
- kragen 6y agoOh dear. I was hoping you wouldn't notice this discussion. Another step off the cliff into the Turing Tarpit... well, I hope my twisted piece of appalling SQL brings joy to your heart, at least, even if supporting it makes SQLite worse. And you are of course in a much better position to judge what would make SQLite better or worse than I am. Have you thought about offering an interface with an intentionally-Turing-incomplete subset of SQL in SQLite, for making queries that can be guaranteed to terminate? Because it doesn't sit right with me that SQL is Turing-complete now. https://news.ycombinator.com/item?id=26529789 https://news.ycombinator.com/item?id=26529789 goes into more detail on decidable query languages that can still accommodate transitive closure.