ADQL - problems with LIMIT OFFSET on sharded DBs

[Dave Morris]

Hi Kristin,

Don't worry, OFFSET is only being proposed as an optional language 
feature, not part of the mandatory core grammar.

By default, it is assumed that OFFSET is not part of the language.

A platform that does implement OFFSET would declare that in the list of 
additional supported features in the service registration.

What we are declaring in the specification is IF a platform supports 
OFFSET, then this is the grammar it should use.

Out of interest, could you let me know the details of the database 
platforms that you are using so that I can include this in the platforms 
that I check for compatibility.

Thanks,
Dave

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

On 2016-04-21 10:18, Kristin Riebe wrote:
> Dear DAL,
> 
> I just realized (well, actually one of our database users pointed me to
> it), that OFFSET causes some trouble for sharded database systems, 
> where
> the data is distributed to a number of shard nodes (e.g. spider engine
> on MySQL). This does not mean that it shouldn't be included in the next
> ADQL version, I just want to caution that data providers using
> distributed databases may not want to support it.
> 
> For example, if there is a query using LIMIT 100 or TOP 100, we just
> hand this query to the shard nodes, collect 100 result rows from each 
> in
> one temporary table on the head node and then apply a LIMIT 100 again,
> to just return the desired 100 rows (obviously this makes only really
> sense, if an ORDER BY clause was applied as well).
> 
> For OFFSET, we cannot do it this way, because it is not clear where the
> OFFSET on each shard node has to start. If we simply apply the same
> behaviour as before, e.g. with LIMIT 100 OFFSET 2000, this would mean 
> we
> collect 100 rows from each of the shards in a combined temporary table
> on the head node. For an example of 10 shards, we have now 1000 rows on
> the head node. Applying LIMIT 100 OFFSET 2000 again (as with the simple
> LIMIT before) will now return nothing.
> 
> In order to handle it properly, we would need to pretend that the data
> was in only one table. So we should not apply LIMIT OFFSET to the shard
> nodes, but instead collect *all* data on the head node and then apply
> LIMIT OFFSET on this combined table. Which can be a huge overhead.
> 
> For other distributed systems where the nodes can communicate with each
> other, there may be better solutions, but for our system OFFSET won't
> work. Queries would either not return results as expected from the user
> or run into timeout.
> 
> So we either won't support it, tell users that it will return different
> results than on a single-server-database or we discourage users from
> using it and if they do it anyway, they most of the times will get a
> timeout.
> 
> Cheers,
> 
> Kristin
> 
> 
> 
> On 04/12/2016 09:27 PM, Walter Landry wrote:
>> Patrick Dowler <pdowler.cadc at gmail.com> wrote:
>>> As far as I can see, Sybase ASE (15.x) does not support LIMIT or
>>> OFFSET.  I don't see anything about Oracle above and that is used by
>>> ALMA and could be adopted by other projects for "reasons".
>> 
>> We (IRSA) also use Oracle.  Oracle added OFFSET in version 12, but we
>> are stuck on 10.2.  The workarounds I have seen are pretty ugly and
>> inefficient [1].  It requires rewriting the query to add a row number.
>> Then we filter based on that row number.  Finally, we would have to
>> remove the row number (but only if the user did not ask for it!).  So
>> there is a fair amount of query analysis that I do not want to think
>> about.  That makes it sufficiently annoying to implement such that
>> we would probably upgrade to a newer version of Oracle first.
>> 
>> Cheers,
>> Walter Landry
>> 
>> [1] 
>> http://stackoverflow.com/questions/7480243/sql-oracle-order-by-and-limit
>> 
> 
> --
> -------------------------------------------------------
> Dr. Kristin Riebe
> E-Science & GAVO
> 
> Email: kriebe at aip.de
> Phone: +49 331 7499-377
> Room:  B6/25
> -------------------------------------------------------
> Leibniz-Institut für Astrophysik Potsdam (AIP)
> An der Sternwarte 16, D-14482 Potsdam
> Vorstand: Prof. Dr. Matthias Steinmetz, Matthias Winker
> Stiftung bürgerlichen Rechts
> Stiftungsverzeichnis Brandenburg: 26 742-00/7026
> -------------------------------------------------------