I am learning SQLite queries. I use prepare statement and one or more calls to a step statement. The prepare statement knows the pointer to the database, the UFT8 select statement and the name of the pointer which will point to the complied SQL select statement. The first step call runs the compiled select statement and if data is returned, the first row is made available via column calls. It seems logical to me that the select statement returns all matching rows but if the first step statement only has access to the first row of data, the balance of matching rows must be stored somewhere. Is this the case and if so where are the query results stored? Or is does this work in some other manner?
Trying to understand SQLite step statement
It seems logical to me that the select statement returns all matching rows
No.
does this work in some other manner?
Yes; it find the matching items one at a time, one per call to step.
Consider a simple query with one table and no indexes. The only state that it keeps is how far through the table it has already got. Each time you call step, it scans forward until it finds the next matching row, and returns that.
(You might prefer to ask questions like this on the sqlite mailing list.)