arrays in ADQL/TAP (Was: ObsCore update discussion : adding Axes information in Obscore table)

[Markus Demleitner]

Hi Pat, hi DAL,

As we're currently talking about extending ADQL, maybe this is the
time to try and come up with something; it would definitely help the
obscore use case.

On Mon, Apr 27, 2015 at 09:07:46AM -0700, Patrick Dowler wrote:
> Anyway, I do agree that arrays can be a useful denormalisation technique and
> I wouldn't be against putting some effort into adding support for arrays of
> primitives (booleans and numbers) to ADQL and TAP.

Although I've been deeply unhappy in particular about string arrays
in VOTable (which we'd presumably need for obscore), I tend to agree
about the desirability of arrays in *AD*QL.

To make that happen, I think we need to figure out what we want in
terms of features and then figure out if everyone can go along.

In terms of features, I'd say the minimum is:

* 1 D arrays
* simple indexing: with a function or rather [] notation?
* building arrays on the fly: the array_agg aggregate function

SQL1992 doesn't have any of this, so we'd have to steal from
somewhere else (I'd propose postgres' grammar, which syntactically 
unfortunately is quite a bit different from SQL1992's grammar).

Features wie might possibly want in addition could include some of:

* n D arrays
* inline array literals ("select [ra, dec], [pmra, pmde] from...")
* operators/functions, e.g., as in 
  http://www.postgresql.org/docs/current/static/functions-array.html

Are there, maybe, other, lower-hanging fruit?


As a first step, I think it'd be useful if people running the various
database backends could report how much trouble the respective
features would be for their backends.  If some of the major ones have
no array support at all, I'd say it's a non-starter right there,
otherwise we could think of a subset of features that won't cause
havoc in our ADQL grammar.

So -- 

Postgres obviously wouldn't have trouble with any of this.

SQLite has no native array support I'm aware of, but if I had a
SQLite backend I suspect I could fairly easily inject the necessary
facilities at least for the basic function set (though I've never
actually done it, so this might be vain overconfidence).

Anyone else?

Cheers,

        Markus

Not sure about anything else.

Cheers,

        Markus