Forum OpenACS Q&A: Query problem after upgrade of PG and driver.

Here is the NOTICE from the pg driver and the error:

NOTICE:  Adding missing FROM-clause entry in subquery for table "ec_orders"
[31/Dec/2001:10:16:07][28943.437262][-conn23-] Error: Ns_PgExec: result status: 7 message: ERROR:  Relation 'o' does not exist
 
[31/Dec/2001:10:16:07][28943.437262][-conn23-] Error: dbinit: error(localhost::acspg3,ERROR:  Relation 'o' does not exist
): '
(select o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id) as price_to_display, o.user_id, u.first_names, u.last_name, count(*) as n_items
from ec_orders o, users u, ec_items i
where o.user_id=u.user_id
and o.order_id=i.order_id
and trunc((sysdate()-o.confirmed_date)) <= 7 and o.order_state='confirmed'
group by o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id), o.user_id, u.first_names, u.last_name)
union
(select o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id) as price_to_display, o.user_id, ''::char as first_names, ''::char as last_name, count(*) as n_items
from ec_orders o, ec_items i
where 0=(select count(*) from users where user_id= ec_orders.user_id)
and o.order_id=i.order_id
and trunc((sysdate()-o.confirmed_date)) <= 7 and o.order_state='confirmed'
group by o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id), o.user_id, first_names, last_name)
order by o.order_id
'             

Okay, once I changed the sub-select 'o' to ec_orders I got a new error both in the webpage and through psql

NOTICE:  Adding missing FROM-clause entry in subquery for table "ec_orders"
[31/Dec/2001:11:37:18][31963.12299][-conn4-] Error: Ns_PgExec: result status: 7 message: ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns

So I decided I didn't really need the order-by at this time, so I removed the final order by, and the query works again.

So the big question is 'Is this a bug in pg?'. Obviously the new version breaks previously good code. Or did I once again fail to do some minor very important tweak?

Oh and 'Happy New Year!!' :)

I decided to look into this after all. For starters, the subselect example you give doesn't work in PG 7.1.2, nor does it work in Oracle.

Also, this doesn't work:

select f.i from foo f union select f.i from foo f order by f.i;

but this does:

select f.i from foo f union select f.i from foo f order by i;

Note that the "order by" in your ecommerce example references the column using the table alias name, which is wrong (Oracle and PG 7.1.2 believe it is wrong, and my reading of Date and Darwin makes me believe both are right). The alia table names ("range variables") don't percolate upwards from the UNION clauses to the surrounding SELECT.

So ... what did you upgrade from? PG 7.0.* to PG 7.1.*? If so, I think you're running into a bug in PG 7.0 that was worked-around by our writing a non-standard query, that now correctly breaks in PG 7.1.*.

Something like that ... could you change the order by and see if that fixes the problem?

And tell me exactly what versions you upgraded from-to?

Don,

The previous version of psql was 7.0.3. The current version is 7.1.3.

I followed your suggestions and got the order by to work correctly. Here is a summary of the order by changes I had to make:

7.0.3                         7.1.3
o.order_id                    order_id
u.first_names, u.last_name    first_names,last_name
ec_total_price(o.order_id)    price_to_display (the as ...)

It seems like the last time I tried the last one in oracle, it didn't work like this, although I had hoped that it would.

Thanks for your help, and Happy New Year!