Bug 111732

Description of problem:
The /admin application contains a hierarchical group browser tree. At
the top level this appears to list all groups whether they are top
level groups or not. The query that generates this top level list of
nodes is found in pdl/com/arsdigita/ui/admin/Admin.pdl, the query
AllNoParentGroups. In 5.2 this is 'optimized' for PostgreSQL:

        select group_id
          from groups
         where not exists (
                           select 1
                           from group_subgroup_map
                           where group_id = subgroup_id)

Unfortunately the 'where' clause in the inner select statement is
flawed because the 'group_id' reference is matching 'group_id' on
'group_subgroup_map' table rather than the outer 'groups' table. So
the overall statement reduces to:

        select group_id
          from groups;

Quite an optmization indeed, but sadly no longer correct behaviour.
The references to field names need to be qualfied:

        select g.group_id
          from groups g
         where not exists (select 1
                           from group_subgroup_map m
                           where g.group_id = m.subgroup_id)



Version-Release number of selected component (if applicable):


How reproducible:
ALWAAYS

Steps to Reproduce:
1. Go to /ccm/admin
2. Go to the 'groups' tab
3. Expand the root node
  
Actual results:
'Content administration Author, contet administration editor, content
administration publisher & more child groups are all visible at the
top level.

Expected results:
Only groups with no parent are visible.

Additional info:

Fixed in p4 38621. Needs to be applied to 5.2, 6.0 & dev.