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
