Question about FETCH FIRST

[Tiago Espinha <ti...@espinhas.net>]

Hey everyone,

I have been doing the buddy testing for the OFFSET and FETCH FIRST features
of 10.5.1.1 RC2 [1] and I ran into something that perhaps one of you can
shine me some light on.

The question is about the following SQL statement:
  select * from t1 fetch first 0 rows only;

Should this statement throw an error according to the standard? Or should it
return simply an empty result set?

All in all, we do allow the OFFSET argument to be zero, which has a null
applicability (since you get the whole result set that you would without the
OFFSET clause in the first place) but it can be useful when integrating with
an application. I was just thinking that perhaps the same should apply to
the FETCH FIRST, even though you'll always get an empty record set should
the argument be zero.

[1] - http://docs.google.com/Doc?id=dgdrw3xt_4f7s6jrht

Regards,
Tiago

Re: Question about FETCH FIRST

[Tiago Espinha <ti...@espinhas.net>]

Hi Dag,
Thank you for your input. :)

I think it's perfect as it is then. If the standard is clear and strict like
that, then I'm all for keeping it the way it is.

Tiago

On Mon, Apr 27, 2009 at 9:51 PM, Dag H. Wanvik <Da...@sun.com> wrote:

>
> Hi Tiago,
>
> thanks for testing this new feature!
>
> Tiago Espinha <ti...@espinhas.net> writes:
>
> > The question is about the following SQL statement:
> >   select * from t1 fetch first 0 rows only;
>
> According to the latest SQL standard draft that I've been able to see,
> this should give an error, cf. section 7.13 (quote):
>
> 7.13 <query expression>
>
> General Rules
> :
> 8) If <fetch first clause> is specified, then:
>   a) Let OCT2 be the cardinality of T.
>
>      NOTE 212 - OCT2 is the cardinality of T after the removal of any
>      rows from T by the application of the prior General Rule.
>   b) Case:
>
>       i) If <fetch first row count> is specified, then let FFRC be
>       the value of <fetch first row count>.
>
>       ii)     Otherwise, let FFRC be 1 (one).
>
>   c) If FFRC is less than 1 (one), then an exception condition is
>       raised: data exception - invalid row count in fetch first
>       clause.
>
> I would have liked the semantics you suggest, but for prudence I think
> we should stick with the standard for now. We could always be more
> liberal later, it is harder to remove something people already rely
> on.
>
> Dag
>

Re: Question about FETCH FIRST

["Dag H. Wanvik" <Da...@Sun.COM>]

Hi Tiago,

thanks for testing this new feature!

Tiago Espinha <ti...@espinhas.net> writes:

> The question is about the following SQL statement:
>   select * from t1 fetch first 0 rows only;

According to the latest SQL standard draft that I've been able to see,
this should give an error, cf. section 7.13 (quote):

7.13 <query expression>

General Rules
:
8) If <fetch first clause> is specified, then:
   a) Let OCT2 be the cardinality of T.

      NOTE 212 - OCT2 is the cardinality of T after the removal of any
      rows from T by the application of the prior General Rule.
   b) Case:

       i) If <fetch first row count> is specified, then let FFRC be
       the value of <fetch first row count>.

       ii)     Otherwise, let FFRC be 1 (one).

   c) If FFRC is less than 1 (one), then an exception condition is
       raised: data exception - invalid row count in fetch first
       clause.

I would have liked the semantics you suggest, but for prudence I think
we should stick with the standard for now. We could always be more
liberal later, it is harder to remove something people already rely
on. 

Dag