Thanks Gunter. I didn’t include the text of the original SELECT as it was basically an unnecessarily complex jumble of WITHs and embedded SELECTS just so I could see the response of the TreeViewSelect. I hoped someone would’ve been able to say “yes, the WITH is just included for completeness but is already integrated into the main SELECT”. I also hoped the ‘IF-NULL-ROW 8’ would have a simple explanation so didn’t think including the complex query would’ve been relevant (similar to including 30 lines of C code and asking what ‘goto’ meant).
I also made a mistake in what I printed in any case as the original WITH doesn’t appear in the part of the tree I printed (it was below the FROM at the end of it). From: Hick Gunter<mailto:h...@scigames.at> Sent: 28 July 2019 09:34 To: 'SQLite mailing list'<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect 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 g the lin 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
