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 BYsortkey
Let me know if this works.
-Dan
