The Separator Nonterminal

The grammar given in appendix A of std:ADQL gives a nonterminal separator, expanding to either a comment or whitespace. This nonterminal, however, is only referenced within the rule for character_string_literal. It is uncontentious that the intent is to allow comments and whitespace wherever SQL1992 allows them. With the nonterminal in the grammar, however, the ADQL standard says differently, and there should be a clarification.

One option for such a clarification is to amend section 2.1 of std:ADQL with a subsection 2.1.4, "Tokens and literals", containing text like the following (taken essentially from std:SQL1992.

Any token may be followed by a separator. A nondelimiter token shall be followed by a delimiter token or a separator.

Since the full rules for the separator are somewhat more complex in std:ADQL, an attractive alternative could be to omit the separator nonterminal from the grammar and to just note:

Whitespace and comments can occur wherever they can occur in std:SQL1992.

Type System

The ADQL specification does not explicitly talk about types. Some intentions regarding types can be taken from the grammar (e.g., the lack of a boolean type), but it is clear that for a predictable behaviour across individual ADQL implementations, ADQL should talk about types. The TAP specification has already covered most of the ground here, with a table on PDF page 19 in version 1.0. The following proposal mainly builds on this.

To introduce a notion of types into section 2 of the ADQL recommendation, it should be amended with a subsection 2.6, "ADQL Type System", as follows:

ADQL defines no data definition language (DDL). It is assumed that table definition and data ingestion are performed in the backend database's native language and type system.

However, column metadata needs to give column types in order to allow the construction of queries that are both syntactically and semantically correct. Examples of such metadata includes VODataService's vs:TAPType std:VODS11 or TAP's TAP_SCHEMA. Services SHOULD, if at all possible, try express their column metadata in these terms even if the underlying database employs different types. Services SHOULD also use the following mapping when interfacing to user data, either by serializing result sets into VOTables or by ingesting user-provided VOTables into ADQL-visible tables. Where non-ADQL types are employed in the backend, implementors SHOULD make sure that all operations that are possible with the recommended ADQL type are also possible with the type used in the backend engine. For instance, the ADQL string concatenation operator || should be applicable to all columns resulting from VOTable char-typed columns.

VOTable			ADQL
datatype	arraysize	xtype	type
boolean	1		implemenation defined
short	1		SMALLINT
int	1		INTEGER
long	1		BIGINT
float	1		REAL
double	1		DOUBLE
(numeric)	> 1		implementation defined
char	1		CHAR(1)
char	n*		VARCHAR(n)
char	n		CHAR(n)
unsignedByte	n*		VARBINARY(n)
unsignedByte	n		BINARY(n)
unsignedByte	n, *, n*	adql:BLOB	BLOB
char	n, *, n*	adql:CLOB	CLOB
char	n, *, n*	adql:TIMESTAMP	TIMESTAMP
char	n, *, n*	adql:POINT	POINT
char	n, *, n*	adql:REGION	REGION

"Implementation defined" in the above table means that an implementation is free to reject attempts to (de-) serialize values in these types. They are to be considered unsupported by ADQL, and the language provides no means to manipulate "native" representations of them.

References to REGION-typed columns must be valid wherever the ADQL region nonterminal is allowed. References to POINT-typed columns must be valid wherever the ADQL point nonterminal is allowed.

Empty Coordinate Systems

The legal values and the semantics of the first arguments to the geometry constructors (POINT, BOX, CIRCLE, POLYGON) have been left largely open by the ADQL standard. The TAP standard clarified those somewhat to the effect that the prescriptions became implementable. On the other hand, the only thing clients can reasonably expect according to TAP (on a recommendation base) from a server is one of four reference frames. Compared to the implementation effort and the potential for user confusion, the additional expressiveness gained by keeping the first argument seems minute. Even allowing more expressive system strings will not help the feature much, since non-trivial transformations (e.g., between reference positions) will need more data than merely the celestial coordinates available to the geometry constructors.

We therefore propose to deprecate the first argument in a point release of ADQL. In the next major release, the first argument as defined in ADQL2 should be declared as ignored. The standard should require constructors both with and without the current first argument, though, in order to ensure backward compatiblity for ADQL2 queries.

To implement the first step, we propose replacing the second paragraph on PDF page 10 of std:ADQL (starting with "For all these functions...") with:

For historical reasons, the geometry constructors (BOX, CIRCLE, POINT, POLYGON) require a string-valued first argument. It was intended to carry information on a reference system or other coordinate system metadata. In this version, we recommend ignoring this first argument, and clients are advised to pass an empty string here. Future versions of this specification will make this first, string-valued parameter optional for the listed functions.

In consequence, the COORDSYS function would be taken out of the enumeration on PDF page 9, and its description on PDF page 11 would be removed, too. All examples would use an empty string rather than "ICRS GEOCENTER" -- which is not contained in the TAP clarification anyway -- as in the current text.

A library of standard generalized user defined functions (see section af-genudf) could provide for simple conversion between reference frames as well as more demanding transformations, e.g., between epochs or reference positions. This, however, depends on allowing geometry-valued user defined functions and is outside of the scope of a clarification. See also section af-genudf.

Simple Crossmatch Function

Since a simple positional crossmatch is such a common operation, we should define a function CROSSMATCH(ra1, dec1, ra2, dec2, radius) -> INTEGER returning 1 if (ra1, dec1) and (ra2, dec2) are within radius degrees of each other. This allows more compact expressions than the conventional CONTAINS(POINT, CIRCLE) construct, and ADQL to SQL translators can more easily exploit special constructs for fast crossmatching that may be built into the backend databases.

No Type-based Decay of INTERSECTS

Section 2.4.11 of std:ADQL stipulates that a call to INTERSECTS should decay to a CONTAINS when one argument is a POINT. This rule is a major implementation liability for simple translators, since it is the only place in the ADQL specification that actually requires a type calculus. For a feature that does not actually add functionality, this seems a high price to pay.

We therefore recommend to strike the text from "Note that if one of the arguments" through "equivalent to INTERSECTS(b,a)" and add at the end for 2.4.11:

The arguments to INTERSECTS SHOULD be geometric expressions evaluating to either BOX, CIRCLE, POLYGON, or REGION. Previous versions of this specification allow POINTs as well and require servers to interpret the expression as a CONTAINS with the POINT moved into the first position. Servers SHOULD still implement that behaviour, but clients SHOULD NOT expect it. It will be dropped in the next major version of this specification.

Generalized User Defined Functions

Currently, user defined functions may only return numbers or strings (in terms of the grammar, only numeric_value_function and string_value_function can expand to user_defined_function). Many interesting functions (e.g., coordinate transforms, applying proper motions) are extremely inconvenient to define with such a restriction. Therefore, we propose to add | <user_defined_function> to the right hand side of the geometry_value_function rule.

With this, we could define some standard functions for manipulating geometries; these should be defined in the standard, but they could remain optional. Clients can determine their availability using std:TAPREGEXT.

A future version of this note will propose a library of such functions, including proper motion, precession, and system transformation.

Case-Insensitive String Comparisons

ADQL currently has no facility reliably allowing case-insensitive string comparisons. This is particularly regrettable since UCDs and at least the majority of the defined utypes are to be compared case-insensitively.

Thus, we propose the addition of a string function LOWER and the case-insensitive variant of LIKE, ILIKE. Since case folding is a nontrivial operation in a multi-encoding world, ADQL would only require standard behaviour for the ASCII characters (which would suffice for UCDs and utypes) and only recommend following algorithm R2 in section 3.13, "Default Case Algorithms" of std:UNICODE outside of ASCII.

The grammar changes are trivial.

Set Operators

ADQL 2.0 does not support any of the SQL UNION, EXCEPT and INTERSECT operators. Since at least set union and intersection are basic operations of relational algebra and combining data from several tables is an operation of significant practical use, this is a serious deficit. Also, there is probably no backend SQL system that does not support these operations.

Thus, to add minimal support of set operations to ADQL, ADQL systems will mainly need to update their grammars. The following rules, adapted from std:SQL1992, will suffice (the query_expression rule replaces the one given in the current grammar, all others are new rules):

 ::=
                
              | 

          ::=
                
              |  UNION  [ ALL ] 
              |  EXCEPT [ ALL ] 

          ::=
                
              | 

          ::=
                
              |  INTERSECT [ ALL ]

          ::=
                
              | 

          ::=
                
              |   

]]>

