Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-08 Thread Keith Medcalf
On Wednesday, 5 November, 2014 22:23, James Lowden said: >On Wed, 05 Nov 2014 08:24:47 -0700, "Keith Medcalf" >wrote: >> The two queries are different. They may end up with the same result, >> but you are asking different questions. In the first you are >> returning only

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread James K. Lowden
On Wed, 05 Nov 2014 08:24:47 -0700 "Keith Medcalf" wrote: > The two queries are different. They may end up with the same result, > but you are asking different questions. In the first you are > returning only matching rows. In the later you are requesting a > projection

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread Vitaliy Filippov
Can you please run ANALYZE then try the plans again ? This was just after running ANALYZE :) the fact that they produce the exact same answer is simply by virtue of your WHERE clause being specifically that Of course, I understand, that's what I've meant - the plan shouldn't differ for

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread Keith Medcalf
On Wednesday, 5 November, 2014 05:14, vita...@yourcmc.ru said: >After playing a little with SQLite as a DBMS for Bugzilla, I've >discovered that LEFT/INNER join affects query plan in a bad way even for >semantically equal queries: >SELECT * FROM bugs b INNER JOIN profiles p ON

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread David Woodhouse
On Wed, 2014-11-05 at 16:00 +0200, RSmith wrote: > On 2014/11/05 15:26, David Woodhouse wrote: > > On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: > > I don't think it's anything to do with the table data being special, > is it? Isn't it generically true that for any LEFT JOIN of > > a,b WHERE

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread RSmith
On 2014/11/05 15:26, David Woodhouse wrote: On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: I don't think it's anything to do with the table data being special, is it? Isn't it generically true that for any LEFT JOIN of a,b WHERE b.anything IS NOT NULL, the results are going to be equal with

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread David Woodhouse
On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: > On 2014/11/05 14:13, vita...@yourcmc.ru wrote: > > Hi! > > > > After playing a little with SQLite as a DBMS for Bugzilla, I've discovered > > that LEFT/INNER join affects query plan in a bad way > > even for semantically equal queries: > > > >

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread Richard Hipp
On Wed, Nov 5, 2014 at 8:12 AM, Clemens Ladisch wrote: > > The WHERE expression then makes the outer join meaningless, > Thank you, Clemens - I missed that detail. So the suggestion is that we should enhance the SQLite query planner to detect when the WHERE clause requires

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread RSmith
On 2014/11/05 14:13, vita...@yourcmc.ru wrote: Hi! After playing a little with SQLite as a DBMS for Bugzilla, I've discovered that LEFT/INNER join affects query plan in a bad way even for semantically equal queries: SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread Clemens Ladisch
vita...@yourcmc.ru wrote: > SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE > p.login_name='vita...@yourcmc.ru' > > Query plan: > SCAN TABLE bugs AS b > SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?) > > Which is of course very slow. > > Maybe you'll

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread Richard Hipp
On Wed, Nov 5, 2014 at 7:13 AM, wrote: > Hi! > > After playing a little with SQLite as a DBMS for Bugzilla, I've discovered > that LEFT/INNER join affects query plan in a bad way even for semantically > equal queries: > I'm not sure what you mean by "semantically equal", but

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread Simon Slavin
On 5 Nov 2014, at 12:13pm, vita...@yourcmc.ru wrote: > Which is of course very slow. Can you please run ANALYZE then try the plans again ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] LEFT joins affect query plan for semantically equal queries

2014-11-05 Thread vitalif
Hi! After playing a little with SQLite as a DBMS for Bugzilla, I've discovered that LEFT/INNER join affects query plan in a bad way even for semantically equal queries: SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE p.login_name='vita...@yourcmc.ru' Query plan: