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