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
negation.

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

-- 
$---+----1----+----2----+----3----+----4----+----5----+----6----+

SCALE GmbH
Niederlassung Dresden
Torsten Landschoff
Pohlandstraße 19
01309 Dresden

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

SCALE GmbH
Registergericht und Sitz: Ingolstadt, HRB 6384
Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to