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
