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

Collapse
1: Problems in intranet/employees/admin
Posted by Bob Fuller on 11/29/00 09:40 PM

In index.tcl (and also update-supervisor.tcl):

The rewrite of the outer join on
  • users_active
  • im_employee_info
  • user_group_map
  • users (omitted in the rewrite)
as a UNION does not work. I tried the two parts of the UNION separately, and they worked independently. However, with the UNION in place, when I run the query from psql (substituting where needed for any of the Tcl used within the SQL), I got some sort of weird Postgresql error message. It looks like it's having trouble with the ORDER BY at the end of the UNION. Looks to me like a bug in Postgresql...

My initial idea for a workaround was to create a temp table and then order from the temp table (after doing two INSERT statements into the temp table, in place of the UNION). However, as I soon discovered, the

ns_db dml $db
statement does not permit the creation of tables, whether temporary or permanent, which probably means that a more convoluted workaround will be necessary.

In update-supervisor-2.tcl:

The two validate_integer statements were broken (missing the curly braces). The fixed lines are as follows:
validate_integer user_id ${dp.im_employee_info.user_id}
validate_integer supervisor_id ${dp.im_employee_info.supervisor_id}
Collapse
2: Response to Problems in intranet/employees/admin (response to 1)
Posted by Dan Wickstrom on 11/29/00 09:52 PM
What version of pg are you using?  I believe that there were some bugs in pg that are related to unions and order by statements.
Collapse
3: Response to Problems in intranet/employees/admin (response to 1)
Posted by Don Baccus on 11/30/00 12:37 AM
Yes, due to limitations in the query tree design, PG 7.0 and prior versions apply the "order by" only to the first clause of the query.

There's no easy way around it.  Two queries, I guess, instead of one, ugh.

The query tree has been redefined in PG 7.1, and now handles such queries perfectly.  I've been able to get an OpenACS 3.2 installed and working on PG 7.1 from current (as of last weekend) sources from the CVS tip.  It will beta very shortly.  You might consider picking it up  as soon as it does if you're planning on using the intranet module.

Collapse
4: Response to Problems in intranet/employees/admin (response to 1)
Posted by Bob Fuller on 11/30/00 11:44 PM
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

Collapse
5: Response to Problems in intranet/employees/admin (response to 1)
Posted by Don Baccus on 12/01/00 01:02 AM
I fixed some of these complex unions by simplification, they weren't correctly translated.  I suspect that you may be seeing something similar.  AFAIK complex unions in PG 7.0 work just fine without "order by", thus far the problems I've run into have been with our translations not PG.