Forum OpenACS Q&A: Response to Problems in intranet/employees/admin

Collapse
Posted by Bob Fuller on
Regarding Dan Wickstrom's <danw@rtp.ericsson.se> reply (see below):

I've noticed that this bug also includes modules where there is a UNION but no ORDER BY, such as:

view.tcl
so it is probably a more general bug that affects more complex UNION's, I would guess, and not just UNION's that are also ORDER BY's.

Many thanks for everyone's quick feedback. It sounds to me like I should get 7.1 when it comes out in beta. Meanwhile, I may try Dan's suggested workaround (below) to see if that does the trick.

>>>>> "Bob" == Bob Fuller <juhof@yahoo.com> writes:

Bob> Wow, that was quick! PostgreSQL 7.0.2 on i686-pc-linux-gnu,
Bob> compiled by gcc egcs-2.91.66
Bob> Do you know if there is a patch for those bugs? Or do I
Bob> simply need to install a different version?

If you can wait, get pg 7.1 beta which should be coming out shortly. If not you might experiment with rewriting the query so that the function calls used for sorting are in the select list. I think something like this might work. Try:

SELECT
upper(users.last_name) || upper(users.first_names) as sortkey,
users.user_id , coalesce(info.salary, 0) as salary,
users.last_name || ', ' || users.first_names as name,
info.supervisor_id, info.years_experience as
n_years_experience, info.salary_period, info.referred_by,
to_char(info.start_date,'Mon DD, YYYY') as start_date_pretty,
(case when info.project_lead_p = 't' then 'Yes'::varchar else 'No'::varchar end) as project_lead,
(case when info.team_leader_p = 't' then 'Yes'::varchar else 'No'::varchar end) as team_lead,
(case when supervisor_id is NULL then '$missing_html' else (select s.first_names || ' ' || s.last_name from users s
where s.user_id=supervisor_id) end) as supervisor_name,
(case when info.referred_by is NULL then '<em>nobody</em>'
else (select r.first_names || ' ' || r.last_name from users r
where r.user_id=referred_by) end) as referral_name
FROM
users_active users, im_employee_info info, user_group_map ugm
WHERE
users.user_id = ugm.user_id
and ugm.group_id = [im_employee_group_id]
and users.user_id = info.user_id
UNION

SELECT

upper(users.last_name) || upper(users.first_names) as sortkey,
users.user_id , 0 as salary, users.last_name || ', ' ||
users.first_names as name, '' as supervisor_id, '' as n_years_experience, '' as
salary_period, '' as referred_by, '' as start_date_pretty,
'No'::varchar as project_lead, 'No'::varchar as team_lead,
'<em>missing</em>' as supervisor_name,
'<em>nobody</em>' as referral_name
FROM
users_active users, user_group_map ugm
WHERE
users.user_id = ugm.user_id and ugm.group_id = [im_employee_group_id]
and not exists (select 1 from im_employee_info where user_id = users.user_id)
ORDER BY
sortkey

Let me know if this works.

-Dan