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.
Accepted as errata -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that this item should be included in the errata note for the current,
std:ADQL-20081030, version of the standard.
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.
Accepted for next version -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that this item should be discussed further, with a view to including it in the
next (minor) version of the std:ADQL standard.
The term TIMESTAMP has additional meanings above and beyond the simple meaning of a date and time,
some of which impose additional constraints on the range of values that can be represented.
With this in mind we would like to propose replacing the terms
TIMESTAMP and ADQL:TIMESTAMP
in the preceeding section with DATETIME
and ADQL:DATETIME.
Rejected -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed to keep the existing term, TIMESTAMP, to maintain compatibility
with the original term defined in std:SQL1992.
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.
Requires further discussion -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the proposal needs more work done on it before it could be included in the
std:ADQL standard.
We would like to propose adding a section of text to both the
std:TAP and std:ADQL specifications that
clarifies the optional/required status of the geometric functions, and
explains how tr:languageFeatures elements from the
std:TAPREGEXT schema extension can be used to describe
which of the geometric functions are supported by a particular TAP
service.
In the current release documents
- Section 1.2.1 of std:TAP-20100327 states that
"Support for ADQL queries is mandatory".
- Section 2.4 of std:ADQL-20081030
describes the geometric functions, and section 2.5 describes support for user defined functions.
However, the current
std:TAP-20100327 and
std:ADQL-20081030 specifications do not describe how
tr:languageFeatures elements from the
std:TAPREGEXT-20120827 schema extension may be used to
describe which of the geometric functions are supported by a service.
The description of the features-adqlgeo feature in the
std:TAPREGEXT-20120827 schema extension implies that some
of the geometric functions may be optional.
However, the current text refers the user back to the std:TAP-20100327
specification for details of which combinations are permitted.
"support for these functions is in general optional for ADQL implementations,
though TAP imposes some constraints on what combinations of support are permitted"
The proposed changes would not alter the technical details of any of
the specifications. The aim is just to add some additional explanations,
references and examples.
Accepted for next version -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that this item should be discussed further, with a view to including it in the
next (minor) version of the std:ADQL standard.
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.
Requires further discussion -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the proposal needs more work done on it before it could be included in the
std:ADQL standard.
- It was agreed that this would be a useful feature for end users
- It was noted that adding this feature could be difficult to implement
- It was noted that part of the rationale for the IVOA services was to implement
difficult things on the server side, making things easier for the end user
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.
Accepted for next version -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the proposed text should be included in the next (minor) version of the
std:ADQL standard.
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.
Accepted as errata -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that there should be no restriction on the return types of User Defined Functions.
It was agreed that this should be included in the errata note for the current,
std:ADQL-20081030, version of the standard.
Futher discussion -
It was also noted that the SimDAl working group would like to be able to define
table value functions in std:ADQL.
It was agreed to continue the discussion to find a way of adding support for table value
functions in a future version of the std:ADQL standard.
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.
Accepted for next version -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the following functions should be included as an optional feature in the
next (minor) version of the std:ADQL standard.
It was agreed that the following operator should be included as an optional feature in the
next (minor) version of the
std:ADQL standard.
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.
Accepted for next version -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the following operators should be included in the next (minor) version of
the std:ADQL standard.
It was agreed that the text describing the set operators in the
std:ADQL
standard should include the following caveats.
- The set operands MUST produce the same number of columns
- The corresponding columns in the operands MUST have the same data types
- The corresponding columns in the operands SHOULD have the same metadata
- The metadata for the results SHOULD be generated from the left-hand operand
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 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".
Requires further discussion -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that although making these changes would be a good thing, more work needs to be
done on identifying and solving potential compatibility issues before the changes can be
included in the std:ADQL standard.
- It was agreed that BOOLEAN data type would be a useful feature to add
- It was agreed that changing the return type of CONTAINS() to be a BOOLEAN would make
it easier to use
- It was agreed that making these changes could cause compatibility issues which could
not be addressed in a (minor) increment of the std:ADQL standard
- It was agreed that both of these changes should be considered for a future (major)
increment of the std:ADQL standard
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 a 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.
Requires further discussion -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the proposal needs more work done on it before it could included in the
std:ADQL standard.
- It was agreed that scaling conversions would not be difficult to implement
- It was agreed that conversion between wavelength and frequency would be difficult to implement consistently
- It was agreed that unit converstions would be most useful in a SELECT list
- It was agreed that unit converstions would be most difficult to implement in a WHERE clause
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.
Requires further discussion -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the proposal needs more work done on it before it could be considered ready
to be included in the std:ADQL standard.
ADQL currently supports modulo as the MOD(x,y) function.
Many of our science users are more familiar using the x % y
operator syntax.
We would therefore like to propose adding the x % y operator
syntax to ADQL.
Many of the poular RDBMS platforms support both the MOD(x,y)
function and the x % y operator syntax.
However, we are aware that some platforms only support the MOD(x,y)
function syntax and not the x % y operator.
Adding the
x % y operator syntax to ADQL
would mean that some platforms would need translate the ADQL
x % y
operator syntax into the
MOD(x,y) function syntax.
Rejected -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the benefits of adding the operator, x % y, syntax were
outweighed by cost of compatibility issues caused by adding a new operator to the grammar.
There are a number of fields in astronomy catalogs that contain combinations
of bit flags. For example, the
Quality Error Bit Flags
in the OmegaCAM Science Archive
encodes 32 bits of quality error information for each source detection
in a single integer column.
In order to use these as filters in a WHERE clause we need to be able
to perform bitwise operations on them.
-9.99995e+8
AND
jmksExt > -9.99995e+8
AND (
(jppErrBits | ksppErrBits) < 0x10000
OR
(jppErrBits | ksppErrBits) & 0x00800000 != 0
)
]]>
We would therefore like to propose adding support for the four main bitwise
operators, AND, OR, XOR and NOT to the ADQL language.
We would also like to propose adding support for hexadecimal literals.
()+
HEX_DIGIT ["0"-"9","a"-"f","A"-"F"]
]]>
Many of the popular RDBMS platforms provide the full set of bitwise operators.
However, some platforms only provide a limited set of bitwise operators.
Given that support for the bitwise operators is not universal, it may be
necessary to define the bitwise operators as ADQL functions in addition to
the bitwise operators.
We can also define a feature in the std:TAPREGEXT
specification, features-bitwise, to describe which of the
bitwise functions a service implements, similar to the
features-adqlgeo feature defined for the geometric
functions.
The meaning of the features-bitwise feature would cover both
the function syntax and the corresponding operator syntax.
So a registration entry that declares support for the bitwise AND function
would also imply support for the bitwise AND operator.
Accepted for next version -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that hexadecimal literal values should be included in the next (minor) version
of the std:ADQL standard.
It was agreed that the following functions should be included as an optional feature in the
next (minor) version of the std:ADQL standard.
BIT_AND(x, y)BIT_OR(x, y)BIT_XOR(x, y)BIT_NOT(x)
It was agreed that the benefits of adding the operator,
exp op exp, syntax for
each operation were outweighed by cost of compatibility issues caused by adding new operators
to the grammar.
There are a number of cases where our scientists use the CAST operator
to control the type, size and precision of numerical values.
An example of this is a query described in
Hambly et al 2008,MNRAS,384,637–662
which is useful for summarizing the contents of a selection and 'binning' the data.
With this use case in mind we would like to propose adding a limited version of the CAST operator to the ADQL language.
AS )
]]>
Note that the proposed syntax is slightly different to that of a normal ADQL function,
using AS rather than a comma as the separator, and a fixed enumeration of
types for the second parameter which would cover just the standard numeric types.
The propsed CAST(<expression> AS <type>) syntax is similar that used in many of the standard RDBMS.
The proposed change does not aim to replicate the full functionality
and range of types provided by the different RDBMS implementations
of CAST. In particular we are not proposing to support CHAR,
VARCHAR or DATETIME conversions.
The aim is just to cover the primary use case of provding a mechanism
for converting between the standard numeric types.
| |
SHORT |
INTEGER |
LONG |
FLOAT |
DOUBLE |
| SHORT |
- |
Y |
Y |
Y |
Y |
| INTEGER |
Y |
- |
Y |
Y |
Y |
| LONG |
Y |
Y |
- |
Y |
Y |
| FLOAT |
Y |
Y |
Y |
- |
Y |
| DOUBLE |
Y |
Y |
Y |
Y |
- |
Accepted for next version -
This item was discussed by members of the working group at the May 2014 IVOA Interop meeting.
It was agreed that the CAST operator should be including as a required operator in the next
(minor) version of the std:ADQL standard.
It was agreed that the set of type conversions should be discussed further, with a view to
finalising the set of conversions supported in the next (minor) version of the
std:ADQL standard.
