Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Thu, May 31, 2012 at 12:11 PM, Marc L. Allen wrote: > True, but an optimizer could only intelligently exclude OUTER JOINS in most > cases. Right, and LEFT OUTER JOINs at that (since SQLite3 doesn't support RIGHT joins). > For instance, if I have a convenience

Re: [sqlite] Why can't sqlite disregard unused outer joins?

PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins? True, but an optimizer could only intelligently exclude OUTER JOINS in most cases. For instance, if I have a convenience view that combines a series of tables to provide an overall

Re: [sqlite] Why can't sqlite disregard unused outer joins?

use. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nico Williams Sent: Thursday, May 31, 2012 12:30 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins? On Thu, May

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Thu, May 31, 2012 at 11:25 AM, Jos Groot Lipman wrote: >> Actually, anytime you have VIEWs that join table sources you >> can benefit from this optimization. > > No, there is only a benefit if the optimization actually finds a table that > can be removed from the join. When

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Tue, May 29, 2012 at 7:33 PM, Igor Tandetnik wrote: > On 5/29/2012 8:21 PM, Charles Samuels wrote: >> Is this a planned feature? > > > Well, it's not a feature, it's a lack thereof. The query planner is not > specifically looking for this particular optimization

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On May 31, 2012, at 3:33 AM, Keith Medcalf wrote: [skip good primer on query rewrite] > SQL is supposed to be declarative and therefore independent of the skill (or > lack thereof) of the query writer. "In theory there is no difference between theory and practice. In practice there is." --

Re: [sqlite] Why can't sqlite disregard unused outer joins?

> And then I put all of those in one giant (almost: only about 15 columns) view > that the user can do arbitrary queries on, most of which only touch one or > two of those columns from C, and many of which touch zero. SQLite does not do query re-write. If you use a database engine that *does* do

Re: [sqlite] Why can't sqlite disregard unused outer joins?

Richard Hipp wrote: > On Wed, May 30, 2012 at 2:17 PM, Yuriy Kaminskiy wrote: > >> Pavel Ivanov wrote: >>> Here is an example when left outer join makes the difference. Example >>> could seem very artificial but SQLite should count on any possible >>> usage. >>> >>> sqlite>

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wednesday, May 30, 2012 2:02:39 e.h. Igor Tandetnik wrote: > How about this. You create two views, say V1 and V2, one with the join > and one without. Take user-provided query, replace all occurences of V1 > with V2, and try to prepare the new query. If that succeeds, the new > query is

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On 5/30/2012 4:21 PM, Charles Samuels wrote: On Wednesday, May 30, 2012 1:10:17 e.h. Marc L. Allen wrote: Wouldn't it be easier to construct a couple of different views and then intelligently decide which view to use based on the user request? If I could determine which tables the user were

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wednesday, May 30, 2012 1:51:54 e.h. Marc L. Allen wrote: > So, the user provides a standard SQL query using your composite view? Or > are they providing some other construct that you convert into a query > using your view? The user speaks SQL. Charles

Re: [sqlite] Why can't sqlite disregard unused outer joins?

>> All requirements are specific :) How do you pick at what point that overhead >> is too much? When the overhead outweighs the benefit.If, for example, you were the only person who ever needed that particular optimization, I would suggest that the overhead is too much. So, the user

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wednesday, May 30, 2012 1:10:17 e.h. Marc L. Allen wrote: > Wouldn't it be easier to construct a couple of different views and then > intelligently decide which view to use based on the user request? If I could determine which tables the user were interested in and then shift the view beneath

Re: [sqlite] Why can't sqlite disregard unused outer joins?

overhead to every single query it executes. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Charles Samuels Sent: Wednesday, May 30, 2012 3:14 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Why can't sqlite disregard

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wednesday, May 30, 2012 12:03:02 e.h. Marc L. Allen wrote: > 1) The left outer join table is joined by a unique column AND > 2) No other data from the joined table is used in the query. > > Is that about right? Almost: add "recursively": I actually have it nested with *another join* with the

Re: [sqlite] Why can't sqlite disregard unused outer joins?

