Hi, I created the following table in sqlite 3.8.6
"CREATE TABLE IF NOT EXISTS test ( a INTEGER, b INTEGER, c INTEGER, d BLOB, PRIMARY KEY (a, b DESC) );" When I execute the following query "SELECT * FROM test WHERE b < ? AND c > ?;" A full table scan is executed because column a is not part of the query. But when I step over the rows they are not returned in primary key sort order. Why is this? If I look at the images at the query planning document (https://www.sqlite.org/queryplanner.html) I get the idea that the primary key B-tree should be used to traverse the table when a full table scan is executed. And since the 'DESC' keyword is used on column b in the primary key, I would expect that, as the rowId increases, the values retuned for column b would decrease. But this is not the case. Does a full table scan then ignore the PK B-tree? Does it perhaps scan all table pages in the order in which they appear in the file system? I guess this would make sense to increase traversal speed, since it needs to check all pages anyway? If I explicitly order using an 'ORDER BY' statement then - looking at the query plan - sqlite seems to perform a full table scan and store the result in a temporary table which is then sorted. I'd like to avoid the memory consumption produced by this query plan... Any help or information would be appreciated! Thanks in advance! Jan Asselman iba AG: Supervisory Board: Horst Anhaus, chairman; Management Board: Dr. Ulrich Lettau CEO, Marta Anhaus, Harald Opel; Registered Office: Fuerth/Germany; Registration Office: Fuerth, HRB 9865, Vat.Reg.No DE 132760166, WEEE-Reg.No. DE11469996 This message is intended only for the named recipient and may contain confidential or privileged information. Taking notice of this message by third parties is not permitted. If you have received it in error, please advise the sender by return e-mail and delete this message and any attachments. Any unauthorized use or dissemination of this information is strictly prohibited.
