Tom Lane Wrote: > Well, this certainly demonstrates that the check I added to > parseCheckAggregates is wrongly placed, but I'm not sure we really > need to forbid the case. David's example query seems to give sane > answers once the bug in begin_partition is fixed: > > parentpart | childpart | quantity | rn > ------------+-----------+----------+---- > KITCHEN | TABLE | 1 | 1 > KITCHEN | COOKER | 1 | 2 > KITCHEN | FRIDGE | 1 | 3 > TABLE | CHAIR | 4 | 1 > CHAIR | LEG | 4 | 1 > (5 rows) >
For what it's worth I've been looking into how DB2 and Sybase handle this. DB2 seems to disallow any functions in the SELECT list of the recursive part of the query. Error message is a little long winded to show here. It's also very generic and also covers GROUP Bys and HAVINGs saying that they're also not allowed. However, Sybase does allow this query. I did modify the window's ORDER BY as previously the order was undefined. The results match PostgreSQL. Also while testing I noticed that this query didn't error out when it should have: (Of course I only noticed because Sybase did) WITH RECURSIVE bom(parentpart,childpart,quantity,rn) AS ( SELECT parentpart,childpart,quantity,ROW_NUMBER() OVER (ORDER BY parentpart,childpart) FROM billofmaterials WHERE parentpart = 'KITCHEN' UNION ALL SELECT b.parentpart,b.childpart,b.quantity,ROW_NUMBER() OVER (ORDER BY parentpart,childpart) FROM billofmaterials b,bom WHERE b.parentpart = bom.childpart ) SELECT * FROM bom; Notice the ORDER BY in the recursive part of the query orders by an ambiguous column without complaint. If I replace b.quantity with just quantity it does error there. So seems to just not be picking up the problem in the window clause. David. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
