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:[email protected]] 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
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
