Explain query plan output and recursive CTE's

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Explain query plan output and recursive CTE's

David Raymond
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users