db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: Question regarding DERBY-4208 Parameters ? with OFFSET and/or FETCH
Date Thu, 09 Jul 2009 14:06:08 GMT
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

Mime
View raw message