On 16.05.2018 01:24, Simon Slavin wrote:
>> I would have expected that b.id is basically an alias for d.id as
> Obvious to a good programmer. Not obvious to SQLite. Your SELECT is
> selecting from the "base" table, but trying to order by a table
> JOINed to it. The fact that d.id is always equal to b.id isn't
> spotted by SQLite.
Actually it is: SQLite will generate the same query plan for both
queries as long as no statistics data is available. Adding the
statistics information will lead to the more complex and slow query plan
which is why I kept the statistics in the example SQL.
> It's a possible enhancement opportunity for SQLite, but the way the
> query is phrased is counter-intuitive and I doubt many people do it.
I wouldn't do it myself actually, it happens to be the way that
SQLAlchemy creates queries for joined-table inheritance. I assume
Hibernate would do the same.
> The processing to spot the opportunity might slow down every JOIN
> operation SQLite performs, so it might not be the right thing for the
> average user.
Again, SQLite does already do this but somehow lets the optimizer spoil
the otherwise perfect original query.
I should have made that more explicit in my original mail but it was
late and I spent the 2 hours before with stripping down our schema and
database to the small reproducing example.
Registergericht und Sitz: Ingolstadt, HRB 6384
Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz
sqlite-users mailing list