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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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