Description of problem: Hierarchy Denormalization fails when adding a subgroup to a group when that group is a subgroup to another group with more than one path. Version-Release number of selected component (if applicable): How reproducible: Every time Steps to Reproduce/Actual results/Expected results: 1. create group1, group2, group3, and group4 2. create a group subgroup map like so |- group1 | |- group2 | | |- group3 | |- group3 |- group4 group_subgroup_trans_index looks like the following: group_id subgroup_id n_paths ^^^^^^^^ ^^^^^^^^^^^ ^^^^^^^ group1 group1 0 group1 group2 1 group1 group3 2 3. place group4 under group3 |- group1 | |- group2 | | |- group3 | | |- group4 | |- group3 | | |- group4 group_subgroup_trans_index looks like the following: group_id subgroup_id n_paths ^^^^^^^^ ^^^^^^^^^^^ ^^^^^^^ group1 group1 0 group1 group2 1 group1 group3 2 group1 group4 1 <--- note: should be 2 4. remove group3 as a subgroup of group2 |- group1 | |- group2 | |- group3 | | |- group4 group_subgroup_trans_index looks like the following: group_id subgroup_id n_paths ^^^^^^^^ ^^^^^^^^^^^ ^^^^^^^ group1 group1 0 group1 group2 1 group1 group3 1 Note: group4 is not a subgroup of group1 according to group_subgroup_trans_index but it should be! Additional info: I am attaching a fix. I am running MS SQL Server 2000 so have only tested it with that database. I just translated the MS SQL Server logic into PL/SQL and don't even know if it complies but if not you should get the idea.
Created attachment 96835 [details] Package Hierarchy Denormalization Fix
When did you port the code? A similar problem was identified in Sept, and fixed on the tip @36509 (Sept 30) for Oracle. This fix was then accidentally lost on Oct 23, and then re-fixed on Nov 3. It was ported to the Postgres denormalization on November 3. Assigning to ashah to verify.
I ported it from the 5.3.0 nightly builds 08.11.2003 however I compared it to 6.1.0 nightly build 12.13.2003 and it was the same.
Fixed (@39777). I modified the patch slightly (for aesthetics) and applied to both oracle and postgres.