The following bug has been logged on the website:
Bug reference: 8144
Logged by: Marc Munro
Email address: m...@bloodnok.com
PostgreSQL version: 9.2.4
Operating system: Linux 3.6.3 (debian wheezy)
Description:
I have a query in which I want to use the result of a window function to
isolate the most relevant results. While I was trying to develop and test
the query, I discovered what looks like a bug in the results of the rank()
function. This has been tried with the same results on 9.1.9 and 9.2.4
I have two versions of the query, the first works as expected while the
second produces incorrect rank values. I have tried various work-arounds
but they all seem to trigger the same behaviour once a certain degree of
complexity is reached.
This version of the query seems to work, producing the expected rank
values:
with recursive parent_orgs(parent_org_id, org_id) as
(
select pr.first_party_id, pr.second_party_id
from party_rels pr
where pr.relationship_type_id in (1009, 1010, 1011, 1012, 1013)
),
ancestor_orgs(
ancestor_org_id, org_id, depth) as
(
select org_id, org_id, 0
from parent_orgs
union all
select p.parent_org_id, a.org_id, a.depth + 1
from ancestor_orgs a
join parent_orgs p
on p.org_id = a.ancestor_org_id
)
select ao.org_id, oi.item_id,
oi.seq_id, oi.complete,
ao.ancestor_org_id, ao.depth,
rank() over (partition by oi.item_id order by ao.depth)
from ancestor_orgs ao
join oitems oi
on oi.org_id = ao.ancestor_org_id
where ao.org_id = 20150;
org_id | item_id | seq_id | complete | ancestor_org_id | depth |
rank
+-++--+-+---+--
20150 | 1 | 1 | t| 20139 | 4 |1
20150 | 2 | 1 | t| 20139 | 4 |1
20150 | 200146 | 1 | t| 20146 | 3 |1
20150 | 200147 | 1 | t| 20146 | 3 |1
20150 | 200148 | 1 | t| 20146 | 3 |1
20150 | 200149 | 2 | t| 20146 | 3 |1
20150 | 200150 | 1 | t| 20146 | 3 |1
20150 | 200151 | 1 | t| 20146 | 3 |1
20150 | 200152 | 1 | t| 20146 | 3 |1
20150 | 200153 | 7 | t| 20150 | 0 |1
20150 | 200153 | 1 | t| 20146 | 3 |2
20150 | 200154 | 1 | t| 20146 | 3 |1
[ rows removed for brevity ]
(38 rows)
This version, which should be equivalent, yields crazy rank values:
with recursive parent_orgs(parent_org_id, org_id) as
(
select pr.first_party_id, pr.second_party_id
from party_rels pr
where pr.relationship_type_id in (1009, 1010, 1011, 1012, 1013)
),
ancestor_orgs(
ancestor_org_id, org_id, depth) as
(
select org_id, org_id, 0
from parent_orgs
union all
select p.parent_org_id, a.org_id, a.depth + 1
from ancestor_orgs a
join parent_orgs p
on p.org_id = a.ancestor_org_id
),
visible_org_items(org_id, item_id,
seq_id, complete, ancestor_org_id,
depth, rank) as
(
select ao.org_id, oi.item_id,
oi.seq_id, oi.complete,
ao.ancestor_org_id, ao.depth,
rank() over (partition by oi.item_id order by ao.depth)
from ancestor_orgs ao
join oitems oi
on oi.org_id = ao.ancestor_org_id
)
select *
from visible_org_items
where org_id = 20150;
org_id | item_id | seq_id | complete | ancestor_org_id | depth |
rank
+-++--+-+---+--
20150 | 1 | 1 | t| 20139 | 4 | 21
20150 | 2 | 1 | t| 20139 | 4 | 21
20150 | 200146 | 1 | t| 20146 | 3 |9
20150 | 200147 | 1 | t| 20146 | 3 |9
20150 | 200148 | 1 | t| 20146 | 3 |9
20150 | 200149 | 2 | t| 20146 | 3 |9
20150 | 200150 | 1 | t| 20146 | 3 |9
20150 | 200151 | 1 | t| 20146 | 3 |9
20150 | 200152 | 1 | t| 20146 | 3 |9
20150 | 200153 | 7 | t| 20150 | 0 |1
20150 | 200153 | 1 | t| 20146 |
