Nifty. I tried out the explain query plan on the sample queries in the recursive doc (http://www.sqlite.org/lang_with.html), the output is below as a nice example. The one comment I have is that the co-routine/sub-query numbers are non-deterministic, so any of your tests that check the output of explain query plan will have to take into account that those can change on every run of it.
--Mandelbrot Set 3.23.1 selectid|order|from|detail 7|0|0|SCAN TABLE xaxis 5|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) 10|0|0|SCAN TABLE yaxis 8|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) 4|0|0|SCAN SUBQUERY 5 4|1|1|SCAN SUBQUERY 8 11|0|0|SCAN TABLE m 3|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) 2|0|0|SCAN SUBQUERY 3 2|0|0|USE TEMP B-TREE FOR GROUP BY 1|0|0|SCAN SUBQUERY 2 1|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|SCAN SUBQUERY 1 --Mandelbrot Set 3.24.0 QUERY PLAN |--CO-ROUTINE 0x5BC208 | |--CO-ROUTINE 0x5BC088 | | |--CO-ROUTINE 0x5BC488 | | | |--SETUP | | | | |--MATERIALIZE 0x5BC388 | | | | | |--SETUP | | | | | | `--SCAN CONSTANT ROW | | | | | `--RECURSIVE STEP | | | | | `--SCAN TABLE xaxis | | | | |--MATERIALIZE 0x5BC288 | | | | | |--SETUP | | | | | | `--SCAN CONSTANT ROW | | | | | `--RECURSIVE STEP | | | | | `--SCAN TABLE yaxis | | | | |--SCAN SUBQUERY 0x5BC388 | | | | `--SCAN SUBQUERY 0x5BC288 | | | `--RECURSIVE STEP | | | `--SCAN TABLE m | | |--SCAN SUBQUERY 0x5BC488 | | `--USE TEMP B-TREE FOR GROUP BY | |--SCAN SUBQUERY 0x5BC088 | `--USE TEMP B-TREE FOR GROUP BY `--SCAN SUBQUERY 0x5BC208 group_concat(rtrim(t),x'0a') ....# ..#*.. ..+####+. .......+####.... + ..##+*##########+.++++ .+.##################+. .............+###################+.+ ..++..#.....*#####################+. ...+#######++#######################. ....+*################################. #############################################... ....+*################################. ...+#######++#######################. ..++..#.....*#####################+. .............+###################+.+ .+.##################+. ..##+*##########+.++++ .......+####.... + ..+####+. ..#*.. ....# +. --Sudoku 3.23.1 selectid|order|from|detail 2|0|0|SCAN SUBQUERY 3 7|0|0|SCAN TABLE digits 5|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) 4|0|0|SCAN TABLE x 4|1|1|SCAN SUBQUERY 5 AS z 4|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 8 11|0|0|SCAN TABLE digits 9|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) 8|0|0|SCAN SUBQUERY 9 AS lp 1|0|0|COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) 0|0|0|SCAN SUBQUERY 1 --Sudoku 3.24.0 QUERY PLAN |--CO-ROUTINE 0x340B08 | |--SETUP | | |--CO-ROUTINE 0x340E08 | | | `--SCAN CONSTANT ROW | | `--SCAN SUBQUERY 0x340E08 | `--RECURSIVE STEP | |--MATERIALIZE 0x340E88 | | |--SETUP | | | `--SCAN CONSTANT ROW | | `--RECURSIVE STEP | | `--SCAN TABLE digits | |--SCAN TABLE x | |--SCAN SUBQUERY 0x340E88 AS z | `--CORRELATED SCALAR SUBQUERY | |--CO-ROUTINE 0x340F88 | | |--SETUP | | | `--SCAN CONSTANT ROW | | `--RECURSIVE STEP | | `--SCAN TABLE digits | `--SCAN SUBQUERY 0x340F88 AS lp `--SCAN SUBQUERY 0x340B08 -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, May 08, 2018 10:43 AM To: General Discussion of SQLite Database Subject: [sqlite] Lots of enhancements coming to version 3.24.0 - please test There are a lot of important changes in the SQLite code base since the previous release. See https://www.sqlite.org/draft/releaselog/current.html for the latest summary. All of our legacy tests pass and many new test cases have been added. Nevertheless, your testing of SQLite in real-world applications is greatly appreciated. Please consider downloading the latest pre-release snapshot (https://www.sqlite.org/download.html) and giving it a whirl. Report any problems or concerns to this mailing list, or directly to me. Thanks. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users