Speak out on ADQL 2.1

If you've always wanted to be part of a standardisation process within the IVOA (and who would not?), the time has rarely been as good as now. Because: We're updating ADQL! Yes! The ADQL you are writing your queries in will receive a few more language elements, and we're carefully trying to heal a few things that turned out to be warts. And while some of the changes are as dull and boring as you may expect standards work to be, on some of them you may wish to have a saying.

Also, you can try things out – the GAVO data center TAP endpoint at http://dc.g-vo.org/tap already has most of the proposed features, and the new DaCHS beta 1.1.2 (out since last Friday) does, too. So, if you're running DaCHS yourself, you can start playing after switching to the beta repository.

What's new?

• You're now supposed to write the standard crossmatch as DISTANCE(ra1, dec1, ra2, dec2)<dist. This replaces the old dance with 1=CONTAINS(POINT(), CIRCLE()) that you've probably learned to hate. Finally: Crossmatching without having to resort to TOPCAT's example menu...

• ADQL geometries used to require a first argument that would give the reference frame, as in POINT('ICRS', ra, dec). The hope was that services could then automagically make a statement like CONTAINS(point_in_icrs, circle_in_galactic) work as presumably intended. Few services ever did (DaCHS still tries reasonably hard), and when they did, there were all kinds of opaque oddities. One of the most common sources of confusion is the question what a service is supposed to do with POINT('GALACTIC', ra, dec), assuming it knows that ra and dec are in, say, B1950 FK4. Also, is there any expectation that services attempt to do anything beyond a simple rotation (FK4, for instance, rotates noticably against the ICRS, so proper motions would need to get fixed, too)? In all, the frame as a first argument was ill thought-out, and it's been deprecated. Simply don't put in the string-typed first argument any more. POINT(long, lat) does it. True: This, more than ever, calls for an ADQL astrometry library so you can easily convert, at least, between Galactic and ICRS (probably a few more would be useful, too). More on this in some future post.

• Services should have CAST now. Sometimes you want to turn a number into a string or a string into a timestamp. In such cases, you can write CAST('1991-02-01', TIMESTAMP) now. The details are not quite, excuse me, cast in stone yet, so if you have a use case for this kind of thing, speak up now. The current draft also calls for a TIMESTAMP(tx) function – but since that's really not different from CAST(tx, TIMESTAMP), I'm trying to dissuade people from adding it.

• Services should have an IN_UNIT function now. That's a nifty thing in particular when you're re-using queries on different services. Just write, say, IN_UNIT(pmra, 'deg/yr') and never worry again if it's arcsec/yr, mas/yr, rad/cy, or whatever. The second argument, by the way, is written according to the Units in the Virtual Observatory standard. It's an optional feature according to the current standard, so perhaps it's too early to party, but I've found this extremely useful, and so I hope we'll see widespread adoption.

• Services should now have set operations. These are UNION, EXCEPT, and INTERSECT and are useful when you have two queries that result in the same table schema (because they won't work otherwise). Say you have two complex ways to filter rows from the table source, but you want to process both sorts of results further on – you can say then say something like:

       SELECT <whatever complex> FROM
           (SELECT a,b,c FROM source
             WHERE <crazy stuff>
             GROUP BY a, b, c) as left
         UNION
           (SELECT a,b,c FROM source
             WHERE <other crazy stuff>
             GROUP BY a, b, c) as right
       WHERE <more complex stuff over a, b, and c>
  
  – and similarly, EXCEPT lets you “punch a hole” in a result table.
  Another interesting use case would be to query many tables on a
  service like VizieR in one go; that still works if you make sure the
  tables defined by the sub-queries have the same columns. Given that a
  lot of cross-table operations actually boil down to JOINs and WHERE
  clauses, the set operations are used less that one would expect. But
  if you need them, there's no real alternative (short of downloading
  far too much and performing the operation locally, which of course
  defeats the purpose of TAP).
  

• Common table expressions (“WITH”). DaCHS doesn't do these yet, and it will only pick them up if someone else implements them first. In the way ADQL 2.1 has them (“nonrecursive”), CTEs are little more than syntactic sugar, and I'm not quite sure if the additional implementation complexity is worth it. If you're curious, check CTEs in the postgres manual. If that makes you drool for WITH in ADQL, let me know. It'll not be too hard to sway me to put them in.

