Re: FETCH FIRST clause PERCENT option

On 11/25/18 1:05 PM, Vik Fearing wrote:
> On 25/11/2018 12:49, Surafel Temesgen wrote:
>>
>>
>> On Sun, Nov 25, 2018 at 1:24 PM Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com
>> <mailto:vik(dot)fearing(at)2ndquadrant(dot)com>> wrote:
>>
>>
>> Also, this query returns 210 rows instead of the expected 208:
>>
>>     select *
>>     from generate_series(1, 1000)
>>     fetch first 20.8 percent rows only
>>
>> this is because fetch first values work with integer and it change
>> fractional number to nearest integer number like select * from
>> generate_series(1, 1000) fetch first 20.3 rows only; is not an error
>> rather it return 20 rows.
>
> I don't see how this behavior is justified by reading the SQL standard.
> Obviously only an integer number of rows is going to be returned, but
> the percentage should be calculated correctly.
>

Right. My draft of SQL standard says this:

<fetch first clause> ::=
FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS }
{ ONLY | WITH TIES }

<fetch first quantity> ::= <fetch first row count>
| <fetch first percentage>

<fetch first percentage> ::= <simple value specification> PERCENT

and then

30) The declared type of <fetch first row count> shall be an exact
numeric with scale 0 (zero).

31) The declared type of the <simple value specification> simply
contained in <fetch first percentage> shall
be numeric.

So the standard pretty much requires treating the value as numeric.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services