This leaves out the CORRESPONDING specifications of SQL92, and it still does not include VALUES and explicit table specifications (which would enter through non_join_query_primary) in ADQL. None of these seem indispensible, although one could probably make a case for VALUES .

Adding a Boolean Type

Having a boolean type in ADQL could make some expressions nicer (e.g., it could eliminate the comparison against 1 for the geometry predicate functions). However, adding boolean functions and allowing references to boolean columns complicates catching syntax errors significantly, since expressions like WHERE colref would then parse and only would only raise an error when it turns out that colref does not refer to a boolean column. Simple ADQL translators may not be able to verify this.

We therefore propose to add a boolean type to the ADQL type system (see section ac-typesystem) without any grammatical support for it. However, the standard prose should be amended to contain:

If the backend database contains columns of type boolean, a comparison of those against the literal strings True and False must be true and false when the column is true and false, respectively. The comparison to other literals is undefined by this specification. Clients should note that the strings have to be entered exactly as given here, without changing case, adding whitespace, or any other modification.

If this change is adopted, the type system table given in section ac-typesystem should be updated; luckily, the VODataService specification underlying VOSI already allows BOOLEAN as a TAPType. In the table row for VOTable boolean, "implementation defined" should be replaced with "BOOLEAN".

Casting to Unit

