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.
