Recursive CTE returns less rows when using aggregate subquery
-------------------------------------------------------------
Key: CORE-3698
URL: http://tracker.firebirdsql.org/browse/CORE-3698
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.0
Environment: Firebird Server 2.5.0.26074 - Classic - Windows 7 32bit
Reporter: Israel Lopez
This returns 12 rows
with recursive b (id, src, bomid, stagebomid,nextqty)
as
(
select bomitem.id, 'a' as src, bom.num as bomid, bomitem.stagebomid,
0 as nextqty
from bom
join bomitem on bom.id = bomitem.bomid
join part on part.id = bomitem.partid
JOIN (SELECT part.ID,SUM(COALESCE(tag.qty,0)) as qty FROM PART LEFT
JOIN TAG ON TAG.PARTID = PART.ID GROUP BY 1) AS QOH ON QOH.ID = PART.ID
where bom.num = 'H0083'
union all
select bomitem.id, 'b' as src, bom.num as bomid, bomitem.stagebomid,
0 as nextqty
from bom
join bomitem on bom.id = bomitem.bomid
join part on part.id = bomitem.partid
JOIN (SELECT part.ID,SUM(COALESCE(tag.qty,0)) as qty FROM PART LEFT
JOIN TAG ON TAG.PARTID = PART.ID GROUP BY 1) AS QOH ON QOH.ID = PART.ID
join b parent on parent.stagebomid = bom.id
)
select * from b
This also returns only 12 rows
with recursive b (id, src, bomid, stagebomid,nextqty)
as
(
select bomitem.id, 'a' as src, bom.num as bomid, bomitem.stagebomid,
0 as nextqty
from bom
join bomitem on bom.id = bomitem.bomid
join part on part.id = bomitem.partid
JOIN (SELECT part.ID,SUM(0) as qty FROM PART GROUP BY 1) AS QOH ON
QOH.ID = PART.ID
where bom.num = 'H0083'
union all
select bomitem.id, 'b' as src, bom.num as bomid, bomitem.stagebomid,
0 as nextqty
from bom
join bomitem on bom.id = bomitem.bomid
join part on part.id = bomitem.partid
JOIN (SELECT part.ID,SUM(0) as qty FROM PART GROUP BY 1) AS QOH ON
QOH.ID = PART.ID
join b parent on parent.stagebomid = bom.id
)
select * from b
This returns the 15 rows I was looking for.
with recursive b (id, src, bomid, stagebomid,nextqty)
as
(
select bomitem.id, 'a' as src, bom.num as bomid, bomitem.stagebomid,
0 as nextqty
from bom
join bomitem on bom.id = bomitem.bomid
join part on part.id = bomitem.partid
JOIN (SELECT part.ID,0 as qty FROM PART) AS QOH ON QOH.ID = PART.ID
where bom.num = 'H0083'
union all
select bomitem.id, 'b' as src, bom.num as bomid, bomitem.stagebomid,
0 as nextqty
from bom
join bomitem on bom.id = bomitem.bomid
join part on part.id = bomitem.partid
JOIN (SELECT part.ID,0 as qty FROM PART) AS QOH ON QOH.ID = PART.ID
join b parent on parent.stagebomid = bom.id
)
select * from b
Maybe it is my misunderstandings of CTEs, but I imagine first query would work.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Cloud Computing - Latest Buzzword or a Glimpse of the Future?
This paper surveys cloud computing today: What are the benefits?
Why are businesses embracing it? What are its payoffs and pitfalls?
http://www.accelacomm.com/jaw/sdnl/114/51425149/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
