Thank you for the answer! At least now I know it can't be done this way. After some searching I was able to find this extension [ext/misc/closure.c](https://www.sqlite.org/src/file?name=ext/misc/closure.c) which implements transitive closures using a virtual table interface. I'll spend some time reading through the source and see if I can implement something similar for this problem.