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

I know this isn't quite a 4.0 problem, but I upgraded my database and driver to the most recent versions available from cvs on Friday. Now I am getting an error on an old ACS 3.x ecommerce page, specifically by-order-state-and-time.tcl. It is in a union query. I'll post it in a follow-up.
Collapse
Posted by Tom Jackson on

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
'             
Collapse
Posted by Tom Jackson on

I'm not sure if this is the issue, but a sub-select referencing an aliased table doesn't seem to work:

acspg3=> select count(*) from ec_orders o
acspg3-> where o.order_id = (select max(order_id) from ec_orders);
 count
-------
     1
(1 row)                                                                                                                    
acspg3=> select count(*) from ec_orders o
acspg3-> where o.order_id = (select max(order_id) from o);
ERROR:  Relation 'o' does not exist 
Collapse
Posted by Tom Jackson on

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!!' :)

Collapse
Posted by Don Baccus on
They ignored my last bug report, substantiated by the standard and Date and Darwin's book.  So I'm going to pass on investing time looking into the standard to decide whether or not the alias problem is a bug or not.

My guess is it is.  Report it and see what they say.

Collapse
Posted by Don Baccus on
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?

Collapse
Posted by Don Baccus on
Oh, the reason your first example (sub-select) doesn't work is that it's not referring to the outer table.  It's a separate select on the table - the range variable (alias) refers to the first appearance of the table, only.

Does this make sense?

Collapse
Posted by Tom Jackson on

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!

Collapse
Posted by Tom Jackson on

Yes! Your suggestion makes perfect sense. I tested and fixed the subselect first, but gave up on the order by until you helped out. My problem was a lack of knowledge of if this was a bug or not, since the query had been working for well over a year without problems.

The current working query is now:

(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)) <= 1 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)) <= 1 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 order_id 

The new correct way does make sense.