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