SQL standard "FETCH FIRST N ROWS ONLY" support

[Steven Schlansker]

Hi all,

The change log for 1.3.161 refers to "Improved compatibility for
"fetch first / next row(s)""
(http://www.h2database.com/html/changelog.html)


However, with "H2 1.3.161 (2011-10-28)" I cannot seem to use this:

select 1 fetch first 1 row only;
Syntax error in SQL statement "SELECT 1 FETCH FIRST[*] 1 ROW ONLY ";
SQL statement:
select 1 fetch first 1 row only [42000-161] 42000/42000 (Help)

The same statement works fine in PostgreSQL which I know supports it
--

postgres=# select 1 fetch first 1 row only;
?column?
----------
1
(1 row)

This is expected to work, yes?

[litailang]

Hi.

currently that statement is supported on mode DB2 or Derby or PostgreSQL.

please try to use SET MODE=PostgreSQL.

litailang

2011/11/12 Steven Schlansker <stevensc...@gmail.com>:

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

--
-----------
Ayataro Kogo

[Steven Schlansker]

Thank you, that solves my problem perfectly!

Would it be reasonable to accept this syntax by default (without
compatibility tweaks) as it is the SQL standard way?

Failing that, could it at least be documented? Perhaps in the
compatibility options (http://h2database.com/html/
features.html#compatibility) or in the main documentation.

Thanks!

On Nov 12, 4:27 pm, litailang <litail...@gmail.com> wrote:
> Hi.
>
> currently that statement is supported on mode DB2 or Derby or PostgreSQL.
>
> please try to use SET MODE=PostgreSQL.
>
> litailang
>

> 2011/11/12 Steven Schlansker <stevenschlans...@gmail.com>:

>
>
>
>
>
>
>
>
>
> > Hi all,
>
> > The change log for 1.3.161 refers to "Improved compatibility for
> > "fetch first / next row(s)""
> > (http://www.h2database.com/html/changelog.html)
>
> > However, with "H2 1.3.161 (2011-10-28)" I cannot seem to use this:
>
> > select 1 fetch first 1 row only;
> > Syntax error in SQL statement "SELECT 1 FETCH FIRST[*] 1 ROW ONLY ";
> > SQL statement:
> > select 1 fetch first 1 row only [42000-161] 42000/42000 (Help)
>
> > The same statement works fine in PostgreSQL which I know supports it
> > --
>
> > postgres=# select 1 fetch first 1 row only;
> >  ?column?
> > ----------
> >        1
> > (1 row)
>
> > This is expected to work, yes?
>
> > --
> > You received this message because you are subscribed to the Google Groups "H2 Database" group.
> > To post to this group, send email to h2-da...@googlegroups.com.
> > To unsubscribe from this group, send email to h2-database...@googlegroups.com.

> > For more options, visit this group athttp://groups.google.com/group/h2-database?hl=en.
>
> --
> -----------
> Ayataro Kogo

[Thomas Mueller]

Hi,

Would it be reasonable to accept this syntax by default (without
compatibility tweaks) as it is the SQL standard way?

Yes, but changing this would possibly impact existing applications (it's unlikely but possible), because "fetch" would become a keyword (currently it's not).

For version 1.4.x I will enable it by default.

Regards,

Thomas