[sqlite] Query flattening for left joins involving subqueries on the right-hand side

On 27.11.2015 10:38, Clemens Ladisch wrote: > Kirill M?ller wrote: >> I see no reason why the following two queries can't be executed with the >> same plans: >> >> ... t1 LEFT JOIN t2 ... >> ... t1 LEFT JOIN (SELECT * FROM t2) ... > In this case, the queries are identical. But SQLite's query opti

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Kirill M?ller >> Sent: Thursday, 26 November, 2015 15:03 >> To: SQLite mailing list >> Subject: Re: [sqlite] Query flattening for left joins involving subqueries >>

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Kirill M?ller wrote: > I see no reason why the following two queries can't be executed with the same > plans: > > ... t1 LEFT JOIN t2 ... > ... t1 LEFT JOIN (SELECT * FROM t2) ... In this case, the queries are identical. But SQLite's query optimizer does not try to optimize this because such tri

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

On 26.11.2015 21:12, Clemens Ladisch wrote: > Kirill M?ller wrote: >> On 25.11.2015 16:32, Clemens Ladisch wrote: >>> Kirill M?ller wrote: For a left join with a subquery on the right-hand side, that subquery doesn't seem to be flattened. >>> This is rule 3 of

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Kirill M?ller wrote: > On 25.11.2015 16:32, Clemens Ladisch wrote: >> Kirill M?ller wrote: >>> For a left join with a subquery on the right-hand side, that subquery >>> doesn't seem to be flattened. >> >> This is rule 3 of . > > I wonder if this ru

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

> To: SQLite mailing list > Subject: Re: [sqlite] Query flattening for left joins involving subqueries > on the right-hand side > > On 26.11.2015 21:12, Clemens Ladisch wrote: > > Kirill M?ller wrote: > >> On 25.11.2015 16:32, Clemens Ladisch wrote: > >>&

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

On 25.11.2015 16:32, Clemens Ladisch wrote: > Kirill M?ller wrote: >> For a left join with a subquery on the right-hand side, that subquery >> doesn't seem to be flattened. > This is rule 3 of . Thanks, missed that. While true, I wonder if this rul

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Kirill M?ller wrote: > For a left join with a subquery on the right-hand side, that subquery > doesn't seem to be flattened. This is rule 3 of . Regards, Clemens

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Hi For a left join with a subquery on the right-hand side, that subquery doesn't seem to be flattened. This seems to work well with an inner join. I have attached a reprex. It creates two tables with $n rows and one ID column each (200k is enough to show substantial slowdown), and joins them