WITH is basically syntactic sugar that allows you to name the result set of a certain select and refer to it by name, so that select has to appear in the generated bytecode and also in the query resolution tree.
Guessing what an element of the query resolution tree does would be very much easier if you were to provide the text of the select statement itself. You might also like to look at EXPLAIN QUERY PLAN and then EXPLAIN to view the bytecode. I would guess that the WITH is only executed if the table internally numbered 8 does not deliver a row. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Samstag, 27. Juli 2019 14:08 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Help with sqlite3TreeViewSelect I’ve been using this while debugging by inserting the following code just before the return statement at the bottom of the sqlite3Select procedure. freopen("c:/SQLiteData/TreeView.txt","w",stdout); sqlite3TreeViewSelect(0, p, 0); freopen("CON","w",stdout); (NB above only works if you define SQLITE_DEBUG). I’ve been looking at the result for various queries. Let’s suppose I want to take the output from TreeView.txt and rebuild the query from that so I have a version of the query where I know which schema, table and column each ID token refers to. It looks fairly easy given the tables and columns are all numbered but there’s a couple of things I’m unsure of. 1. When I include a WITH statement in the query it prints the WITH statement but it also seems to incorporate the associated select(s) into the main SELECT such that the WITH statement can be effectively ignored. Is that always the case? 2. In the text below what does the line ‘IF NULL-ROW 8’ mean (the SELECT below that line is what was contained in the WITH statement)? |-- SELECT (7/23B3648) selFlags=0xc4 nSelectRow=253 |-- result-set | |-- {0:-1} flags=0x820000 | |-- {0:7} flags=0x820000 | '-- IF-NULL-ROW 8 | '-- SELECT-expr flags=0x2220800 | '-- SELECT (2/23B3A68) selFlags=0x40c4 nSelectRow=0 | |-- result-set | | '-- {9:1} flags=0x820000 | |-- FROM | | |-- {9,*} Course tab='course' nCol=9 ptr=23B18A8 | | '-- {11,*} Dam tab='dam' nCol=3 ptr=23B1908 | |-- WHERE | | '-- AND | | |-- EQ | | | |-- {9:-1} flags=0x820000 | | | '-- 28 | | '-- EQ | | |-- {9:-1} flags=0x20008 | | | '-- 28 | | '-- {11:-1} flags=0x820000 | '-- LIMIT | '-- 1 |-- FROM ..... Also, is there any way I could redirect the stdout to a memory buffer rather than a file (I’m using clang compiler on windows)? It would be great if there was a function along the lines of sqlite3_normalized_sql(stmt) that returned the sqlite3TreeViewSelect text. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
