db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lance Andersen <Lance.Ander...@Sun.COM>
Subject Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Date Thu, 09 Jul 2009 14:20:46 GMT
Hi Dag,

We are also adding support for this via a JDBC ESCAPE in JDBC 4.1

The current versions of Sybase support TOP and  SQL Anywhere and MS  
SQL Server supports it as well

INFORMIX IDS supports Limit

On Jul 9, 2009, at 10:06 AM, Dag H. Wanvik wrote:

> Kathey Marsden <kmarsdenderby@sbcglobal.net> writes:
>> I am hesitant to introduce behavior that is not standard compliant,
>> but may be less hesitant if it is a sort of implied industry  
>> standard.
>> What other database products do/do not support this syntax?
> * MySQL allows it in their LIMIT construct, cf.
> http://dev.mysql.com/doc/refman/5.0/en/select.html
> * DB2 has a syntax similar to the standard, but doesn't appear to
> allow dynamic parameters:
> http://bytes.com/groups/ibm-db2/185741-jdbc-fetch-first-rows-only
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0005280.htm
> *  PostGreSQL has both a LIMIT and the new OFFSET/FETCH FIRST syntax:
> http://www.postgresql.org/docs/current/static/sql-select.html
> It appears to allow dynamic evaluation, c.f this quote:
> "If the count expression evaluates to NULL, it is treated as LIMIT
> ALL, i.e., no limit. If start evaluates to NULL, it is treated the
> same as OFFSET 0."
> in other words it can be an expression, not just a literal as the
> standard currently requires.
> * Sybase has a special statement called SET ROWCOUNT, it seems. It's  
> not
> equivalent, I think, since "A limit violation occurs when the number
> of rows returned by a select statement exceeds the limit value", says
> the documentation:
> http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sag2/html/sag2/sag234.htm
> but presumably this can be set before each execution of SELECT,
> although the docs are not explicit about this, at least not in the
> cited section.
> * SQL Server has a similar statement to Sybase, but the semantics seem
> more benign:
> "Causes SQL Server to stop processing the query after the specified
> number of rows are returned." (no talk of violation or error here).
> http://msdn.microsoft.com/en-us/library/ms188774.aspx
> Looking at this example, it seems work dynamically:
> http://authors.aspalliance.com/stevesmith/articles/sprowcount.asp
> * Oracle has a ROWNUM builtin that can be used in dynamic queries,
>  akin to the standard ROW_NUMBER(). This can be used for DERBY also,
>  but the current implementation of ROW_NUMBER has some bugs and users
>  seems to balk at the WINDOWing complexity for something as simple as
>  limiting the number of rows returned. Significantly, it still doesn't
>  work in conjunction with ORDER BY (DERBY-3634).
>  Note that the Oracle version requires a subquery, but does not
>  require a windowing syntax:
>  select * from ( select * from emp order by sal desc ) where ROWNUM  
> <= 5;
> http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
> In summary, the record is a bit mixed. Hearsay from the SQL committee
> indicates that the 2011 version will allow dynamic parameters for
> OFFSET/FETCH NEXT, and it seem the PostGreSQL people have had similar
> thoughts.
> Thanks,
> Dag

View raw message