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

Re: Frames vs partitions: is SQL2008 completely insane?

From: "David Rowley" <dgrowley(at)gmail(dot)com>
To: "'Hitoshi Harada'" <umi(dot)tanuki(at)gmail(dot)com>,"'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Frames vs partitions: is SQL2008 completely insane?
Date: 2008-12-27 22:50:59
Message-ID: 835DCEAA0D7B4D4B88AF0C55BE37E812@amd64 (view raw or flat)
Thread:
Lists: pgsql-hackers
Hitoshi Harada wrote:
> I tested on Oracle 10.2.0, and the results are:
> 
> select depname, empno, salary,
> lead(salary, 1) over (order by salary),
> lag(salary, 1) over (order by salary),
> first_value(salary) over (order by salary),
> last_value(salary) over (order by salary)
> from empsalary;
> 
> DEPNAME	 EMPNO	 SALARY	 LEAD(SALARY,1)OVER(ORDERBYSALARY)
> LAG(SALARY,1)OVER(ORDERBYSALARY)
> FIRST_VALUE(SALARY)OVER(ORDERBYSALARY)
> LAST_VALUE(SALARY)OVER(ORDERBYSALARY)
> personnel	 5	 3500	 3900	     	 3500	 3500
> personnel	 2	 3900	 4200	 3500	 3500	 3900
> develop	 7	 4200	 4500	 3900	 3500	 4200
> develop	 9	 4500	 4800	 4200	 3500	 4500
> sales	 4	 4800	 4800	 4500	 3500	 4800
> sales	 3	 4800	 5000	 4800	 3500	 4800
> sales	 1	 5000	 5200	 4800	 3500	 5000
> develop	 10	 5200	 5200	 5000	 3500	 5200
> develop	 11	 5200	 6000	 5200	 3500	 5200
> develop	 8	 6000	     	 5200	 3500	 6000
> 
> which means the section 4.15 is true. Could anyone try DB2?

DB2 9.5 results:

Using the empsalary table from the regression test in the patch:

select depname, empno, salary,
lead(salary, 1) over (order by salary),
lag(salary, 1) over (order by salary),
first_value(salary) over (order by salary),
last_value(salary) over (order by salary)
from empsalary;

personnel	5	3500	3900		3500	3500
personnel	2	3900	4200	3500	3500	3900
develop	7	4200	4500	3900	3500	4200
develop	9	4500	4800	4200	3500	4500
sales	4	4800	4800	4500	3500	4800
sales	3	4800	5000	4800	3500	4800
sales	1	5000	5200	4800	3500	5000
develop	10	5200	5200	5000	3500	5200
develop	11	5200	6000	5200	3500	5200
develop	8	6000		5200	3500	6000


Which matches with your Oracle results. So either they both got it wrong by
one copying the other... <sarcasm> Of course we all know it couldn't be
Oracle copying IBM, that would never happen... </sarcasm>

David



In response to

pgsql-hackers by date

Next:From: David RowleyDate: 2008-12-27 22:54:40
Subject: Re: Frames vs partitions: is SQL2008 completely insane?
Previous:From: Lawrence, RamonDate: 2008-12-27 20:10:26
Subject: Re: Proposed Patch to Improve Performance of Multi-BatchHash Join for Skewed Data Sets

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