Can you please send the database schema, and possibly some test data?
On Tue, Feb 25, 2014 at 6:21 AM, Mark Lawrence <no...@null.net> wrote: > I am seeing what I think may be buggy behaviour, using a recent sqlite > packaged by debian: > > 3.8.3.1 2014-02-11 14:52:19 ea3317a4803d71d88183b29f1d3086f46d68a00e > > What I am seeing is too few rows returned from a UNION ALL query. > I can break it down as follows. > > Query 1 on its own works fine, returning 2 rows: > > SELECT > 'project' AS "kind", > status.uuid as update_order > FROM > project_updates > INNER JOIN > topics AS projects > ON > projects.id = project_updates.project_id > LEFT JOIN > topics AS status > ON > status.id = project_updates.status_id > WHERE > project_updates.update_id = 2 > ; > > Results 1: > > kind update_order > ------------------------------------------------- > project NULL > project d2e2b16d45d4a7e514da610cdc46cbcfec29431a > > Query 2 on its own works fine return 5 rows: > > SELECT > 'project_status' AS "kind", > 1 AS update_order > FROM > updates > INNER JOIN > project_status_updates > ON > project_status_updates.update_id = updates.id > WHERE > updates.id = 2 > ; > > Results 2: > > kind update_order > -------------------- ---------------------------------------- > project_status 1 > project_status 1 > project_status 1 > project_status 1 > project_status 1 > > If I "union all" those two queries together I get the expected 7 (2 + > 5) rows. > > kind update_order > --------------------------------------------------------- > project NULL > project d2e2b16d45d4a7e514da610cdc46cbcfec29431a > project_status 1 > project_status 1 > project_status 1 > project_status 1 > project_status 1 > > However if I union all with an ORDER BY clause like so: > > SELECT > 'project' AS "kind", > status.uuid as update_order > FROM > project_updates > INNER JOIN > topics AS projects > ON > projects.id = project_updates.project_id > LEFT JOIN > topics AS status > ON > status.id = project_updates.status_id > WHERE > project_updates.update_id = 2 > UNION ALL > SELECT > 'project_status' AS "kind", > 1 AS update_order > FROM > updates > INNER JOIN > project_status_updates > ON > project_status_updates.update_id = updates.id > WHERE > updates.id = 2 > ORDER BY > update_order > ; > > Then I get a rather surprising three rows: > > kind update_order > --------------------------------------------------------- > project NULL > project_status 1 > project d2e2b16d45d4a7e514da610cdc46cbcfec29431a > > As far as I understand union all, it should never return less than the > sum of the individual queries. Any ideas? > > Mark. > -- > Mark Lawrence > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