ADQL translators can typically introspect the tables they operate on, and thus can typically infer the (physical) unit of a column. Manually converting units (as in col_in_deg*3600 is error-prone, and expressions like that make it almost impossible to infer the unit of the result.

This problem is addressed by the introduction of a function IN_UNIT(expr, <character_string_literal>); the second argument has to be a literal in order to make sure that an ADQL translator has access to its value; this value must be in the format defined by std:VOUNIT. The intended functionality is that the translator replaces the function call with an new expression that is expr given in the unit defined by the second argument if the translator can figure out expr's unit, and it knows how to convert values in one unit into another. In every other case, the query must be rejected as erroneous.

Column References with UCD Patterns

In the same spirit of a function that really is a macro evaluated by an ADQL translator, we suggest a new function UCDCOL(<character_string_literal>). The character_string_literal in this case specifies a posix shell pattern (i.e., users write * for a sequence of 0 or more arbitrary chars, ? for exactly one arbitrary char, [] for a character range, and the backslash is the escape character) for a UCD. The translator replaces the entire function call with the first match of a column matching this pattern. If no such column exists, the query must be rejected as erroneous.

Updating Parameters

Section 2.1.11 of std:UWS states that a "particular implementation of UWS may choose to allow the parameters to be updated after the initial job creation step, before the Phase is set to the executing state" and successively allows POSTing to jobs/job-id, jobs/job-id/parameters and PUTting to jobs/job-id/parameters/parameter-name.

It turned out that the concrete semantics of this cavalier approach quickly become difficult. We therefore propose to amend the language on changing parameters post-creation by:

In most cases, the values of the parameters are all established during the initial POST that creates the job. However, a particular implementation of UWS may choose to allow the parameters to be updated after the initial job creation step, before the Phase is set to the executing state. It should, however, not offer the ability to create new parameters nor delete existing parameters. The next major version of this specification will remove the ability to set an individual parameter.

From the client perspective, there is only one guaranteed way to set a parameter that all UWS services must implement: In the initial POST that creates the job.

Behaviour for Failed Job Creation

In Section 2.2.3.1 of std:UWS a UWS is required to return a "code 303 'See other'" "unless the service rejects the request". It is not specified what should happen when the service rejects the request.

We propose to add, at an appropriate position, the following text:

If the execution of an UWS request fails, the service has to generate an appropriate error message with codes in the 400 (client error) or 500 (server error) ranges according to std:HTTP. If the erroneous request is recoverable (e.g., a request for a transition to an impossible state), the job does not go into the ERROR state because of a failed request.

The payload of such an error message SHOULD be a user-presentable error message plain text, which SHOULD not be re-flowed by clients. Clients MUST accept other documents coming back as payloads of such request responses. As such events can be assumed major server failures, it is recommended to abandon a job that had a non-text/plain response to any UWS request.

Format of Quote

Section 2.2.1 of std:UWS states that the jobs/job-id/quote resource represents quote as a number of seconds, while the schema represents quote as an xs:dateTime.

This is an unnecessary inconsistency. If no schema change is required by other changes in a UWS revision, we propose to solve it by requiring the representation in the resource to be in std:DALI YYYY-mm-ddThh:mm:ss form. While doing this, we should also clarify the format for the value of desctruction, that currently just defers to std:iso8601; this should now refer to std:DALI as ISO 8601 allows many variants that are clearly not intended here.

If the UWS schema needs changing for other reasons, we suggest to unify the representations to the number of seconds on grounds that it is the more logical specification for the estimated duration of a job.

Names of Uploaded Tables

Section 2.5 of std:TAP requires the name of the uploaded tables to be a "legal ADQL table name with no catalog or schema (e.g. an unqualified table name)". This language probably allows delimited identifiers, as the ADQL table_name can expand to one. This, however, was clearly not the intention of text, as the use of delimited identifiers is not (fully) supported by the syntax of the UPLOAD parameter. To resolve these difficulties, we propose to replace the parenthesis starting with "e.g." with:

i.e., a string following the regular_identifier production of std:ADQL.

This could, in theory, invalidate existing clients that might want to use delimited identifiers in uploads. Due to the difficulties with the UPLOAD parameter syntax, however, that would not really be supported in version 1, either. Thus, we claim that this language can enter in a minor version.

Multiple UPLOAD Posts

Since UWS allows posting parameters after job creation Section 2.5.1 of std:TAP needs to specify what happens when the UPLOAD parameter is posted into a job that already has one or more uploads. We propose to add at the end of the section:

UPLOADs are accumulating, i.e., each UPLOAD parameter given will create one or more tables in TAP_UPLOAD. When the table names from two or more upload items agree after case folding, the service behaviour is unspecified. Clients thus cannot reliably overwrite uploaded tables; to correct errors, they have to tear down the existing job and create a new one.

Database Column Types

Section 2.5 of std:TAP gives "database column types" for all kinds of VOTable objects. Given the lack of an ADQL type system, this must be clearly be taken with a grain of salt; the types given in this column at least cannot be taken as conformance criteria. We propose to add the following language before section 2.5.1:

Note that the last column of Table (x) is not normative. Implementations SHOULD try to make sure that the actual types chosen are at least signature-compatible with the recommended types (i.e., integers should remain integers, floating-point values floating-point values, etc.), such that clients can reliably write queries against uploaded tables.

For columns with xtype adql:REGION, this is particularly critical, since databases typically use different types to represent various STC-S objects. Clients are advised to assume that such columns will be approximated with polygons in the actual database table.

The size Column in TAP_SCHEMA

The table TAP_SCHEMA.columns as specified in section 2.6.3 of std:TAP has a column named size. This is unfortunate since SIZE is an ADQL reserved word, and thus must be quoted in queries.

We therefore propose to append the following language to section 2.6.3:

To use size in a query, it must be put in double quotes since it collides with an ADQL reserved word. Since delimited identifiers are case-sensitive, for the size column both clients and servers MUST always (in particular, in the DDL for TAP_SCHEMA) use lower case exclusively.

In the next major version of TAP, this column will be called arraysize.

Use of VOTable

An examples Endpoint

Feedback from TAP users indicates that providing query examples is considered most helpful, which is probably not surprising since to effectively use a TAP service, a user has to combine knowlege of a fairly complex query language with server-specific metadata like table schemata and local extensions as well as domain knowledge. A head start as provided by examples doing something related to what the users actually want is therefore most welcome.

TAP services are usually accessed through specialized clients. Therefore, a simple link "for examples see here" will in general not work for them. In principle, one could simply communicate an example URL to a client and let the user browse it. Allowing a certain amount of structuring within the document at this URL, however, lets clients do some useful in-application presentation of the examples.

std:DALI defines a simple system to communicate examples to humans and machine clients alike, based on RDFa. This section specifies how the generic DALI specification is to be applied to TAP.

     
       http://localhost:8080/tap/examples
     
   
]]>

