Quick thing I noticed looking at some explain query plan outputs. When you get to the compound subqueries part in a recursive CTE it always seems to give the subquery numbers as 0 and 0. I’m not sure if that’s a bug, a limitation, or intended behavior. There're no issues with the queries themselves, just the text in the explain query plan output.
(following queries aren't equivalent, they're just showing the numbering issue) "Normal", non-recursive, and recursive examples given. SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo (x); sqlite> explain query plan select x from foo union select x + 1 from foo; selectid|order|from|detail 1|0|0|SCAN TABLE foo 2|0|0|SCAN TABLE foo 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) sqlite> explain query plan with bar as (select x from foo) select * from bar union select * from foo; selectid|order|from|detail 1|0|0|SCAN TABLE foo 2|0|0|SCAN TABLE foo 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) sqlite> explain query plan with recursive bar (x) as (select x from foo union select x + 1 from bar) select * from bar; selectid|order|from|detail 2|0|0|SCAN TABLE foo 3|0|0|SCAN TABLE bar 1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION) 0|0|0|SCAN SUBQUERY 1 sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
