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