A plan Endpoint

CDS have a debug endpoint with additional information; join their concepts with this.

As already noted in std:TAP, it is notoriously difficult to predict the runtime of SQL queries. For nontrivial queries, even experts may have a hard time figuring out performance bottlenecks. Therefore, most database systems provide some mechanism to obtain a query plan, that is, to inspect what elementary operations will be performed for a given query.

Since TAP queries are typically formulated by persons not intimately familiar with the database queried, the need for a mechanism allowing insights into the database engine's reasoning is even more pronounced. On the other hand, different database systems give their plans in completely different formats and even schemata. In addition, as the Postgres Documentation says: "Plan-reading is an art that deserves an extensive tutorial" (doc:Postgres92, Sect. 14.1).

Thus, specifying a fixed format for query plans that would be both expressive enough and sufficiently generic to be easily adaptible to various backend database engines is probably impossible. To still allow users to inspect actual query plans, we propose the following language be added at the end of section 2.2.2 of std:TAP:

In addition to the UWS resources, a TAP server SHOULD support a child plan for each job resource. If retrieving this resource is successful (i.e., results in a 200 HTTP response after possible redirects and authentication), it MUST be a preformatted document with MIME type text/plain. Within it, the actual query as executed by the database engine MUST come first.

After at least one blank line, a rendering of the query plan follows. Note that the query as excecuted may contain blank lines, which means that machine clients cannot use the blank line to separate query and plan. In general, clients SHOULD display the plan without any reformatting in a fixed-width font.