So... you're suggesting the optimizer discard a left outer join when: 1) The left outer join table is joined by a unique column AND 2) No other data from the joined table is used in the query. Is that about right? Out of curiosity, why is code being written like the SQL you're providing? Is

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wednesday, May 30, 2012 11:29:00 f.h. Black, Michael (IS) wrote: > Since you have a one-to-one relationship I'm not sure why you don't just > put the inseam with the Employee, but perhaps you're just giving an > example here. It is an example. > > I would do it this way which is going to run

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wed, May 30, 2012 at 2:17 PM, Yuriy Kaminskiy wrote: > Pavel Ivanov wrote: > > Here is an example when left outer join makes the difference. Example > > could seem very artificial but SQLite should count on any possible > > usage. > > > > sqlite> create table Employee (name

Re: [sqlite] Why can't sqlite disregard unused outer joins?

Pavel Ivanov wrote: > Here is an example when left outer join makes the difference. Example > could seem very artificial but SQLite should count on any possible > usage. > > sqlite> create table Employee (name int); > sqlite> create table Uniform (employeename, inseam, constraint ue > unique

Re: [sqlite] Why can't sqlite disregard unused outer joins?

Since you have a one-to-one relationship I'm not sure why you don't just put the inseam with the Employee, but perhaps you're just giving an example here. I would do it this way which is going to run a heck of lot faster than using string compares as you are doing. pragma foreign_keys = on;

Re: [sqlite] Why can't sqlite disregard unused outer joins?

Here is an example when left outer join makes the difference. Example could seem very artificial but SQLite should count on any possible usage. sqlite> create table Employee (name int); sqlite> create table Uniform (employeename, inseam, constraint ue unique (employeename)); sqlite> insert into

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On 5/30/2012 1:05 PM, Charles Samuels wrote: "A planned feature" refers to a feature that does not yet exist. I am asking if sqlite will ever be able to do this optimization? Well, I'm not developing SQLite, I'm just a user like yourself. In the remote case you are interested in my personal

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On 30 May 2012, at 6:15pm, Charles Samuels wrote: > sqlite> select Name from Employee left outer join Uniform on > Employee.name=Uniform.employeename; > Joe > Steve > Eric > Dave > sqlite> explain query plan select Name from Employee left outer join Uniform > on

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wednesday, May 30, 2012 10:14:22 f.h. Charles Samuels wrote: > sqlite> select Name from Employee join Uniform on > Employee.name=Uniform.employeename; > Joe > Dave > sqlite> explain query plan select Name from Employee join Uniform on > Employee.name=Uniform.employeename; > 0|0|0|SCAN TABLE

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wednesday, May 30, 2012 10:02:24 f.h. Igor Tandetnik wrote: > On 5/29/2012 8:21 PM, Charles Samuels wrote: > > Suppose you have a query like this: > > > > select Employee.name from Employees left join Uniform on > > > > (EmployeeSize.name=Uniform.employeeName) > > Doesn't look like a

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Wednesday, May 30, 2012 9:45:16 f.h. David Bicking wrote: > If Uniform has a given EmployeeName twice, you will get the Employee.Name > twice in this query. Thus it would be a different result than if you did > not join with Uniform. On Wednesday, May 30, 2012 9:57:00 f.h. Petite Abeille

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On May 30, 2012, at 2:21 AM, Charles Samuels wrote: > This query's result should be identical weather or not we have that join; > it's > an outer join, not an inner join, afterall. The outer join could affect the cardinality of the result (i.e. there could be multiple rows matching the

Re: [sqlite] Why can't sqlite disregard unused outer joins?

SQLite Database <sqlite-users@sqlite.org> Sent: Tuesday, May 29, 2012 8:21 PM Subject: [sqlite] Why can't sqlite disregard unused outer joins? Suppose you have a query like this: select Employee.name from Employees left join Uniform on     (EmployeeSize.name=Uniform.employeeName) This qu

Re: [sqlite] Why can't sqlite disregard unused outer joins?

On 5/29/2012 8:21 PM, Charles Samuels wrote: Suppose you have a query like this: select Employee.name from Employees left join Uniform on (EmployeeSize.name=Uniform.employeeName) Doesn't look like a valid query to me. What's Employee and EmployeeSize? I assume you meant Employees in

[sqlite] Why can't sqlite disregard unused outer joins?

Suppose you have a query like this: select Employee.name from Employees left join Uniform on (EmployeeSize.name=Uniform.employeeName) This query's result should be identical weather or not we have that join; it's an outer join, not an inner join, afterall. However, explain query plan