=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: > Consider the following scenario:
> select * from (Select nextval('seq_ab') as nv, > * from ( select > t_product.id,t_text.value,t_price.price > from t_product,t_price,t_text > where t_product.id = t_price.product_id > and t_product.name = t_text.id > and t_text.lang='de' > and t_price.typ = 'default' > order by price desc ) as t ) as u > WHERE nv <= 1 > ; I don't think there's any very clean way to fix this sort of problem in general. We could make this particular example work if (1) we prevented a subquery containing volatile functions in its targetlist from being flattened into the parent query, and (2) we prevented outer WHERE clauses from being pushed down into a subquery when they reference subquery outputs containing volatile functions. There has been some recent discussion about doing (1) but I think we forgot about the necessity to also do (2); otherwise you'd end up with select * from (Select nextval('seq_ab') as nv, ... WHERE nextval('seq_ab') <= 1 ) as u ; which is hardly any better. Now those things are both doable but where it really falls down is when you join the subselect to some other table. Short of materializing the subselect there'd be no way to guarantee single evaluation of any one row in the subselect. I'd be willing to do (1) and (2) but not to force materialization; the performance hit for that just seems unacceptable. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]