ADQL evolution: OFFSET?

[gerard.lemson at gmail.com]

Hi Tom

> -----Original Message-----
> From: dal-bounces at ivoa.net [mailto:dal-bounces at ivoa.net] On Behalf Of 
> Tom McGlynn (NASA/GSFC Code 660.1)
> Sent: Monday, January 11, 2016 3:30 PM
> To: dal at ivoa.net
> Subject: Re: ADQL evolution: OFFSET?
> 
> In this discussion it seems to be implicit than using an ORDER BY 
> expression ensures that the rows will be returned in some defined 
> order.  While that is a necessary condition it is not sufficient.  The 
> value of the expression must be unique in each row to guarantee a fixed ordering.

Yes, that is true. A unique (set of) column(s) would need to be included.

> Given this below are a few cogitations on the idea of whether we 
> should couple OFFSET and ORDER BY.
> 
> - So why do we want to have the OFFSET explicitly tied to an ORDER BY?
> 
> If we want to guarantee that we can do pagination correctly, this won't do it.
> If we want to enable users to do pagaination correctly they will still 
> need to carefully pick the expression they use in the order by clause.  
> So it's not really much easier.
No, but it is still tied to an ORDER BY.

> 
> - Are there costs to requiring order by?
> 
> Order by's can dramatically slow down queries, so it's not entirely 
> free.  Since some static databases actually do return rows in a 
> consistent order (for a given query), this could be a performance hit.

Often depends on the content of the cache. If someone just ran a query that put some rows in cache, the DB may decide to start returning those while retrieving other rows from disk. But it is true I suppose that 'order by' a non-unique column can cause different orders.
I am curious to the use case for 'OFFSET number' without ORDER BY.
 
> And of course if the database is not static, then even with an order 
> by in the query one is not guananteed to get consistent results since 
> new rows may have been added or deleted.
> 
I suppose the only way in which we could be sure we'd get what we want, i.e. consistent, non-repeating pagination, is if we could open a cursor and fetch results from it. I am assuming a cursor to be transactionally stable.
I don't think this solution would work.

> - What about supporting OFFSET where the underlying database requires 
> the OFFSET to be in an ORDER BY clause: Can we handle lone OFFSETs?
> 
> I'm not familiar with the exact syntax required, but perhaps something like:
>     ORDER BY 'x' OFFSET 100
> could be used if there is no existing ORDER BY clause in the query.  
> Any constant expression would do except that I believe some databases 
> use simple integer constants to refer to a column number in the 
> result.  But this would need to be tested.
> 
I just now tied this on CasJobs' SQLServer and the error message says: 'A constant expression was encountered in the ORDER BY list'
What was accepted was ' ORDER BY 0*<numerical column> OFFSET 10 ROWS'
> 
> - So my feeling is that we shouldn't go out of our way to couple 
> OFFSET and ORDER BY.
> 
> - The SQL 2008 standard construct is
> 
>     OFFSET start ROW[S]  (at least according to POSTGRESQL which 
> supports many variants here)
> 
Same for MS SQLServer.

> While ADQL is based upon an earlier version of the SQL standard, it 
> seems gratuitously silly to not follow the agreed standard when we're 
> adding something new.  It sets a tone that we don't care about other 
> standards development.
> 
> - In the vein of the previous comment, we might also consider adding 
> support for the standard
>     FETCH FIRST count ROWS ONLY
> as a standard if verbose synonym for TOP.    [I don't like the idea of
> adding support for LIMIT since it is another non-standard keyword, 
> though since the HEASARC's TAP is running in Postgres it's easy enough 
> for us to support.]
> 
For what it's worth, I agree.

But I would now like to see the reason for introducing this clause at all without ORDER BY.


Gerard

>      Tom McGlynn
> 
> François-Xavier Pineau wrote:
> > Dear Markus, dear all,
> >
> > Here after our (Laurent Michel and I) feelings about this.
> >
> > On 01/11/2016 11:31 AM, Markus Demleitner 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
> > We are not DBMS experts but we are slightly concerned by the 
> > possible consequences of the usage of OFFSET to harvest large 
> > tables, e.g a catalogue like 2MASS.
> >
> > First point (not specific to large tables) underlined by Gerard: to 
> > obtain consistent results, one have to use OFFSET together with 
> > ORDER BY (at least in PostgresSQL), see PSQL doc here:
> > http://www.postgresql.org/docs/9.5/static/queries-limit.html.
> >
> > Then (specific to large tables), like it is also mentioned in the 
> > PSQL
> > doc:
> > "The rows skipped by an OFFSET clause still have to be computed 
> > inside the server; therefore a large OFFSET might be inefficient."
> >
> > So using LIMIT, ORDER BY and OFFSET may not be the best way to 
> > harvest a large table (huge server load), except (maybe) if ORDER BY 
> > is made on an indexed column, without filtering rows (no 'WHERE'), 
> > and if the table is clustered according to the indexed column.
> >
> >> (2) It enables pagination UIs bolted on top of an ADQL service
> > Except for large results (let's say > 100 000 rows for a web 
> > browser), the pagination can be made on the client side.
> > And above 100 000 rows, you will not look at all values but probably 
> > want to order then according to a given column or put additional or 
> > more restrictive constraints.
> >> (3) The OFFSET 0 hack that helps in several database systems to set a
> >>      confused query planner right.
> > May OFFSET 0 confuse the query planner?
> > If not, I would vote for systematically add it on the server side 
> > (the user do not have to know about it, it is internal cuisine).
> >
> > Having said this does not means that we are against its 
> > implementation in ADQL.
> >
> > Cheers,
> >
> > François-Xavier Pineau (CDS) and Laurent Michel (OAS)
> >
> >>
> >> Given that, and given I've missed OFFSET even back in 2010 or so 
> >> (when I wrote the example 
> >> http://dc.g-vo.org/tap/examples#Crossmatchforaguidestar), I'm a bit 
> >> surprised OFFSET hasn't been part of my TAP implementation notes.
> >>
> >> I don't remember why I dropped it -- perhaps because, equally 
> >> surprisingly, OFFSET is not part of SQL92.  Nevertheless, I'd like 
> >> to propose the addition of (at first optional, mandatory starting 
> >> 3.0) support for OFFSET in ADQL 2.1.
> >>
> >> This would entail giving a new rule
> >>
> >>    <offset_spec> ::= "OFFSET" <unsigned_integer>
> >>
> >> and changing the query_specification production to
> >>
> >>    <query_specification> ::=
> >>      "SELECT" [ <set_quantifier> ] [ <set_limit> ]
> >>        <select_list> <table_expression> [ <offset_spec> ]
> >>
> >> -- which would correspond to the postgres syntax of offset.
> >>
> >> Since it's not part of SQL 92, a bit of prose would be required in 
> >> the body of the spec, which I'd be happy to contribute if people 
> >> don't protest.
> >>
> >> 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)?
> >>
> >> Cheers,
> >>
> >>            Markus
> >
> >