ADQL evolution: OFFSET?

[Dave Morris]

Hi Markus,

On 2016-01-12 10:21, Markus Demleitner wrote:
> 
> So -- it seems nobody wanted to keep OFFSET out of ADQL 2.1 really
> hard.  Now, Dave, as the editor of ADQL 2.1, what's your stance:
> Should OFFSET go in?  And if so, attached to query_specification or
> to order_by_clause?  Should I put something into the text on volute?
> Should we solicit more opinions?
> 

I agree there is a good case for accepting OFFSET.

By my count we have positive responses for

     PostgreSQL (Marcus)
     MySQL (Kristin)

"possible with work arounds" responses for

     SQLServer (Theresa, Gerard, Dave)
     Oracle (Walter)

and as yet, no "definitely not" responses.

----

As to the syntax, I agree with Tom, ideally we want to keep as close as 
possible to the SQL 2008 standard.

I think that on its own the case for linking OFFSET to ORDER BY on 
efficiency grounds is not strong enough to justify diverging from the 
SQL standard.

As number of people have pointed out, this is just one of many ways to 
write syntactically correct but computationally inefficient queries. Not 
least of which, you could have OFFSET with ORDER BY on a non-indexed 
non-unique column, which would be syntactically correct, inefficient and 
inaccurate all at the same time.

The issue of query efficiency is perhaps better handled as part of a 
query plan response from the TAP service, rather than trying to tweak 
the query language grammar to exclude inefficient queries.

However, I think we need to do a bit more work before we can define a 
syntax that will work on all the platforms we want to include.

A number of platforms only allow OFFSET as part of a LIMIT construct, so 
we will need to find a way for them to implement it.

Assuming we find a syntax that will work on all the platforms, I'm happy 
to include OFFSET as optional in 2.1 with the option to look at making 
it mandatory in 3.x.

Cheers,
Dave

--------
Dave Morris
Software Developer
Wide Field Astronomy Unit
Institute for Astronomy
University of Edinburgh
--------