Thank you Clemens. In the general case, myagg() doesn't have the appropriate property to distribute over tableA and tableB seperately.
Let me be clear, we are not looking for alternatives to get our query running more efficiently, we sincerely believe that we have found a defect in the sqlite query planner. This is a bug report, not a request for assistance on formulating our query. I do appreciate the effort and your good will, though. Thank you again. Manos On 23 January 2015 at 18:48, Clemens Ladisch <[email protected]> wrote: > Emmanouil Karvounis wrote: > > In short, 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; > > > > The sqlite query plan creates a temporary B-tree to hold all the records > of > > both tables to execute the group by. This incurs too much time and space > > complexity > > If the number of result rows is small compared to the number of table > rows, and if myagg() works correctly when called on its own results, > then you can reduce the size of the temporary B-tree by doing another > GROUP BY in the subqueries: > > select c1, c2, myagg(prop) from ( > select c1, c2, myagg(prop) as prop from tableA group by c1, c2 > union all > select c1, c2, myagg(prop) from tableB group by c2, c2 > ) > group by c1, c2; > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
