IVOA Documentation View
Astronomical Data Query Language¶
Official bibliographic entry for published version [ADQL2.1].
- Status:
ADQL 2.1 PR 2023-11-07
Acknowledgements¶
The authors would like to acknowledge the contributors to this standard by the members of the IVOA Data Access Layer (DAL) and Virtual Observatory Query Language (VOQL) working groups.
1 Introduction¶
The Astronomical Data Query Language (ADQL) is the language used by the IVOA to represent astronomy queries posted to VO services. The IVOA has developed several standardized protocols to access astronomical data, e.g., Simple Image Access (SIA) protocol and Simple Spectral Access (SSA) protocol for image and spectral data respectively. These protocols might be satisfied using a single table query. However, different VO services have different needs in terms of query complexity and ADQL arises in this context.
ADQL is based on the Structured Query Language (SQL), especially on SQL 92 [] [1].
The VO has a number of tabular data sets and many of them are stored in relational databases, making SQL a convenient access means. A subset of the SQL grammar has been extended to support queries that are specific to astronomy.
This specification provides a complete definition of the ADQL through a single Backus Naur Form (BNF) based language definition. It includes both mandatory and optional features. This BNF aims to help implementing ADQL core features as well as the defined optional ones.
1.1 Role within the VO architecture¶
Figure ADQL:fig:archdiag shows the role this document plays within the IVOA architecture .
It is the only mandatory query language within the Table Access Protocol ; it is thus an important element in enabling interoperable queries against remote astronomical databases.
TAP services communicate their metadata, and hence metadata about the specific version of ADQL they implement, in capabilities of the VOResource type defined in TAPRegExt [TAPRegExt1.0]. Within these TAPRegExt records, the language defined in this document, ADQL 2.1, has the IVOA identifier
Clients should note that IVOA identifiers need to be compared case-insensitively.
DALI ref defines conventions for many of the types ADQL is talking about, in particular geometry objects, timestamps, and intervals.
1.2 Extended functionality¶
This document defines the minimum set of functions, operators and data types that a service MUST implement in order to register as a service that implements this version of the ADQL specification. Optional features defined in this specification MAY be implemented. However, if a service does provide one of them, it MUST be implemented according to this specification.
Of course, service implementations are free to extend this functionality by providing additional functions, operators or data types beyond those defined in this specification, as long as the extended functionality does not conflict with anything defined in this specification.
2 Language structure¶
This section describes the ADQL language structure. We will define in subsequent sections the syntax for the special characters, reserved and non- reserved words, identifiers and literals and then, finally, the syntax for the query expression.
The formal notation for syntax of computing languages is often expressed in BNF. This syntax is used by popular tools for producing parsers. Appendix A to this document provides the full BNF grammar for ADQL. The following conventions are used through this document:
Optional items are enclosed in meta symbols
[and]A group of items is enclosed in meta symbols
{and}Repetitive item (zero or more times) are followed by
...Terminal symbols are enclosed by
<and>Terminals of meta-symbol characters (
:=[]{}<>.") are surrounded by quotes (“) to distinguish them from meta-symbolsCase-insensitive unless otherwise stated.
2.1 Characters, keywords, identifiers and literals¶
2.1.1 Characters¶
The language allows simple Latin letters (lower and upper case, i.e. {aA-zZ}), digits ({0-9}) and the following special characters:
space
single quote
'double quote
"percent
%left and right parenthesis
()asterisk
*plus sign
+minus sign
-comma
,period
.solidus
/colon
:semicolon
;less than operator
<equals operator
=greater than operator
>underscore
_ampersand
&question mark
?circumflex
^tilde
~vertical bar
|
2.1.2 Keywords and identifiers¶
Besides the character set, the language provides a list of reserved keywords plus the syntax description for regular identifiers.
A reserved keyword has a special meaning in ADQL and cannot be used as an identifier unless it is isolated using the ADQL escape syntax defined in .
The ADQL specification extends the list of SQL92 reserved keywords to accommodate those useful for astronomical purposes and/or present in a subset of vendor specific languages only (e.g. TOP).
Although the following lists are all in UPPERCASE, the matching of keywords is case-insensitive.
2.1.3 SQL reserved keywords¶
2.1.4 ADQL reserved keywords¶
2.1.5 Identifiers¶
Identifiers MUST begin with a letter {aA-zZ}, subsequent characters MAY be letters, underscores or digits {0-9} as follows:
<Latin_letter>... [{ <digit> | <Latin_letter> | <underscore> }...]
2.1.6 Delimited identifiers¶
To address reserved keyword and special character conflicts the ADQL language provides a way to escape a non-compliant identifier by using the double quote character " as a delimiter.
For example, to use the reserved word SIZE as a column name, in upper or lower case, it must be isolated using double quotes.
size– Invalid column name"size"– Valid column name
2.1.7 Case sensitivity¶
In addition to isolating keyword conflicts and special characters, the double quote escape syntax also denotes case sensitivity.
Without double quotes, the following identifiers are all equivalent:
alpha == Alpha == ALPHA
When escaped using double quotes, the same set of identifiers are not equivalent:
"alpha" != "Alpha" != "ALPHA"
Data providers should avoid defining column names using delimited identifiers. Indeed, when identifiers are mixed-case or are not written in the default case (uppercase by SQL92, lowercase in most modern RDBMSes), users must use delimited identifiers. This, in turn, may lead to interoperability problems between services offering the same table, or to failing backwards compatibility when moving between backend RDBMSes.
2.1.8 Literals¶
String literals are expressed as a character expression delimited by single quotes.
<character_string_literal> ::=
<quote> [ <character_representation>... ] <quote>
A single quote inside a string literal must be escaped with a second single quote. For instance:
'Earth''s satellite is the Moon.'
Numeric literals are expressed as an exact decimal value, e.g. 12 or 12.3, or a floating point number with an exponent, e.g. 12.3E4.
<signed_numeric_literal> ::= [<sign>] <unsigned_numeric_literal>
<unsigned_numeric_literal> ::=
<exact_numeric_literal>
| <approximate_numeric_literal>
<exact_numeric_literal> ::=
<unsigned_decimal> [<period> [<unsigned_decimal>]]
| <period><unsigned_decimal>
<approximate_numeric_literal> ::= <mantissa> E <exponent>
<mantissa> ::= <exact_numeric_literal>
<exponent> ::= <signed_decimal>
<signed_decimal> ::= [<sign>] <unsigned_decimal>
<unsigned_decimal> ::= <digit>...
<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
<sign> ::= <plus_sign> | <minus_sign>
2.1.9 Whitespace¶
The rules on where whitespace is allowed and required are as in SQL-92; essentially, any <token> may be followed by a <separator>.
2.2 Query syntax¶
A more detailed definition of the SELECT statement is given by the <query_specification> construct defined in .
A simplified syntax for the SELECT statement follows, showing the main constructs for the query specification:
[ WITH { cte_name AS ( SELECT ... ) }, ... ]
SELECT
[ ALL | DISTINCT ]
[ TOP unsigned_decimal ]
{
*
| { value_expression [ [AS] column_name ] }, ...
}
FROM {
{
table_name [ [AS] identifier ]
| ( SELECT ...) [AS] identifier
| table_reference [NATURAL]
[ INNER | { LEFT | RIGHT | FULL [OUTER] } ]
JOIN table_reference
[ON search_condition | USING ( column_name,...) ]
},
...
}
[ WHERE search_condition ]
[ GROUP BY group_by_term, ... ]
[ HAVING search_condition ]
[ {
{ UNION | INTERSECT | EXCEPT } [ALL]
SELECT ...
}
...
]
[ ORDER BY { order_by_expression } [ ASC | DESC ], ... ]
[ OFFSET unsigned_decimal ]
The SELECT statement defines a query to apply to a set of tables specified in the FROM clause. As a result of this query, a subset of the tables is returned. The order of the rows MAY be arbitrary unless an ORDER BY clause is specified. A TOP clause may be specified to limit the number of rows returned. An OFFSET clause may be specified to skip a number of rows at the start of the results. If both TOP and OFFSET are used together then OFFSET is applied first followed by TOP .
The order of the columns in the query results SHALL be the same as the order specified in the selection list, unless an asterisk is specified.
2.2.1 Subqueries¶
Table subqueries may be used by predicates such as IN and EXISTS in the WHERE clause of a query:
SELECT alpha_source.id
FROM alpha_source
WHERE alpha_sourceid >=5
AND alpha_sourceid IN (
SELECT id
FROM alpha_source
WHERE id < 10
)
Table subqueries may be used for declaring derived tables in the FROM clause of a query:
SELECT alpha_source.id
FROM alpha_source,
(
SELECT alpha_source.id
FROM alpha_source
WHERE id < 10
) AS subsample
WHERE alpha_source.id >=5
AND alpha_source.id = subsample.id
If supported by the implementation, table subqueries may be used for declaring named result sets in the WITH clause of the main query:
WITH subsample AS (
SELECT alpha_source.id
FROM alpha_source
WHERE id < 10
)
SELECT alpha_source.id
FROM alpha_source
INNER JOIN subsample USING(id)
WHERE alpha_source.id >= 5
2.2.2 Joins¶
ADQL supports INNER and OUTER (LEFT, RIGHT and FULL) joins. If no type is specified, the default is INNER. All of these can be NATURAL or not.
2.2.3 Search condition¶
A search condition may be part of other clauses including JOIN, HAVING and WHERE.
A search condition may contain the standard logical operators, AND, OR and NOT.
A search condition may contain the following predicates:
Standard comparison operators:
=,!=,<>,<,>,<=,>=Range comparison:
BETWEENMembership check:
INCase-sensitive string comparison:
LIKENull value checks:
IS NULL,IS NOT NULLNon-empty subquery check:
EXISTS
In addition, some service implementations may also support the optional ILIKE case-insensitive string comparison operator, defined in .
ILIKE
2.3 Mathematical and Trigonometrical Functions¶
ADQL declares a list of reserved keywords which include the mathematical and trigonometrical function names. Their syntax, usage and description are detailed in tables ADQL:table:math.functions and ADQL:table:trig.functions.
3 Type system¶
ADQL defines no data definition language (DDL). It is assumed that table definition and data ingestion are performed in the underlying database’s native language and type system.
However, service 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 the TAP_SCHEMA tables defined in the and the /tables webservice response defined in the .
Services SHOULD, if at all possible, try to express their column metadata in these terms even if the underlying database employs different types. Services SHOULD also use the following mappings when interfacing to user data, either by serializing result sets into VOTables or by ingesting user-provided VOTables into ADQL-visible tables.
3.1 Numeric types¶
3.1.1 Numeric primitives¶
The numeric datatypes, BIT, SMALLINT, INTEGER, BIGINT, REAL map to the corresponding datatypes defined in the .
Where possible ADQL numeric values SHOULD be implemented using database types that correspond to the VOTable serialization types, e.g. SMALLINT should map to a 16 bit integer, INTEGER should map to a 32 bit integer, etc.
3.1.2 INTERVAL¶
The defines INTERVAL as a pair of integer or floating-point numeric values which are serialized as an array of numbers.
None of the ADQL operators apply to INTERVAL values. However, specific implementations MAY provide user defined functions that operate on some INTERVAL values.
The details of how INTERVAL values behave in ADQL are not yet defined.
3.2 Date and time¶
Where possible, date and time values SHOULD be implemented as described in the .
3.2.1 TIMESTAMP¶
The TIMESTAMP datatype maps to the corresponding type defined in the .
TIMESTAMP-s can be created from string literals using the CAST() function (if supported) described in .
The basic comparison operators =, <, >, <=, >=, <> and BETWEEN can all be applied to TIMESTAMP values.
For instance:
obstime > CAST('2015-01-01' AS TIMESTAMP)
obstime BETWEEN CAST('2014-01-01' AS TIMESTAMP) AND CAST('2014-01-02' AS TIMESTAMP)
Within the database, the details of how TIMESTAMP values are implemented are platform dependent. The primary requirement is that the results of the comparison operators on TIMESTAMP values are consistent with respect to chronological time.
3.3 Character types¶
3.3.1 Character primitives¶
The CHAR and VARCHAR datatypes map to the char or unicodeChar type defined in the .
The choice of whether CHAR and VARCHAR map to char or unicodeChar is implementation dependent and may depend on the data content.
3.3.2 CLOB¶
To provide support for string values which are generated by the server, ADQL includes the Character Large OBject (CLOB) datatype, which behaves as an opaque immutable string of characters.
None of the ADQL operators apply to CLOB values. However, specific database implementations MAY provide user defined functions that operate on some CLOB values.
CLOB values are serialized as arrays of characters.
The details of how CLOB values are handled within a database is implementation dependent.
An example use case for CLOB is a URL field that is generated on the fly using one or more fields stored in the database. Although some of the components are stored in the database, the final URL that appears in the results is not stored in the database. Hence it would not be possible to apply ADQL functions or operators to the URL field without special knowledge of the internal database structure. However, a service implementation could provide user defined functions that used knowledge of the internal database structure to perform specific operations on the generated URL field.
3.4 Binary types¶
3.4.1 Binary primitives¶
The BINARY and VARBINARY datatypes map to the unsignedByte type defined in the .
3.4.2 BLOB¶
To support large blocks of binary data such as images, ADQL includes the Binary Large OBject (BLOB) datatype, which behaves as an opaque immutable array of bytes.
None of the ADQL operators apply to BLOB values. However, specific database implementations MAY provide user defined functions that operate on some BLOB values.
BLOB values are serialized as arrays of unsignedByte defined in the .
The details of how BLOB values are handled within a database is implementation dependent.
An example use case for BLOB is for storing thumbnail images in the database alongside the tabular data. ADQL does not provide functions or operations that operate on images. However, a service implementation could provide user defined functions that use implementation specific features to perform operations on the image data.
3.5 Geometric types¶
ADQL provides support for the POINT, CIRCLE and POLYGON spherical geometry types defined in the .
ADQL also provides support for STC-S based geometric regions, as defined in the , using the REGION datatype.
3.5.1 POINT¶
The POINT datatype maps to the corresponding type for spherical coordinates defined in the .
POINT values are serialized as arrays of floating point numbers using the point xtype defined in the .
POINT literals can be expressed using the POINT() constructor defined in .
For example:
POINT(12.3, 45.6)
3.5.2 CIRCLE¶
The CIRCLE datatype maps to the corresponding type for spherical coordinates defined in the .
CIRCLE values are serialized as arrays of floating point numbers using the circle xtype defined in the .
CIRCLE literals can be expressed using the CIRCLE() constructor defined in . For example:
CIRCLE(12.3, 45.6, 0.5)
3.5.3 POLYGON¶
The POLYGON datatype maps to the corresponding type for spherical coordinates defined in the .
POLYGON values are serialized as arrays of floating point numbers using the polygon xtype defined in the .
POLYGON literals can be expressed using the POLYGON() constructor defined in .
For example:
POLYGON(10.0, -10.5, 20.0, 20.5, 30.0, 30.5)
describes a triangle with vertices at (10.0, -10.5), (20.0, 20.5) and (30.0, 30.5) degrees.
3.5.4 REGION¶
REGION is introduced as the type of the result of intersections and unions of other geometries (although ADQL at this point does not support these operations).
We do not specify the serialisation of REGION-typed values in result sets. It is expected that next version of DALI will provide normative guidance on this. However, at least for the s_region column described in the , informal practice is to produce strings conforming to the with an xtype of adql:region.
4 Optional components¶
In addition to the core components, the ADQL language also includes support for optional features and functions.
The following sections define the optional features that are part of the the ADQL grammar, but are not required in order to meet the standard for a basic ADQL service.
It is up to each service implementation to declare which optional or additional features it supports.
If a service does not declare support for an optional feature, then a client SHOULD assume that the service does NOT support that feature, and SHOULD NOT make use of that feature in any ADQL queries that it sends.
4.1 Service capabilities¶
The defines an XML schema that a service SHOULD use to declare which optional features it supports.
In general, each group of language features is identified by a type URI, and each individual feature within the group is identified by the feature name.
In the context of ADQL, the type URI is expected to be an IVOID.
Note that IVOIDs are case insensitive. However, in order to minimize interoperability issues when clients neglect to normalize IVOID case, ADQL services MUST provide the feature IVOIDs in all-lower case as shown in the present document.
contains examples of how to declare support for each of the language features defined in this document using the XML schema from the .
For full details on the XML schema and how it can be used, please refer to the .
4.2 Geometrical functions¶
4.2.1 Overview¶
In addition to the mathematical functions, ADQL provides the following geometrical functions to facilitate queries commonly useful in astronomy:
AREA
BOX
CENTROID
CIRCLE
CONTAINS
COORD1
COORD2
COORDSYS
DISTANCE
INTERSECTS
POINT
POLYGON
REGION
4.2.2 Language feature¶
All functions described in this section use the following IVOID:
ivo://ivoa.net/std/tapregext#features-adqlgeo
Each geometrical function is declared using this IVOID and its name. See for more details.
All other optional features have an IVOID starting with:
ivo://ivoa.net/std/tapregext#features-adql-
Note the ending hyphen. The IVOID for the geometry features does not have this hyphen. This is not a typo. It actually comes from the which originally defined this IVOID. At that time, it was not clear yet that other language features would be created for ADQL. So, for historical and interoperability reasons the IVOID for the geometry features must not have this hyphen.
4.2.3 Datatype functions¶
Some of the functions described in this section (e.g. POINT, CIRCLE) are constructors for each of the geometry datatypes. The semantics of these datatypes are based on the corresponding concepts from the data model.
The geometry datatypes and expressions are part of the core <value_expression> in the ADQL grammar.
<value_expression> ::=
NULL
| <numeric_value_expression>
| <string_value_expression>
| <geometry_value_expression>
A <geometry_value_expression> does not simply cover the geometry datatype constructors (POINT, CIRCLE, etc.) but also includes user defined functions and column values where a geometry datatype is stored in a column.
Therefore, <geometry_value_expression> is expanded as:
<geometry_value_expression> ::=
<value_expression_primary>
| <geometry_value_function>
where
<geometry_value_function> ::=
<box>
| <centroid>
| <circle>
| <point>
| <polygon>
| <region>
| <user_defined_function>
and <value_expression_primary> enables the use of geometric functions and column references.
4.2.4 Coordinate limits¶
The arguments for a geometric function represent spherical coordinates in units of degrees (square degrees for area).
ADQL implementors and users MUST follow coordinate ranges defined in DALI 1.1 and later.
Note that at the time of the ADQL 2.1 recommendation, no agreed-upon, reliable, IVOA-approved convention for what ranges apply to which reference system exists. Such convention is foreseen to be defined in DALI. Presently, DALI 1.1 only defines ranges for equatorial coordinates. However, this is expected to be updated in future DALI versions.
Details of the mechanism for reporting the out of range arguments are implementation dependent.
4.2.5 Coordinate system¶
For historical reasons, the geometry constructors (BOX, CIRCLE, POINT and POLYGON) all accept a string literal as the first argument, hereafter called the COOSYS argument.
The COOSYS argument was originally intended to carry information on a reference system or other coordinate system metadata. This was helpful in order to deal with data specified in different coordinate systems while performing geometric operations. It was up to the ADQL service to perform the appropriate conversion to make these operations possible.
Since version 2.1, this argument is deprecated and has been made optional. Future versions of this specification will remove this parameter from the listed functions.
Coordinate conversions SHOULD now be explictly requested. The ADQL implementers have to allow it through User Defined Functions. An interoperable facility for frame transformations is in preparation as of this writing and is expected to be part of the .
DISTANCE, CONTAINS and INTERSECTS MAY still convert coordinates of its geometric operands if they are expressed in different coordinate systems. However, be aware that in a future version of ADQL, these functions will no longer be expected to perform any coordinate conversion. Consequently, it is recommanded to avoid relying on this deprecated feature. For interoperability reasons, queries against 2.0 or later services SHOULD NOT pass arguments with differing COOSYS arguments to DISTANCE, CONTAINS or INTERSECTS, as behaviour is undefined in that case.
4.2.6 Predicate functions¶
Functions CONTAINS and INTERSECTS each accept two geometry datatypes and return a numeric value of 1 or 0 according to whether the relevant verb (e.g. contains) is satisfied against the two input geometries; 1 if the condition is met and 0 if it is not.
Each of these functions can be used as a WHERE clause predicate by comparing the numeric result with zero or one. For example:
SELECT *
FROM table
WHERE 1 = CONTAINS(POINT(...), CIRCLE(...))
4.2.7 Preferred sky crossmatch syntax¶
An especially common operation that astronomers require when working with source catalogues is the positional sky crossmatch. In its simplest form this is a join between two tables with the requirement that the distance along a great circle between the sky positions of the two associated rows is less than or equal to a given threshold.
The geometrical functions provided by ADQL offer a number of semantically equivalent ways to specify such a condition in either the JOIN or the WHERE clause, using various combinations of POINT, CIRCLE and DISTANCE. While a correct implementation MUST generate the same result for any of these alternatives, the performance characteristics may differ dramatically depending on implementation. Given this, it is difficult for (human or machine) ADQL authors to know how to phrase a crossmatch with the expectation that it will be executed efficiently, and difficult for services to know which forms of query to optimise. The result can be the unnecessarily slow operation of the common sky crossmatch operation.
This section therefore recommends a preferred form of ADQL to use for sky crossmatching and the related cone search operation, namely to impose an upper limit on one of the two forms of the DISTANCE function. Clients submitting crossmatch-like and cone-like queries are advised to phrase them in this way rather than using semantically equivalent alternatives, and services are encouraged to ensure that these forms of query are executed efficiently; this might involve identifying such ADQL input clauses and rewriting them appropriately for efficient processing on the database backend. Alternative semantically equivalent forms however MAY still be used by clients, and MUST still be handled correctly by services.
An example sky position-only crossmatch joining rows of tables t1 and t2 within one arcsecond might therefore look like:
JOIN ...
ON DISTANCE(t1.ra, t1.dec, t2.ra, t2.dec) < 0.00027
and a cone search for rows within 2.5 degrees of the center of M31 might look like:
SELECT ...
WHERE DISTANCE(t.pos, POINT(10.68, 41.27)) <= 2.5
4.2.8 AREA¶
For example, an expression to calculate the area of a POLYGON could be written as follows:
AREA(POLYGON(10.0, -10.5, 20.0, 20.5, 30.0, 30.5))
The AREA of a single POINT is zero.
The geometry argument may be a literal value, as above, or it may be a column reference, function or expression that returns a geometric type. For example:
AREA(t1.footprint)
where t1.footprint is a reference to a database column that contains geometric (POINT, BOX, CIRCLE, POLYGON or REGION) values.
4.2.9 BOX¶
BOX function has been deprecated in version 2.1 of the standard, and will be removed from future versions of the specification.The BOX function expresses a box on the sky. A BOX is a special case of POLYGON, defined purely for convenience, and it corresponds semantically to the equivalent term, Box, defined in the .
It is specified by a center position and size (in both axes) defining a cross centered on the center position and with arms extending, parallel to the coordinate axes at the center position, for half the respective sizes on either side. The box’s sides are line segments or great circles intersecting the arms of the cross in its end points at right angles with the arms.
The function arguments specify the center position and the width and height, where:
the center position is given by a pair of numeric coordinates in degrees, or a single geometric POINT
the values of coordinates of the center position are subject to the constraints laid down in
the width and height are given by numeric values in degrees
For example, a BOX of ten degrees centered on a position (25.4, -20.0) in degrees could be written as follows:
BOX(25.4, -20.0, 10.0, 10.0)
Alternatively, the center position could be expressed as a POINT:
BOX(POINT(25.4, -20.0), 10.0, 10.0)
The function arguments may be literal values, as above, or they may be column references, functions or expressions that return the appropriate datatypes. For example:
BOX(t1.center, t1.width, t1.height)
where t1.center, t1.width and t1.height are references to database columns that contain POINT, DOUBLE and DOUBLE values respectively.
For historical reasons, the BOX function accepts an optional string literal as the first argument. As of version 2.1 of the specification this parameter has been marked as deprecated. Future versions of this specification may remove this parameter .
4.2.10 CENTROID¶
For example, an expression to calculate the centroid of a POLYGON could be written as follows :
CENTROID(POLYGON(10.0, -10.5, 20.0, 20.5, 30.0, 30.5))
The CENTROID of a single POINT is that POINT.
The geometry argument may be a literal value, as above, or it may be a column reference, function or expression that returns a geometric type. For example:
CENTROID(t1.footprint)
where t1.footprint is a reference to a database column that contains geometric (POINT, BOX, CIRCLE, POLYGON or REGION) values.
4.2.11 CIRCLE¶
The function arguments specify the center position and the radius, where:
the center position is given by a pair of numeric coordinates in degrees, or a single geometric POINT
the values of coordinates of the center position are subject to the constraints laid down in
the radius is a numeric value in degrees.
For example, a CIRCLE of ten degrees radius centered on position (25.4, -20.0) in degrees could be written as follows:
CIRCLE(25.4, -20.0, 10.0)
Alternatively, the center position may be expressed as a POINT:
CIRCLE(POINT(25.4, -20.0), 10.0)
The position argument may be a literal value, as above, or it may be a column reference, function or expression that returns a geometric type. For example:
CIRCLE(t1.center, t1.radius)
where t1.center and t1.radius are references to database columns that contain POINT and DOUBLE values respectively.
For historical reasons, the CIRCLE function accepts an optional string literal as the first argument. As of version 2.1 of the specification this parameter has been marked as deprecated. Future versions of this specification may remove this parameter .
4.2.12 CONTAINS¶
For example, an expression to determine whether the point (25.0, -19.5) degrees is within a circle of ten degrees radius centered on position (25.4, -20.0) degrees, could be written as follows:
CONTAINS(POINT(25.0, -19.5), CIRCLE(25.4, -20.0, 10.0))
The CONTAINS function is not symmetric in the meaning of the arguments.
The CONTAINS function returns the integer value 1 if the first argument is in, or on, the boundary of the second argument and the integer value 0 if it is not.
When used as a predicate in the WHERE clause of a query, the returned integer value must be compared to the integer values 0 or 1 to form a SQL predicate:
WHERE 1 = CONTAINS(POINT(25.0, -19.5),
CIRCLE(25.4, -20.0, 10.0))
for “does contain” and
WHERE 0 = CONTAINS(POINT(25.0, -19.5),
CIRCLE(25.4, -20.0, 10.0))
for “does not contain”.
The geometric arguments for CONTAINS may be literal values, as above, or they may be column references, functions or expressions that return geometric values. For example:
WHERE 0 = CONTAINS(t1.center, t2.footprint)
where t1.center and t2.footprint are references to database columns that contain POINT and geometric (BOX, CIRCLE, POLYGON or REGION) values respectively.
Geometric arguments SHOULD be expressed in the same coordinate system. See for more details.
4.2.13 COORD1¶
For example, the right ascension of a point with position (25, -19.5) in degrees would be obtained using the following expression:
COORD1(POINT(25.0, -19.5))
which would return a numeric value of 25.0 degrees.
For example:
COORD1(t.center)
where t.center is a reference to a column that contains POINT values.
4.2.14 COORD2¶
For example, the declination of a point with position (25, -19.5) in degrees, could be obtained using the following expression:
COORD2(POINT(25.0, -19.5))
which would return a numeric value of -19.5 degrees.
The COORD2 function may be applied to any expression that returns a geometric POINT value. For example:
COORD2(t.center)
where t.center is a reference to a column that contains POINT values.
4.2.15 COORDSYS¶
TAP_SCHEMA tables defined in the and the /tables webservice response defined in the .The COORDSYS function returns the formal name of the coordinate system for a given geometry as a string.
The following example would return the coordinate system of a POINT literal:
COORDSYS(POINT(25.0, -19.5))
which would return a string value representing the coordinate system used to create the POINT.
The COORDSYS function may be applied to any expression that returns a geometric datatype. For example:
COORDSYS(t.footprint)
where t.footprint is a reference to a database column that contains geometric (POINT, BOX, CIRCLE, POLYGON or REGION) values.
4.2.16 DISTANCE¶
The specification defines two versions of the DISTANCE function, one that accepts two POINT values, and a second that accepts four separate numeric values.
If an ADQL service implementation declares support for DISTANCE, then it must implement both the two parameter and four parameter forms of the function.
For example, an expression calculating the distance between two points of coordinates (25,-19.5) and (25.4,-20) could be written as follows:
DISTANCE(POINT(25.0, -19.5), POINT(25.4, -20.0))
where all numeric values and the returned arc length are in degrees.
The equivalent call to the four parameter form of the function would be:
DISTANCE(25.0, -19.5, 25.4, -20.0)
The DISTANCE function may be applied to any expression that returns a geometric POINT value. Behaviour for expressions returning a geometry different from a POINT is undefined at this point (but may be defined later).
For example, the distance between two points stored in the database could be calculated as follows:
DISTANCE(t1.base, t2.target)
where t1.base and t2.target are references to database columns that contain POINT values.
Geometric arguments SHOULD be expressed in the same coordinate system, even in the four numeric parameter form. See for more details.
4.2.17 INTERSECTS¶
For example, an expression to determine whether a circle of one degree radius centered on position (25.4, -20.0) degrees overlaps with a POLYGON, could be written as follows:
INTERSECTS(CIRCLE(25.4, -20.0, 1),
POLYGON(20.0, -15.0,
20.0, -5.0,
10.0, -5.0,
10.0, -15.0))
where the INTERSECTS function returns the integer value 1 if the two arguments overlap and 0 if they do not.
When used as a predicate in the WHERE clause of a query, the returned integer value should be compared to the integer values 0 or 1 to form a SQL predicate:
WHERE 1 = INTERSECTS(CIRCLE(25.4, -20.0, 1),
POLYGON(20.0, -15.0,
20.0, -5.0,
10.0, -5.0,
10.0, -15.0))
for “does intersect” and
WHERE 0 = INTERSECTS(CIRCLE(25.4, -20.0, 1),
POLYGON(20.0, -15.0,
20.0, -5.0,
10.0, -5.0,
10.0, -15.0))
for “does not intersect”.
The geometric arguments for INTERSECTS may be literal values, as above, or they may be column references, functions or expressions that return geometric values. For example:
WHERE 0 = INTERSECTS(t1.target, t2.footprint)
where t1.target and t2.footprint are references to database columns that contain geometric (BOX, CIRCLE, POLYGON or REGION) values.
The arguments to INTERSECTS SHOULD be geometric expressions evaluating to either BOX, CIRCLE, POLYGON or REGION.
Previous versions of this specification also allowed POINT values and required server implementations to interpret the expression as a CONTAINS with the POINT moved into the first position. Server implementations SHOULD still implement that behaviour, but clients SHOULD NOT expect it. This behaviour MAY be dropped in the next major version of this specification.
Geometric arguments SHOULD be expressed in the same coordinate system. See for more details.
4.2.18 POINT¶
The function arguments specify the position, where:
the position is given by a pair of numeric coordinates in degrees
the values of coordinates are subject to the constraints laid down in
For example, a function expressing a point with right ascension of 25 degrees and declination of -19.5 degrees would be written as follows:
POINT(25.0, -19.5)
where numeric values are in degrees.
The coordinates for POINT may be literal values, as above, or they may be column references, functions or expressions that return numeric values. For example:
POINT(t.ra, t.dec)
where t.ra and t.dec are references to database columns that contain numeric values.
For historical reasons, the POINT function accepts an optional string literal as the first argument. As of version 2.1 of the specification this parameter has been marked as deprecated. Future versions of this specification may remove this parameter .
4.2.19 POLYGON¶
A polygon is described by a list of vertices in a single coordinate system, with each vertex connected to the next along a great circle and the last vertex implicitly connected to the first vertex.
The function arguments specify three or more vertices, where:
the position of the vertices are given as a sequence of numeric coordinates in degrees, or as a sequence of geometric POINTs
the values of coordinates are subject to the constraints laid down in
For example, a function expressing a triangle with vertices at (10.0, -10.5), (20.0, 20.5) and (30.0,30.5) in degrees would be written as follows:
POLYGON(10.0, -10.5, 20.0, 20.5, 30.0, 30.5)
where all numeric values are in degrees.
The coordinates for the POLYGON vertices may be literal values, as above, or they may be column references, functions or expressions that return numeric values. For example:
POLYGON(t1.ra , t1.dec + 5,
t1.ra - 5, t1.dec - 5,
t1.ra - 5, t1.dec + 5)
where t1.ra and t1.dec are references to database columns that contain numeric values.
Alternatively, the coordinates for the POLYGON vertices may be column references, functions or expressions that return POINT values. For example:
POLYGON(t2.toppoint, t2.bottomleft, t2.bottomright)
where t2.toppoint, t2.bottomleft and t2.bottomright are references to database columns that contain POINT values.
The coordinates for the vertices MUST all be expressed in the same datatype. The POLYGON function does not support a mixture of numeric and POINT arguments.
For historical reasons, the POLYGON function accepts an optional string literal as the first argument. As of version 2.1 of the specification this parameter has been marked as deprecated. Future versions of this specification may remove this parameter .
4.2.20 REGION¶
This document does not specify possible syntaxes for REGION literals. A de-facto standard that many services understanding ADQL 2.0 implemented at least partially is given by the , and implementations of ADQL 2.1 are encouraged to support as much of that as reasonable for them.
4.3 User defined functions¶
4.3.1 Overview¶
ADQL also provides a place holder to define user specific functions. The grammar definition for user defined functions includes a variable list of parameters.
<user_defined_function> ::=
<user_defined_function_name> <left_paren>
[
<value_expression>
[
{
<comma> <value_expression>
}...
]
]
<right_paren>
In order to avoid name conflicts, user defined function names SHOULD include a prefix which indicates the name of the institute or project which created the function.
For example, the names of align and convert functions developed by the Wide Field Astronomy Unit (WFAU) could be prefixed as follows:
wfau_align()
wfau_convert()
This enables users to distinguish between functions with similar names developed by a different service provider, e.g. the German Astrophysical Virtual Observatory (GAVO):
gavo_align()
gavo_convert()
The ivo prefix is reserved for functions that have been defined in an IVOA specification or Endorsed Note. For example the defines the following functions:
ivo_nocasematch()
ivo_hasword()
ivo_hashlist_has()
ivo_string_agg()
The collects many ivo prefixed functions defined in other IVOA specifications, Endorsed Notes and functions defined in at least two implementations. At the time of this writing it is the only endorsed IVOA document providing such a collection of ivo prefixed functions. It is then strongly recommended to follow function names and definitions from this catalogue when providing functions offering identical or similar functionnality.
4.3.2 Metadata¶
The URI for identifying the language feature for a user defined function is defined as part of the .
ivo://ivoa.net/std/tapregext#features-udf
For user defined functions, the form element of the language feature declaration must contain the signature of the function, written to match the signature nonterminal in the following grammar:
signature ::= <funcname> <arglist> "->" <type_name>
funcname ::= <regular_identifier>
arglist ::= "(" <arg> { "," <arg> } ")"
arg ::= <regular_identifier> <type_name>
<type_name> should be one of the terms defined in .
For example, the following fragment declares a user defined function that takes two string parameters and returns an integer, zero or one, depending on the regular expression pattern matching:
<languageFeatures type="ivo://ivoa.net/std/tapregext#features-udf">
<feature>
<form>match(pattern VARCHAR, string VARCHAR) -> INTEGER</form>
<description>
match returns 1 if the POSIX regular expression pattern
matches anything in string, 0 otherwise.
</description>
</feature>
</languageFeatures>
See the for full details on how to use the XML schema to declare user defined functions.
4.4 String functions and operators¶
An ADQL service implementation MAY include support for the following optional string manipulation and comparison operators:
LOWER()Lower case conversionUPPER()Upper case conversionILIKECase-insensitive comparison.
4.4.1 Case folding¶
Since case folding is a nontrivial operation in a multi-encoding world, ADQL requires standard behaviour for the ASCII characters, and recommends following algorithms described in Section 3.13, “Default Case Algorithms” of for characters outside the ASCII set:
algorithm R1 for
UPPER()algorithm R2 for
LOWER()andILIKE
4.4.2 LOWER¶
LOWER('Francis Albert Augustus Charles Emmanuel')
=>
francis albert augustus charles emmanuel
4.4.3 UPPER¶
UPPER('Francis Albert Augustus Charles Emmanuel')
=>
FRANCIS ALBERT AUGUSTUS CHARLES EMMANUEL
4.4.4 ILIKE¶
'Francis' LIKE 'francis' => False
'Francis' ILIKE 'francis' => True
4.5 Common table expressions¶
An ADQL service implementation MAY include support for the following optional common table expressions:
WITH
4.5.1 WITH¶
Using a common table expression can make complex queries easier to understand by factoring subqueries out of the main SQL statement.
For example, the following query with a nested subquery:
SELECT ra, dec
FROM (
SELECT *
FROM alpha_source
WHERE id % 10 = 0
) AS alpha_subset
WHERE ra > 10
AND ra < 20
can be refactored as a named WITH query and a simpler main query:
WITH alpha_subset AS (
SELECT *
FROM alpha_source
WHERE id % 10 = 0
)
SELECT ra, dec
FROM alpha_subset
WHERE ra > 10
AND ra < 20
The current version of ADQL does not support recursive common table expressions.
Common table expressions can be defined only in the main query. They are not allowed in sub-queries.
4.6 Set operators¶
An ADQL service implementation MAY include support for the following optional set operators:
UNIONEXCEPTINTERSECT
For a set operation to be valid in ADQL, the following criteria must be met:
the two queries MUST result in the same number of columns
the columns in the operands MUST have the same datatypes.
In addition, the columns returned by a set operation SHOULD have the same metadata, e.g. units, UCD, etc. These metadata SHOULD be generated from the left-hand operand of the set operation.
4.6.1 UNION¶
4.6.2 EXCEPT¶
4.6.3 INTERSECT¶
4.6.4 Duplicated rows¶
UNION, EXCEPT and INTERSECT remove duplicated rows, while UNION ALL, EXCEPT ALL and INTERSECT ALL keep all of them.
Note that the comparison used for removing duplicated rows is based purely on the column value and does not take into account the units. This means that a row with a numeric value of 2 and unit of m and a row with a numeric value of 2 and unit of km will be considered equal, despite the difference in units.
4.6.5 Operands¶
Operands of any of the set operators can only be SELECT queries. Unless within parentheses, such queries can not use any ORDER BY or OFFSET clause.
Example: sorting result of a UNION operation:
SELECT id, ra, dec FROM table1
UNION
SELECT id, ra, dec FROM table2
ORDER BY id -- sort the UNION result
Example: sorting result of the UNION operands:
-- take the 10 first
(SELECT TOP 10 id, ra, dec FROM atable ORDER BY id ASC)
UNION
-- take the 10 last
(SELECT TOP 10 id, ra, dec FROM atable ORDER BY id DESC)
Common table expressions are not allowed in any set operator operand. They must always be declared at the main level.
Example: sorting result of the UNION operands: with common table expressions
WITH tenFirst AS (SELECT TOP 10 id, ra, dec FROM atable ORDER BY id ASC),
tenLast AS (SELECT TOP 10 id, ra, dec FROM atable ORDER BY id DESC)
SELECT * FROM tenFirst
UNION
SELECT * FROM tenLast
4.6.6 Precedence¶
When set operators are used together, the resulting expression is evaluated in the context of the following precedence:
Expressions within parentheses
The
INTERSECToperatorThe
UNIONandEXCEPToperators evaluated from left to right
Example:
SELECT id, ra, dec FROM table1
UNION
SELECT id, ra, dec FROM table2
INTERSECT
SELECT id, ra, dec FROM table3
is equivalent to:
SELECT id, ra, dec FROM table1
UNION
(
SELECT id, ra, dec FROM table2
INTERSECT
SELECT id, ra, dec FROM table3
)
4.7 Type operations¶
An ADQL service implementation MAY include support for the following optional type conversion functions:
CAST()
4.7.1 CAST¶
CAST() function returns the value of the first argument converted into the datatype specified by the second argument.Syntax¶
<cast_specification>
CAST <left_paren>
<value_expression> AS <cast_target>
<right_paren>
Target types¶
This function does not replicate the full functionality and range of types supported by common RDBMS implementations of CAST(). Here is the minimum range of types that MUST be supported if CAST() is implemented:
Exact numeric:
INTEGERSMALLINTBIGINT
Approximate numeric:
REALDOUBLE PRECISION
Character:
CHARorCHAR(n)(where n is the fixed string length)VARCHARorVARCHAR(n)(where n is the maximum string length)
Date, Time:
TIMESTAMP
Examples:
CAST(3 AS REAL)
CAST('3.14159265358979323846' AS DOUBLE PRECISION)
Input types¶
The range of types allowed for the value to cast entirely depends on the target type. Although cast operations may vary from one implementation to another, ADQL SHOULD support the ones listed in Table ADQL:table:cast.inputtypes.
Cast into a smaller datatype¶
Converting a value to a datatype that is too small to represent it SHOULD be treated as an error. Details of the mechanism for reporting the error condition are implementation dependent.
This rule especially applies when casting a value into a character string too small to contain its entire serialization. The output string may be truncated, adjusted to the needed length, or an error may be thrown.
Fixed-length character¶
The creation of a fixed-length character string is implementation dependent. In function of the implementation, CHAR may be equivalent to CHAR(1) or to a CHAR just big enough to contain the entire string to create.
Approximate numeric¶
The rounding mechanism used when converting from approximate numerics (REAL or DOUBLE PRECISION) to precise numerics (SMALLINT, INTEGER or BIGINT) is implementation dependent.
Timestamp¶
Only a character string can be casted into a timestamp. This string MUST follow the syntax defined in the :
YYYY-MM-DD[’T’hh:mm:ss[.SSS][’Z’]]
Example:
CAST('2021-01-14T11:25:00' AS TIMESTAMP)
Note that other serializations or any other kind of value MAY also be supported.
Geometry¶
CAST() MAY also produce geometries. If an implementation wants to support this particular cast operation, it MUST accept a character string following the DALI serialization matching the precise geometry type to produce.
Then, the supported geometry types SHOULD be:
POINTCIRCLEPOLYGON
Examples:
CAST('12.3 45.6' AS POINT)
CAST('12.3 45.6 1.0' AS CIRCLE)
CAST('1.0 0.1 2.0 0.2 3.0 0.3' AS POLYGON)
Note that other serializations (e.g. STC-S) or any other kind of value MAY also be supported.
4.8 Conditional Functions¶
An ADQL service implementation MAY include support for the following optional conditional functions:
COALESCE()
4.8.1 COALESCE¶
All arguments must be of the same datatype. An error should be returned if this rule is not respected. The way to report this error is implementation dependent.
This is typically used to provide fallback values. For instance,
COALESCE(access_url, '')
is NULL.
4.9 Unit operations¶
An ADQL service implementation MAY include support for the following optional unit conversion functions:
IN_UNIT()
4.9.1 IN_UNIT¶
IN_UNIT() function returns the value of the first argument transformed into the unit defined by the second argument.The first argument MUST be a numeric value expression. If this argument is a column name, the VOUnits for this column ought to be found in the metadata attached to this column (e.g. TAP_SCHEMA.columns.unit in a TAP service).
The second argument MUST be a string literal containing a valid unit description using the formatting defined in the .
Example:
-- ra: column expressed in 'deg'
IN_UNIT(ra, 'rad') -- OK
IN_UNIT(ra, 'arcmin') -- OK
It has to be noted that a unitless value, as defined by the , can not be converted. Similarly a non-unitless value can not be converted into a unitless one.
The system MUST report an error in response to the following conditions:
if the second argument is not a valid unit description
if the system is not able to convert the value into the requested unit
Examples:
-- ra : column expressed in 'deg'
-- phot_cnt : column expressing a unitless quantity
IN_UNIT(ra, '') -- ERROR: Conversion into a 'unitless'
-- value impossible
IN_UNIT(phot_cnt, 'deg') -- ERROR: Impossible to convert a
-- unitless value
IN_UNIT(ra, 'foo') -- ERROR: Incorrect unit definition
IN_UNIT(ra, 'kg') -- ERROR: Impossible conversion
-- (deg -> kg)
Details of the mechanism for reporting the error condition are implementation dependent.
Currently, ADQL does not provide any way to specify the unit of a numeric literal (e.g. a number such as 42 or 3.14) or of the result of an operation or function. Consequently, regarding the unitless aspect of such a numeric expression, IN_UNIT() MUST report an error.
Examples:
IN_UNIT(42, 'kg') -- ERROR: no unit can be specified for
-- 42, so, it is unitless
-- => its conversion is
-- impossible
IN_UNIT(40+2, 'deg') -- ERROR: idem
IN_UNIT(sqrt(42), 'deg') -- ERROR: idem
An ADQL service implementation could use a unit inference mechanism. In such case, the units of numeric expressions can be inferred (or at least guessed). With the help of a such mechanism, IN_UNIT() MAY convert more complex expressions.
Examples, with an advanced unit inference mechanism:
-- ra : column expressed in 'deg'
-- ra_error : column expressed in 'deg'
-- pm_ra : column expressing in 'deg/yr'
-- pm_dec : column expressing in 'deg/yr'
IN_UNIT(ra+ra_error, 'rad') -- OK
IN_UNIT(ra+10, 'rad') -- OK
IN_UNIT(sqrt(power(pm_ra,2)+power(pm_dec,2)), 'rad/yr') -- OK
Note that the complexity and the behavior of a unit inference mechanism is implementation dependent. This mechanism is OPTIONAL and is described here as it could significantly improve the behavior of IN_UNIT().
4.10 Cardinality¶
An ADQL service implementation MAY include support for the following optional clauses to modify the cardinality of query results:
OFFSET
4.10.1 OFFSET¶
If a query contains both an ORDER BY clause and an OFFSET clause, then the ORDER BY is applied before the specified number of rows are dropped by the OFFSET clause.
If the total number of rows is less than the value specified by the OFFSET clause, then the result set is empty.
If a query contains both an OFFSET clause and a TOP clause, then the OFFSET clause is applied first, dropping the specified number of rows from the beginning of the result set before the TOP clause is applied to limit the number of rows returned.
Appendices¶
A BNF grammar¶
<ADQL_language_character> ::=
<simple_Latin_letter>
| <digit>
| <SQL_special_character>
<ADQL_reserved_word> ::=
ABS
| ACOS
| AREA
| ASIN
| ATAN
| ATAN2
| BIGINT
| BOX
| CEILING
| CENTROID
| CIRCLE
| CONTAINS
| COORD1
| COORD2
| COORDSYS
| COS
| COT
| DEGREES
| DISTANCE
| EXP
| FLOOR
| ILIKE
| INTERSECTS
| IN_UNIT
| LOG
| LOG10
| MOD
| OFFSET
| PI
| POINT
| POLYGON
| POWER
| RADIANS
| REGION
| RAND
| ROUND
| SIN
| SQRT
| TOP
| TAN
| TRUNCATE
<SQL_embedded_language_character> ::=
<left_bracket> | <right_bracket>
<SQL_reserved_word> ::=
ABSOLUTE | ACTION | ADD | ALL
| ALLOCATE | ALTER | AND
| ANY | ARE
| AS | ASC
| ASSERTION | AT
| AUTHORIZATION | AVG
| BEGIN | BETWEEN | BIT | BIT_LENGTH
| BOTH | BY
| CASCADE | CASCADED | CASE | CAST
| CATALOG
| CHAR | CHARACTER | CHAR_LENGTH
| CHARACTER_LENGTH | CHECK | CLOSE
| COALESCE | COLLATE | COLLATION
| COLUMN | COMMIT
| CONNECT
| CONNECTION | CONSTRAINT
| CONSTRAINTS | CONTINUE
| CONVERT | CORRESPONDING | COUNT | CREATE | CROSS
| CURRENT
| CURRENT_DATE | CURRENT_TIME
| CURRENT_TIMESTAMP | CURRENT_USER | CURSOR
| DATE | DAY | DEALLOCATE
| DECIMAL | DECLARE | DEFAULT | DEFERRABLE
| DEFERRED | DELETE | DESC | DESCRIBE | DESCRIPTOR
| DIAGNOSTICS
| DISCONNECT | DISTINCT | DOMAIN | DOUBLE | DROP
| ELSE | END | END-EXEC | ESCAPE
| EXCEPT | EXCEPTION
| EXEC | EXECUTE | EXISTS
| EXTERNAL | EXTRACT
| FALSE | FETCH | FIRST | FLOAT | FOR
| FOREIGN | FOUND | FROM | FULL
| GET | GLOBAL | GO | GOTO
| GRANT | GROUP
| HAVING | HOUR
| IDENTITY | IMMEDIATE | IN | INDICATOR
| INITIALLY | INNER | INPUT
| INSENSITIVE | INSERT | INT | INTEGER | INTERSECT
| INTERVAL | INTO | IS
| ISOLATION
| JOIN
| KEY
| LANGUAGE | LAST | LEADING | LEFT
| LEVEL | LIKE | LOCAL | LOWER
| MATCH | MAX | MIN | MINUTE | MODULE
| MONTH
| NAMES | NATIONAL | NATURAL | NCHAR | NEXT | NO
| NOT | NULL
| NULLIF | NUMERIC
| OCTET_LENGTH | OF
| ON | ONLY | OPEN | OPTION | OR
| ORDER | OUTER
| OUTPUT | OVERLAPS
| PAD | PARTIAL | POSITION | PRECISION | PREPARE
| PRESERVE | PRIMARY
| PRIOR | PRIVILEGES | PROCEDURE | PUBLIC
| READ | REAL | REFERENCES | RELATIVE | RESTRICT
| REVOKE | RIGHT
| ROLLBACK | ROWS
| SCHEMA | SCROLL | SECOND | SECTION
| SELECT
| SESSION | SESSION_USER | SET
| SIZE | SMALLINT | SOME | SPACE | SQL | SQLCODE
| SQLERROR | SQLSTATE
| SUBSTRING | SUM | SYSTEM_USER
| TABLE | TEMPORARY
| THEN | TIME | TIMESTAMP
| TIMEZONE_HOUR | TIMEZONE_MINUTE
| TO | TRAILING | TRANSACTION
| TRANSLATE | TRANSLATION | TRIM | TRUE
| UNION | UNIQUE | UNKNOWN | UPDATE | UPPER | USAGE
| USER | USING
| VALUE | VALUES | VARCHAR | VARYING | VIEW
| WHEN | WHENEVER | WHERE | WITH | WORK | WRITE
| YEAR
| ZONE
<SQL_special_character> ::=
<space>
| <double_quote>
| <percent>
| <ampersand>
| <quote>
| <left_paren>
| <right_paren>
| <asterisk>
| <plus_sign>
| <comma>
| <minus_sign>
| <period>
| <solidus>
| <colon>
| <semicolon>
| <less_than_operator>
| <equals_operator>
| <greater_than_operator>
| <question_mark>
| <underscore>
| <vertical_bar>
<ampersand> ::= &
<approximate_numeric_literal> ::= <mantissa>E<exponent>
<approximate numeric type> ::=
REAL
| DOUBLE PRECISION
<area> ::= AREA <left_paren> <geometry_value_expression> <right_paren>
<as_clause> ::= [ AS ] <column_name>
<asterisk> ::= *
<between_predicate> ::=
<value_expression> [ NOT ] BETWEEN
<value_expression> AND <value_expression>
<boolean_factor> ::= [ NOT ] <boolean_primary>
<boolean_primary> ::=
<left_paren> <search_condition> <right_paren>
| <predicate>
<boolean_term> ::=
<boolean_factor>
| <boolean_term> AND <boolean_factor>
<box> ::=
BOX <left_paren>
[ <coord_sys> <comma> ]
<box_center>
<comma> <numeric_value_expression>
<comma> <numeric_value_expression>
<right_paren>
<box_center> ::=
<coordinates>
| <coord_value>
<cast_specification> ::=
CAST <left_paren> <value_expression> AS <cast_target> <right_paren>
<cast_target> ::=
<character_string_type>
| <numeric_type>
| <datetime_type>
| <geometry_type>
<catalog_name> ::= <identifier>
<centroid> ::=
CENTROID <left_paren>
<geometry_value_expression>
<right_paren>
<character_factor> ::= <character_primary>
<character_primary> ::=
<value_expression_primary>
| <string_value_function>
<character_representation> ::= <nonquote_character> | <quote_symbol>
<character_string_literal> ::=
<quote> [ <character_representation>... ] <quote>
<character_string_type> ::=
CHAR [ <left paren> <length> <right paren> ]
| VARCHAR [ <left paren> <length> <right paren> ]
<character_value_expression> ::= <concatenation> | <character_factor>
<circle> ::=
CIRCLE <left_paren>
[ <coord_sys> <comma> ]
<circle_center>
<comma> <radius>
<right_paren>
<circle_center> ::=
<coordinates>
| <coord_value>
<circumflex> ::= ^
<coalesce_expression> ::=
COALESCE <left_paren>
<value_expression>
[ { <comma> <value_expression }... ] <right_paren>
<colon> ::= ":"
<column_name> ::= <identifier>
<column_name_list> ::= <column_name> [ { <comma> <column_name> }... ]
<column_reference> ::= [ <qualifier> <period> ] <column_name>
<comma> ::= ,
<comment> ::= <comment_introducer> [ <comment_character>... ] <newline>
<comment_character> ::= <nonquote_character> | <quote>
<comment_introducer> ::= <minus_sign><minus_sign> [<minus_sign>...]
<comp_op> ::=
<equals_operator>
| <not_equals_operator>
| <less_than_operator>
| <greater_than_operator>
| <less_than_or_equals_operator>
| <greater_than_or_equals_operator>
<comparison_predicate> ::=
<value_expression> <comp_op> <value_expression>
<concatenation> ::=
<character_value_expression>
<concatenation_operator>
<character_factor>
<concatenation_operator> ::= "||"
<contains> ::=
CONTAINS <left_paren>
<geometry_value_expression> <comma> <geometry_value_expression>
<right_paren>
<coord1> ::= COORD1 <left_paren> <coord_value> <right_paren>
<coord2> ::= COORD2 <left_paren> <coord_value> <right_paren>
<coord_sys> ::= <character_string_literal>
<coord_value> ::= <point_value> | <column_reference>
<coordinate1> ::= <numeric_value_expression>
<coordinate2> ::= <numeric_value_expression>
<coordinates> ::= <coordinate1> <comma> <coordinate2>
<correlation_name> ::= <identifier>
<correlation_specification> ::= [ AS ] <correlation_name>
<datetime_type> ::= TIMESTAMP
<default_function_prefix> ::= (see text)
<delimited_identifier> ::=
<double_quote> <delimited_identifier_body> <double_quote>
<delimited_identifier_body> ::= <delimited_identifier_part>...
<delimited_identifier_part> ::=
<nondoublequote_character> | <double_quote_symbol>
<delimiter_token> ::=
<character_string_literal>
| <delimited_identifier>
| <SQL_special_character>
| <not_equals_operator>
| <greater_than_or_equals_operator>
| <less_than_or_equals_operator>
| <concatenation_operator>
| <double_period>
| <left_bracket>
| <right_bracket>
<derived_column> ::= <value_expression> [ <as_clause> ]
<derived_table> ::= <table_subquery>
<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
<distance_function> ::=
DISTANCE <left_paren>
<coord_value> <comma>
<coord_value>
<right_paren>
| DISTANCE <left_paren>
<numeric_value_expression> <comma>
<numeric_value_expression> <comma>
<numeric_value_expression> <comma>
<numeric_value_expression>
<right_paren>
<double_period> ::= ".."
<double_quote> ::= """
<double_quote_symbol> ::= <double_quote><double_quote>
<equals_operator> ::= "="
<exact_numeric_literal> ::=
<unsigned_decimal> [ <period> [ <unsigned_decimal> ] ]
| <period> <unsigned_decimal>
<exists_predicate> ::= EXISTS <table_subquery>
<exponent> ::= <signed_integer>
<extract_coordsys> ::=
COORDSYS <left_paren>
<geometry_value_expression>
<right_paren>
<exact_numeric_type> ::=
SMALLINT
| INTEGER
| BIGINT
<factor> ::= [ <sign> ] <numeric_primary>
<case_folding_function> ::=
LOWER <left_paren> <character_value_expression> <right_paren>
| UPPER <left_paren> <character_value_expression> <right_paren>
<from_clause> ::=
FROM <table_reference>
[ { <comma> <table_reference> }... ]
<general_literal> ::= <character_string_literal>
<general_set_function> ::=
<set_function_type> <left_paren>
[ <set_quantifier> ] <value_expression>
<right_paren>
<geometry_type> ::=
POINT
| CIRCLE
| POLYGON
<geometry_value_expression> ::=
<value_expression_primary> | <geometry_value_function>
<geometry_value_function> ::=
<box>
| <centroid>
| <circle>
| <point>
| <polygon>
| <region>
| <user_defined_function>
<greater_than_operator> ::= ">"
<greater_than_or_equals_operator> ::= ">="
<group_by_clause> ::= GROUP BY <group_by_term_list>
<group_by_term> ::=
<column_reference>
| <value_expression>
<group_by_term_list> ::=
<group_by_term>
[ { <comma> <group_by_term> }... ]
<having_clause> ::= HAVING <search_condition>
<identifier> ::= <regular_identifier> | <delimited_identifier>
<in_predicate> ::=
<value_expression> [ NOT ] IN <in_predicate_value>
<in_predicate_value> ::=
<table_subquery> | <left_paren> <in_value_list> <right_paren>
<in_unit_function> ::=
IN_UNIT <left_paren>
<numeric_value_expression> <comma> <character_string_literal>
<right_paren>
<in_value_list> ::=
<value_expression> { <comma> <value_expression> } ...
<intersects> ::=
INTERSECTS <left_paren>
<geometry_value_expression> <comma> <geometry_value_expression>
<right_paren>
<join_column_list> ::= <column_name_list>
<join_condition> ::= ON <search_condition>
<join_specification> ::= <join_condition> | <named_columns_join>
<join_type> ::=
INNER | <outer_join_type> [ OUTER ]
<joined_table> ::=
<qualified_join> | <left_paren> <joined_table> <right_paren>
<keyword> ::= <SQL_reserved_word> | <ADQL_reserved_word>
<left_bracket> ::= "["
<left_paren> ::= (
<less_than_operator> ::= "<"
<less_than_or_equals_operator> ::= "<="
<like_predicate> ::=
<match_value> [ NOT ] LIKE <pattern>
| <match_value> [ NOT ] ILIKE <pattern>
<mantissa> ::= <exact_numeric_literal>
<match_value> ::= <character_value_expression>
<math_function> ::=
ABS <left_paren> <numeric_value_expression> <right_paren>
| CEILING <left_paren> <numeric_value_expression> <right_paren>
| DEGREES <left_paren> <numeric_value_expression> <right_paren>
| EXP <left_paren> <numeric_value_expression> <right_paren>
| FLOOR <left_paren> <numeric_value_expression> <right_paren>
| LOG <left_paren> <numeric_value_expression> <right_paren>
| LOG10 <left_paren> <numeric_value_expression> <right_paren>
| MOD <left_paren>
<numeric_value_expression> <comma> <numeric_value_expression>
<right_paren>
| PI <left_paren><right_paren>
| POWER <left_paren>
<numeric_value_expression> <comma> <numeric_value_expression>
<right_paren>
| RADIANS <left_paren> <numeric_value_expression> <right_paren>
| RAND <left_paren> [ <unsigned_decimal> ] <right_paren>
| ROUND <left_paren>
<numeric_value_expression> [ <comma> <signed_integer>]
<right_paren>
| SQRT <left_paren> <numeric_value_expression> <right_paren>
| TRUNCATE <left_paren>
<numeric_value_expression>
[ <comma> <signed_integer>]
<right_paren>
<minus_sign> ::= -
<named_columns_join> ::=
USING <left_paren>
<join_column_list>
<right_paren>
<newline> ::=
<non_predicate_geometry_function> ::=
<area>
| <coord1>
| <coord2>
| <distance>
<nondelimiter_token> ::=
<regular_identifier>
| <keyword>
| <unsigned_numeric_literal>
<nondoublequote_character> ::= any character except "
<nonquote_character> ::= any character except '
<not_equals_operator> ::= <not_equals_operator1> | <not_equals_operator2>
<not_equals_operator1> ::= "<>"
<not_equals_operator2> ::= "!="
<null_predicate> ::= <column_reference> IS [ NOT ] NULL
<numeric_geometry_function> ::=
<predicate_geometry_function> | <non_predicate_geometry_function>
<numeric_primary> ::=
<value_expression_primary>
| <numeric_value_function>
<numeric_type> ::=
<exact_numeric_type>
| <approximate_numeric_type>
<numeric_value_expression> ::=
<term>
| <numeric_value_expression> <plus_sign> <term>
| <numeric_value_expression> <minus_sign> <term>
<numeric_value_function> ::=
<trig_function>
| <math_function>
| <in_unit_function>
| <numeric_geometry_function>
| <user_defined_function>
<offset_clause> ::= OFFSET <unsigned_decimal>
<order_by_clause> ::= ORDER BY <order_by_term_list>
<order_by_direction> ::= ASC | DESC
<order_by_expression> ::=
<unsigned_decimal>
| <column_reference>
| <value_expression>
<order_by_term> ::=
<order_by_expression> [ <order_by_direction> ]
<order_by_term_list> ::=
<order_by_term> [ { <comma> <order_by_term> }... ]
<outer_join_type> ::= LEFT | RIGHT | FULL
<pattern> ::= <character_value_expression>
<percent> ::= %
<period> ::= "."
<plus_sign> ::= +
<point> ::=
POINT <left_paren>
[ <coord_sys> <comma> ]
<coordinates>
<right_paren>
<point_value> ::= <point> | <centroid> | <user_defined_function>
<polygon> ::=
POLYGON <left_paren>
[ <coord_sys> <comma> ]
<polygon_vertices>
<right_paren>
<polygon_vertices> ::=
<coordinates>
<comma> <coordinates>
<comma> <coordinates>
{ <comma> <coordinates> }...
|
<coord_value>
<comma> <coord_value>
<comma> <coord_value>
{ <comma> <coord_value> }...
<predicate> ::=
<comparison_predicate>
| <between_predicate>
| <in_predicate>
| <like_predicate>
| <null_predicate>
| <exists_predicate>
<predicate_geometry_function> ::= <contains> | <intersects>
<qualified_join> ::=
<table_reference> [ NATURAL ] [ <join_type> ] JOIN
<table_reference> [ <join_specification> ]
<qualifier> ::= <table_name> | <correlation_name>
<query_expression> ::=
<select_expression>
| <joined_table>
<query_name> ::= <identifier>
<query_set_expression> ::=
<query_set_term>
| <query_set_expression> UNION [ ALL ] <query_set_term>
| <query_set_expression> EXCEPT [ ALL ] <query_set_term>
<query_set_primary> ::=
<select_query>
| <left_paren> <select_expression> <right_paren>
<query_set_term> ::=
<query_set_primary>
| <query_set_term> INTERSECT [ ALL ] <query_set_expression>
<query_specification> ::=
[ <with_clause> ]
<select_expression>
<question_mark> ::= ?
<quote> ::= '
<quote_symbol> ::= <quote> <quote>
<radius> ::= <numeric_value_expression>
<region> ::=
REGION <left_paren> <character_string_literal> <right_paren>
<regular_identifier> ::=
<simple_Latin_letter>...
[ { <digit> | <simple_Latin_letter> | <underscore> }... ]
<right_bracket> ::= "]"
<right_paren> ::= )
<schema_name> ::= [ <catalog_name> <period> ] <unqualified_schema name>
<search_condition> ::=
<boolean_term>
| <search_condition> OR <boolean_term>
<select_expression> ::=
<query_set_expression>
[ <order_by_clause> ]
[ <offset_clause> ]
<select_list> ::=
<asterisk>
| <select_sublist> [ { <comma> <select_sublist> }... ]
<select_query> ::=
SELECT
[ <set_quantifier> ]
[ <set_limit> ]
<select_list>
<from_clause>
[ <where_clause> ]
[ <group_by_clause> ]
[ <having_clause> ]
<select_sublist> ::= <derived_column> | <qualifier> <period> <asterisk>
<semicolon> ::= ;
<set_function_specification> ::=
COUNT <left_paren> <asterisk> <right_paren>
| <general_set_function>
<set_function_type> ::= AVG | MAX | MIN | SUM | COUNT
<set_limit> ::= TOP <unsigned_decimal>
<set_quantifier> ::= DISTINCT | ALL
<sign> ::= <plus_sign> | <minus_sign>
<signed_integer> ::= [ <sign> ] <unsigned_decimal>
<simple_Latin_letter> ::=
<simple_Latin_upper_case_letter>
| <simple_Latin_lower_case_letter>
<simple_Latin_lower_case_letter> ::=
a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z
<simple_Latin_upper_case_letter> ::=
A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z
<solidus> ::= /
<space> ::=
<string_geometry_function> ::= <extract_coordsys>
<string_value_expression> ::= <character_value_expression>
<string_value_function> ::=
<string_geometry_function>
| <case_folding_function>
| <user_defined_function>
<subquery> ::= <left_paren> <query_expression> <right_paren>
<table_name> ::= [ <schema_name> <period> ] <identifier>
<table_reference> ::=
<table_name> [ <correlation_specification> ]
| <derived_table> <correlation_specification>
| <joined_table>
<table_subquery> ::= <subquery>
<term> ::=
<factor>
| <term> <asterisk> <factor>
| <term> <solidus> <factor>
<tilde> ::= ~
<token> ::=
<nondelimiter_token> | <delimiter_token>
<trig_function> ::=
ACOS <left_paren> <numeric_value_expression> <right_paren>
| ASIN <left_paren> <numeric_value_expression> <right_paren>
| ATAN <left_paren> <numeric_value_expression> <right_paren>
| ATAN2 <left_paren>
<numeric_value_expression> <comma> <numeric_value_expression>
<right_paren>
| COS <left_paren> <numeric_value_expression> <right_paren>
| COT <left_paren> <numeric_value_expression> <right_paren>
| SIN <left_paren> <numeric_value_expression> <right_paren>
| TAN <left_paren> <numeric_value_expression> <right_paren>
<underscore> ::= _
<unqualified_schema name> ::= <identifier>
<unsigned_decimal> ::= <digit>...
<unsigned_literal> ::=
<unsigned_numeric_literal>
| <general_literal>
<unsigned_numeric_literal> ::=
<exact_numeric_literal>
| <approximate_numeric_literal>
<unsigned_value_specification> ::= <unsigned_literal>
<user_defined_function> ::=
<user_defined_function_name> <left_paren>
[
<user_defined_function_param>
[
{
<comma> <user_defined_function_param>
}...
]
]
<right_paren>
<user_defined_function_name> ::=
[ <default_function_prefix> ] <regular_identifier>
<user_defined_function_param> ::= <value_expression>
<value_expression> ::=
NULL
| <numeric_value_expression>
| <string_value_expression>
| <geometry_value_expression>
<value_expression_primary> ::=
<unsigned_value_specification>
| <column_reference>
| <set_function_specification>
| <cast_specification>
| <coalesce_expression>
| <left_paren> <value_expression> <right_paren>
<vertical_bar> ::= "|"
<where_clause> ::= WHERE <search_condition>
<with_clause> ::=
WITH <with_query> {, <with_query>} ...
<with_query> :=
<query_name> AS (<select_expression>)
B Language feature support¶
In the XML schema, each group of features is described by a languageFeatures element, which has a type URI that identifies the group, and contains a form element for each individual feature from the group that the service supports.
For example, the following XML fragment describes a service that supports the POINT and CIRCLE functions from the set of geometrical functions,
<languageFeatures type="ivo://ivoa.net/std/tapregext#features-adqlgeo">
<feature>
<form>POINT</form>
</feature>
<feature>
<form>CIRCLE</form>
</feature>
</languageFeatures>
C Changes from previous versions¶
C.1 Between 2.0 and 2.1¶
Applied ADQL-2.0’s errata:
Erratum 1 - add clarification about the usage of white spaces between ADQL terms
Erratum 2 - fix description of
mod(),rand(),round()andtruncate()Erratum 3 - the coordinate system argument of geometrical functions must be a string literal
Erratum 4 - accept
NULLas a valid expression value
General
Added
IVOA architecture diagram
Recommendation to data providers about case folding (see and )
Description of accepted datatypes and their mapping with VOTable: numeric types (including
INTERVAL), character types (includingCLOB), geometrical types,TIMESTAMPandBLOBLanguage feature description (see )
Syntax description for comments inside a query (see )
Description of how to escape single quotes in a string literal (see )
Updated
Description of SQL-92 with links/URLs
BNF grammar syntax fixes (see )
ORDER BYaccepts qualified column names (i.e. prefixed by a table name eventually prefixed by a schema name) (see )ORDER BYaccepts expressions (see )
Optional features
Added
Case sensitive functions and operators:
LOWER(),UPPER()andILIKECommon table expressions (i.e.
WITHkeyword)Set operators:
UNION,INTERSECTandEXCEPTDatatype conversion function:
CAST()Conditional function:
COALESCE()Unit conversion function:
IN_UNIT()OFFSET
Updated
A User Defined Function (UDF) can return a geometrical value
Recommendation for data providers to look at the before implementing a UDF
Geometries
Deprecated
Coordinate system argument in all geometrical functions ; temporarily made optional
COORDSYS()BOX()
Added
Recommendation to use UDFs from the for coordinates conversions
Updated
Clarification about the serialization of geometries in the
SELECTclause: basically, use the syntax described in DALIClarification about coordinate limits
Clarification about geometry types and functions being spherical (see and )
Recommendation of a preferred sky crossmatch and cone search syntax (use of
DISTANCE()instead ofCONTAINS())REGION()argument restricted to a string literal and clarification about its syntaxDescription and BNF of
DISTANCE()(see and )BOX(),CIRCLE(),DISTANCE()andPOLYGON()with a coordinate version of their arguments instead ofPOINT()(example:CIRCLE(0, 0, 1/60.)equivalent toCIRCLE(POINT(0,0), 1/60.)) (see , , , )
2.1.10 Comments¶
As in SQL-92, comments are supported in ADQL. A comment is syntactically legal wherever whitespace is legal and can then stand in for that whitespace. It starts with a double minus sign and ends with the end of line.
Comments are not expected to be interpreted by ADQL parsers.