ADQL - problems with LIMIT OFFSET on sharded DBs

[Kristin Riebe]

Hi Dave,

ah, alright, if it's optional, we can easily decide to not support it. :-)

We are using MariaDB/MySQL with the SpiderEngine.
(So the language natively has the [ LIMIT offset, row]  built in, which
works fine for single-server-databases, but is problematic when used
with the SpiderEngine.)

Cheers,

Kristin


On 04/21/2016 02:52 PM, Dave Morris wrote:
> 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
>> -------------------------------------------------------
> 

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