ADQL evolution: OFFSET?

[Markus Demleitner]

Dear Colleagues,

On Mon, Jan 18, 2016 at 11:34:07AM +0000, Dave Morris wrote:
> 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.

Great.

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

Absolutely.  It turns out there's publicly available material (i.e.,
late working drafts) on that at
http://www.wiscorp.com/SQLStandards.html.  And that tells me that:

<query expression> ::=
  [ <with clause> ] <query expression body>
  [ <order by clause> ] [ <result offset clause> ] 
  [ <fetch first clause> ]
(pdfp. 427)

-- so, offset and order by are not linked in SQL 2008.  Also,

<result offset clause> ::=
  OFFSET <offset row count> { ROW | ROWS }

-- so they have the stupid "row" thing, and, if I read their version
of BNF correctly, it's even mandatory.

Also,

<offset row count> ::=
  <simple value specification>

and <simple value specification> can be all kinds of messy things ("SQL
parameter reference", "embedded variable name", oh my).

So, what does "as close as possible" mean?  I'd like to kick the
"ROW" thing, which has a heavy reek of COBOL.

As a service for the SQLServer folks whom I wouldn't want to force to
insert artificial ORDER BY clauses, I'm fine with hanging OFFSET off
of <order by clause> (which, conversely means that ADQL query writers
may have to add "artificial" order bys; but that's probably a good
idea anyway, even if it may mean some extremely whacky applications
relying on implementation details might become impossible).

The result would be, for ADQL,

<order_by_clause> ::= 
  ORDER BY <sort_specification_list> <result offset clause>
<result offset clause> ::=
  OFFSET <unsigned integer>

-- which is a relevant simplification, in particular restricting
ourselves to unsigned integers, but I propose that's a good idea.
Dropping the ROW[S], however, makes this not even a proper subset of
SQL 2008.  So, if anyone stands up and says "let's keep the ROW[S]",
I'll have no choice but to concur.

> 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.

Oh?  Well, I guess if that is true just inserting an absurdly large
LIMIT in the query morpher would be a general and IMHO acceptable
workaround.

> 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.

"work on all platforms" allows rewrites, yes?  Then I'd suggest the
grammar above should work.



While I'm writing, let me also briefly reply to Gilles:

On Wed, Jan 13, 2016 at 03:08:09PM +0100, gilles landais wrote:
> This option is clearly interesting for pagination.
> Could we considered to push this pagination capability in UWS?
> UWS is may be not dedicated for tables; however a UWS capability allowing to
> get a "cut" of a result (delimited by an offset of lines or an option to
> split the result) could be an other solution?

Well, the UWS spec contains a SQL-like cursor as an example
application, though I've never quite wrapped my head around how the
example is will work.  I also suspect we already have patterns
that might support pagination as a part of VOSpace.

It's a different layer than SQL, though; offset has more applications
than just paging, and given most RDBMSes already support it
painlessly, it's much cheaper, too.

So -- I'd definitely be interested in seeing a generic UWS (or
VOSpace)-based pagination capability in action, but I think it's only
loosely related to the question whether we want OFFSET in ADQL.

Cheers,

          Markus