This is fixed in the current head of trunk.  Although the implementation may 
change, it will appear in the next release.

https://www.sqlite.org/src/timeline?n=50

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 25 November, 2017 10:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Getting an advance list of RowIDs for a query
>result set
>
>Simon, I’ve no users. I’ve been teaching myself c++ (and SQLite)
>during a lengthy illness so this is just experimentation for me.
>
>
>
>This subject touches on a previous question of mine you were involved
>in regarding redundant tables. The following may jog your memory
>
>
>
>create table TblA(A integer primary key, B int, C int);
>
>create table TblB(B integer primary key, BX int);
>
>create table TblC(C integer primary key, CX int);
>
>
>
>explain query plan select A from TblA left join TblB using (B) left
>join TblC using (C) where BX=?;
>
>0|0|0|SCAN TABLE TblA
>
>0|1|1|SEARCH TABLE TblB USING INTEGER PRIMARY KEY (rowid=?)
>
>
>
>explain query plan select A from TblA left join TblB using (B) left
>join TblC using (C) where CX=?;
>
>0|0|0|SCAN TABLE TblA
>
>0|1|1|SEARCH TABLE TblB USING INTEGER PRIMARY KEY (rowid=?)
>
>0|2|2|SEARCH TABLE TblC USING INTEGER PRIMARY KEY (rowid=?)
>
>
>
>In the first explain SQLite drops the trailing redundant table but in
>the second explain it doesn’t drop the middle redundant table.
>
>
>
>As TblB is included in the second query it must surely run slower
>than if it were omitted.
>
>
>
>I’m not complaining about the SQLite optimiser failing to spot the
>redundancy as it’s got to deal with a variety of queries far removed
>from my narrow experiment. Checking for such redundancies would
>likely slow down prepares and, when it comes down to it, anyone
>including TblB in the second query is only getting what they asked
>for.
>
>
>
>I do think though that it’s possible to write code to remove these
>redundancies so as to get the vector of RowIDs as fast as possible.
>So far I’ve been splitting SQL into ‘RowSQL’ (returns RowIDs involved
>in correct order) and ‘ColSQL’ (returns columns requested in original
>SQL for the requested range as shown in my second post) but I’ve only
>been doing it visually via knowledge of the tables. What I’m trying
>to do is write a function to automatically ‘split’ the sql into
>RowSQL and ColSQL. I’ll make another post later showing where I’m at
>with that.
>
>________________________________
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on
>behalf of Simon Slavin <slav...@bigfraud.org>
>Sent: Saturday, November 25, 2017 1:26:00 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Getting an advance list of RowIDs for a query
>result set
>
>
>
>On 25 Nov 2017, at 1:15pm, curmudgeon <tam118...@hotmail.com> wrote:
>
>> Given a select where a 'base table' is attached to lookup tables
>> how can I determine which of the lookup tables can be removed from
>the table
>> such that
>>
>> select BaseTbl.RowID from ... where ... order by ...
>>
>> will find the set of records that represents the original query.
>
>That optimization could be done at the level of the SQL engine.  You
>wouldn’t want to do it inside your own code since that would make
>your code extremely complicated.  So just execute the query without
>trying to optimize it and see what happens.  Does it run fast enough
>for your users ?
>
>Simon.
>_______________________________________________
>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



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to