Thank you very much, Richard. I'm sure this enhancement, modelled in a more general way, e.g., like an isSorted flag on the subquery to be used by the outer query, can be a great enhancement for many other types of queries employing nesting. I think it will help both in time and in space complexity, something very important when aiming at the embedded systems enviroment.
Let me know if you need us to provide any further details or other assistance. Regards, Manos On 23 January 2015 at 21:30, Richard Hipp <d...@sqlite.org> wrote: > On 1/23/15, Emmanouil Karvounis <man...@di.uoa.gr> wrote: > > We have two tables that are already sorted on a combination of > > two fields (which are their primary keys) and we want to union them and > > apply group by on both the aforementioned fields, like so: > > > > select c1, c2, myagg(*) from ( > > select * from tableA > > union all > > select * from tableB > > ) > > group by c1, c2; > > > > where tableA, tableB have primary key (c1, c2) and their schema > comprises 3 > > integers: c1, c2, and prop. > > > > The sqlite query plan creates a temporary B-tree to hold all the records > of > > both tables to execute the group by. > > You are correct. Here is the test case I am using: > > CREATE TABLE tableA(c1 INT, c2 INT, payload INT, PRIMARY KEY(c1,c2)); > CREATE TABLE tableB(c1 INT, c2 INT, payload INT, PRIMARY KEY(c1,c2)); > > explain query plan > SELECT * FROM tableA > UNION ALL > SELECT * FROM tableB > ORDER BY 1, 2; > > .print --------------------------------------------------------- > > explain query plan > SELECT c1, c2, sum(payload) FROM ( > SELECT * FROM tableA > UNION ALL > SELECT * FROM tableB > ) > GROUP BY c1, c2; > > The first query - the UNION ALL with the ORDER BY does not use a > separate B-tree for sorting. So it seems logical that the second > query that does a GROUP BY over the same UNION ALL should be able to > get by without a separate sorting B-Tree too. > > For various technical reasons, this is complex change in SQLite. But > it seems like a worthwhile enhancement, so we'll take your suggestion > under advisement and attempt to do a better job of handling your query > in future releases. > > Thanks for posting. > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
