Skip site navigation (1) Skip section navigation (2)

Re: Windowing Function Patch Review -> Standard Conformance

From: "David Rowley" <dgrowley(at)gmail(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"'Hitoshi Harada'" <umi(dot)tanuki(at)gmail(dot)com>
Cc: "'PostgreSQL-development'" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Windowing Function Patch Review -> Standard Conformance
Date: 2008-12-29 22:35:33
Message-ID: 5D62993BA65248E480A22D1E37286C58@amd64 (view raw or flat)
Thread:
Lists: pgsql-hackers
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.




In response to

Responses

pgsql-hackers by date

Next:From: Alex HunsakerDate: 2008-12-29 22:46:36
Subject: Re: contrib/pg_stat_statements 1226
Previous:From: Alex HunsakerDate: 2008-12-29 22:25:54
Subject: Re: new libpq SSL connection option

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group