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

Re: Windowing Function Patch Review -> NTILE function

From: "Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com>
To: "David Rowley" <dgrowley(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Windowing Function Patch Review -> NTILE function
Date: 2008-11-10 14:19:02
Message-ID: e08cc0400811100619w46a1b013x2051f6a8f2dad116@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2008/11/9 David Rowley <dgrowley(at)gmail(dot)com>:
>
> I've done a little testing with NTILE(). I think a check should be added to
> the ntile() function in wfunc.c.
>
> david=# select name,salary,ntile(0) over (order by salary) as n from
> employees;
> ERROR:  floating-point exception
> DETAIL:  An invalid floating-point operation was signaled. This probably
> means an out-of-range result or an invalid operation, such as division by
> zero.
>
> I tracked that message back to the signal handler in postgres.c :-( simple
> fix though. Any value less than 1 does not really make sense to me.
>
> Maybe we should add something like:
>
> if (PG_WINDOW_ARG(0) < 1)
>  elog(ERROR, "negative or zero ntile argument not allowed");
>
> What do you think?
>
> Oracle errors out on less than 1, Sybase seems not to have ntile.
> MSSQL 2008 also errors out on less than 1
>
> David.
>
>

I am so sorry but I missed this thread.

I found in the spec:
1) If NT is the null value, then the result is the null value.
2) If NT is less than or equal to 0 (zero), then an exception
condition is raised: data exception
― invalid argument for NTILE function.

My patch violates both of two :-( As you point, we must add the value
check and also allow null case to return null.

will be fixed soon.


Regards,


-- 
Hitoshi Harada

In response to

pgsql-hackers by date

Next:From: Hitoshi HaradaDate: 2008-11-10 14:24:41
Subject: Re: Windowing Function Patch Review -> NTH_VALUE
Previous:From: Matthew T. O'ConnorDate: 2008-11-10 14:14:19
Subject: Re: [WIP] In-place upgrade

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