/[volute]/trunk/projects/dal/ADQL/ADQL.tex
ViewVC logotype

Contents of /trunk/projects/dal/ADQL/ADQL.tex

Parent Directory Parent Directory | Revision Log Revision Log


Revision 4244 - (show annotations)
Mon Sep 11 15:26:18 2017 UTC (3 years, 10 months ago) by yrvafhom
File MIME type: application/x-tex
File size: 98256 byte(s)
Typo fix
1 \documentclass[11pt,a4paper]{ivoa}
2 \input tthdefs
3
4 \usepackage[utf8]{inputenc}
5 \usepackage{tabularx}
6 \usepackage{mathtools}
7
8 \usepackage{listings}
9 \lstloadlanguages{XML,sh}
10 \lstset{flexiblecolumns=true,numberstyle=\small,numbers=left}
11
12 \usepackage{hyperref}
13
14 %\newcommand{\hfoot}[1] {\footnote{See URL: \href{#1} {{\tt #1}} }}
15
16 \title{Astronomical Data Query Language}
17
18 \ivoagroup{Data Access Layer Working Group}
19
20 \author[http://wiki.ivoa.net/twiki/bin/view/IVOA/IvoaVOQL]{The IVOA Virtual Observatory Query Language (VOQL) working group members}
21 \author[http://wiki.ivoa.net/twiki/bin/view/IVOA/IvoaDAL]{The IVOA Data Access Layer (DAL) working group members}
22
23 \editor[http://wiki.ivoa.net/twiki/bin/view/IVOA/DaveMorris]{Dave Morris}
24
25 \previousversion[http://www.ivoa.net/Documents/ADQL/2.0]{ADQL-2.0}
26
27 \begin{document}
28
29 \begin{abstract}
30 This document describes the Astronomical Data Query Language (ADQL).
31 ADQL has been developed based on SQL92.
32 This document describes the subset of the SQL grammar supported by ADQL.
33 Special restrictions and extensions to SQL92 have been defined in order
34 to support generic and astronomy specific operations.
35 \end{abstract}
36
37 \section*{Acknowledgments}
38
39 The authors would like to acknowledge all contributors to this and previous
40 versions of this standard, especially:
41 P. Dowler,
42 J. Lusted,
43 M. A. Nieto-Santisteban,
44 W. O'Mullane,
45 M. Ohishi,
46 I. Ortiz,
47 P. Osuna,
48 Y Shirasaki,
49 and
50 A. Szalay.
51
52 \section*{Conformance-related definitions}
53
54 The words ``MUST'', ``SHALL'', ``SHOULD'', ``MAY'', ``RECOMMENDED'', and
55 ``OPTIONAL'' (in upper or lower case) used in this document are to be
56 interpreted as described in IETF standard, \citet{std:RFC2119}.
57
58 The \emph{Virtual Observatory (VO)} is a general term for a collection of
59 federated resources that can be used to conduct astronomical research,
60 education, and outreach. The \href{http://www.ivoa.net}{International Virtual
61 Observatory Alliance (IVOA)} is a global collaboration of separately funded
62 projects to develop standards and infrastructure that enable VO applications.
63
64 \clearpage
65 \section{Introduction}
66 \label{sec:introduction}
67
68 The Astronomical Data Query Language (ADQL) is the language used by the
69 International Virtual Observatory Alliance (IVOA) to represent astronomy
70 queries posted to VO services. The IVOA has developed several standardized
71 protocols to access astronomical data, e.g., SIAP and SSAP for image and
72 spectral data respectively. These protocols might be satisfied using a single
73 table query. However, different VO services have different needs in terms
74 of query complexity and ADQL arises in this context.
75
76 The ADQL specification makes no distinction between core and advanced or
77 extended functionalities. Hence ADQL has been built according to a single
78 Backus Naur Form (BNF) based language definition. Any service making use of ADQL would
79 then define the level of compliancy to the language. This would allow the
80 notion of core and extension to be service-driven and it would decouple the
81 language from the service specifications.
82
83 ADQL is based on the Structured Query Language (SQL), especially on SQL 92. The
84 VO has a number of tabular data sets and many of them are stored in relational
85 databases, making SQL a convenient access means. A subset of the SQL grammar
86 has been extended to support queries that are specific to astronomy. Similarly
87 to SQL, the ADQL language definition is not semantically safe by design and
88 therefore this specification defines syntactical correctness only. Type safety
89 has been achieved as far as it can be done in SQL. The exact meaning of keywords
90 indicating requirement levels can be found in the References section.
91 %Should this be 'Conformance-related definitions' not 'References' ?
92
93 \clearpage
94 \subsection{Role within the VO Architecture}
95 \label{sec:role}
96
97 \begin{figure}
98 \centering
99 \includegraphics[width=0.9\textwidth]{archdiag.png}
100 \caption{Architecture diagram for this document}
101 \label{fig:archdiag}
102 \end{figure}
103
104 Fig.~\ref{fig:archdiag} shows the role this document plays within the
105 IVOA architecture \citep{note:VOARCH}.
106
107 \clearpage
108 \section{Astronomical Data Query Language (ADQL)}
109 \label{sec:language}
110
111 This section describes the ADQL language specification. We will define in
112 subsequent sections the syntax for the special characters, reserved and non-
113 reserved words, identifiers and literals and then, finally, the syntax for
114 the query expression.
115
116 The formal notation for syntax of computing languages is often expressed
117 in BNF. This syntax is used by popular tools for
118 producing parsers. Appendix A to this document provides the full BNF grammar
119 for ADQL. The following conventions are used through this document:
120
121 \begin{itemize}
122 \item Optional items are enclosed in meta symbols \verb:[: and \verb:]:
123 \item A group of items is enclosed in meta symbols \verb:{: and \verb:}:
124 \item Repetitive item (zero or more times) are followed by \verb:...:
125 \item Terminal symbols are enclosed by \verb:<: and \verb:>:
126 \item Terminals of meta-symbol characters (\verb:=,[,],(,),<,>,*:) are surrounded by quotes (\verb:“:) to distinguish them from meta-symbols
127 \item Case insensitiveness unless otherwise stated
128 \end{itemize}
129
130 \clearpage
131 \subsection{Characters, Keywords, Identifiers and Literals}
132 \subsubsection{Characters}
133 \label{sec:characters}
134
135 The language allows simple Latin letters (lower and upper case, i.e.
136 \verb:{aA-zZ}):, digits (\verb:{0-9}:) and the following special characters:
137
138 \begin{itemize}
139 \item space
140 \item single quote (\verb:’:)
141 \item double quote (\verb:“:)
142 \item percent (\verb:%:)
143 \item left and right parenthesis
144 \item asterisk (\verb:*:)
145 \item plus sign (\verb:+:)
146 \item minus sign (\verb:-:)
147 \item comma (\verb:,:)
148 \item period (\verb:.:)
149 \item solidus (\verb:/:)
150 \item colon (\verb.:.)
151 \item semicolon (\verb:;:)
152 \item less than operator (\verb:<:)
153 \item equals operator (\verb:=:)
154 \item greater than operator (\verb:>:)
155 \item underscore (\verb:_:)
156 \item ampersand (\verb:&:)
157 \item question mark (\verb:?:)
158 \item circumflex (\verb:^:)
159 \item tilde (\verb:~:)
160 \item vertical bar (\verb:|:)
161 \end{itemize}
162
163 \subsubsection{Keywords and Identifiers}
164 \label{sec:keywords}
165
166 Besides the character set, the language provides a list of reserved keywords
167 plus the syntax description for regular identifiers.
168
169 A reserved keyword has a special meaning in ADQL and cannot be used as
170 an identifier unless it is isolated using the ADQL escape syntax defined
171 in section \ref{sec:adql.escape}.
172
173 The AQDL specification extends the list of SQL92 reserved keywords to accommodate
174 those useful for astronomical purposes and/or present in a subset of vendor
175 specific languages only (e.g. \verb:TOP:).
176
177 Although the following lists are all in UPPERCASE, the matching of keywords
178 is case insensitive.
179
180 \subsubsection{SQL reserved keywords}
181 \label{sec:adql.keywords}
182
183 \noindent
184 \texttt{ABSOLUTE,} \texttt{ACTION,} \texttt{ADD,} \texttt{ALL,}
185 \texttt{ALLOCATE,} \texttt{ALTER,} \texttt{AND,} \texttt{ANY,}
186 \texttt{ARE,} \texttt{AS,} \texttt{ASC,} \texttt{ASSERTION,}
187 \texttt{AT,} \texttt{AUTHORIZATION,} \texttt{AVG,} \texttt{BEGIN,}
188 \texttt{BETWEEN,} \texttt{BIT,} \texttt{BIT\_LENGTH,} \texttt{BOTH,}
189 \texttt{BY,} \texttt{CASCADE,} \texttt{CASCADED,} \texttt{CASE,}
190 \texttt{CAST,} \texttt{CATALOG,} \texttt{CHAR,} \texttt{CHARACTER,}
191 \texttt{CHARACTER\_LENGTH,} \texttt{CHAR\_LENGTH,} \texttt{CHECK,}
192 \texttt{CLOSE,} \texttt{COALESCE,} \texttt{COLLATE,}
193 \texttt{COLLATION,} \texttt{COLUMN,} \texttt{COMMIT,}
194 \texttt{CONNECT,} \texttt{CONNECTION,} \texttt{CONSTRAINT,}
195 \texttt{CONSTRAINTS,} \texttt{CONTINUE,} \texttt{CONVERT,}
196 \texttt{CORRESPONDING,} \texttt{COUNT,} \texttt{CREATE,}
197 \texttt{CROSS,} \texttt{CURRENT,} \texttt{CURRENT\_DATE,}
198 \texttt{CURRENT\_TIME,} \texttt{CURRENT\_TIMESTAMP,}
199 \texttt{CURRENT\_USER,} \texttt{CURSOR,} \texttt{DATE,} \texttt{DAY,}
200 \texttt{DEALLOCATE,} \texttt{DECIMAL,} \texttt{DECLARE,}
201 \texttt{DEFAULT,} \texttt{DEFERRABLE,} \texttt{DEFERRED,}
202 \texttt{DELETE,} \texttt{DESC,} \texttt{DESCRIBE,}
203 \texttt{DESCRIPTOR,} \texttt{DIAGNOSTICS,} \texttt{DISCONNECT,}
204 \texttt{DISTINCT,} \texttt{DOMAIN,} \texttt{DOUBLE,} \texttt{DROP,}
205 \texttt{ELSE,} \texttt{END,} \texttt{END-EXEC,} \texttt{ESCAPE,}
206 \texttt{EXCEPT,} \texttt{EXCEPTION,} \texttt{EXEC,} \texttt{EXECUTE,}
207 \texttt{EXISTS,} \texttt{EXTERNAL,} \texttt{EXTRACT,} \texttt{FALSE,}
208 \texttt{FETCH,} \texttt{FIRST,} \texttt{FLOAT,} \texttt{FOR,}
209 \texttt{FOREIGN,} \texttt{FOUND,} \texttt{FROM,} \texttt{FULL,}
210 \texttt{GET,} \texttt{GLOBAL,} \texttt{GO,} \texttt{GOTO,}
211 \texttt{GRANT,} \texttt{GROUP,} \texttt{HAVING,} \texttt{HOUR,}
212 \texttt{IDENTITY,} \texttt{IMMEDIATE,} \texttt{IN,}
213 \texttt{INDICATOR,} \texttt{INITIALLY,} \texttt{INNER,}
214 \texttt{INPUT,} \texttt{INSENSITIVE,} \texttt{INSERT,} \texttt{INT,}
215 \texttt{INTEGER,} \texttt{INTERSECT,} \texttt{INTERVAL,}
216 \texttt{INTO,} \texttt{IS,} \texttt{ISOLATION,} \texttt{JOIN,}
217 \texttt{KEY,} \texttt{LANGUAGE,} \texttt{LAST,} \texttt{LEADING,}
218 \texttt{LEFT,} \texttt{LEVEL,} \texttt{LIKE,} \texttt{LOCAL,}
219 \texttt{LOWER,} \texttt{MATCH,} \texttt{MAX,} \texttt{MIN,}
220 \texttt{MINUTE,} \texttt{MODULE,} \texttt{MONTH,} \texttt{NAMES,}
221 \texttt{NATIONAL,} \texttt{NATURAL,} \texttt{NCHAR,} \texttt{NEXT,}
222 \texttt{NO,} \texttt{NOT,} \texttt{NULL,} \texttt{NULLIF,}
223 \texttt{NUMERIC,} \texttt{OCTET\_LENGTH,} \texttt{OF,} \texttt{ON,}
224 \texttt{ONLY,} \texttt{OPEN,} \texttt{OPTION,} \texttt{OR,}
225 \texttt{ORDER,} \texttt{OUTER,} \texttt{OUTPUT,} \texttt{OVERLAPS,}
226 \texttt{PAD,} \texttt{PARTIAL,} \texttt{POSITION,}
227 \texttt{PRECISION,} \texttt{PREPARE,} \texttt{PRESERVE,}
228 \texttt{PRIMARY,} \texttt{PRIOR,} \texttt{PRIVILEGES,}
229 \texttt{PROCEDURE,} \texttt{PUBLIC,} \texttt{READ,} \texttt{REAL,}
230 \texttt{REFERENCES,} \texttt{RELATIVE,} \texttt{RESTRICT,}
231 \texttt{REVOKE,} \texttt{RIGHT,} \texttt{ROLLBACK,} \texttt{ROWS,}
232 \texttt{SCHEMA,} \texttt{SCROLL,} \texttt{SECOND,} \texttt{SECTION,}
233 \texttt{SELECT,} \texttt{SESSION,} \texttt{SESSION\_USER,}
234 \texttt{SET,} \texttt{SIZE,} \texttt{SMALLINT,} \texttt{SOME,}
235 \texttt{SPACE,} \texttt{SQL,} \texttt{SQLCODE,} \texttt{SQLERROR,}
236 \texttt{SQLSTATE,} \texttt{SUBSTRING,} \texttt{SUM,}
237 \texttt{SYSTEM\_USER,} \texttt{TABLE,} \texttt{TEMPORARY,}
238 \texttt{THEN,} \texttt{TIME,} \texttt{TIMESTAMP,}
239 \texttt{TIMEZONE\_HOUR,} \texttt{TIMEZONE\_MINUTE,} \texttt{TO,}
240 \texttt{TRAILING,} \texttt{TRANSACTION,} \texttt{TRANSLATE,}
241 \texttt{TRANSLATION,} \texttt{TRIM,} \texttt{TRUE,} \texttt{UNION,}
242 \texttt{UNIQUE,} \texttt{UNKNOWN,} \texttt{UPDATE,} \texttt{UPPER,}
243 \texttt{USAGE,} \texttt{USER,} \texttt{USING,} \texttt{VALUE,}
244 \texttt{VALUES,} \texttt{VARCHAR,} \texttt{VARYING,} \texttt{VIEW,}
245 \texttt{WHEN,} \texttt{WHENEVER,} \texttt{WHERE,} \texttt{WITH,}
246 \texttt{WORK,} \texttt{WRITE,} \texttt{YEAR,} \texttt{ZONE}
247
248 \subsubsection{ADQL reserved keywords}
249 \label{sec:adql.keywords}
250
251 \noindent
252 \texttt{ABS,} \texttt{ACOS,} \texttt{ASIN,} \texttt{ATAN,}
253 \texttt{ATAN2,} \texttt{CEILING,} \texttt{COS,} \texttt{DEGREES,}
254 \texttt{EXP,} \texttt{FLOOR,} \texttt{LOG,} \texttt{LOG10,}
255 \texttt{MOD,} \texttt{PI,} \texttt{POWER,} \texttt{RADIANS,}
256 \texttt{RAND,} \texttt{ROUND,} \texttt{SIN,} \texttt{SQRT,}
257 \texttt{TAN,} \texttt{TOP,} \texttt{TRUNCATE}
258 \newline
259 \newline
260 \noindent
261 \texttt{AREA,} \texttt{BOX,} \texttt{CENTROID,} \texttt{CIRCLE,}
262 \texttt{CONTAINS,} \texttt{COORD1,} \texttt{COORD2,}
263 \texttt{COORDSYS,} \texttt{DISTANCE,} \texttt{INTERSECTS,}
264 \texttt{POINT,} \texttt{POLYGON}
265
266 \subsubsection{ADQL deprecated keywords}
267 \label{sec:adql.depwords}
268
269 \noindent
270 \texttt{REGION}
271
272 \subsubsection{Identifiers}
273 \label{sec:adql.identifiers}
274
275 Identifiers MUST begin with a letter
276 \verb:{aA-zZ}:, subsequent characters MAY be letters, underscores or
277 digits \verb:{0-9}: as follows:
278
279 \begin{verbatim}
280 <Latin_letter>... [{ <digit> | <Latin_letter> | <underscore> | }...]
281 \end{verbatim}
282
283 \subsubsection{Escape syntax}
284 \label{sec:adql.escape}
285
286 To address reserved keyword and special character conflicts the ADQL language
287 provides a way to escape a non-compliant identifiers by using the double
288 quote character \verb:": as a delimiter.
289
290 For example, to use the reserved word \verb:size: as a column name
291 it must be isolated using double quotes.
292
293 \begin{itemize}
294 \item \verb:size: -- Invalid column name
295 \item \verb:"size": -- Valid column name
296 \end{itemize}
297
298 \subsubsection{Case sensitivity}
299 \label{sec:adql.case}
300
301 In addition to isolating keyword conflicts and and special characters,
302 the double quote escape syntax also denotes case sensitivity.
303
304 Without double quotes, the following identifiers are all equivalent:
305 \begin{verbatim}
306 alpha == Alpha == ALPHA
307 \end{verbatim}
308
309 When escaped using double quotes, the same set of identifiers are not equivalent:
310 \begin{verbatim}
311 "alpha" != "Alpha" != "ALPHA"
312 \end{verbatim}
313
314 \subsubsection{Literals}
315 \label{sec:literals}
316
317 Finally we define the syntax rules for the different data types: string,
318 numeric and boolean.
319
320 A string literal is a character expression delimited by single quotes.
321
322 \begin{verbatim}
323 <character_string_literal> ::=
324 <quote> [ <character_representation>... ] <quote>
325 \end{verbatim}
326
327 Literal numbers are expressed in BNF as follows:
328
329 \begin{verbatim}
330 <signed_numeric_literal> ::= [<sign>] <unsigned_numeric_literal>
331
332 <unsigned_numeric_literal> ::=
333 <exact_numeric_literal>
334 | <approximate_numeric_literal>
335 | <unsigned_hexadecimal>
336
337 <exact_numeric_literal> ::=
338 <unsigned_decimal> [<period> [<unsigned_decimal>]]
339 | <period><unsigned_decimal>
340
341 <approximate_numeric_literal> ::= <mantissa> E <exponent>
342
343 <mantissa> ::= <exact_numeric_literal>
344
345 <exponent> ::= <signed_decimal>
346
347 <signed_decimal> ::= [<sign>] <unsigned_decimal>
348
349 <unsigned_decimal> ::= <digit>...
350
351 <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
352
353 <sign> ::= <plus_sign> | <minus_sign>
354 \end{verbatim}
355
356 Hexadecimal literals are expressed using the 'C' style notation, e.g. \verb:0xFF:,
357 defined in BNF as follows :
358 \begin{verbatim}
359 <unsigned_hexadecimal> ::= 0x<hex_digit>...
360
361 hex_digit ::= <digit> | a | b | c | d | e | f | A | B | C | D | E | F
362 \end{verbatim}
363
364 Hexadecimal literals are not case sensitive.
365
366 Hexadecimal literals can only be used to create integer data types, SMALLINT, INTEGER and BIGINT.
367
368 Boolean literals are expressed in BNF as follows:
369
370 \begin{verbatim}
371 <boolean_literal> ::= True | False
372 \end{verbatim}
373
374 Boolean literals are not case sensitive.
375
376 Regarding the usage of other data types like datetime and timestamp, ADQL
377 can deal with them similarly to how SQL does: using the string literal
378 construct. As Relation Database Manager Systems (RDBMS) do, a service should
379 be able to implicitly convert strings to internal (datetime or timestamp)
380 form using a variety of techniques, where e.g. ISO 8601 is an acceptable
381 format. Therefore, as with other string representations, it should be up to
382 the service capability to understand such specific formats.
383
384 \clearpage
385 \subsection{Query syntax}
386 \label{sec:syntax}
387
388 A full and complete syntax of the select statement can be found in “Appendix
389 A: BNF Grammar” at the \verb:<query_specification>: construct. A simplified
390 syntax for the \verb:SELECT: statement follows, showing the main constructs for the query
391 specification:
392
393 \begin{verbatim}
394 SELECT
395 [ ALL | DISTINCT ]
396 [ TOP unsigned_decimal ]
397 {
398 * |
399 { value_expression [ [AS] column_name ] }, ...
400 }
401 FROM {
402 {
403 table_name [ [AS] identifier ] |
404 ( SELECT ....) [ [AS] identifier ] |
405 table_name [NATURAL]
406 [ INNER | { LEFT | RIGHT | FULL [OUTER] } ]
407 JOIN table_name
408 [ON search_condition | USING ( column_name,...) ]
409 },
410 ...
411 }
412
413 [ WHERE search_condition ]
414 [ GROUP BY column_name, ... ]
415 [ HAVING search_condition ]
416 [ ORDER BY
417 { column_name | unsigned_decimal } [ ASC | DESC],
418 ...
419 ]
420 [ OFFSET unsigned_decimal ]
421 \end{verbatim}
422
423 The SELECT statement defines a query to some derived table(s) specified
424 in the FROM clause. As a result of this query, a subset of the table(s)
425 is returned.
426 The order of the rows MAY be arbitrary unless an ORDER BY clause is specified.
427 A TOP clause MAY be specified to limit the number of rows returned.
428 An OFFSET clause MAY be specified to skip a number of rows at the start
429 of the results.
430 If OFFSET is used in combination with a TOP clause then OFFSET is applied
431 first, then the result set is limited by TOP (see \ref{sec:offset}).
432
433 The order of the columns to return SHALL be the same as the
434 order specified in the selection list, or the order defined in the original
435 table if asterisk is specified. The selection list MAY include any numeric,
436 string or geometry value expression.
437 In the following sections some constructs requiring further description
438 are presented.
439
440 \subsubsection{Table subqueries and Joins}
441 \label{sec:subqueries}
442
443 Table subqueries are present and can be used by some existing predicates
444 within the search condition (IN and BETWEEN most likely) or as an artifact
445 of building derived tables. Among the different types of join, ADQL supports
446 INNER and OUTER (LEFT, RIGHT and FULL) joins. If none is specified, the
447 default is INNER. All of these can be NATURAL or not. The join condition
448 does not support embedded sub joins.
449
450 \subsubsection{Search condition}
451 \label{sec:search}
452
453 The search condition can be part of several other clauses: JOIN, HAVING and,
454 obviously, WHERE. Standard logical operators are present in its description
455 (AND, OR and NOT). Five different types of predicates are present in which
456 different types of reserved keywords or characters are used:
457
458 \begin{itemize}
459 \item Standard comparison operators: \verb:=:, \verb:!=:, \verb:<>:, \verb:<:, \verb:>:, \verb:<=:, \verb:>=:
460 \item \verb:BETWEEN:
461 \item \verb:LIKE:
462 \item \verb:NULL:
463 \item \verb:EXISTS:
464 \end{itemize}
465
466 In addition, some service implementations may also support the optional
467 ILIKE case-insensitive string comparison operator, defined in section \ref{sec:string.functions.ilike}.
468
469 \begin{itemize}
470 \item \verb:ILIKE:
471 \end{itemize}
472
473 \clearpage
474 \subsection{Mathematical and Trigonometrical Functions}
475 \label{sec:math.functions}
476
477 ADQL declares a list of reserved keywords (Section \ref{sec:keywords}) which include
478 the mathematical and trigonometrical function names. Their syntax,
479 usage and description are detailed in the following tables:
480
481 \begin{table}[thm]\footnotesize
482 \begin{tabular}{|p{0.20\textwidth}|p{0.125\textwidth}|p{0.125\textwidth}|p{0.55\textwidth}|}
483 \hline
484
485 \hline
486 \textbf{Name} &
487 \textbf{Argument \newline data type} &
488 \textbf{Return \newline data type} &
489 \textbf{Description}
490 \tabularnewline
491
492 \hline
493 abs(x) &
494 double&double &
495 Returns the absolute value of x.
496 \tabularnewline
497
498 \hline
499 ceiling(x) &
500 double&double &
501 Returns the smallest double value that is not less than the argument x and is equal to a mathematical integer.
502 \tabularnewline
503
504 \hline
505 degrees(x) &
506 double &
507 double &
508 Converts an angle to degrees. Argument x must be in radians.
509 \tabularnewline
510
511 \hline
512 exp(x) &
513 double &
514 double &
515 Returns Euler’s number e raised to the power of x.
516 \tabularnewline
517
518 \hline
519 floor(x) &
520 double &
521 double &
522 Returns the largest double value that is not greater than the argument x and is equal to a mathematical integer.
523 \tabularnewline
524
525 \hline
526 log(x) &
527 double &
528 double &
529 Returns the natural logarithm (base e) of a double value. Value x must be greater than zero.
530 \tabularnewline
531
532 \hline
533 log10(x) &
534 double &
535 double &
536 Returns the base 10 logarithm of a double value. Value x must be greater than zero.
537 \tabularnewline
538
539 \hline
540 mod(x, y) &
541 double &
542 double &
543 Returns the remainder of x/y.
544 \tabularnewline
545
546 \hline
547 pi() &
548 n/a &
549 double &
550 The \(\pi\) constant.
551 \tabularnewline
552
553 \hline
554 power(x, y) &
555 x double \newline y double &
556 double &
557 Returns the value of the first argument raised to the power of the second argument.
558 \tabularnewline
559
560 \hline
561 radians(x) &
562 double &
563 double &
564 Converts an angle to radians. Argument x must be in degrees.
565 \tabularnewline
566
567 \hline
568 sqrt(x) &
569 double &
570 double &
571 Returns the positive square root of a double value.
572 \tabularnewline
573
574 \hline
575 rand(x) &
576 integer &
577 double &
578 Returns a random value between 0.0 and 1.0, where x is a seed value.
579 \tabularnewline
580
581 \hline
582 round(x, n) &
583 x double \newline n integer &
584 double &
585 Rounds double value x to n number of decimal places, with the default being to round to the nearest integer.
586 To round to the left of the decimal point, a negative number should be provided.
587 \tabularnewline
588
589 \hline
590 truncate(x, n) &
591 x double \newline n integer &
592 double &
593 Returns the result of truncating the argument x to n decimal places.
594 \tabularnewline
595
596 \hline
597 \end{tabular}
598 \caption{Mathematical functions}
599 \label{table:math.functions.table}
600 \end{table}
601
602 \begin{table}[thm]\footnotesize
603 \begin{tabular}{|p{0.20\textwidth}|p{0.125\textwidth}|p{0.125\textwidth}|p{0.55\textwidth}|}
604 \hline
605
606 \hline
607 \textbf{Name} &
608 \textbf{Argument \newline data type} &
609 \textbf{Return \newline data type} &
610 \textbf{Description}
611 \tabularnewline
612
613 \hline
614 acos(x) &
615 double &
616 double &
617 Returns the arc cosine of an angle, in the range of 0 through \(\pi\) radians. Absolute value of x must be lower or equal than 1.0.
618 \tabularnewline
619
620 \hline
621 asin(x) &
622 double &
623 double &
624 Returns the arc sine of an angle, in the range of -\(\pi\)/2 through \(\pi\)/2 radians. Absolute value of x must be and lower or equal than 1.0.
625 \tabularnewline
626
627 \hline
628 atan(x) &
629 double &
630 double &
631 Returns the arc tangent of an angle, in the range of -\(\pi\)/2 through \(\pi\)/2 radians.
632 \tabularnewline
633
634 \hline
635 atan2(y,x) &
636 double &
637 double &
638 Converts rectangular coordinates x,y to polar angle. It computes the arc tangent of y/x in the range of –\(\pi\) through \(\pi\) radians.
639 \tabularnewline
640
641 \hline
642 cos(x) &
643 double &
644 double &
645 Returns the cosine of an angle, in the range of -1.0 through 1.0. Argument x must be in radians.
646 \tabularnewline
647
648 \hline
649 sin(x) &
650 double &
651 double &
652 Returns the sine of an angle, in the range of -1.0 through 1.0. Argument x must be in radians.
653 \tabularnewline
654
655 \hline
656 tan(x) &
657 double &
658 double &
659 Returns the tangent of an angle. Argument x must be in radians.
660 \tabularnewline
661
662 \hline
663 \end{tabular}
664 \caption{Trigonometrical functions}
665 \label{table:trig.functions.table}
666 \end{table}
667
668 \clearpage
669 \section{ADQL Type System}
670 \label{sec:types}
671
672 ADQL defines no data definition language (DDL).
673 It is assumed that table definition and data ingestion are performed in
674 the underlying database's native language and type system.
675
676 However, column metadata needs to give column types in order to allow the
677 construction of queries that are both syntactically and semantically correct.
678 Examples of such metadata includes VODataService's \verb:TAPType:
679 (VODataService-1.1, \citet{std:VODS11}) or TAP's \verb:TAP_SCHEMA: (TAP-1.0, \citet{std:TAP}).
680
681 Services SHOULD, if at all possible, try to express their column metadata in
682 these terms even if the underlying database employs different types.
683 Services SHOULD also use the following mapping when interfacing to user data,
684 either by serializing result sets into VOTables or by ingesting user-provided
685 VOTables into ADQL-visible tables.
686 Where non-ADQL types are employed in the underlying database, implementors
687 SHOULD make sure that all operations that are possible with the recommended
688 ADQL type are also possible with the type used in the backend engine.
689 For instance, the ADQL string concatenation operator \verb:||: should be
690 applicable to all columns resulting from VOTable char-typed columns.
691
692 \begin{table}[thm]\footnotesize
693 \begin{tabular}{|p{0.15\textwidth}|p{0.15\textwidth}|p{0.25\textwidth}|p{0.30\textwidth}|}
694 \hline
695
696 \hline
697 \multicolumn{3}{|c|}{\textbf{VOTable}} &
698 \multicolumn{1}{|c|}{\textbf{ADQL}}
699 \tabularnewline
700
701 \hline
702 \textbf{datatype} &
703 \textbf{arraysize} &
704 \textbf{xtype} &
705 \textbf{type}
706 \tabularnewline
707
708 \hline
709 boolean &
710 1 &
711 - &
712 BOOLEAN
713 \tabularnewline
714
715 \hline
716 short &
717 1 &
718 - &
719 SMALLINT
720 \tabularnewline
721
722 \hline
723 int &
724 1 &
725 - &
726 INTEGER
727 \tabularnewline
728
729 \hline
730 long &
731 1 &
732 - &
733 BIGINT
734 \tabularnewline
735
736 \hline
737 float &
738 1 &
739 - &
740 REAL
741 \tabularnewline
742
743 \hline
744 double &
745 1 &
746 - &
747 DOUBLE
748 \tabularnewline
749
750 \hline
751 (numeric) &
752 > 1 &
753 - &
754 implementation defined
755 \tabularnewline
756
757 \hline
758 char &
759 1 &
760 - &
761 CHAR(1)
762 \tabularnewline
763
764 \hline
765 char &
766 n &
767 - &
768 CHAR(n)
769 \tabularnewline
770
771 \hline
772 char &
773 n* &
774 - &
775 VARCHAR(n)
776 \tabularnewline
777
778 \hline
779 unsignedByte &
780 n &
781 - &
782 BINARY(n)
783 \tabularnewline
784
785 \hline
786 unsignedByte &
787 n* &
788 - &
789 VARBINARY(n)
790 \tabularnewline
791
792 \hline
793 unsignedByte &
794 n, *, n* &
795 adql:BLOB &
796 BLOB
797 \tabularnewline
798
799 \hline
800 char &
801 n, *, n* &
802 adql:CLOB &
803 CLOB
804 \tabularnewline
805
806 \hline
807 char &
808 n, *, n* &
809 adql:TIMESTAMP &
810 TIMESTAMP
811 \tabularnewline
812
813 \hline
814 char &
815 n, *, n* &
816 adql:POINT &
817 POINT
818 \tabularnewline
819
820 \hline
821 char &
822 n, *, n* &
823 adql:REGION &
824 REGION
825 \tabularnewline
826
827 \hline
828 \end{tabular}
829 \caption{VOTable/ADQL type mapping}
830 \label{table:adql.votable.type.map}
831 \end{table}
832
833 \textit{"Implementation defined"} in the above table means that an
834 implementation is free to reject attempts to (de-)serialize values in
835 these types.
836 They are to be considered unsupported by ADQL, and the language provides
837 no means to manipulate \textit{"native"} representations of them.
838
839 References to REGION-typed columns must be valid wherever the ADQL
840 \textit{region} nonterminal is allowed. References to POINT-typed columns
841 must be valid wherever the ADQL \textit{point} nonterminal is allowed.
842
843 Comparing the equality of a boolean value or expression with another
844 boolean returns a boolean result.
845
846 When comparing the size of a boolean with another boolean, the value
847 True is greater than the value False.
848
849 Unless explicitly stated, the result of any other operation on boolean
850 values is undefined.
851
852 \clearpage
853 \section{Optional components}
854 \label{sec:optional}
855
856 In addition to the core components, the ADQL language also includes support
857 for optional features and functions.
858
859 The following sections define the optional features that are part of the
860 the ADQL language, but are not required in order to meet the standard for
861 a basic ADQL service.
862
863 It is up to each service implementation to declare which optional or
864 additional features it supports.
865
866 If a service does not declare support for an optional or additional feature,
867 then a client SHOULD NOT assume that the service supports that feature,
868 and SHOULD NOT make use of that feature in any ADQL queries that it sends.
869
870 \subsection{Service capabilities}
871 \label{sec:capabilities}
872
873 The TAPRegExt-1.0 standard \citep{std:TAPREGEXT} defines an XML schema that a service SHOULD
874 use to declare which optional or additional features it supports.
875
876 In general, each group of langauge features is identified by a \verb:type:
877 URI, and each individual feature within the group is identified by the
878 feature name.
879
880 Appendix \ref{sec:features} contains examples of how to declare support
881 for each of the langauge features defined in this document using the
882 TAPRegExt XML schema.
883
884 For full details on the XML schema and how it can be used, please refer to
885 the TAPRegExt \citep{std:TAPREGEXT} standard.
886
887 \subsection{Geometrical Functions}
888 \label{sec:geom.functions}
889 \subsubsection{Overview}
890 \label{sec:geom.functions.overview}
891
892 In addition to the mathematical functions, ADQL provides a set of geometrical
893 functions to enhance the astronomical usage of the language.
894
895 \begin{itemize}
896 \item AREA
897 \item BOX
898 \item CENTROID
899 \item CIRCLE
900 \item CONTAINS
901 \item COORD1
902 \item COORD2
903 \item COORDSYS
904 \item DISTANCE
905 \item INTERSECTS
906 \item POINT
907 \item POLYGON
908 \item REGION
909 \end{itemize}
910
911 Special attention has to be paid to the REGION function. As can be seen more
912 in detail in Section \ref{sec:geom.functions.region}, this construct is a general purpose function and
913 it takes a string value expression as argument. The format of the string is
914 to be specified by a service that accepts ADQL by referring to a standard
915 format. Currently STC/s is the only standardized string
916 representation a service can declare.
917 % STC-reference 'Currently STC/s (See [3] and [4])'
918
919 As can also be seen in the following sections, all these functions
920 have arguments being a geometrical, a string and/or a numerical value
921 expression. When these values represent spherical coordinates the units MUST
922 be in degrees (square degrees for area). If the cartesian coordinate system
923 is used, the vector coordinates MUST be normalized.
924
925 Regarding the legal ranges, for spherical coordinates, these SHOULD be [0, 360]
926 and [-90, 90]. In a cartesian coordinate system, there are no inherent limits
927 apart from the already mentioned constraint that vectors should be normalized. It remains
928 up to the service making use of ADQL to define the errors that should be raised
929 when using values outside these ranges.
930
931 For historical reasons, the geometry constructors (BOX, CIRCLE, POINT,
932 POLYGON) require a string-valued first argument. It was intended to carry
933 information on a reference system or other coordinate system metadata.
934 As of this version of the specification (2.1), this parameter has been
935 marked as deprecated. Services are permitted to ignore this parameter and
936 clients are advised to pass an empty string here. Future versions of this
937 specification may remove this parameter from the listed functions.
938
939 Generally speaking, all these geometrical functions cover three different
940 topics: data types, predicates and utility calculations. Each of these are
941 covered below.
942
943 \subsubsection{Data Type Functions}
944 \label{sec:geom.functions.type}
945
946 Certain functions represent geometry data types. These data types are BOX,
947 CENTROID, CIRCLE, POINT and POLYGON together with the generalized REGION data
948 type. The functions are similarly named and return a variable length binary
949 value. The semantics of these data types are based on the corresponding
950 concepts from the STC data model.
951 % STC-reference 'STC data model (See [3])'
952
953 Geometry data types are centered around the BNF construct
954 \verb:<value_expression>: which is central to data types within SQL.
955
956 \begin{verbatim}
957 <value_expression> ::=
958 <numeric_value_expression>
959 | <string_value_expression>
960 | <boolean_value_expression>
961 | <geometry_value_expression>
962 \end{verbatim}
963
964 A \verb:<geometry_value_expression>: does not simply cover data type functions
965 (POINT, CIRCLE etc) but must also allow for user defined functions and
966 column values where a geometry data type is stored in a column.
967
968 Therefore, \verb:<geometry_value_expression>: is expanded as:
969
970 \begin{verbatim}
971 <geometry_value_expression> ::=
972 <value_expression_primary>
973 | <geometry_value_function>
974 \end{verbatim}
975
976 , where
977
978 \begin{verbatim}
979 <geometry_value_function> ::=
980 <box>
981 | <centroid>
982 | <circle>
983 | <point>
984 | <polygon>
985 | <region>
986 | <user_defined_function>
987 \end{verbatim}
988
989 and \verb:<value_expression_primary>: makes possible to use a column reference.
990
991 \subsubsection{Predicate Functions}
992 \label{sec:geom.functions.predicate}
993
994 Functions CONTAINS and INTERSECTS each accept two geometry data types
995 and return 1 or 0 according to whether the relevant verb (e.g.: "contains") is
996 satisfied against the two input geometries; 1 represents true and 0 represents
997 false. Each of these functions can be assembled into a predicate:
998
999 \begin{verbatim}
1000 SELECT * FROM SDSS as s WHERE CONTAINS(POINT(...), CIRCLE(...)) = 1
1001 \end{verbatim}
1002
1003 , where the ... would represent the constituent parts of a CIRCLE and POINT
1004 geometry.
1005
1006 One would expect later additions to ADQL to add to this range of functions. For
1007 example: equals, disjoint, touches, crosses, within, overlaps and relate
1008 are possibilities.
1009
1010 \subsubsection{Utility Functions}
1011 \label{sec:geom.functions.utility}
1012
1013 Function COORDSYS extracts the coordinate system string from a given
1014 geometry. To do so it accepts a geometry expression and returns a calculated
1015 string value.
1016
1017 This function has been included as a string value function because it
1018 returns a simple string value. Hence:
1019
1020 \begin{verbatim}
1021 <string_value_function> :: =
1022 <string_geometry_function> | <user_defined_function>
1023
1024 <string_geometry_function> ::= <extract_coordsys>
1025
1026 <extract_coordsys> ::=
1027 COORDSYS <left_paren> <geometry_value_expression> <right_paren>
1028 \end{verbatim}
1029
1030 Note - as of this version of the specification (2.1), the COORDSYS function has
1031 been marked as deprecated. This function may be removed in future versions
1032 of this specification.
1033
1034 Functions like AREA, COORD1, COORD2 and DISTANCE accept a geometry and
1035 return a calculated numeric value.
1036
1037 The specification defines two versions of the DISTANCE function,
1038 one that accepts accept two geometries, and one that accepts four
1039 separate numeric values, both forms return a numeric value.
1040
1041 The Predicate and most of the Utility functions have been included as numeric
1042 value functions because they return simple numeric values. Thus:
1043
1044 \begin{verbatim}
1045 <numeric_value_function> ::=
1046 <trig_function>
1047 | <math_function>
1048 | <numeric_geometry_function>
1049 | <user_defined_function>
1050 \end{verbatim}
1051
1052 where
1053
1054 \begin{verbatim}
1055 <numeric_geometry_function> ::=
1056 <predicate_geometry_function>
1057 | <non_predicate_geometry_function>
1058 \end{verbatim}
1059
1060 and
1061
1062 \begin{verbatim}
1063 <non_predicate_geometry_function> ::=
1064 AREA <left_paren> <geometry_value_expression> <right_paren>
1065 | COORD1 <left_paren> <coord_value> <right_paren>
1066 | COORD2 <left_paren> <coord_value> <right_paren>
1067 | DISTANCE <left_paren>
1068 <coord_value> <comma>
1069 <coord_value>
1070 <right_paren>
1071 | DISTANCE <left_paren>
1072 <numeric_value_expression> <comma>
1073 <numeric_value_expression> <comma>
1074 <numeric_value_expression> <comma>
1075 <numeric_value_expression>
1076 <right_paren>
1077 \end{verbatim}
1078
1079 and
1080
1081 \begin{verbatim}
1082 <predicate_geometry_function> ::= <contains> | <intersects>
1083 \end{verbatim}
1084
1085 %\subsubsection{Function definitions}
1086 \clearpage
1087 \label{sec:geom.functions.definitions}
1088
1089 The following sections provide a detailed description for each geometrical
1090 function. In each case, the functionality and usage is described rather
1091 than going into the BNF grammar details as above.
1092
1093 \subsubsection{AREA}
1094 \label{sec:geom.functions.area}
1095 {\footnotesize Language feature :}\\
1096 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1097 {\footnotesize \verb|name: AREA|}\\
1098
1099 This function computes the area, in square degrees, of a given geometry.
1100
1101 For example, the area of a circle of one degree radius centered on a position
1102 of (25.4, -20.0) degrees would be written as follows:
1103
1104 \begin{verbatim}
1105 AREA(CIRCLE(‘’, 25.4, -20.0, 1))
1106 \end{verbatim}
1107
1108 The coordinates of the circle center could also be directly derived from
1109 either a POINT function (See \ref{sec:geom.functions.point}) or
1110 the coordinate’s column references:
1111
1112 \begin{verbatim}
1113 AREA(CIRCLE(‘’, t.ra, t.dec, 1))
1114 \end{verbatim}
1115
1116 , where \textit{t} would be the table and \textit{ra}, \textit{dec} the
1117 column references for the circle center.
1118
1119 Inappropriate geometries for this construct (e.g. POINT) SHOULD either return
1120 zero or throw an error message, the later to be defined by the service making
1121 use of ADQL.
1122
1123 \subsubsection{BOX}
1124 \label{sec:geom.functions.box}
1125 {\footnotesize Language feature :}\\
1126 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1127 {\footnotesize \verb|name: BOX|}\\
1128
1129 This function expresses a box on the sky. A box is a special case of Polygon,
1130 defined purely for convenience, and it corresponds semantically to the STC Box
1131 region. It is specified by a center position and size
1132 % STC-reference 'region ([3], Section 4.5.1.5)'
1133 (in both coordinates) defining a cross centered on the center position and
1134 with arms extending, parallel to the coordinate axes at the center position,
1135 for half the respective sizes on either side. The box’s sides are line
1136 segments or great circles intersecting the arms of the cross in its end
1137 points at right angles with the arms.
1138
1139 The function arguments specify the coordinate system, the center position
1140 and both the width and height (arms) values, where:
1141
1142 \begin{itemize}
1143 \item the coordinate system is a string value expression as defined in Section \ref{sec:geom.functions.overview}.
1144 \item the center position is a comma separated numeric duple, with units and legal ranges as defined in Section \ref{sec:geom.functions.overview}.
1145 \item and the arms are numeric value expressions in degrees.
1146 \end{itemize}
1147
1148 For example, a function expressing a box of ten degrees centered on a position
1149 (25.4, -20.0) in degrees would be written as follows:
1150
1151 \begin{verbatim}
1152 BOX(‘’, 25.4, -20.0, 10, 10)
1153 \end{verbatim}
1154
1155 As another example, the coordinates of the center position could also be
1156 extracted from either a POINT function (See \ref{sec:geom.functions.point})
1157 or the coordinate’s column references:
1158
1159 \begin{verbatim}
1160 BOX(‘’, t.ra, t.dec, 10, 10)
1161 \end{verbatim}
1162
1163 , where \textit{t} would be the table and \textit{ra}, \textit{dec} the
1164 column references for the center position.
1165
1166 To see what this function would return when listed in the select clause,
1167 see Section \ref{sec:geom.select}.
1168
1169 \subsubsection{CENTROID}
1170 \label{sec:geom.functions.centroid}
1171 {\footnotesize Language feature :}\\
1172 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1173 {\footnotesize \verb|name: CENTROID|}\\
1174
1175 This function computes the centroid of a given geometry and returns a POINT.
1176
1177 For example, the centroid of a circle of one degree radius centered in a
1178 position of (25.4, -20.0) degrees would be written as follows :
1179
1180 \begin{verbatim}
1181 CENTROID(CIRCLE (‘’, 25.4, -20.0, 1))
1182 \end{verbatim}
1183
1184 \subsubsection{CIRCLE}
1185 \label{sec:geom.functions.circle}
1186 {\footnotesize Language feature :}\\
1187 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1188 {\footnotesize \verb|name: CIRCLE|}\\
1189
1190 This function expresses a circular region on the sky (a cone in space) and
1191 corresponds semantically to the STC Circle region. The
1192 function arguments specify the coordinate system, the center position,
1193 and the radius, where:
1194 % STC-reference STC Circle region ([3], Section 4.5.1.2)
1195
1196 \begin{itemize}
1197 \item the coordinate system is a string value expression as defined in Section \ref{sec:geom.functions.overview}.
1198 \item the center position is a comma separated numeric duple, with units and legal ranges as defined in Section \ref{sec:geom.functions.overview}.
1199 \item and the radius is a numeric value expression in degrees.
1200 \end{itemize}
1201
1202 For example, a function expressing a circle of one degree radius centered on a
1203 position of (25.4, -20.0) degrees would be written as follows:
1204
1205 \begin{verbatim}
1206 CIRCLE(‘’, 25.4, -20.0, 1)
1207 \end{verbatim}
1208
1209 The coordinates of the center position could also be derived from either a
1210 POINT function (See \ref{sec:geom.functions.point}) or the coordinate’s column references:
1211
1212 \begin{verbatim}
1213 CIRCLE(‘’, t.ra, t.dec, 1)
1214 \end{verbatim}
1215
1216 , where \textit{t} would be the table and \textit{ra}, \textit{dec} the
1217 column references for the center position.
1218
1219 To see what this function would return when listed in the select clause, see
1220 Section \ref{sec:geom.select}.
1221
1222 \subsubsection{CONTAINS}
1223 \label{sec:geom.functions.contains}
1224 {\footnotesize Language feature :}\\
1225 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1226 {\footnotesize \verb|name: CONTAINS|}\\
1227
1228 This numeric function determines if a geometry is wholly contained within
1229 another. This is most commonly used to express the "point-in-shape" condition.
1230
1231 For example, to determine if a point with right ascension of 25 degrees
1232 and declination of -19.5 degrees is within a circle of one degree radius
1233 centered in a position of (25.4, -20.0) degrees and defined according to the
1234 same coordinate system, we would make use of the CONTAINS function as follows:
1235
1236 \begin{verbatim}
1237 CONTAINS(
1238 POINT(‘’, 25.0,-19.5),
1239 CIRCLE(‘’, 25.4, -20.0, 1)
1240 )
1241 \end{verbatim}
1242
1243 , where the CONTAINS function returns 1 (true) if the first argument is in
1244 or on the boundary of the circle and 0 (false) otherwise. Thus, contains is
1245 not symmetric in the meaning of the arguments. When used in the WHERE clause
1246 of a query, the value must be compared to 0 or 1 to form an SQL predicate:
1247
1248 \begin{verbatim}
1249 CONTAINS(
1250 POINT(‘’, 25.0,-19.5),
1251 CIRCLE(‘’, 25.4, -20.0, 1)
1252 ) = 1
1253 \end{verbatim}
1254
1255 for "does contain" and
1256
1257 \begin{verbatim}
1258 CONTAINS(
1259 POINT(‘’, 25.0,-19.5),
1260 CIRCLE(‘’, 25.4, -20.0, 1)
1261 ) = 0
1262 \end{verbatim}
1263
1264 for "does not contain".
1265
1266 The arguments to the CONTAINS function can be (literal) values created
1267 from the geometry types or they can be single column names or aliases (for
1268 geometry stored in a database table). Since the two argument geometries may
1269 be expressed in different coordinate systems, the function is responsible
1270 for converting one (or both). If it cannot do so, it SHOULD throw an error
1271 message, to be defined by the service making use of ADQL.
1272
1273 \subsubsection{COORD1}
1274 \label{sec:geom.functions.coord1}
1275 {\footnotesize Language feature :}\\
1276 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1277 {\footnotesize \verb|name: COORD1|}\\
1278
1279 This function extracts the first coordinate value, in degrees, of a given
1280 POINT (See \ref{sec:geom.functions.point}) or column reference.
1281
1282 For example, the right ascension of a point with position (25, -19.5) in
1283 degrees would be obtained using the following expression:
1284
1285 \begin{verbatim}
1286 COORD1(POINT(‘’, 25.0,-19.5))
1287 \end{verbatim}
1288
1289 , being the result a numeric value of 25.0 degrees. The first coordinate
1290 could also be derived directly from a column reference as follows:
1291
1292 \begin{verbatim}
1293 COORD1(t.point)
1294 \end{verbatim}
1295
1296 , where \textit{t} is the table and \textit{point} the column reference for
1297 the POINT geometry stored in the database table.
1298
1299 \subsubsection{COORD2}
1300 \label{sec:geom.functions.coord2}
1301 {\footnotesize Language feature :}\\
1302 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1303 {\footnotesize \verb|name: COORD2|}\\
1304
1305 This function extracts the second coordinate value, in degrees, of a given
1306 POINT (See \ref{sec:geom.functions.point}) or column reference.
1307
1308 For example, the declination of a point with position (25, -19.5) in degrees,
1309 would be obtained using the following expression:
1310
1311 \begin{verbatim}
1312 COORD2(POINT(‘’, 25.0,-19.5))
1313 \end{verbatim}
1314
1315 , being the result a numeric value of -19.5 degrees. The second coordinate
1316 could also be derived directly from a column reference as follows:
1317
1318 \begin{verbatim}
1319 COORD2(t.point)
1320 \end{verbatim}
1321
1322 , where \textit{t} is the table and \textit{point} the column reference for
1323 the POINT geometry stored in the database table.
1324
1325 \subsubsection{COORDSYS}
1326 \label{sec:geom.functions.coordsys}
1327 {\footnotesize Language feature :}\\
1328 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1329 {\footnotesize \verb|name: COORDSYS|}\\
1330
1331 Note - as of this version of the specification (2.1), the COORDSYS function has
1332 been marked as deprecated. This function may be removed in future versions
1333 of this specification.
1334
1335 This function extracts the coordinate system string value from a given
1336 geometry.
1337
1338 As described in Section \ref{sec:geom.functions.overview}, the allowed return values must be defined
1339 by any service making use of ADQL, and a list of standard coordinate system
1340 literals can be found in the STC specification.
1341 % STC-reference 'STC specification [3]'
1342
1343 For example, a function extracting the coordinate system of a point with
1344 position (25, -19.5) in degrees according to the ICRS coordinate system with
1345 GEOCENTER reference position, would be written as follows:
1346
1347 \begin{verbatim}
1348 COORDSYS(POINT(‘ICRS GEOCENTER’, 25.0,-19.5))
1349 \end{verbatim}
1350
1351 , returning the ‘ICRS GEOCENTER’ string literal. As other samples above,
1352 the coordinate system could also be derived from a column referencing any
1353 other geometry data type:
1354
1355 \begin{verbatim}
1356 COORDSYS(t.circle)
1357 \end{verbatim}
1358
1359 , where \textit{t} is the table and \textit{circle} the column reference
1360 for the CIRCLE geometry stored in the database table.
1361
1362 \subsubsection{DISTANCE}
1363 \label{sec:geom.functions.distance}
1364 {\footnotesize Language feature :}\\
1365 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1366 {\footnotesize \verb|name: DISTANCE|}\\
1367
1368 The DISTANCE function computes the arc length along a great circle between two
1369 points and returns a numeric value expression in degrees.
1370
1371 The specification defines two versions of the DISTANCE function, one that
1372 accepts two geometries, and one that accepts four separate numeric
1373 values.
1374
1375 If an ADQL service implementation declares support for DISTANCE,
1376 then it must implement both the two parameter and four parameter
1377 forms of the function.
1378
1379 For example, a function computing the distance between two points of
1380 coordinates (25,-19.5) and (25.4,-20) would be written as follows:
1381
1382 \begin{verbatim}
1383 DISTANCE(
1384 POINT(‘’, 25.0, -19.5),
1385 POINT(‘’, 25.4, -20.0)
1386 )
1387 \end{verbatim}
1388
1389 , where all numeric values and the returned arc-length are in degrees.
1390
1391 The equivalent call to the four parameter form of the function would be:
1392
1393 \begin{verbatim}
1394 DISTANCE(
1395 25.0,
1396 -19.5,
1397 25.4,
1398 -20.0
1399 )
1400 \end{verbatim}
1401
1402 The distance between two points could also be derived from two columns
1403 referencing POINT geometries stored in the database tables as follows:
1404
1405 \begin{verbatim}
1406 DISTANCE(
1407 t.p1,
1408 t.p2
1409 )
1410 \end{verbatim}
1411
1412 , where \textit{t} would be the table and \textit{p1}, \textit{p2} the column
1413 references for the POINT geometries.
1414
1415 If the two arguments to the two parameter form are expressed in different
1416 coordinate systems, the function is responsible for converting one (or both).
1417 If it cannot do so, it SHOULD throw an error message, to be defined by
1418 the service making use of ADQL.
1419
1420 It is assumed that the arguments for the four parameter form all use
1421 the same coordinate system.
1422
1423 \subsubsection{INTERSECTS}
1424 \label{sec:geom.functions.intersects}
1425 {\footnotesize Language feature :}\\
1426 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1427 {\footnotesize \verb|name: INTERSECTS|}\\
1428
1429 This numeric function determines if two geometry values overlap. This is
1430 most commonly used to express a "shape-vs-shape" intersection test.
1431
1432 For example, to determine whether a circle of one degree radius centered
1433 in a position of (25.4, -20.0) degrees overlaps with a box of ten degrees
1434 centered in a position (20.0, -15.0) in degrees, we would make use of the
1435 INTERSECTS function as follows:
1436
1437 \begin{verbatim}
1438 INTERSECTS(
1439 CIRCLE(‘’, 25.4, -20.0, 1),
1440 BOX(‘’, 20.0, -15.0, 10, 10)
1441 )
1442 \end{verbatim}
1443
1444 , where the INTERSECTS function returns 1 (true) if the two arguments overlap
1445 and 0 (false) otherwise. When used in the where clause of a query, the value
1446 must be compared to 0 or 1 to form an SQL predicate:
1447
1448 \begin{verbatim}
1449 INTERSECTS(CIRCLE(‘’, 25.4, -20.0, 1),
1450 BOX(‘’, 20.0, -15.0, 10, 10)
1451 ) = 1
1452 \end{verbatim}
1453
1454 for "does intersect" and
1455
1456 \begin{verbatim}
1457 INTERSECTS(
1458 CIRCLE(‘’, 25.4, -20.0, 1),
1459 BOX(‘’, 20.0, -15.0, 10, 10)
1460 ) = 0
1461 \end{verbatim}
1462
1463 for "does not intersect".
1464
1465 The arguments to the INTERSECTS function can be (literal) values created from
1466 the geometry types or they can be single column names or aliases (for geometry
1467 stored in a database table).
1468
1469 Since the two argument points may be expressed in different coordinate
1470 systems, the function is responsible for converting one (or both). If it
1471 cannot do so, it SHOULD throw an error message, to be defined by the service
1472 making use of ADQL.
1473
1474 The arguments to INTERSECTS SHOULD be geometric expressions evaluating to
1475 either BOX, CIRCLE, POLYGON, or REGION. Previous versions of this
1476 specification allow POINTs as well and require servers to interpret the
1477 expression as a CONTAINS with the POINT moved into the first position. Servers
1478 SHOULD still implement that behaviour, but clients SHOULD NOT expect it. This
1479 behaviour will be dropped in the next major version of this specification.
1480
1481 \subsubsection{POINT}
1482 \label{sec:geom.functions.point}
1483 {\footnotesize Language feature :}\\
1484 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1485 {\footnotesize \verb|name: POINT|}\\
1486
1487 This function expresses a single location on the sky, and corresponds
1488 semantically to an STC SpatialCoord. The arguments
1489 specify the coordinate system and the position, where:
1490 % STC-reference 'STC SpatialCoord ([3], Section 4.4.2)'
1491
1492 \begin{itemize}
1493 \item the coordinate system is a string value expression as defined in Section \ref{sec:geom.functions.overview}.
1494 \item the position is a comma separated numeric duple, with units and legal ranges as defined in Section \ref{sec:geom.functions.overview}.
1495 \end{itemize}
1496
1497 For example, a function expressing a point with right ascension of 25 degrees
1498 and declination of -19.5 degrees would be written as follows:
1499
1500 \begin{verbatim}
1501 POINT(‘’, 25.0,-19.5)
1502 \end{verbatim}
1503
1504 , where numeric values are in degrees. The coordinates of the POINT could
1505 also be derived from the coordinate’s column references:
1506
1507 \begin{verbatim}
1508 POINT(‘’, t.ra, t.dec)
1509 \end{verbatim}
1510
1511 , where \textit{t} would be the table and \textit{ra}, \textit{dec} the
1512 column references for the position.
1513
1514 The coordinates of a POINT could also be individually extracted using the
1515 COORD1 and COORD2 functions (See \ref{sec:geom.functions.coord1} and
1516 \ref{sec:geom.functions.coord2}).
1517
1518 To see what this function would return when listed in the select clause,
1519 see Section \ref{sec:geom.select}.
1520
1521 \subsubsection{POLYGON}
1522 \label{sec:geom.functions.polygon}
1523 {\footnotesize Language feature :}\\
1524 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1525 {\footnotesize \verb|name: POLYGON|}\\
1526
1527 This function expresses a region on the sky with sides denoted by great
1528 circles passing through specified coordinates. It corresponds semantically
1529 to the STC Polygon region. The arguments specify the
1530 coordinate system and three or more sets of 2-D coordinates, where:
1531 % STC-reference 'STC Polygon region ([3], Section 4.5.1.4)'
1532
1533 \begin{itemize}
1534 \item the coordinate system is a string value expression as defined in Section \ref{sec:geom.functions.overview}.
1535 \item the coordinate sets are comma separated numeric duples, with units and legal ranges as defined in Section \ref{sec:geom.functions.overview}.
1536 \end{itemize}
1537
1538 For example, a function expressing a triangle, whose vertices are (10.0,
1539 -10.5), (20.0, 20.5) and (30.0,30.5) in degrees would be written
1540 as follows:
1541
1542 \begin{verbatim}
1543 POLYGON(‘’, 10.0, -10.5, 20.0, 20.5, 30.0, 30.5)
1544 \end{verbatim}
1545
1546 , where all numeric values are in degrees,
1547
1548 As for other geometries like BOX, CIRCLE and POINT, one could also derive
1549 the coordinates from database column references instead:
1550
1551 \begin{verbatim}
1552 POLYGON(‘’, t.ra, t.dec, 20.0, 20.5, 30.0, 30.5)
1553 \end{verbatim}
1554
1555 , where t would be the table and ra, dec the column references for one of
1556 the triangle’s corner position.
1557
1558 Thus, the polygon is a list of vertices in a single coordinate system, with
1559 each vertex connected to the next along a great circle and the last vertex
1560 implicitly connected to the first vertex.
1561
1562 \subsubsection{REGION}
1563 \label{sec:geom.functions.region}
1564 {\footnotesize Language feature :}\\
1565 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1566 {\footnotesize \verb|name: REGION|}\\
1567
1568 This function provides a generic way of expressing a region represented by
1569 a single string input parameter. The format of the string MUST be specified
1570 by a service that accepts ADQL by referring to a standard format. Currently
1571 STC/s is the only standardized string representation a service can declare.
1572
1573 For example, given a string serialization of an STC region, the REGION
1574 function just embeds such literal within parenthesis in the following way:
1575
1576 \begin{verbatim}
1577 REGION(‘Convex ... Position ... Error ... Size’)
1578 \end{verbatim}
1579
1580 A detailed description on how to use STC/s can be seen in the referenced
1581 document. Inappropriate geometries for this construct SHOULD throw an
1582 error message, to be defined by the service making use of ADQL.
1583 % STC-reference 'referenced document [4]'
1584
1585 \subsubsection{Geometry in the SELECT clause}
1586 \label{sec:geom.select}
1587
1588 Geometry values (literals or columns containing geometry values) may be
1589 listed in the SELECT clause, in which case they must be converted into a
1590 text form.
1591
1592 This text form will be identical to the way a literal value would be
1593 specified in a query, including the geometry type (POINT, CIRCLE, BOX,
1594 or POLYGON) and all the required numeric arguments.
1595
1596 Previous versions of this specification required the text form to include the
1597 coordinate system string. However, as the coordinate system has been marked
1598 as deprecated in this version of the specification (2.1), the text form
1599 should contain an empty string \verb:'': in place of the coordinate
1600 system. Future versions of this specification may remove the coordinate
1601 system parameter from the text form.
1602
1603 \begin{verbatim}
1604 SELECT circle('', 1, 2, 0.5)
1605 \end{verbatim}
1606
1607 could return
1608
1609 \begin{verbatim}
1610 CIRCLE('', 1.0, 2.0, 0.5)
1611 \end{verbatim}
1612
1613 or equivalent.
1614 The output may alter the numeric format by converting whole numbers to
1615 floating point (as in the example above) but should not gratuitously add
1616 digits. Otherwise, numeric output must conform to the rules for numeric
1617 expressions in the ADQL BNF.
1618
1619 \subsection{User Defined Functions}
1620 \label{sec:user.functions}
1621 \subsubsection{Overview}
1622
1623 ADQL also provides a placeholder to define user specific functions. Such
1624 construct supports a variable list of parameters as input in the following way:
1625
1626 \begin{verbatim}
1627 <user_defined_function> ::=
1628 <user_defined_function_name> <left_paren>
1629 [
1630 <user_defined_function_param>
1631 [
1632 {
1633 <comma> <user_defined_function_param>
1634 }...
1635 ]
1636 ]
1637 <right_paren>
1638 \end{verbatim}
1639
1640 The function names can be qualified with a prefix to ease parsing of the
1641 ADQL statement:
1642
1643 \begin{verbatim}
1644 <user_defined_function_name> ::=
1645 [ <default_function_prefix> ] <regular_identifier>
1646 \end{verbatim}
1647
1648 , while the function parameters are generic enough to support string,
1649 numeric and geometrical expressions:
1650
1651 \begin{verbatim}
1652 <user_defined_function_param> ::= <value_expression>
1653 \end{verbatim}
1654
1655 If metadata on a user defined function is available, this should be used. For
1656 example function names and cardinality of arguments should be checked against
1657 metadata where available.
1658
1659 \subsubsection{Metadata}
1660 \label{sec:user.metadata}
1661
1662 The URI for identifying the language feature for a user defined function
1663 is defined as part of the TAPRegExt-1.0 standard \citep{std:TAPREGEXT}.
1664
1665 \begin{verbatim}
1666 ivo://ivoa.net/std/TAPRegExt#features-udf
1667 \end{verbatim}
1668
1669 For user defined functions, the \verb:form: element of the language feature
1670 declaration must contain the signature of the function, written to match
1671 the signature nonterminal in the following grammar:
1672
1673 \begin{verbatim}
1674 signature ::= <funcname> <arglist> "->" <type_name>
1675 funcname ::= <regular_identifier>
1676 arglist ::= "(" <arg> { "," <arg> } ")"
1677 arg ::= <regular_identifier> <type_name>
1678 \end{verbatim}
1679
1680 For example, the following fragment declares a user defined function that
1681 takes two \verb:TEXT: parameters and returns an integer, zero or one,
1682 depending on the regular expression pattern matching.
1683
1684 \begin{verbatim}
1685 <languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-udf">
1686 <feature>
1687 <form>match(pattern TEXT, string TEXT) -> INTEGER</form>
1688 <description>
1689 match returns 1 if the POSIX regular expression pattern
1690 matches anything in string, 0 otherwise.
1691 </description>
1692 </feature>
1693 </languageFeatures>
1694 \end{verbatim}
1695
1696 See the TAPRegExt standard for full details on how to use the
1697 XML schema to declare user defined functions.
1698
1699 \subsection{String functions and operators}
1700 \label{sec:string.functions}
1701
1702 An ADQL service implementation MAY include support for the following optional
1703 string manipulation and comparison operators:
1704
1705 \begin{itemize}
1706 \item \verb:LOWER(): Lower case conversion
1707 \item \verb:ILIKE: Case insensitive comparison
1708 \end{itemize}
1709
1710 \subsubsection{LOWER}
1711 \label{sec:string.functions.lower}
1712 {\footnotesize Language feature :}\\
1713 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-string|}\\
1714 {\footnotesize \verb|name: LOWER|}\\
1715
1716 The LOWER function converts its string parameter to lower case.
1717
1718 Since case folding is a nontrivial operation in a multi-encoding world,
1719 ADQL requires standard behaviour for the ASCII characters, and recommends
1720 following algorithm R2 described in Section 3.13, "Default Case Algorithms"
1721 of \citet{std:UNICODE} for characters outside th ASCII set.
1722
1723 \begin{verbatim}
1724 LOWER('Francis Albert Augustus Charles Emmanuel')
1725 =>
1726 'francis albert augustus charles emmanuel'
1727 \end{verbatim}
1728
1729 \subsubsection{ILIKE}
1730 \label{sec:string.functions.ilike}
1731 {\footnotesize Language feature :}\\
1732 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-string|}\\
1733 {\footnotesize \verb|name: ILIKE|}\\
1734
1735 The ILIKE string comparison operator performs a case insensitive comparison
1736 of its string operands.
1737
1738 \begin{verbatim}
1739 'Francis' LIKE 'francis' => False
1740
1741 'Francis' ILIKE 'francis' => True
1742 \end{verbatim}
1743
1744 Since case folding is a nontrivial operation in a multi-encoding world,
1745 ADQL requires standard behaviour for the ASCII characters, and recommends
1746 following algorithm R2 described in Section 3.13, "Default Case Algorithms"
1747 of \citet{std:UNICODE} for characters outside th ASCII set.
1748
1749 \subsection{Set operators}
1750 \label{sec:set.operators}
1751
1752 An ADQL service implementation MAY include support for the following optional
1753 set operators:
1754
1755 \begin{itemize}
1756 \item \verb:UNION:
1757 \item \verb:EXCEPT:
1758 \item \verb:INTERSECT:
1759 \end{itemize}
1760
1761 \subsubsection{UNION}
1762 {\footnotesize Language feature :}\\
1763 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-sets|}\\
1764 {\footnotesize \verb|name: UNION|}\\
1765
1766 ``The UNION clause combines the results of two SQL queries into a single
1767 table of all matching rows. Any duplicate records are automatically removed
1768 unless UNION ALL is used."\footnote{\url{https://en.wikipedia.org/wiki/Set\_operations\_\%28SQL\%29UNION\_operator}}
1769
1770 For a UNION operation to be valid in ADQL, the following criteria MUST be met:
1771
1772 \begin{itemize}
1773 \item The two queries MUST result in the same number of columns.
1774 \item The corresponding columns in the operands MUST have the same data types.
1775 \item The corresponding columns in the operands SHOULD have the same metadata, e.g. units, UCD etc.
1776 \item The metadata for the results SHOULD be generated from the left-hand operand.
1777 \end{itemize}
1778
1779 Note that the comparison used for removing duplicates is based purely on the
1780 column value only and does not take into account the units.
1781 This means that row with a numeric value of \verb:2: and units of \verb:m:
1782 and a row with a numeric value of \verb:2: and units of \verb:km: will be
1783 considered equal.
1784
1785 \subsubsection{EXCEPT}
1786 {\footnotesize Language feature :}\\
1787 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-sets|}\\
1788 {\footnotesize \verb|name: EXCEPT|}\\
1789
1790 ``The EXCEPT operator takes the distinct rows of one query and returns the
1791 rows that do not appear in a second result set. The EXCEPT ALL operator
1792 does not remove duplicates. For purposes of row elimination and duplicate
1793 removal, the EXCEPT operator does not distinguish between NULLs."\footnote{\url{https://en.wikipedia.org/wiki/Set\_operations\_\%28SQL\%29\#EXCEPT\_operator}}
1794
1795 For an EXCEPT operation to be valid in ADQL, the following criteria MUST be met:
1796
1797 \begin{itemize}
1798 \item The two queries MUST result in the same number of columns.
1799 \item The corresponding columns in the operands MUST have the same data types.
1800 \item The corresponding columns in the operands SHOULD have the same metadata, e.g. units, UCD etc.
1801 \item The metadata for the results MUST be generated from the left-hand operand.
1802 \end{itemize}
1803
1804 Note that the comparison used for identifying matching rows and for removing
1805 duplicates is based purely on the column value only and does not take into
1806 account any units declared in the metadata.
1807
1808 This means that row with a numeric value of \verb:2: and units of \verb:m:
1809 and a row with a numeric value of \verb:2: and units of \verb:km: will be
1810 considered equal.
1811
1812 \subsubsection{INTERSECT}
1813 {\footnotesize Language feature :}\\
1814 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-sets|}\\
1815 {\footnotesize \verb|name: INTERSECT|}\\
1816
1817 ``The INTERSECT operator takes the results of two queries and returns only
1818 rows that appear in both result sets. For purposes of duplicate removal
1819 the INTERSECT operator does not distinguish between NULLs. The INTERSECT
1820 operator removes duplicate rows from the final result set. The INTERSECT
1821 ALL operator does not remove duplicate rows from the final result set."\footnote{\url{https://en.wikipedia.org/wiki/Set\_operations\_\%28SQL\%29\#INTERSECT\_operator}}
1822
1823 For an INTERSECT operation to be valid in ADQL, the following criteria MUST be met:
1824
1825 \begin{itemize}
1826 \item The two queries MUST result in the same number of columns.
1827 \item The corresponding columns in the operands MUST have the same data types.
1828 \item The corresponding columns in the operands SHOULD have the same metadata, e.g. units, UCD etc.
1829 \item The metadata for the results MUST be generated from the left-hand operand.
1830 \end{itemize}
1831
1832 Note that the comparison used for identifying matching rows and for removing
1833 duplicates is based purely on the column value only and does not take into
1834 account the units.
1835
1836 This means that row with a numeric value of \verb:2: and units of \verb:m:
1837 and a row with a numeric value of \verb:2: and units of \verb:km: will be
1838 considered equal.
1839
1840 \subsection{Common table expressions}
1841 \label{sec:common-table}
1842
1843 An ADQL service implementation MAY include support for the following optional
1844 support for common table expressions:
1845
1846 \begin{itemize}
1847 \item \verb:WITH:
1848 \end{itemize}
1849
1850 \subsubsection{WITH}
1851 {\footnotesize Language feature :}\\
1852 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-common-table|}\\
1853 {\footnotesize \verb|name: WITH|}\\
1854
1855 ``A Common Table Expression, or CTE, is a temporary named result set, derived
1856 from a simple query and defined within the execution scope of a SELECT
1857 statement."\footnote{\url{https://en.wikipedia.org/wiki/Hierarchical\_and\_recursive\_queries\_in\_SQL\#Common\_table\_expression}}
1858
1859 Using a common table expression can make queries easier to understand by factoring
1860 subqueries out of the main SQL statement. For example:
1861
1862 \begin{verbatim}
1863 WITH alpha_subset AS
1864 (
1865 SELECT
1866 *
1867 FROM
1868 alpha_source
1869 WHERE
1870 id % 10 = 0
1871 )
1872 SELECT
1873 *
1874 FROM
1875 alpha_subset
1876 WHERE
1877 ra BETWEEN 10 AND 20
1878 \end{verbatim}
1879
1880 \subsection{Type operations}
1881 \label{sec:type}
1882
1883 An ADQL service implementation MAY include support for the following optional
1884 type conversion functions:
1885
1886 \begin{itemize}
1887 \item \verb:CAST():
1888 \end{itemize}
1889
1890 \subsubsection{CAST}
1891 \label{sec:type.cast}
1892 {\footnotesize Language feature :}\\
1893 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-type|}\\
1894 {\footnotesize \verb|name: CAST|}\\
1895
1896 The \verb:CAST(): function returns the value of the first argument converted
1897 to the data type specified by the second argument.
1898
1899 The ADQL \verb:CAST(): function does not replicate the full functionality
1900 and range of types supported by common RDBMS implementations of CAST.
1901
1902 The ADQL \verb:CAST(): function only supports type conversion between the
1903 standard numeric data types. The \verb:CAST(): function does not support
1904 casting to or from the character, binary, datetime or geometric data types.
1905
1906 \begin{table}[thm]\footnotesize
1907 \begin{tabular}{
1908 |p{0.14\textwidth}
1909 |p{0.14\textwidth}
1910 |p{0.14\textwidth}
1911 |p{0.14\textwidth}
1912 |p{0.14\textwidth}
1913 |p{0.14\textwidth}
1914 |}
1915
1916 \hline
1917 \textbf{Type} &
1918 \textbf{Numeric} &
1919 \textbf{Character} &
1920 \textbf{Binary} &
1921 \textbf{Datetime} &
1922 \textbf{Geometric}
1923 \tabularnewline
1924
1925 \hline
1926 \textbf{Numeric} &
1927 Y &
1928 N &
1929 N &
1930 N &
1931 N
1932 \tabularnewline
1933
1934 \hline
1935 \textbf{Character} &
1936 N &
1937 N &
1938 N &
1939 N &
1940 N
1941 \tabularnewline
1942
1943 \hline
1944 \textbf{Binary} &
1945 N &
1946 N &
1947 N &
1948 N &
1949 N
1950 \tabularnewline
1951
1952 \hline
1953 \textbf{Datetime} &
1954 N &
1955 N &
1956 N &
1957 N &
1958 N
1959 \tabularnewline
1960
1961 \hline
1962 \textbf{Geometric} &
1963 N &
1964 N &
1965 N &
1966 N &
1967 N
1968 \tabularnewline
1969
1970 \hline
1971 \end{tabular}
1972 \caption{CAST type groups}
1973 \label{table:adql.cast.type.group}
1974 \end{table}
1975
1976
1977 The ADQL \verb:CAST(): function supports type conversion between the numeric
1978 data types.
1979
1980 \begin{table}[thm]\footnotesize
1981 \begin{tabular}{
1982 |p{0.14\textwidth}
1983 |p{0.14\textwidth}
1984 |p{0.14\textwidth}
1985 |p{0.14\textwidth}
1986 |p{0.14\textwidth}
1987 |p{0.14\textwidth}
1988 |}
1989
1990 \hline
1991 \textbf{Type} &
1992 \textbf{SHORTINT} &
1993 \textbf{INTEGER} &
1994 \textbf{BIGINT} &
1995 \textbf{REAL} &
1996 \textbf{DOUBLE}
1997 \tabularnewline
1998
1999 \hline
2000 \textbf{SHORTINT} &
2001 - &
2002 Y &
2003 Y &
2004 Y &
2005 Y
2006 \tabularnewline
2007
2008 \hline
2009 \textbf{INTEGER} &
2010 Y &
2011 - &
2012 Y &
2013 Y &
2014 Y
2015 \tabularnewline
2016
2017 \hline
2018 \textbf{BIGINT} &
2019 Y &
2020 Y &
2021 - &
2022 Y &
2023 Y
2024 \tabularnewline
2025
2026 \hline
2027 \textbf{REAL} &
2028 Y &
2029 Y &
2030 Y &
2031 - &
2032 Y
2033 \tabularnewline
2034
2035 \hline
2036 \textbf{DOUBLE} &
2037 Y &
2038 Y &
2039 Y &
2040 Y &
2041 -
2042 \tabularnewline
2043
2044 \hline
2045 \end{tabular}
2046 \caption{CAST numeric types}
2047 \label{table:adql.cast.numeric}
2048 \end{table}
2049
2050 When converting from floating point value (REAL or DOUBLE) to an integer
2051 value (SHORTINT, INTEGER or BIGINT) the rounding mechanism used is
2052 implementation dependent.
2053
2054 When converting a numeric value to a data type that is too small to represent
2055 the value, this SHOULD be treated as an error. However, the mechanism for
2056 reporting the overflow condition is implementation dependent.
2057
2058 \subsection{Unit operations}
2059 \label{sec:unit}
2060
2061 An ADQL service implementation MAY include support for the following optional
2062 unit conversion functions:
2063
2064 \begin{itemize}
2065 \item \verb:IN_UNIT():
2066 \end{itemize}
2067
2068 \subsubsection{IN\_UNIT}
2069 \label{sec:unit.cast}
2070 {\footnotesize Language feature :}\\
2071 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-unit|}\\
2072 {\footnotesize \verb|name: IN_UNIT|}\\
2073
2074 The \verb:IN_UNIT(): function returns the value of the first argument
2075 transformed into the units defined by the second argument.
2076
2077 The second argument MUST be a string literal containing a valid unit
2078 description using the formatting defined in the VOUnits specification \citep{std:VOUNIT}.
2079
2080 \begin{itemize}
2081 \item If the second argument is not a valid unit description, then the query is rejected as erroneous.
2082 \item If the translator does not know how to convert the value into the requested units, then the query is rejected as erroneous.
2083 \end{itemize}
2084
2085 \subsection{Bitwise operators}
2086 \label{sec:bitwise}
2087
2088 An ADQL service implementation MAY include support for the following optional
2089 bitwise operators:
2090
2091 \begin{itemize}
2092 \item not \verb:~ x:
2093 \item and \verb:x & y:
2094 \item or \verb:x | y:
2095 \item xor \verb:x ^ y:
2096 \end{itemize}
2097
2098 \subsubsection{Bit AND}
2099 \label{sec:bitwise.and}
2100 {\footnotesize Language feature :}\\
2101 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-bitwise|}\\
2102 {\footnotesize \verb|name: BIT_AND|}\\
2103
2104 The ampersand (\verb:&:) operator performs a bitwise AND operation
2105 on two integer operands.
2106
2107 \begin{verbatim}
2108 x & y
2109 \end{verbatim}
2110
2111 The bitwise AND operation is only valid for integer numeric values,
2112 SMALLINT, INTEGER or BIGINT.
2113 If the operands are not integer values, then the result of the bitwise
2114 AND operation is undefined.
2115
2116 \subsubsection{Bit OR}
2117 \label{sec:bitwise.or}
2118 {\footnotesize Language feature :}\\
2119 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-bitwise|}\\
2120 {\footnotesize \verb|name: BIT_OR|}\\
2121
2122 The vertical bar (\verb:|:) operator performs a bitwise OR operation
2123 on two integer operands.
2124
2125 \begin{verbatim}
2126 x | y
2127 \end{verbatim}
2128
2129 The bitwise OR operation is only valid for integer numeric values,
2130 SMALLINT, INTEGER or BIGINT.
2131 If the operands are not integer values, then the result of the bitwise OR
2132 operation is undefined.
2133
2134 \subsubsection{Bit XOR}
2135 \label{sec:bitwise.xor}
2136 {\footnotesize Language feature :}\\
2137 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-bitwise|}\\
2138 {\footnotesize \verb|name: BIT_XOR|}\\
2139
2140 The circumflex (\verb:^:) operator performs a bitwise XOR (exclusive or)
2141 operation on two integer operands.
2142
2143 \begin{verbatim}
2144 x ^ y
2145 \end{verbatim}
2146
2147 The bitwise XOR operation is only valid for integer numeric values,
2148 SMALLINT, INTEGER or BIGINT.
2149 If the operands are not integer values, then the result of the bitwise
2150 XOR operation is undefined.
2151
2152 \subsubsection{Bit NOT}
2153 \label{sec:bitwise.not}
2154 {\footnotesize Language feature :}\\
2155 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-bitwise|}\\
2156 {\footnotesize \verb|name: BIT_NOT|}\\
2157
2158 The tilde (\verb:~:) operator performs a bitwise NOT operation on an integer operand.
2159
2160 \begin{verbatim}
2161 ~ x
2162 \end{verbatim}
2163
2164 The bitwise NOT operation is only valid for integer numeric values,
2165 SMALLINT, INTEGER or BIGINT.
2166 If the operand is not an integer value, then the result of the bitwise
2167 NOT operation is undefined.
2168
2169 \subsection{Cardinality}
2170 \label{sec:cardinality}
2171
2172 An ADQL service implementation MAY include support for the following optional
2173 clauses to modify the cardinality of query results:
2174
2175 \begin{itemize}
2176 \item \verb:OFFSET:
2177 \end{itemize}
2178
2179 \subsubsection{OFFSET}
2180 \label{sec:offset}
2181
2182 {\footnotesize Language feature :}\\
2183 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-offset|}\\
2184 {\footnotesize \verb|name: OFFSET|}\\
2185
2186 An ADQL service implementation MAY include support for the OFFSET clause
2187 which limits the number of rows returned by removing a specified number
2188 of rows from the beginning of the result set.
2189
2190 If a query contains both an ORDER BY clause and an OFFSET clause,
2191 then the ORDER BY is applied before the specified number of
2192 of rows are dropped by the OFFSET clause.
2193
2194 If the total number of rows is less than the value
2195 specified by the OFFSET clause, then the result set is empty.
2196
2197 If a query contains both an OFFSET clause and a TOP clause,
2198 then the OFFSET clause is applied first, dropping the specified
2199 number of rows from the begining of the result set before the
2200 TOP clause is applied to limit the number of rows returned.
2201
2202 \appendix
2203 \section{BNF Grammar}
2204 \label{sec:grammar}
2205
2206 An easier to navigate version of the BNF grammar can be found at\\
2207 \url{http://www.ivoa.net/internal/IVOA/IvoaVOQL/adql-bnf-v2.0.html}
2208
2209 \begin{verbatim}
2210
2211 <ADQL_language_character> ::=
2212 <simple_Latin_letter>
2213 | <digit>
2214 | <SQL_special_character>
2215
2216 <ADQL_reserved_word> ::=
2217 ABS
2218 | ACOS
2219 | AREA
2220 | ASIN
2221 | ATAN
2222 | ATAN2
2223 | BIT_AND
2224 | BIT_NOT
2225 | BIT_OR
2226 | BIT_XOR
2227 | BOX
2228 | CEILING
2229 | CENTROID
2230 | CIRCLE
2231 | CONTAINS
2232 | COORD1
2233 | COORD2
2234 | COORDSYS
2235 | COS
2236 | DEGREES
2237 | DISTANCE
2238 | EXP
2239 | FLOOR
2240 | ILIKE
2241 | INTERSECTS
2242 | IN_UNIT
2243 | LOG
2244 | LOG10
2245 | MOD
2246 | PI
2247 | POINT
2248 | POLYGON
2249 | POWER
2250 | RADIANS
2251 | REGION
2252 | RAND
2253 | ROUND
2254 | SIN
2255 | SQRT
2256 | TOP
2257 | TAN
2258 | TRUNCATE
2259
2260 <SQL_embedded_language_character> ::=
2261 <left_bracket> | <right_bracket>
2262
2263 <SQL_reserved_word> ::=
2264 ABSOLUTE | ACTION | ADD | ALL
2265 | ALLOCATE | ALTER | AND
2266 | ANY | ARE
2267 | AS | ASC
2268 | ASSERTION | AT
2269 | AUTHORIZATION | AVG
2270 | BEGIN | BETWEEN | BIT | BIT_LENGTH
2271 | BOTH | BY
2272 | CASCADE | CASCADED | CASE | CAST
2273 | CATALOG
2274 | CHAR | CHARACTER | CHAR_LENGTH
2275 | CHARACTER_LENGTH | CHECK | CLOSE | COALESCE
2276 | COLLATE | COLLATION
2277 | COLUMN | COMMIT
2278 | CONNECT
2279 | CONNECTION | CONSTRAINT
2280 | CONSTRAINTS | CONTINUE
2281 | CONVERT | CORRESPONDING | COUNT | CREATE | CROSS
2282 | CURRENT
2283 | CURRENT_DATE | CURRENT_TIME
2284 | CURRENT_TIMESTAMP | CURRENT_USER | CURSOR
2285 | DATE | DAY | DEALLOCATE
2286 | DECIMAL | DECLARE | DEFAULT | DEFERRABLE
2287 | DEFERRED | DELETE | DESC | DESCRIBE | DESCRIPTOR
2288 | DIAGNOSTICS
2289 | DISCONNECT | DISTINCT | DOMAIN | DOUBLE | DROP
2290 | ELSE | END | END-EXEC | ESCAPE
2291 | EXCEPT | EXCEPTION
2292 | EXEC | EXECUTE | EXISTS
2293 | EXTERNAL | EXTRACT
2294 | FALSE | FETCH | FIRST | FLOAT | FOR
2295 | FOREIGN | FOUND | FROM | FULL
2296 | GET | GLOBAL | GO | GOTO
2297 | GRANT | GROUP
2298 | HAVING | HOUR
2299 | IDENTITY | IMMEDIATE | IN | INDICATOR
2300 | INITIALLY | INNER | INPUT
2301 | INSENSITIVE | INSERT | INT | INTEGER | INTERSECT
2302 | INTERVAL | INTO | IS
2303 | ISOLATION
2304 | JOIN
2305 | KEY
2306 | LANGUAGE | LAST | LEADING | LEFT
2307 | LEVEL | LIKE | ILIKE | LOCAL | LOWER
2308 | MATCH | MAX | MIN | MINUTE | MODULE
2309 | MONTH
2310 | NAMES | NATIONAL | NATURAL | NCHAR | NEXT | NO
2311 | NOT | NULL
2312 | NULLIF | NUMERIC
2313 | OCTET_LENGTH | OF
2314 | ON | ONLY | OPEN | OPTION | OR
2315 | ORDER | OUTER
2316 | OUTPUT | OVERLAPS
2317 | PAD | PARTIAL | POSITION | PRECISION | PREPARE
2318 | PRESERVE | PRIMARY
2319 | PRIOR | PRIVILEGES | PROCEDURE | PUBLIC
2320 | READ | REAL | REFERENCES | RELATIVE | RESTRICT
2321 | REVOKE | RIGHT
2322 | ROLLBACK | ROWS
2323 | SCHEMA | SCROLL | SECOND | SECTION
2324 | SELECT
2325 | SESSION | SESSION_USER | SET
2326 | SIZE | SMALLINT | SOME | SPACE | SQL | SQLCODE
2327 | SQLERROR | SQLSTATE
2328 | SUBSTRING | SUM | SYSTEM_USER
2329 | TABLE | TEMPORARY
2330 | THEN | TIME | TIMESTAMP
2331 | TIMEZONE_HOUR | TIMEZONE_MINUTE
2332 | TO | TRAILING | TRANSACTION
2333 | TRANSLATE | TRANSLATION | TRIM | TRUE
2334 | UNION | UNIQUE | UNKNOWN | UPDATE | UPPER | USAGE
2335 | USER | USING
2336 | VALUE | VALUES | VARCHAR | VARYING | VIEW
2337 | WHEN | WHENEVER | WHERE | WITH | WORK | WRITE
2338 | YEAR
2339 | ZONE
2340
2341 <SQL_special_character> ::=
2342 <space>
2343 | <double_quote>
2344 | <percent>
2345 | <ampersand>
2346 | <quote>
2347 | <left_paren>
2348 | <right_paren>
2349 | <asterisk>
2350 | <plus_sign>
2351 | <comma>
2352 | <minus_sign>
2353 | <period>
2354 | <solidus>
2355 | <colon>
2356 | <semicolon>
2357 | <less_than_operator>
2358 | <equals_operator>
2359 | <greater_than_operator>
2360 | <question_mark>
2361 | <underscore>
2362 | <vertical_bar>
2363
2364 <ampersand> ::= &
2365
2366 <approximate_numeric_literal> ::= <mantissa>E<exponent>
2367
2368 <area> ::= AREA <left_paren> <geometry_value_expression> <right_paren>
2369
2370 <as_clause> ::= [ AS ] <column_name>
2371
2372 <asterisk> ::= *
2373
2374 <between_predicate> ::=
2375 <value_expression> [ NOT ] BETWEEN
2376 <value_expression> AND <value_expression>
2377
2378 <bitwise_expression> ::=
2379 <bitwise_not> <numeric_value_expression>
2380 | <numeric_value_expression> <bitwise_and> <numeric_value_expression>
2381 | <numeric_value_expression> <bitwise_or> <numeric_value_expression>
2382 | <numeric_value_expression> <bitwise_xor> <numeric_value_expression>
2383
2384 <bitwise_and> ::= <ampersand>
2385 <bitwise_not> ::= <tilde>
2386 <bitwise_or> ::= <vertical_bar>
2387 <bitwise_xor> ::= <circumflex>
2388
2389 <boolean_factor> ::= [ NOT ] <boolean_primary>
2390
2391 <boolean_function> ::=
2392
2393 <boolean_literal> ::= True | False
2394
2395 <boolean_primary> ::=
2396 <left_paren> <search_condition> <right_paren>
2397 | <predicate>
2398 | <boolean_value_expression>
2399
2400 <boolean_term> ::=
2401 <boolean_factor>
2402 | <boolean_term> AND <boolean_factor>
2403
2404 <boolean_value_expression> ::=
2405 <boolean_literal>
2406 | <boolean_function>
2407 | <user_defined_function>
2408
2409 <box> ::=
2410 BOX <left_paren>
2411 <coord_sys>
2412 <comma> <coordinates>
2413 <comma> <numeric_value_expression>
2414 <comma> <numeric_value_expression>
2415 <right_paren>
2416
2417 <catalog_name> ::= <identifier>
2418
2419 <centroid> ::=
2420 CENTROID <left_paren>
2421 <geometry_value_expression>
2422 <right_paren>
2423
2424 <character_factor> ::= <character_primary>
2425
2426 <character_primary> ::=
2427 <value_expression_primary>
2428 | <string_value_function>
2429
2430 <character_representation> ::= <nonquote_character> | <quote_symbol>
2431
2432 <character_string_literal> ::=
2433 <quote> [ <character_representation>... ] <quote>
2434
2435 <character_value_expression> ::= <concatenation> | <character_factor>
2436
2437 <circle> ::=
2438 CIRCLE <left_paren>
2439 <coord_sys>
2440 <comma> <coordinates>
2441 <comma> <radius>
2442 <right_paren>
2443
2444 <circumflex> ::= ^
2445
2446 <colon> ::= :
2447
2448 <column_name> ::= <identifier>
2449
2450 <column_name_list> ::= <column_name> [ { <comma> <column_name> }... ]
2451
2452 <column_reference> ::= [ <qualifier> <period> ] <column_name>
2453
2454 <comma> ::= ,
2455
2456 <comment> ::= <comment_introducer> [ <comment_character>... ] <newline>
2457
2458 <comment_character> ::= <nonquote_character> | <quote>
2459
2460 <comment_introducer> ::= <minus_sign><minus_sign> [<minus_sign>...]
2461
2462 <comp_op> ::=
2463 <equals_operator>
2464 | <not_equals_operator>
2465 | <less_than_operator>
2466 | <greater_than_operator>
2467 | <less_than_or_equals_operator>
2468 | <greater_than_or_equals_operator>
2469
2470 <comparison_predicate> ::=
2471 <value_expression> <comp_op> <value_expression>
2472
2473 <concatenation> ::=
2474 <character_value_expression>
2475 <concatenation_operator>
2476 <character_factor>
2477
2478 <concatenation_operator> ::= ||
2479
2480 <contains> ::=
2481 CONTAINS <left_paren>
2482 <geometry_value_expression> <comma> <geometry_value_expression>
2483 <right_paren>
2484
2485 <coord1> ::= COORD1 <left_paren> <coord_value> <right_paren>
2486
2487 <coord2> ::= COORD2 <left_paren> <coord_value> <right_paren>
2488
2489 <coord_sys> ::= <string_value_expression>
2490
2491 <coord_value> ::= <point> | <column_reference>
2492
2493 <coordinate1> ::= <numeric_value_expression>
2494
2495 <coordinate2> ::= <numeric_value_expression>
2496
2497 <coordinates> ::= <coordinate1> <comma> <coordinate2>
2498
2499 <correlation_name> ::= <identifier>
2500
2501 <correlation_specification> ::= [ AS ] <correlation_name>
2502
2503 <default_function_prefix> ::=
2504
2505 <delimited_identifier> ::=
2506 <double_quote> <delimited_identifier_body> <double_quote>
2507
2508 <delimited_identifier_body> ::= <delimited_identifier_part>...
2509
2510 <delimited_identifier_part> ::=
2511 <nondoublequote_character> | <double_quote_symbol>
2512
2513 <delimiter_token> ::=
2514 <character_string_literal>
2515 | <delimited_identifier>
2516 | <SQL_special_character>
2517 | <not_equals_operator>
2518 | <greater_than_or_equals_operator>
2519 | <less_than_or_equals_operator>
2520 | <concatenation_operator>
2521 | <double_period>
2522 | <left_bracket>
2523 | <right_bracket>
2524
2525 <derived_column> ::= <value_expression> [ <as_clause> ]
2526
2527 <derived_table> ::= <table_subquery>
2528
2529 <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
2530
2531 <distance_function> ::=
2532 DISTANCE <left_paren>
2533 <coord_value> <comma>
2534 <coord_value>
2535 <right_paren>
2536 | DISTANCE <left_paren>
2537 <numeric_value_expression> <comma>
2538 <numeric_value_expression> <comma>
2539 <numeric_value_expression> <comma>
2540 <numeric_value_expression>
2541 <right_paren>
2542
2543 <double_period> ::= ..
2544
2545 <double_quote> ::= "
2546
2547 <double_quote_symbol> ::= <double_quote><double_quote>
2548
2549 <equals_operator> ::= =
2550
2551 <exact_numeric_literal> ::=
2552 <unsigned_decimal> [ <period> [ <unsigned_decimal> ] ]
2553 | <period> <unsigned_decimal>
2554
2555 <exists_predicate> ::= EXISTS <table_subquery>
2556
2557 <exponent> ::= <signed_integer>
2558
2559 <extract_coordsys> ::=
2560 COORDSYS <left_paren>
2561 <geometry_value_expression>
2562 <right_paren>
2563
2564 <factor> ::= [ <sign> ] <numeric_primary>
2565
2566 <from_clause> ::=
2567 FROM <table_reference>
2568 [ { <comma> <table_reference> }... ]
2569
2570 <general_literal> ::= <character_string_literal>
2571
2572 <general_set_function> ::=
2573 <set_function_type> <left_paren>
2574 [ <set_quantifier> ] <value_expression>
2575 <right_paren>
2576
2577 <geometry_value_expression> ::=
2578 <value_expression_primary > | <geometry_value_function>
2579
2580 <geometry_value_function> ::=
2581 <box>
2582 | <centroid>
2583 | <circle>
2584 | <point>
2585 | <polygon>
2586 | <region>
2587 | <user_defined_function>
2588
2589 <greater_than_operator> ::= >
2590
2591 <greater_than_or_equals_operator> ::= >=
2592
2593 <group_by_clause> ::= GROUP BY <grouping_column_reference_list>
2594
2595 <grouping_column_reference> ::= <column_reference>
2596
2597 <grouping_column_reference_list> ::=
2598 <grouping_column_reference>
2599 [ { <comma> <grouping_column_reference> }... ]
2600
2601 <having_clause> ::= HAVING <search_condition>
2602
2603 <hex_digit> ::= <digit> | a | b | c | d | e | f | A | B | C | D | E | F
2604
2605 <identifier> ::= <regular_identifier> | <delimited_identifier>
2606
2607 <in_predicate> ::=
2608 <value_expression> [ NOT ] IN <in_predicate_value>
2609
2610 <in_predicate_value> ::=
2611 <table_subquery> | <left_paren> <in_value_list> <right_paren>
2612
2613 <in_value_list> ::=
2614 <value_expression> { <comma> <value_expression> } ...
2615
2616 <intersects > ::=
2617 INTERSECTS <left_paren>
2618 <geometry_value_expression> <comma> <geometry_value_expression>
2619 <right_paren>
2620
2621 <join_column_list> ::= <column_name_list>
2622
2623 <join_condition> ::= ON <search_condition>
2624
2625 <join_specification> ::= <join_condition> | <named_columns_join>
2626
2627 <join_type> ::=
2628 INNER | <outer_join_type> [ OUTER ]
2629
2630 <joined_table> ::=
2631 <qualified_join> | <left_paren> <joined_table> <right_paren>
2632
2633 <keyword> ::= <SQL_reserved_word> | <ADQL_reserved_word>
2634
2635 <left_bracket> ::= [
2636
2637 <left_paren> ::= (
2638
2639 <less_than_operator> ::= <
2640
2641 <less_than_or_equals_operator> ::= <=
2642
2643 <like_predicate> ::=
2644 <match_value> [ NOT ] LIKE <pattern>
2645 | <match_value> [ NOT ] ILIKE <pattern>
2646
2647 <mantissa> ::= <exact_numeric_literal>
2648
2649 <match_value> ::= <character_value_expression>
2650
2651 <math_function> ::=
2652 ABS <left_paren> <numeric_value_expression> <right_paren>
2653 | CEILING <left_paren> <numeric_value_expression> <right_paren>
2654 | DEGREES <left_paren> <numeric_value_expression> <right_paren>
2655 | EXP <left_paren> <numeric_value_expression> <right_paren>
2656 | FLOOR <left_paren> <numeric_value_expression> <right_paren>
2657 | LOG <left_paren> <numeric_value_expression> <right_paren>
2658 | LOG10 <left_paren> <numeric_value_expression> <right_paren>
2659 | MOD <left_paren>
2660 <numeric_value_expression> <comma> <numeric_value_expression>
2661 <right_paren>
2662 | PI <left_paren><right_paren>
2663 | POWER <left_paren>
2664 <numeric_value_expression> <comma> <numeric_value_expression>
2665 <right_paren>
2666 | RADIANS <left_paren> <numeric_value_expression> <right_paren>
2667 | RAND <left_paren> [ <unsigned_decimal> ] <right_paren>
2668 | ROUND <left_paren>
2669 <numeric_value_expression> [ <comma> <signed_integer>]
2670 <right_paren>
2671 | SQRT <left_paren> <numeric_value_expression> <right_paren>
2672 | TRUNCATE <left_paren>
2673 <numeric_value_expression>
2674 [ <comma> <signed_integer>]
2675 <right_paren>
2676
2677 <minus_sign> ::= -
2678
2679 <named_columns_join> ::=
2680 USING <left_paren>
2681 <join_column_list>
2682 <right_paren>
2683
2684 <newline> ::=
2685
2686 <non_predicate_geometry_function> ::=
2687 <area>
2688 | <coord1>
2689 | <coord2>
2690 | <distance>
2691
2692 <nondelimiter_token> ::=
2693 <regular_identifier>
2694 | <keyword>
2695 | <unsigned_numeric_literal>
2696
2697 <nondoublequote_character> ::=
2698
2699 <nonquote_character> ::=
2700
2701 <not_equals_operator> ::= <not_equals_operator1> | <not_equals_operator2>
2702
2703 <not_equals_operator1> ::= <>
2704
2705 <not_equals_operator2> ::= !=
2706
2707 <non_join_query_expression> ::=
2708 <non_join_query_term>
2709 | <query_expression> UNION [ ALL ] <query_term>
2710 | <query_expression> EXCEPT [ ALL ] <query_term>
2711
2712 <non_join_query_primary> ::=
2713 <query_specification>
2714 | <left_paren> <non_join_query_expression> <right_paren>
2715
2716 <non_join_query_term> ::=
2717 <non_join_query_primary>
2718 | <query_term> INTERSECT [ ALL ] <query_expression>
2719
2720 <null_predicate> ::= <column_reference> IS [ NOT ] NULL
2721
2722 <numeric_geometry_function> ::=
2723 <predicate_geometry_function> | <non_predicate_geometry_function>
2724
2725 <numeric_primary> ::=
2726 <value_expression_primary>
2727 | <numeric_value_function>
2728
2729 <numeric_value_expression> ::=
2730 <term>
2731 | <bitwise_expression>
2732 | <numeric_value_expression> <plus_sign> <term>
2733 | <numeric_value_expression> <minus_sign> <term>
2734
2735 <numeric_value_function> ::=
2736 <trig_function>
2737 | <math_function>
2738 | <numeric_geometry_function >
2739 | <user_defined_function>
2740
2741 <offset_clause> ::= OFFSET <unsigned_decimal>
2742
2743 <order_by_clause> ::= ORDER BY <sort_specification_list>
2744
2745 <ordering_specification> ::= ASC | DESC
2746
2747 <outer_join_type> ::= LEFT | RIGHT | FULL
2748
2749 <pattern> ::= <character_value_expression>
2750
2751 <percent> ::= %
2752
2753 <period> ::= .
2754
2755 <plus_sign> ::= +
2756
2757 <point> ::=
2758 POINT <left_paren>
2759 <coord_sys> <comma> <coordinates>
2760 <right_paren>
2761
2762 <polygon> ::=
2763 POLYGON <left_paren>
2764 <coord_sys>
2765 <comma> <coordinates>
2766 <comma> <coordinates>
2767 { <comma> <coordinates> } ?
2768 <right_paren>
2769
2770 <predicate> ::=
2771 <comparison_predicate>
2772 | <between_predicate>
2773 | <in_predicate>
2774 | <like_predicate>
2775 | <null_predicate>
2776 | <exists_predicate>
2777
2778 <predicate_geometry_function> ::= <contains> | <intersects>
2779
2780 <qualified_join> ::=
2781 <table_reference> [ NATURAL ] [ <join_type> ] JOIN
2782 <table_reference> [ <join_specification> ]
2783
2784 <qualifier> ::= <table_name> | <correlation_name>
2785
2786 <query_expression> ::=
2787 <non_join_query_expression>
2788 | <joined_table>
2789
2790 <query_term> ::=
2791 <non_join_query_term>
2792 | <joined_table>
2793
2794 <query_name> ::= <identifier>
2795
2796 <query_specification> :=
2797 WITH <with_query> [, ...]
2798 <select_query>
2799
2800 <question_mark> ::= ?
2801
2802 <quote> ::= '
2803
2804 <quote_symbol> ::= <quote> <quote>
2805
2806 <radius> ::= <numeric_value_expression>
2807
2808 <region> ::=
2809 REGION <left_paren> <string_value_expression> <right_paren>
2810
2811 <regular_identifier> ::=
2812 <simple_Latin_letter>...
2813 [ { <digit> | <simple_Latin_letter> | <underscore> }... ]
2814
2815 <right_bracket> ::= ]
2816
2817 <right_paren> ::= )
2818
2819 <schema_name> ::= [ <catalog_name> <period> ] <unqualified_schema name>
2820
2821 <search_condition> ::=
2822 <boolean_term>
2823 | <search_condition> OR <boolean_term>
2824
2825 <select_list> ::=
2826 <asterisk>
2827 | <select_sublist> [ { <comma> <select_sublist> }... ]
2828
2829 <select_query> ::=
2830 SELECT
2831 [ <set_quantifier> ]
2832 [ <set_limit> ]
2833 <select_list>
2834 <table_expression>
2835
2836 <select_sublist> ::= <derived_column> | <qualifier> <period> <asterisk>
2837
2838 <semicolon> ::= ;
2839
2840 <set_function_specification> ::=
2841 COUNT <left_paren> <asterisk> <right_paren>
2842 | <general_set_function>
2843
2844 <set_function_type> ::= AVG | MAX | MIN | SUM | COUNT
2845
2846 <set_limit> ::= TOP <unsigned_decimal>
2847
2848 <set_quantifier> ::= DISTINCT | ALL
2849
2850 <sign> ::= <plus_sign> | <minus_sign>
2851
2852 <signed_integer> ::= [ <sign> ] <unsigned_decimal>
2853
2854 <simple_Latin_letter> ::=
2855 <simple_Latin_upper_case_letter>
2856 | <simple_Latin_lower_case_letter>
2857
2858 <simple_Latin_lower_case_letter> ::=
2859 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
2860
2861 <simple_Latin_upper_case_letter> ::=
2862 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
2863
2864 <solidus> ::= /
2865
2866 <sort_key> ::= <column_name> | <unsigned_decimal>
2867
2868 <sort_specification> ::=
2869 <sort_key> [ <ordering_specification> ]
2870
2871 <sort_specification_list> ::=
2872 <sort_specification> [ { <comma> <sort_specification> }... ]
2873
2874 <space> ::=
2875
2876 <string_geometry_function> ::= <extract_coordsys>
2877
2878 <string_value_expression> ::= <character_value_expression>
2879
2880 <string_value_function> ::=
2881 <string_geometry_function> | <user_defined_function>
2882
2883 <subquery> ::= <left_paren> <query_expression> <right_paren>
2884
2885 <table_expression> ::=
2886 <from_clause>
2887 [ <where_clause> ]
2888 [ <group_by_clause> ]
2889 [ <having_clause> ]
2890 [ <order_by_clause> ]
2891 [ <offset_clause> ]
2892
2893 <table_name> ::= [ <schema_name> <period> ] <identifier>
2894
2895 <table_reference> ::=
2896 <table_name> [ <correlation_specification> ]
2897 | <derived_table> <correlation_specification>
2898 | <joined_table>
2899
2900 <table_subquery> ::= <subquery>
2901
2902 <term> ::=
2903 <factor>
2904 | <term> <asterisk> <factor>
2905 | <term> <solidus> <factor>
2906
2907 <tilde> ::= ~
2908
2909 <token> ::=
2910 <nondelimiter_token> | <delimiter_token>
2911
2912 <trig_function> ::=
2913 ACOS <left_paren> <numeric_value_expression> <right_paren>
2914 | ASIN <left_paren> <numeric_value_expression> <right_paren>
2915 | ATAN <left_paren> <numeric_value_expression> <right_paren>
2916 | ATAN2 <left_paren>
2917 <numeric_value_expression> <comma> <numeric_value_expression>
2918 <right_paren>
2919 | COS <left_paren> <numeric_value_expression> <right_paren>
2920 | COT <left_paren> <numeric_value_expression> <right_paren>
2921 | SIN <left_paren> <numeric_value_expression> <right_paren>
2922 | TAN <left_paren> <numeric_value_expression> <right_paren>
2923
2924 <underscore> ::= _
2925
2926 <unqualified_schema name> ::= <identifier>
2927
2928 <unsigned_decimal> ::= <digit>...
2929
2930 <unsigned_hexadecimal> ::= 0x<hex_digit>...
2931
2932 <unsigned_literal> ::=
2933 <unsigned_numeric_literal>
2934 | <general_literal>
2935
2936 <unsigned_numeric_literal> ::=
2937 <exact_numeric_literal>
2938 | <approximate_numeric_literal>
2939 | <unsigned_hexadecimal>
2940
2941 <unsigned_value_specification> ::= <unsigned_literal>
2942
2943 <user_defined_function> ::=
2944 <user_defined_function_name> <left_paren>
2945 [
2946 <user_defined_function_param>
2947 [
2948 {
2949 <comma> <user_defined_function_param>
2950 }...
2951 ]
2952 ]
2953 <right_paren>
2954
2955 <user_defined_function_name> ::=
2956 [ <default_function_prefix> ] <regular_identifier>
2957
2958 <user_defined_function_param> ::= <value_expression>
2959
2960 <value_expression> ::=
2961 <numeric_value_expression>
2962 | <string_value_expression>
2963 | <boolean_value_expression>
2964 | <geometry_value_expression>
2965
2966 <value_expression_primary> ::=
2967 <unsigned_value_specification>
2968 | <column_reference>
2969 | <set_function_specification>
2970 | <left_paren> <value_expression> <right_paren>
2971
2972 <vertical_bar> ::= |
2973
2974 <where_clause> ::= WHERE <search_condition>
2975
2976 <with_query> :=
2977 <query_name>
2978 [ (<column_name> [,...]) ] AS (<query_specification>)
2979
2980 \end{verbatim}
2981
2982 \section{Language feature support}
2983 \label{sec:features}
2984
2985 Within the TAPRegExt \citep{std:TAPREGEXT} XML schema, each group of features is
2986 described by a \verb:languageFeatures: element, which has a \verb:type:
2987 URI that identifies the group, and contains a \verb:form: element for each
2988 individual feature from the group that the service supports.
2989
2990 For example, the following XML fragment describes a service that supports the
2991 \verb:POINT: and \verb:CIRCLE: functions from the set of geometrical functions
2992 identified by the URI \verb|ivo://ivoa.net/std/TAPRegExt#features-adql-geo|.
2993
2994 \begin{verbatim}
2995 <languageFeatures
2996 type="ivo://ivoa.net/std/TAPRegExt#features-adql-geo"
2997 >
2998 <feature>
2999 <form>POINT</form>
3000 </feature>
3001 <feature>
3002 <form>CIRCLE</form>
3003 </feature>
3004 </languageFeatures>
3005 \end{verbatim}
3006
3007 \section{Changes from Previous Versions}
3008 \label{sec:changes}
3009 \subsection{Changes from ADQL-2.0}
3010
3011 \begin{itemize}
3012
3013 \item Changes from WD-ADQL-2.1-20160502-05
3014 \begin{itemize}
3015
3016 \item Fixed typo in definition of MOD (svn version 3456).
3017 \item Fixed section references (svn version 3637).
3018 \item Improved wording for keywords and identifiers (svn version 4242).
3019
3020 \end{itemize}
3021
3022 \item Changes from ADQL-20150601
3023 \begin{itemize}
3024
3025 \item Added boolean type (svn version 3364).
3026
3027 \item Removed bitwise functions and updated the operators (svn version 3365).
3028
3029 \item Changed 'hierarchical queries' to 'common table expressions' (svn version 3366).
3030
3031 \item Added OFFSET clause (svn version 3367).
3032
3033 \item Added four parameter DISTANCE (svn version 3370).
3034
3035 \item Added hexadecimal literals (svn version 3374).
3036
3037 \end{itemize}
3038
3039 \item Changes from \citet{note:TAPNotes}
3040 \begin{itemize}
3041
3042 \item 2.1.1. (done) The Separator Nonterminal
3043 \begin{itemize}
3044 \item Imported changes from \citet{note:ADQL2Err1}
3045 \end{itemize}
3046
3047 \item 2.1.2. (done) Type System
3048 \item 2.1.4. (done) Empty Coordinate Systems
3049 \item 2.1.5. (done) Explanation of optional features
3050 \item 2.2.2. (done) No Type-based Decay of INTERSECTS
3051 \item 2.2.3. (done) Generalized User Defined Functions
3052 \item 2.2.4. (done) Case-Insensitive String Comparisons
3053 \item 2.2.5. (done) Set Operators
3054 \item 2.2.6. (TODO) Boolean Type
3055 \item 2.2.7. (done) Casting to Unit
3056 \item 2.2.10. (done) Bitwise operators
3057 \item 2.2.10. (TODO) Hexadecimal literals
3058 \item 2.2.11. (done) CAST operator
3059 \item 2.NN (done) WITH
3060
3061 \end{itemize}
3062
3063 \item Created [Optional components] section.
3064 \item Moved [Geometrical Functions] into [Optional components].
3065 \item Added [Language feature] information.
3066
3067 \end{itemize}
3068
3069 \bibliography{ivoatex/ivoabib}
3070
3071 \end{document}
3072

msdemlei@ari.uni-heidelberg.de
ViewVC Help
Powered by ViewVC 1.1.26