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
