Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Recursive CTE returns less rows when using aggregate subquery [CORE3698] #4046

Closed
firebird-automations opened this issue Dec 15, 2011 · 11 comments
Closed

Recursive CTE returns less rows when using aggregate subquery [CORE3698] #4046

firebird-automations opened this issue Dec 15, 2011 · 11 comments
Labels
affect-version: 2.5.0 priority: major qa: covered by another tests resolution: duplicate type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Israel Lopez (ilopez)

Duplicates CORE3683

Attachments:
testcte.sql
testqueries.sql

This returns 12 rows

with recursive b (id, src, bomid, stagebomid,nextqty)
as
(
select http://bomitem.id, 'a' as src, bom.num as bomid, bomitem.stagebomid, 0 as nextqty
from bom
join bomitem on http://bom.id = bomitem.bomid
join part on http://part.id = bomitem.partid
JOIN (SELECT http://part.ID,SUM(COALESCE(tag.qty,0)) as qty FROM PART LEFT JOIN TAG ON TAG.PARTID = http://PART.ID GROUP BY 1) AS QOH ON http://QOH.ID = http://PART.ID
where bom.num = 'H0083'

    union all

    select  <http://bomitem.id>, 'b' as src, bom\.num as bomid,  bomitem\.stagebomid, 0 as nextqty
    from bom
        join bomitem on <http://bom.id> = bomitem\.bomid
        join part on <http://part.id> = bomitem\.partid
        JOIN \(SELECT <http://part.ID>,SUM\(COALESCE\(tag\.qty,0\)\) as qty FROM PART LEFT JOIN TAG ON TAG\.PARTID = <http://PART.ID> GROUP BY 1\) AS QOH ON <http://QOH.ID> = <http://PART.ID>

        join b parent on parent\.stagebomid = <http://bom.id>
\)

select * from b

This also returns only 12 rows

with recursive b (id, src, bomid, stagebomid,nextqty)
as
(
select http://bomitem.id, 'a' as src, bom.num as bomid, bomitem.stagebomid, 0 as nextqty
from bom
join bomitem on http://bom.id = bomitem.bomid
join part on http://part.id = bomitem.partid
JOIN (SELECT http://part.ID,SUM(0) as qty FROM PART GROUP BY 1) AS QOH ON http://QOH.ID = http://PART.ID
where bom.num = 'H0083'

    union all

    select  <http://bomitem.id>, 'b' as src, bom\.num as bomid,  bomitem\.stagebomid, 0 as nextqty
    from bom
        join bomitem on <http://bom.id> = bomitem\.bomid
        join part on <http://part.id> = bomitem\.partid
        JOIN \(SELECT <http://part.ID>,SUM\(0\) as qty FROM PART GROUP BY 1\) AS QOH ON <http://QOH.ID> = <http://PART.ID>

        join b parent on parent\.stagebomid = <http://bom.id>
\)

select * from b

This returns the 15 rows I was looking for.

with recursive b (id, src, bomid, stagebomid,nextqty)
as
(
select http://bomitem.id, 'a' as src, bom.num as bomid, bomitem.stagebomid, 0 as nextqty
from bom
join bomitem on http://bom.id = bomitem.bomid
join part on http://part.id = bomitem.partid
JOIN (SELECT http://part.ID,0 as qty FROM PART) AS QOH ON http://QOH.ID = http://PART.ID
where bom.num = 'H0083'

    union all

    select  <http://bomitem.id>, 'b' as src, bom\.num as bomid,  bomitem\.stagebomid, 0 as nextqty
    from bom
        join bomitem on <http://bom.id> = bomitem\.bomid
        join part on <http://part.id> = bomitem\.partid
        JOIN \(SELECT <http://part.ID>,0 as qty FROM PART\) AS QOH ON <http://QOH.ID> = <http://PART.ID>

        join b parent on parent\.stagebomid = <http://bom.id>
\)

select * from b

Maybe it is my misunderstandings of CTEs, but I imagine first query would work.

====== Test Details ======

See test for CORE3683
@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE3683 [ CORE3683 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Looks similar to case Core-3683

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

A complete test case (including data) would be appreciated. Or try this example with the latest FB 3.0 snapshot build and report back.

@firebird-automations
Copy link
Collaborator Author

Commented by: Israel Lopez (ilopez)

I just added two files, one SQL Schema file, and two CTE queries that expose the problem.

On FB 2.5.0 - I see the problem.
On FB 3.0.0.29643 I do not see the problem.

I guess consider this issue fixed for 3.0.

@firebird-automations
Copy link
Collaborator Author

Modified by: Israel Lopez (ilopez)

Attachment: testcte.sql [ 12076 ]

Attachment: testqueries.sql [ 12077 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE3683 [ CORE3683 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE3683 [ CORE3683 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: No test => Covered by another test(s)

Test Details: See test for CORE3683

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 2.5.0 priority: major qa: covered by another tests resolution: duplicate type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations