ADQL evolution: OFFSET?

[Walter Landry]

Markus Demleitner <msdemlei at ari.uni-heidelberg.de> wrote:
> Dear DAL list,
> 
> One of the users of DaCHS' ADQL engine requested the addition of
> OFFSET, and indeed I think there's at least three good reasons to do
> so:
> 
> (1) It aids harvesting, which in particular with obscore is IMHO a
>     very nice thing to have
> (2) It enables pagination UIs bolted on top of an ADQL service
> (3) The OFFSET 0 hack that helps in several database systems to set a
>     confused query planner right.

I would like to add that I support this in the general sense of making
it possible to do more sophisticated analysis on the server.

<snip>
> But: How does everyone else feel about this?  In particular: Is this
> an issue for non-postgres databases (and I'm mainly thinking of MySQL
> and Oracle here; SQLlite has the feature and even syntax as proposed
> here)?

On Oracle 11g, we can workaround it to some degree by rewriting the
query to use rownum

  http://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle#2912182
  http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/

However, the solution that those links propose does add a column.  So
if a user made a query like

  select ra,dec from fp_psc limit 10 offset 13

it gets turned into

  select * from
    ( select ra, dec, rownnum as rn
        from fp_psc )
    where rn >= 13 and rn <= 10+13

which returns a table with ra, dec, and rownum, which is wrong.  We
could work around this by using a unique name like
T0fdd9d4d63c5772cade2001 instead of rn.  Then we would postprocess the
table to remove any columns with that name.  I guess that we have to
do this for TOP anyway [1], so it not any additional work.

Cheers,
Walter Landry

[1] I realized that right now we use a cursor and just advance it, but
that does not work for subqueries.  Sigh.  Yet another bug in our
service :(