ADQL evolution: OFFSET?

[Jesus Salgado]

Hi all,

Sybase has problems with this but I am not sure if there are ADQL mappings
on Sybase around (?)

The possibility to combine LIMIT and OFFSET to control (paginate) 
results could
be quite useful. This was also one of our (ESA) ADQL evolution 
requirements for
consideration.

Cheers,
Jesus


On 11/01/2016 16:05, Kristin Riebe wrote:
> Hi all,
>
> OFFSET would also work for MySQL. The documentation for 5.7 says:
> "For compatibility with PostgreSQL, MySQL also supports the LIMIT
> row_count OFFSET offset syntax."
>
> So there should be no problems with MySQL/MariaDB, if this keyword is added.
>
> Cheers,
>
> Kristin
>
>
> 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
>> (2) It enables pagination UIs bolted on top of an ADQL service
>> (3) The OFFSET 0 hack that helps in several database systems to set a
>>      confused query planner right.
>>
>> 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
>>


This message and any attachments are intended for the use of the addressee or addressees only.
The unauthorised disclosure, use, dissemination or copying (either in whole or in part) of its
content is not permitted.
If you received this message in error, please notify the sender and delete it from your system.
Emails can be altered and their integrity cannot be guaranteed by the sender.

Please consider the environment before printing this email.