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.fearing@2ndquadrant.com
>> <mailto:vik.fearing@2ndquadrant.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