ADQL evolution: OFFSET?

[Kristin Riebe]

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
> 

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