On 16.05.2018 01:48, Keith Medcalf wrote:
> There is no LEFT join visible to anyone except someone who is in love
> with LEFT joins without knowing what one is.

Sorry, I corrupted my text while trying to simplify it. I originally
wrote "since this is not a left outer join", but tried to remove the

Actually this is an inner join. Sorry.

> Your query is misformed.  You proper query ought to be:
> select * from base b join derived d using (id) order by id;

If I were in the position to just write this query than this is what I
would write. But sitting behind an ORM this is taken out of my hands.

Or rather it isn't - I just replaced the code with hand-crafted SQL
which is fine and dandy but means that I have code to update if the
schema ever changes.

> There is no b.id nor d.id in the output row.  It is a figment of your
> imagination.  The output row contains an "id".  Of course the problem
> arises because of all the other people who also like to write broken
> SQL and expect it it work.

Yeah. The code above is incredibly broken by having two identical id
columns in the result.

Still it is a mystery to me how this leads the optimizer to come up with
a much worse query plan after the data is analyzed.

> Why do you expect that your brokenness
> should win out over someone else's brokenness?

I don't.

I only wanted to express my surprise how an otherwise really stable
database engine like SQLite is caught off guard when confronted with
less than optimal SQL.

Greetings, Torsten


Niederlassung Dresden
Torsten Landschoff
Pohlandstraße 19
01309 Dresden

Tel: +49-351-312002-10
Fax: +49-351-312002-29

Registergericht und Sitz: Ingolstadt, HRB 6384
Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz
sqlite-users mailing list

Reply via email to