• Bitwise Operations. That's when integers are treated as bit patterns. If this sounds like nerd stuff to you, well, it happens quite a bit in actual catalogs. See, for instance, Note 3 for the PPMXL. You'd need the flags column described there if you wanted to exclude PPMXL objects that replaced multiple USNO-B1.0 objects (bit 3), you will right now have to write something like MOD(flags,16)>7. That's a bit of magic that everyone will have to think about for a while. With bitwise operations, you'll just write BITWISE_AND(flags,8)=8, which will look familiar to everyone who has used the pattern before (in particular, it's clear we're talking about bit 3). There still is discussion whether bitwise operations are common enough to warrant special syntax – the draft currently says the above should be written as flags&8=8 – or whether the functions DaCHS has at the moment (they're called BITWISE_AND, BITWISE_OR, BITWISE_XOR, and BITWISE_NOT) are good enough.

• Offset. If you've ever done anything with ADQL, you'll know that SELECT TOP 10 * FROM hipparcos.main ORDER BY parallax DESC will give you the 10 objects with the larges parallaxes. But what if you want the next but 10 closest stars? Well, OFFSET to the rescue:

  SELECT TOP 10 *
  FROM hipparcos.main
  ORDER BY parallax DESC
  OFFSET 10
  

  There is another, more sinister, application for OFFSET, which happens to be the actual reason I've put it into DaCHS' ADQL ages ago: Written as OFFSET 0 several databases use it to denote a barries for the query planner. This is explained to some degree in the class DaCHS TAP example Crossmatch for a Guide Star – which still mentions the first hack I had built into DaCHS to let query authors rein in overzealous query planners.

• LOWER and ILIKE. ADQL has been extremely weak on the side of text processing, so weak indeed that it wasn't nearly enough to cover the use cases for the registry when it moved to RegTAP. ADQL 2.1 adds two basic features – LOWER, a function that lets people query in a case-insensitive fashion, and ILIKE, an operator that is like LIKE, but again ignores case. While both features are obviously great as soon as people dump any kind of text (think object names) into their databases, I'm not terribly happy with ILIKE, as it does the same as RegTAP's ivoa_nocasematch user defined function, and it's always bad when a two standards forsee two different mechanisms for the same thing.

• Geometry-typed arguments. CIRCLE and POLYGON now accept POINTs in alternative constructor functions. That is, you can now say CIRCLE(POINT(ra, dec), radius) in addition to the traditional CIRCLE(ra, dec, radius). In itself, that's probably not terribly exciting, but when you have actual POINTs in your database, it's much more compact to write, say:

  SELECT *
  FROM zcosmos.data
  WHERE 0=CONTAINS(
    ssa_targetpos,
    CIRCLE(ssa_location, ssa_aperture))
  

  (which would return rows for those spectra for which the declared aperture does not contain the declared target). Before, you'd had to write some fairly ugly expression involving COORD1 and whatnot in order to achieve the same effect.

• Boolean expressions. That's another one that's still a bit up in the air. First, the rough goal is to allow boolean values in ADQL-accessible tables, which so far have been a hack at best. In the future, you should be able to say WHERE is_broken=True. However, people coming from other languages will find that odd, and indeed, in python I'd cringe on if is_broken==True:. What I'd expect is if is_broken:. Do we want this in ADQL? Currently, it's in the grammar (more or less like this), but this kind of thing makes it still harder to produce useful syntax error messages. Is it worth it, either way? I'm not sure.

That about concludes my quick review of the new features of ADQL 2.1. If you'd like to know more, the current draft is on the IVOA document repository, and if you can deal with version control (you should!), you can follow the bleeding edge in the ADQL document in Volute. Discussion happens on the DAL mailing list.

Update (2018-04-13): Well, as to the CTEs, I couldn't resist after all, and they're in with DaCHS 1.1.3. And I have to say a love them -- they weren't hard to put in, and once they're there they make so many queries a good deal more readable than before. I've even put it a server-defined example for CTEs on the Heidelberg TAP service showcasing a particularly compelling use case.