Since it is hard to define a generic and sufficiently expressive format for query plans and the authors want to avoid excessive implemenation cost for this feature, this specification does not give a format for the query plan. Implementors are advised to keep as much of the "native" plan format of their database engine as possible.

After the plan, the service is free to give additional debugging information. The indended audience for this information are again humans, so even in cases where proprietary clients actually parse out information from that area, such information should still be decipherable by knowledgeable humans.

If the creation of the query plan fails, the service MUST reply with a 400 (if the failure appears to be due to syntax errors in the query, the query plan not being available in this UWS phase, or similar problems) or 500 HTTP status code. Errors in plan generation do not change the phase of the job. Clients may thus use the plan endpoint to check the syntax of a query on services supporting it.

Services that cannot or choose not to support the retrieval of query plans MUST respond with a 404 HTTP code to requests for plan children of job resources.

Except for 404 responses, all documents delivered from the plan endpoint MUST have the MIME type text/plain. They should contain ASCII exclusively, but clients SHOULD assume UTF-8 encoding and if no character set is declared by HTTP means.

Scaleable tables Endpoint

For archives serving hundreds or thousands of tables, the tables endpoint on TAP services as defined by std:VOSI will have to return documents of several dozen megabytes. This results in nontrivial transfer times for data that in all likelihood is uninteresting to the user that typically will only write queries against fairly few of those tables.

To mitigate this problem, we propose to define that vs:Table typed elements in responses from VOSI table endpoints that have no column children are to be regarded as stubs by clients. A client SHOULD give the user the possibility to request "full" information on such a stubbed table. This full information is available from a child resource of tables named like the table, in exactly the captialization as given in the name child of the table stub; it would come as the full table element.

As an example, a service might return the following from its tables endpoint:

  
    ppmxl
    ppmxl.main
 
]]>

A client could then retrieve the url .../tables/ppmxl.main and would receive something like this:

  ppmxl.main
   PPMXL is a catalog of positions, proper motions...
  
  
    ipix
    Identifier (Q3C ipix of the USNO-B 1.0 object)
	...
]]>

More formally, we propose to replace the last paragraph of section 3.4, "Table metadata", of std:VOSI, Version 1.0, with the following text:

In the REST binding, the registred URL retrieves an XML document containing this element. However, services exposing a large number of tables may only write table stubs into the document retrieved from this web resource. Table stubs are table elements containing no column children. While the XSD requires a name child to be present, the services may or may not include any of the remaining table metadata.

Still in the REST binding, the server that has produced such a columnless table element should provide a child of the tables resource named like the content of the tables name child element, with any leading or trailing whitespace removed. If a request for this resource is successful, the document received must contain a XML document containing a single element of the type {http://www.ivoa.net/xml/VODataService/v1.1}Table with all metadata available for the table.