Virtual tables query closes cursor too early when no rows returned
(1) By Eric Scott (escott) on 2020-08-19 14:30:46 [link] [source]
Summary: When joining two vtables that result in zero rows, the cursor is closed such that calling code can't get the schema of the result. Sqlite Version: 3.30.1 python 3.7 apsw version 3.30.1-r3 Example: tblA: id val1 val2 --- ---- ---- 2 3 27 tblB: id x y --- ---- ---- 3 23.4 98.3 SQL: SELECT a.id, a.val1, a.val2, b.x, b.y FROM tblA a JOIN tblB b ON a.id = b.id When tblA and tblB are implemented as virtual tables, the cursor is closed before the return from cursor.execute(). This causes an exception when the calling code uses cursor.description to get the schema. If tblA and tblB are sqlite tables, the cursor is not closed before the return from execute() and the caller can get the schema. Moreover, if we change the sql to something with the same net result: SELECT a.id, a.val1, a.val2, b.x, b.y FROM tblA a LEFT JOIN tblB b ON a.id = b.id WHERE b.id is not null the virtual tables approach does not close the cursor prematurely. (So I have a work around, at the cost of a performance hit, since the query plan changes.) Notes, cuz I figure you'll ask: + I have checked that this is not caused by an exception in my vtbl implementation. + I don't have example vtbl code for this because I'm not allowed to export it. Still working on a contrived example.
(2) By Gunter Hick (gunter_hick) on 2020-08-19 15:26:31 in reply to 1 [source]
Can you post the EXPLAIN output = SQLite Program fo the two queries? What does cursor.description do and what C interface does it call?
(3.1) By Keith Medcalf (kmedcalf) on 2020-08-19 16:10:43 edited from 3.0 in reply to 1 [link] [source]
You should examine the code for pysqlite2 to find out why this is happening. I don't think it has anything whatsoever to do with SQLite3.
From SQLite3's perspective, the a statement (VDBE program) was created when the .execute() method was called, and then is was executed with sqlite3_exec. In both cases the VDBE program (statement) ran to completion without producing any rows (returned SQLITE_DONE) and the statement (VDBE program) was reset (but not yet finalized) and is in the non-executing state the same as between the sqlite3_prepare and the first sqlite3_exec call.
In this state, metadata from the compiled statement will be available. Information regarding the "current row" will not (there is no current row). In the case of "virtual tables" even such calls as are attempting to retrieve the statement (VDBE program) metadata may fail (technically, there is none, since the virtual tables are no longer "connected" and therefore not subject to introspection). How pysqlite2 handles this I do not know. An examination of the pysqlite2 source code would have to be made to determine this.
In other words, except in the case where a row is returned I would not expect that the .description method would be able to return any data. Why would you expect a description to be available for a query that produces no rows?