Re: Windowing Function Patch Review -> Standard Conformance

Tom Lane Wrote:
> I've spent quite a bit of time reviewing the window functions patch,
> and I think it is now ready to commit, other than the documentation
> (which I've not looked at yet at all). Attached is my current patch
> against HEAD, sans documentation. This incorporates the recently
> discussed aggregate-function API changes and support for tuplestore
> trimming. There's a number of things that could be improved yet:
> * we really ought to have some support for non-built-in
> window functions
> * I think the planner could be a bit smarter about when to
> sort or not
> * tuplestore_advance and related code really needs to be made
> more efficient; it didn't matter much before but it does now
> but I think these things can be worked on after the core patch is
> committed.
>
> regards, tom lane

I've started running my test queries that I used when reviewing the patch.
The following crashes the backend:

CREATE TABLE billofmaterials (
parentpart VARCHAR(20) NOT NULL,
childpart VARCHAR(20) NOT NULL,
quantity FLOAT NOT NULL,
CHECK(quantity > 0),
PRIMARY KEY(parentpart, childpart)
);

INSERT INTO billofmaterials VALUES('KITCHEN','TABLE',1);
INSERT INTO billofmaterials VALUES('KITCHEN','COOKER',1);
INSERT INTO billofmaterials VALUES('KITCHEN','FRIDGE',1);
INSERT INTO billofmaterials VALUES('TABLE','CHAIR',4);
INSERT INTO billofmaterials VALUES('CHAIR','LEG',4);

WITH RECURSIVE bom AS (
SELECT parentpart,childpart,quantity,ROW_NUMBER() OVER (ORDER BY
parentpart DESC) rn
FROM billofmaterials
WHERE parentpart = 'KITCHEN'
UNION ALL
SELECT b.parentpart,b.childpart,b.quantity,ROW_NUMBER() OVER (ORDER BY
parentpart ASC) rn
FROM billofmaterials b
INNER JOIN bom ON b.parentpart = bom.childpart
)
SELECT * from bom;

It seems not to like recursively calling row_number(). It does not crash if
I replace the 2nd row_number() with the constant 1

I compared everything to Oracle again and found no differences in results.
These tests test all window functions in some way or another. I compared all
results to Oracle 10g results apart from the queries that have NTH_VALUE as
this is not implemented by Oracle 10g. Also seems like NTH_VALUE is not
implemented by DB2 9.5 either. Anyone know of any database that does have
NTH_VALUE?

David.