Re: Window functions patch v04 for the September commit fest

Heikki Linnakangas wrote:
> I'll review the parser/planner changes from the current patch.

Looks pretty sane to me. Few issues:

Is it always OK to share a window between two separate window function
invocations, if they both happen to have identical OVER clause? It seems
OK for stable functions, but I'm not sure that's correct for expressions
involving volatile functions. I wonder if the SQL spec has anything to
say about that.

As you noted in the comments, the planner could be a lot smarter about
avoiding sorts. Currently you just always put a sort node below each
Window, and another on top of them if there's an ORDER BY. There's
obviously a lot of room for improvement there.

This line:
> result_plan->targetlist = preprocess_window(tlist, result_plan);
in planner.c, won't work if result_plan is one that can't do projection.
A few screenfuls above that call, there's this:
> /*
> * If the top-level plan node is one that cannot do expression
> * evaluation, we must insert a Result node to project the
> * desired tlist.
> */
> if (!is_projection_capable_plan(result_plan))
> {
> result_plan = (Plan *) make_result(root,
> sub_tlist,
> NULL,
> result_plan);
> }
> else
> {
> /*
> * Otherwise, just replace the subplan's flat tlist with
> * the desired tlist.
> */
> result_plan->targetlist = sub_tlist;
> }
which is what you need to do with the preprocess_window call as well. I
think this is caused by that:
postgres=# explain SELECT row_number() OVER (ORDER BY id*10) FROM
(SELECT * FROM foo UNION ALL SELECT * FROM foo) sq;
ERROR: bogus varattno for OUTER var: 2

And then there's these:

postgres=# explain SELECT * FROm foo WHERE row_number() OVER (ORDER BY
id) > 10;
ERROR: winaggref found in non-Window plan node
postgres=# explain UPDATE foo SET id = 10 RETURNING (ROW_NUMBER() OVER
(ORDER BY random())) ;
ERROR: winaggref found in non-Window plan node
postgres=# explain SELECT row_number() OVER (ORDER BY (1)) FROm foo;
ERROR: ORDER/GROUP BY expression not found in targetlist
postgres=# explain SELECT row_number() OVER (ORDER BY length('foo'))
FROM foo;
ERROR: could not find pathkey item to sort

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com