Re: Windowing Function Patch Review -> Standard Conformance

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.