/[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 4587 - (show annotations)
Tue Nov 28 04:07:45 2017 UTC (3 years, 9 months ago) by yrvafhom
File MIME type: application/x-tex
File size: 116895 byte(s)
Added use case for BLOB and CLOB
1 \documentclass[11pt,a4paper]{ivoa}
2 \input tthdefs
3
4 \SVN$Rev$
5 \SVN$Date$
6 \SVN$URL$
7
8 \usepackage[utf8]{inputenc}
9 \usepackage{tabularx}
10 \usepackage{mathtools}
11
12 \usepackage{listings}
13 \lstloadlanguages{XML,sh}
14 \lstset{flexiblecolumns=true,numberstyle=\small,numbers=left}
15
16 \usepackage{hyperref}
17
18 % Preserve spaces after \newcommand{}
19 % https://tex.stackexchange.com/a/17873
20 \usepackage{xspace}
21
22 %\newcommand{\VOTspec} {TAP specification \citet{std:VOTable}\xspace}
23 %\newcommand{\TAPspec} {TAP specification \citet{std:TAP}\xspace}
24 %\newcommand{\DALIspec} {TAP specification \citet{std:DALI}\xspace}
25 %\newcommand{\VOSIspec} {TAP specification \citet{std:VOSI}\xspace}
26 %\newcommand{\RegTAPspec}{RegTAP specification \citep{std:REGTAP}\xspace}
27 %\newcommand{\TAPRegExtSpec}{TAPRegExt specification \citep{std:TAPREGEXT}\xspace}
28
29 \newcommand{\VOTspec}{VOTable specification\xspace}
30 \newcommand{\TAPspec}{TAP specification\xspace}
31 \newcommand{\DALIspec}{DALI specification\xspace}
32 \newcommand{\VOSIspec}{VOSI specification\xspace}
33 \newcommand{\VOUnitSpec}{VOUnits specification\xspace}
34 \newcommand{\RegTAPspec}{RegTAP specification\xspace}
35 \newcommand{\TAPRegExtSpec}{TAPRegExt specification\xspace}
36
37 \newcommand{\STCspec}{STC specification\xspace}
38 \newcommand{\STCSspec}{STC-S specification\xspace}
39
40 \newcommand{\SectionRef}[1]{Section \ref{#1}\xspace}
41 \newcommand{\SectionSee}[1]{(see Section\ref{#1})\xspace}
42 \newcommand{\AppendixRef}[1]{Appendix \ref{#1}\xspace}
43
44
45 \title{Astronomical Data Query Language}
46
47 \ivoagroup{Data Access Layer Working Group}
48
49 \author[http://wiki.ivoa.net/twiki/bin/view/IVOA/IvoaVOQL]{The IVOA Virtual Observatory Query Language (VOQL) working group members}
50 \author[http://wiki.ivoa.net/twiki/bin/view/IVOA/IvoaDAL]{The IVOA Data Access Layer (DAL) working group members}
51
52 \editor[http://wiki.ivoa.net/twiki/bin/view/IVOA/DaveMorris]{Dave Morris}
53
54 \previousversion[http://www.ivoa.net/Documents/ADQL/2.0]{ADQL-2.0}
55
56 \begin{document}
57
58 \begin{abstract}
59 This document describes the Astronomical Data Query Language (ADQL).
60 ADQL has been developed based on SQL92.
61 This document describes the subset of the SQL grammar supported by ADQL.
62 Special restrictions and extensions to SQL92 have been defined in order
63 to support generic and astronomy specific operations.
64 \end{abstract}
65
66 \section*{Acknowledgements}
67
68 The authors would like to acknowledge all contributors to this and previous
69 versions of this standard, especially:
70 P. Dowler,
71 J. Lusted,
72 M. A. Nieto-Santisteban,
73 W. O'Mullane,
74 M. Ohishi,
75 I. Ortiz,
76 P. Osuna,
77 Y Shirasaki
78 and
79 A. Szalay.
80
81 \section*{Conformance-related definitions}
82
83 The words ``MUST'', ``SHALL'', ``SHOULD'', ``MAY'', ``RECOMMENDED'' and
84 ``OPTIONAL'' (in upper or lower case) used in this document are to be
85 interpreted as described in the
86 \href{https://www.ietf.org/}{Internet Engineering Task Force (IETF)}
87 standard, \citet{std:RFC2119}.
88
89 The \emph{Virtual Observatory (VO)} is a general term for a collection of
90 federated resources that can be used to conduct astronomical research,
91 education and outreach. The
92 \href{http://www.ivoa.net}{International Virtual Observatory Alliance (IVOA)}
93 is a global collaboration of separately funded
94 projects to develop standards and infrastructure that enable VO applications.
95
96 \clearpage
97 \section{Introduction}
98 \label{sec:introduction}
99
100 The Astronomical Data Query Language (ADQL) is the language used by the
101 IVOA to represent astronomy queries posted to VO services.
102 The IVOA has developed several standardized protocols to access astronomical
103 data, e.g., Simple Image Access (SIA) protocol and Simple Spectral Access (SSA)
104 protocol for image and spectral data respectively.
105 These protocols might be satisfied using a single
106 table query. However, different VO services have different needs in terms
107 of query complexity and ADQL arises in this context.
108
109 The ADQL specification makes no distinction between core and advanced or
110 extended functionalities. Hence ADQL has been built according to a single
111 Backus Naur Form (BNF) based language definition. Any service making use of ADQL would
112 then define the level of compliancy to the language. This would allow the
113 notion of core and extension to be service-driven and it would decouple the
114 language from the service specifications.
115
116 ADQL is based on the Structured Query Language (SQL), especially on SQL 92. The
117 VO has a number of tabular data sets and many of them are stored in relational
118 databases, making SQL a convenient access means. A subset of the SQL grammar
119 has been extended to support queries that are specific to astronomy. Similarly
120 to SQL, the ADQL language definition is not semantically safe by design and
121 therefore this specification defines syntactical correctness only. Type safety
122 has been achieved as far as it can be done in SQL. The exact meaning of keywords
123 indicating requirement levels can be found in the References section.
124 %Should this be 'Conformance-related definitions' not 'References' ?
125
126 \clearpage
127 \subsection{Role within the VO architecture}
128 \label{sec:role}
129
130 \begin{figure}
131 \centering
132 \includegraphics[width=0.9\textwidth]{ADQL-archdiag.png}
133 \caption{Architecture diagram for this document}
134 \label{fig:archdiag}
135 \end{figure}
136
137 Figure ~\ref{fig:archdiag} shows the role this document plays within the
138 IVOA architecture \citep{note:VOARCH}.
139
140 \subsection{Extended functionality}
141 \label{sec:extending}
142
143 % Requested by Alberto Micol at ESO.
144 % Explicit permisson for services that provide additional functionality.
145 This document defines the minimum set of functions, operators and datatypes
146 that a service MUST implement in order to register as a service that
147 implements this version of the ADQL specification.
148
149 Service implementations are free to extend this functionality by providing
150 additional functions, operators or datatypes beyond those defined in this
151 specification.
152
153 \clearpage
154 \section{Language structure}
155 \label{sec:language}
156
157 This section describes the ADQL language structure. We will define in
158 subsequent sections the syntax for the special characters, reserved and non-
159 reserved words, identifiers and literals and then, finally, the syntax for
160 the query expression.
161
162 The formal notation for syntax of computing languages is often expressed
163 in BNF. This syntax is used by popular tools for
164 producing parsers. Appendix A to this document provides the full BNF grammar
165 for ADQL. The following conventions are used through this document:
166
167 \begin{itemize}
168 \item Optional items are enclosed in meta symbols \verb:[: and \verb:]:
169 \item A group of items is enclosed in meta symbols \verb:{: and \verb:}:
170 \item Repetitive item (zero or more times) are followed by \verb:...:
171 \item Terminal symbols are enclosed by \verb:<: and \verb:>:
172 \item Terminals of meta-symbol characters (\verb:=,[,],(,),<,>,*:) are surrounded by quotes (\verb:“:) to distinguish them from meta-symbols
173 \item Case-insensitive unless otherwise stated.
174 \end{itemize}
175
176 \clearpage
177 \subsection{Characters, keywords, identifiers and literals}
178 \subsubsection{Characters}
179 \label{sec:characters}
180
181 The language allows simple Latin letters (lower and upper case, i.e.
182 \verb:{aA-zZ}):, digits (\verb:{0-9}:) and the following special characters:
183
184 \begin{itemize}
185 \item space
186 \item single quote \verb:’:
187 \item double quote \verb:“:
188 \item percent \verb:%:
189 \item left and right parenthesis \verb:():
190 \item asterisk \verb:*:
191 \item plus sign \verb:+:
192 \item minus sign \verb:-:
193 \item comma \verb:,:
194 \item period \verb:.:
195 \item solidus \verb:/:
196 \item colon \verb.:.
197 \item semicolon \verb:;:
198 \item less than operator \verb:<:
199 \item equals operator \verb:=:
200 \item greater than operator \verb:>:
201 \item underscore \verb:_:
202 \item ampersand \verb:&:
203 \item question mark \verb:?:
204 \item circumflex \verb:^:
205 \item tilde \verb:~:
206 \item vertical bar \verb:|:
207 \end{itemize}
208
209 \subsubsection{Keywords and identifiers}
210 \label{sec:keywords}
211
212 Besides the character set, the language provides a list of reserved keywords
213 plus the syntax description for regular identifiers.
214
215 A reserved keyword has a special meaning in ADQL and cannot be used as
216 an identifier unless it is isolated using the ADQL escape syntax defined
217 in \SectionRef{sec:adql.escape}.
218
219 The ADQL specification extends the list of SQL92 reserved keywords to accommodate
220 those useful for astronomical purposes and/or present in a subset of vendor
221 specific languages only (e.g. \verb:TOP:).
222
223 Although the following lists are all in UPPERCASE, the matching of keywords
224 is case-insensitive.
225
226 \subsubsection{SQL reserved keywords}
227 \label{sec:adql.keywords}
228
229 \noindent
230 \texttt{ABSOLUTE,} \texttt{ACTION,} \texttt{ADD,} \texttt{ALL,}
231 \texttt{ALLOCATE,} \texttt{ALTER,} \texttt{AND,} \texttt{ANY,}
232 \texttt{ARE,} \texttt{AS,} \texttt{ASC,} \texttt{ASSERTION,}
233 \texttt{AT,} \texttt{AUTHORIZATION,} \texttt{AVG,} \texttt{BEGIN,}
234 \texttt{BETWEEN,} \texttt{BIT,} \texttt{BIT\_LENGTH,} \texttt{BOTH,}
235 \texttt{BY,} \texttt{CASCADE,} \texttt{CASCADED,} \texttt{CASE,}
236 \texttt{CAST,} \texttt{CATALOG,} \texttt{CHAR,} \texttt{CHARACTER,}
237 \texttt{CHARACTER\_LENGTH,} \texttt{CHAR\_LENGTH,} \texttt{CHECK,}
238 \texttt{CLOSE,} \texttt{COALESCE,} \texttt{COLLATE,}
239 \texttt{COLLATION,} \texttt{COLUMN,} \texttt{COMMIT,}
240 \texttt{CONNECT,} \texttt{CONNECTION,} \texttt{CONSTRAINT,}
241 \texttt{CONSTRAINTS,} \texttt{CONTINUE,} \texttt{CONVERT,}
242 \texttt{CORRESPONDING,} \texttt{COUNT,} \texttt{CREATE,}
243 \texttt{CROSS,} \texttt{CURRENT,} \texttt{CURRENT\_DATE,}
244 \texttt{CURRENT\_TIME,} \texttt{CURRENT\_TIMESTAMP,}
245 \texttt{CURRENT\_USER,} \texttt{CURSOR,} \texttt{DATE,} \texttt{DAY,}
246 \texttt{DEALLOCATE,} \texttt{DECIMAL,} \texttt{DECLARE,}
247 \texttt{DEFAULT,} \texttt{DEFERRABLE,} \texttt{DEFERRED,}
248 \texttt{DELETE,} \texttt{DESC,} \texttt{DESCRIBE,}
249 \texttt{DESCRIPTOR,} \texttt{DIAGNOSTICS,} \texttt{DISCONNECT,}
250 \texttt{DISTINCT,} \texttt{DOMAIN,} \texttt{DOUBLE,} \texttt{DROP,}
251 \texttt{ELSE,} \texttt{END,} \texttt{END-EXEC,} \texttt{ESCAPE,}
252 \texttt{EXCEPT,} \texttt{EXCEPTION,} \texttt{EXEC,} \texttt{EXECUTE,}
253 \texttt{EXISTS,} \texttt{EXTERNAL,} \texttt{EXTRACT,} \texttt{FALSE,}
254 \texttt{FETCH,} \texttt{FIRST,} \texttt{FLOAT,} \texttt{FOR,}
255 \texttt{FOREIGN,} \texttt{FOUND,} \texttt{FROM,} \texttt{FULL,}
256 \texttt{GET,} \texttt{GLOBAL,} \texttt{GO,} \texttt{GOTO,}
257 \texttt{GRANT,} \texttt{GROUP,} \texttt{HAVING,} \texttt{HOUR,}
258 \texttt{IDENTITY,} \texttt{IMMEDIATE,} \texttt{IN,}
259 \texttt{INDICATOR,} \texttt{INITIALLY,} \texttt{INNER,}
260 \texttt{INPUT,} \texttt{INSENSITIVE,} \texttt{INSERT,} \texttt{INT,}
261 \texttt{INTEGER,} \texttt{INTERSECT,} \texttt{INTERVAL,}
262 \texttt{INTO,} \texttt{IS,} \texttt{ISOLATION,} \texttt{JOIN,}
263 \texttt{KEY,} \texttt{LANGUAGE,} \texttt{LAST,} \texttt{LEADING,}
264 \texttt{LEFT,} \texttt{LEVEL,} \texttt{LIKE,} \texttt{LOCAL,}
265 \texttt{LOWER,} \texttt{MATCH,} \texttt{MAX,} \texttt{MIN,}
266 \texttt{MINUTE,} \texttt{MODULE,} \texttt{MONTH,} \texttt{NAMES,}
267 \texttt{NATIONAL,} \texttt{NATURAL,} \texttt{NCHAR,} \texttt{NEXT,}
268 \texttt{NO,} \texttt{NOT,} \texttt{NULL,} \texttt{NULLIF,}
269 \texttt{NUMERIC,} \texttt{OCTET\_LENGTH,} \texttt{OF,} \texttt{ON,}
270 \texttt{ONLY,} \texttt{OPEN,} \texttt{OPTION,} \texttt{OR,}
271 \texttt{ORDER,} \texttt{OUTER,} \texttt{OUTPUT,} \texttt{OVERLAPS,}
272 \texttt{PAD,} \texttt{PARTIAL,} \texttt{POSITION,}
273 \texttt{PRECISION,} \texttt{PREPARE,} \texttt{PRESERVE,}
274 \texttt{PRIMARY,} \texttt{PRIOR,} \texttt{PRIVILEGES,}
275 \texttt{PROCEDURE,} \texttt{PUBLIC,} \texttt{READ,} \texttt{REAL,}
276 \texttt{REFERENCES,} \texttt{RELATIVE,} \texttt{RESTRICT,}
277 \texttt{REVOKE,} \texttt{RIGHT,} \texttt{ROLLBACK,} \texttt{ROWS,}
278 \texttt{SCHEMA,} \texttt{SCROLL,} \texttt{SECOND,} \texttt{SECTION,}
279 \texttt{SELECT,} \texttt{SESSION,} \texttt{SESSION\_USER,}
280 \texttt{SET,} \texttt{SIZE,} \texttt{SMALLINT,} \texttt{SOME,}
281 \texttt{SPACE,} \texttt{SQL,} \texttt{SQLCODE,} \texttt{SQLERROR,}
282 \texttt{SQLSTATE,} \texttt{SUBSTRING,} \texttt{SUM,}
283 \texttt{SYSTEM\_USER,} \texttt{TABLE,} \texttt{TEMPORARY,}
284 \texttt{THEN,} \texttt{TIME,} \texttt{TIMESTAMP,}
285 \texttt{TIMEZONE\_HOUR,} \texttt{TIMEZONE\_MINUTE,} \texttt{TO,}
286 \texttt{TRAILING,} \texttt{TRANSACTION,} \texttt{TRANSLATE,}
287 \texttt{TRANSLATION,} \texttt{TRIM,} \texttt{TRUE,} \texttt{UNION,}
288 \texttt{UNIQUE,} \texttt{UNKNOWN,} \texttt{UPDATE,} \texttt{UPPER,}
289 \texttt{USAGE,} \texttt{USER,} \texttt{USING,} \texttt{VALUE,}
290 \texttt{VALUES,} \texttt{VARCHAR,} \texttt{VARYING,} \texttt{VIEW,}
291 \texttt{WHEN,} \texttt{WHENEVER,} \texttt{WHERE,} \texttt{WITH,}
292 \texttt{WORK,} \texttt{WRITE,} \texttt{YEAR,} \texttt{ZONE}
293
294 \subsubsection{ADQL reserved keywords}
295 \label{sec:adql.reswords}
296
297 Mathematical functions and operators:
298 \noindent
299 \texttt{ABS,} \texttt{ACOS,} \texttt{ASIN,} \texttt{ATAN,}
300 \texttt{ATAN2,} \texttt{CEILING,} \texttt{COS,} \texttt{DEGREES,}
301 \texttt{EXP,} \texttt{FLOOR,} \texttt{LOG,} \texttt{LOG10,}
302 \texttt{MOD,} \texttt{PI,} \texttt{POWER,} \texttt{RADIANS,}
303 \texttt{RAND,} \texttt{ROUND,} \texttt{SIN,} \texttt{SQRT,}
304 \texttt{TAN,} \texttt{TOP,} \texttt{TRUNCATE}
305 \newline
306 \newline
307
308 Geometric functions and operators:
309 \noindent
310 \texttt{AREA,} \texttt{BOX,} \texttt{CENTROID,} \texttt{CIRCLE,}
311 \texttt{CONTAINS,} \texttt{COORD1,} \texttt{COORD2,}
312 \texttt{COORDSYS,} \texttt{DISTANCE,} \texttt{INTERSECTS,}
313 \texttt{POINT,} \texttt{POLYGON}
314
315 \subsubsection{ADQL deprecated keywords}
316 \label{sec:adql.depwords}
317
318 The following terms are ADQL keywords that are no longer used,
319 but still remain reserved for backwards compatibility.
320
321 \noindent
322 \texttt{REGION}
323
324 \subsubsection{Identifiers}
325 \label{sec:adql.identifiers}
326
327 Identifiers MUST begin with a letter
328 \verb:{aA-zZ}:, subsequent characters MAY be letters, underscores or
329 digits \verb:{0-9}: as follows:
330
331 \begin{verbatim}
332 <Latin_letter>... [{ <digit> | <Latin_letter> | <underscore> | }...]
333 \end{verbatim}
334
335 \subsubsection{Escape syntax}
336 \label{sec:adql.escape}
337
338 To address reserved keyword and special character conflicts the ADQL language
339 provides a way to escape a non-compliant identifier by using the double
340 quote character \verb:": as a delimiter.
341
342 For example, to use the reserved word \verb:size: as a column name
343 it must be isolated using double quotes.
344
345 \begin{itemize}
346 \item \verb:size: -- Invalid column name
347 \item \verb:"size": -- Valid column name
348 \end{itemize}
349
350 \subsubsection{Case sensitivity}
351 \label{sec:adql.case}
352
353 In addition to isolating keyword conflicts and special characters,
354 the double quote escape syntax also denotes case sensitivity.
355
356 Without double quotes, the following identifiers are all equivalent:
357 \begin{verbatim}
358 alpha == Alpha == ALPHA
359 \end{verbatim}
360
361 When escaped using double quotes, the same set of identifiers are not equivalent:
362 \begin{verbatim}
363 "alpha" != "Alpha" != "ALPHA"
364 \end{verbatim}
365
366 \subsubsection{Literals}
367 \label{sec:literals}
368
369 String literals are expressed as a character expression delimited by single quotes.
370
371 \begin{verbatim}
372 <character_string_literal> ::=
373 <quote> [ <character_representation>... ] <quote>
374 \end{verbatim}
375
376 Numeric literals may be are expressed as an exact decimal value, e.g. \verb:12: or \verb:12.3:,
377 a floating point number with an exponent, e.g. \verb:12.3E4:, or a unsigned hexadecimal
378 value, e.g. \verb:0x2F:.
379
380 \begin{verbatim}
381 <signed_numeric_literal> ::= [<sign>] <unsigned_numeric_literal>
382
383 <unsigned_numeric_literal> ::=
384 <exact_numeric_literal>
385 | <approximate_numeric_literal>
386 | <unsigned_hexadecimal>
387
388 <exact_numeric_literal> ::=
389 <unsigned_decimal> [<period> [<unsigned_decimal>]]
390 | <period><unsigned_decimal>
391
392 <approximate_numeric_literal> ::= <mantissa> E <exponent>
393
394 <mantissa> ::= <exact_numeric_literal>
395
396 <exponent> ::= <signed_decimal>
397
398 <signed_decimal> ::= [<sign>] <unsigned_decimal>
399
400 <unsigned_decimal> ::= <digit>...
401
402 <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
403
404 <sign> ::= <plus_sign> | <minus_sign>
405 \end{verbatim}
406
407 Hexadecimal literals are expressed using the 'C' style notation, prefixed by a zero and 'x' e.g. \verb:0x2F:.
408
409 \begin{verbatim}
410 <unsigned_hexadecimal> ::= 0x<hex_digit>...
411
412 hex_digit ::= <digit> | a | b | c | d | e | f | A | B | C | D | E | F
413 \end{verbatim}
414
415 Hexadecimal literals are not case-sensitive.
416 Hexadecimal literals can only be used to create integer datatypes, SMALLINT, INTEGER and BIGINT.
417
418 Boolean literals are expressed in BNF as follows:
419
420 \begin{verbatim}
421 <boolean_literal> ::= True | False
422 \end{verbatim}
423
424 Boolean literals are not case-sensitive.
425
426 \clearpage
427 \subsection{Query syntax}
428 \label{sec:syntax}
429
430 A more detailed definition of the select statement is given by the \verb:<query_specification>:
431 construct defined in \AppendixRef{sec:grammar}.
432
433 A simplified syntax for the \verb:SELECT: statement follows, showing the main constructs for
434 the query specification:
435
436 \begin{verbatim}
437 SELECT
438 [ ALL | DISTINCT ]
439 [ TOP unsigned_decimal ]
440 {
441 * |
442 { value_expression [ [AS] column_name ] }, ...
443 }
444 FROM {
445 {
446 table_name [ [AS] identifier ] |
447 ( SELECT ....) [ [AS] identifier ] |
448 table_name [NATURAL]
449 [ INNER | { LEFT | RIGHT | FULL [OUTER] } ]
450 JOIN table_name
451 [ON search_condition | USING ( column_name,...) ]
452 },
453 ...
454 }
455
456 [ WHERE search_condition ]
457 [ GROUP BY group_by_term, ... ]
458 [ HAVING search_condition ]
459 [ ORDER BY
460 { order_by_expression } [ ASC | DESC],
461 ...
462 ]
463 [ OFFSET unsigned_decimal ]
464 \end{verbatim}
465
466 The \verb:SELECT: statement defines a query to apply to a set of tables specified
467 in the \verb:FROM: clause. As a result of this query, a subset of the tables
468 is returned.
469 The order of the rows MAY be arbitrary unless an \verb:ORDER BY: clause is specified.
470 A \verb:TOP: clause MAY be specified to limit the number of rows returned.
471 An \verb:OFFSET: clause MAY be specified to skip a number of rows at the start
472 of the results.
473 If both \verb:TOP: and \verb:OFFSET: are used together then \verb:OFFSET: is applied
474 first followed by \verb:TOP: \SectionSee{sec:offset}.
475
476 The order of the columns in the query results SHALL be the same as the
477 order specified in the selection list,
478 %or the order defined in the original table if
479 unless an asterisk is specified.
480 The selection list MAY include numeric,
481 string or geometry value expressions.
482
483 \subsubsection{Subqueries}
484 \label{sec:subqueries}
485 %TBD - cosmopterix tests for this
486
487 Table subqueries MAY be used by predicates such as \verb:IN: and \verb:EXISTS:
488 in the \verb:WHERE: clause of a query:
489
490 \begin{verbatim}
491 SELECT
492 alpha_source.id
493 FROM
494 alpha_source
495 WHERE
496 alpha_sourceid >=5
497 AND
498 alpha_sourceid IN
499 (
500 SELECT id FROM alpha_source WHERE id < 10
501 )
502 \end{verbatim}
503
504 Table subqueries MAY be used for declaring derived tables in the \verb:FROM: clause
505 of a query:
506
507 \begin{verbatim}
508 SELECT
509 alpha_source.id
510 FROM
511 alpha_source,
512 (
513 SELECT alpha_source.id FROM alpha_source WHERE id < 10
514 ) AS subsample
515 WHERE
516 alpha_source.id >=5
517 AND
518 alpha_source.id = subsample.id
519 \end{verbatim}
520
521 \subsubsection{Joins}
522 \label{sec:joins}
523 %TBD - cosmopterix tests for this
524
525 ADQL supports \verb:INNER: and \verb:OUTER:
526 (\verb:LEFT:, \verb:RIGHT: and \verb:FULL:) joins. If no type is specified, the
527 default is \verb:INNER:. All of these can be \verb:NATURAL: or not.
528
529 %REMOVED: The join condition does not support embedded sub joins.
530 %REASON: The BNF allows nested JOINs.
531
532 \subsubsection{Search condition}
533 \label{sec:search}
534
535 A search condition MAY be part of other clauses including \verb:JOIN:, \verb:HAVING: and \verb:WHERE:.
536
537 A search condition MAY contain the standard logical operators, \verb:AND:, \verb:OR: and \verb:NOT:.
538
539 A search condition MAY contain the following predicates:
540
541 \begin{itemize}
542 \item Standard comparison operators: \verb:=:, \verb:!=:, \verb:<>:, \verb:<:, \verb:>:, \verb:<=:, \verb:>=:
543 \item Range comparison, \verb:BETWEEN:
544 \item Case-sensitive string comparison, \verb:LIKE:
545 \item Null value checks, \verb:IS NULL: and \verb:IS NOT NULL:
546 \item Non-empty subquery check, \verb:EXISTS:
547 \end{itemize}
548
549 In addition, some service implementations may also support the optional \verb:ILIKE:
550 case-insensitive string comparison operator, defined in \SectionRef{sec:string.functions.ilike}.
551
552 \begin{itemize}
553 \item \verb:ILIKE:
554 \end{itemize}
555
556 \clearpage
557 \subsection{Mathematical and Trigonometrical Functions}
558 \label{sec:math.functions}
559
560 ADQL declares a list of reserved keywords \SectionSee{sec:keywords} which include
561 the mathematical and trigonometrical function names. Their syntax,
562 usage and description are detailed in the following tables:
563
564 \begin{table}[thm]\footnotesize
565 \begin{tabular}{|p{0.20\textwidth}|p{0.125\textwidth}|p{0.125\textwidth}|p{0.55\textwidth}|}
566 \hline
567
568 \hline
569 \textbf{Name} &
570 \textbf{Argument \newline datatype} &
571 \textbf{Return \newline datatype} &
572 \textbf{Description}
573 \tabularnewline
574
575 \hline
576 abs(x) &
577 \textit{x} double &
578 double &
579 Returns the absolute value of \textit{x}.
580 \tabularnewline
581
582 \hline
583 ceiling(x) &
584 \textit{x} double &
585 double &
586 Returns the smallest integer that is not less than \textit{x}.
587 \tabularnewline
588
589 \hline
590 degrees(x) &
591 \textit{x} double &
592 double &
593 Converts the angle \textit{x} from radians to degrees.
594 \tabularnewline
595
596 \hline
597 exp(x) &
598 \textit{x} double &
599 double &
600 Returns Euler’s number \textit{e} raised to the power of \textit{x}.
601 \tabularnewline
602
603 \hline
604 floor(x) &
605 \textit{x} double &
606 double &
607 Returns the largest integer that is not greater than \textit{x}.
608 \tabularnewline
609
610 \hline
611 log(x) &
612 \textit{x} double &
613 double &
614 Returns the natural logarithm (base \textit{e}) of \textit{x}. The value of \textit{x} must be greater than zero.
615 \tabularnewline
616
617 \hline
618 log10(x) &
619 \textit{x} double &
620 double &
621 Returns the base 10 logarithm of \textit{x}. The value of \textit{x} must be greater than zero.
622 \tabularnewline
623
624 \hline
625 mod(x,y) &
626 \textit{x} double,
627 \newline
628 \textit{y} double &
629 double &
630 Returns the remainder \textit{r} of \textit{x/y} as a double.
631 \tabularnewline
632
633 \hline
634 pi() &
635 &
636 double &
637 The numeric constant \(\pi\).
638 \tabularnewline
639
640 \hline
641 power(x,y) &
642 \textit{x} double,
643 \newline
644 \textit{y} double &
645 double &
646 Returns the value of \textit{x} raised to the power of \textit{y}.
647 \tabularnewline
648
649 \hline
650 radians(x) &
651 \textit{x} double &
652 double &
653 Converts the angle \textit{x} from degrees to radians.
654 \tabularnewline
655
656 \hline
657 sqrt(x) &
658 \textit{x} double &
659 double &
660 Returns the positive square root of \textit{x}.
661 \tabularnewline
662
663 \hline
664 rand() &
665 &
666 double &
667 Returns a random value between 0.0 and 1.0.
668 \tabularnewline
669
670 \hline
671 round(x,n) &
672 \textit{x} double,
673 \newline
674 \textit{n} integer &
675 double &
676 Rounds \textit{x} to \textit{n} number of decimal places, with the default being to round to the nearest integer.
677 A negative value of \textit{n} will round to the left of the decimal point.
678 \tabularnewline
679
680 \hline
681 truncate(x, n) &
682 \textit{x} double
683 \newline
684 \textit{n} integer &
685 double &
686 Truncates \textit{x} to \textit{n} decimal places.
687 \tabularnewline
688
689 \hline
690 \end{tabular}
691 \caption{Mathematical functions}
692 \label{table:math.functions}
693 \end{table}
694
695 \begin{table}[thm]\footnotesize
696 \begin{tabular}{|p{0.20\textwidth}|p{0.125\textwidth}|p{0.125\textwidth}|p{0.55\textwidth}|}
697 \hline
698
699 \hline
700 \textbf{Name} &
701 \textbf{Argument \newline datatype} &
702 \textbf{Return \newline datatype} &
703 \textbf{Description}
704 \tabularnewline
705
706 \hline
707 acos(x) &
708 \textit{x} double &
709 double &
710 Returns the arc cosine of \textit{x}, in the range of 0 through \(\pi\) radians. The absolute value of \textit{x} must be less than or equal to 1.0.
711 \tabularnewline
712
713 \hline
714 asin(x) &
715 \textit{x} double &
716 double &
717 Returns the arc sine of \textit{x}, in the range of -\(\pi\)/2 through \(\pi\)/2 radians. The absolute value of \textit{x} must be less than or equal to 1.0.
718 \tabularnewline
719
720 \hline
721 atan(x) &
722 \textit{x} double &
723 double &
724 Returns the arc tangent of \textit{x} , in the range of -\(\pi\)/2 through \(\pi\)/2 radians.
725 \tabularnewline
726
727 \hline
728 atan2(y,x) &
729 \textit{x} double,
730 \newline
731 \textit{y} double &
732 double &
733 Converts rectangular coordinates \textit{x,y} to polar angle. It computes the arc tangent of \textit{y/x} in the range of –\(\pi\) through \(\pi\) radians.
734 \tabularnewline
735
736 \hline
737 cos(x) &
738 \textit{x} double &
739 double &
740 Returns the cosine of the angle \textit{x} in radians, in the range of -1.0 through 1.0.
741 \tabularnewline
742
743 \hline
744 sin(x) &
745 \textit{x} double &
746 double &
747 Returns the cosine of the angle \textit{x} in radians, in the range of -1.0 through 1.0.
748 \tabularnewline
749
750 \hline
751 tan(x) &
752 \textit{x} double &
753 double &
754 Returns the tangent of the angle \textit{x} in radians, in the range of -1.0 through 1.0.
755 \tabularnewline
756
757 \hline
758 \end{tabular}
759 \caption{Trigonometrical functions}
760 \label{table:trig.functions}
761 \end{table}
762
763 \clearpage
764 \section{Type system}
765 \label{sec:types}
766
767 ADQL defines no data definition language (DDL).
768 It is assumed that table definition and data ingestion are performed in
769 the underlying database's native language and type system.
770
771 However, service metadata needs to give column types in order to allow the
772 construction of queries that are both syntactically and semantically correct.
773 Examples of such metadata includes the \verb:TAP_SCHEMA: tables defined
774 in the \TAPspec and the \verb:/tables:
775 webservice response defined in the \VOSIspec.
776
777 Services SHOULD, if at all possible, try to express their column metadata in
778 these terms even if the underlying database employs different types.
779 Services SHOULD also use the following mappings when interfacing to user data,
780 either by serializing result sets into VOTables or by ingesting user-provided
781 VOTables into ADQL-visible tables.
782
783 \subsection{Logical types}
784 \label{sec:types.logical}
785 \subsubsection{BOOLEAN}
786 \label{sec:types.logical.boolean}
787
788 The BOOLEAN datatype maps to the corresponding \verb:boolean: datatype is defined in the \DALIspec.
789 The serialization format for \verb:boolean: is defined in the \VOTspec.
790
791 \begin{table}[thm]\footnotesize
792 \begin{tabular}
793 {|p{0.20\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
794 \hline
795
796 \hline
797 \multicolumn{1}{|c|}{\textbf{ADQL}} &
798 \multicolumn{3}{|c|}{\textbf{VOTable}}
799 \tabularnewline
800
801 \hline
802 \textbf{type} &
803 \textbf{datatype} &
804 \textbf{arraysize} &
805 \textbf{xtype}
806 \tabularnewline
807
808 \hline
809 BOOLEAN &
810 boolean &
811 1 &
812 -
813 \tabularnewline
814
815 \hline
816 \end{tabular}
817 \caption{ADQL type mapping for BOOLEAN}
818 \label{table:types.logical.boolean}
819 \end{table}
820
821 The literal values 1 and \verb:TRUE: are equivalent,
822 and the values 0 and \verb:FALSE: are equivalent:
823 \begin{verbatim}
824 foo = 1
825 foo = TRUE
826
827 bar = 0
828 bar = FALSE
829 \end{verbatim}
830
831 The literal values \verb:TRUE: and \verb:FALSE:
832 are not case-sensitive:
833 \begin{verbatim}
834 foo = true
835 foo = True
836 foo = TRUE
837
838 bar = 0
839 bar = false
840 bar = False
841 bar = FALSE
842 \end{verbatim}
843
844 Comparing the equality of a BOOLEAN value or expression with another
845 BOOLEAN returns a BOOLEAN result.
846
847 When comparing the size of a BOOLEAN with another BOOLEAN, the value
848 \verb:TRUE: is greater than the value \verb:FALSE:.
849
850 Unless explicitly stated, the result of any other operation on a BOOLEAN
851 value is undefined.
852
853 \subsection{Numeric types}
854 \label{sec:types.numeric}
855
856 \subsubsection{Numeric primitives}
857 \label{sec:types.numeric.primitive}
858
859 The numeric datatypes, BIT, SMALLINT, INTEGER, BIGINT, REAL
860 and DOUBLE map to the corresponding datatypes defined
861 in the \VOTspec.
862
863 \begin{table}[thm]\footnotesize
864 \begin{tabular}
865 {|p{0.20\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
866 \hline
867
868 \hline
869 \multicolumn{1}{|c|}{\textbf{ADQL}} &
870 \multicolumn{3}{|c|}{\textbf{VOTable}}
871 \tabularnewline
872
873 \hline
874 \textbf{type} &
875 \textbf{datatype} &
876 \textbf{arraysize} &
877 \textbf{xtype}
878 \tabularnewline
879
880 \hline
881 BIT &
882 bit &
883 - &
884 -
885 \tabularnewline
886
887 % \hline
888 % ?? &
889 % unsignedByte &
890 % - &
891 % -
892 % \tabularnewline
893
894 \hline
895 SMALLINT &
896 short &
897 - &
898 -
899 \tabularnewline
900
901 \hline
902 INTEGER &
903 int &
904 - &
905 -
906 \tabularnewline
907
908 \hline
909 BIGINT &
910 long &
911 - &
912 -
913 \tabularnewline
914
915 \hline
916 REAL &
917 float &
918 - &
919 -
920 \tabularnewline
921
922 \hline
923 DOUBLE &
924 double &
925 - &
926 -
927 \tabularnewline
928
929 \hline
930 \end{tabular}
931 \caption{ADQL type mapping for numeric values}
932 \label{table:types.numeric.primitive}
933 \end{table}
934
935 Where possible ADQL numeric values SHOULD be implemented using database types
936 that correspond to the VOTable serialization types, e.g. SMALLINT should map to a
937 16 bit integer, INTEGER should map to a 32 bit integer, etc.
938
939 \subsubsection{INTERVAL}
940 \label{sec:types.numeric.interval}
941
942 The \DALIspec defines INTERVAL as a pair of integer or floating-point
943 numeric values which are serialized as an array of numbers.
944
945 TBD - The details of how INTERVAL values behave in ADQL are not yet defined.
946
947 \begin{table}[thm]\footnotesize
948 \begin{tabular}
949 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
950 \hline
951
952 \hline
953 \multicolumn{1}{|c|}{\textbf{ADQL}} &
954 \multicolumn{3}{|c|}{\textbf{VOTable}}
955 \tabularnewline
956
957 \hline
958 \textbf{type} &
959 \textbf{datatype} &
960 \textbf{arraysize} &
961 \textbf{xtype}
962 \tabularnewline
963
964 \hline
965 INTERVAL &
966 short, int, float, double &
967 2 &
968 interval
969 \tabularnewline
970 \hline
971 \end{tabular}
972 \caption{ADQL type mapping for INTERVAL}
973 \label{table:types.numeric.interval}
974 \end{table}
975
976 \subsection{Date and time}
977 \label{sec:types.datetime}
978
979 Where possible, date and time values SHOULD be implemented as
980 described in the \DALIspec.
981
982 \subsubsection{TIMESTAMP}
983 \label{sec:types.datetime.timestamp}
984
985 The TIMESTAMP datatype maps to the corresponding type defined in the
986 \DALIspec.
987
988 \begin{table}[thm]\footnotesize
989 \begin{tabular}
990 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
991 \hline
992
993 \hline
994 \multicolumn{1}{|c|}{\textbf{ADQL}} &
995 \multicolumn{3}{|c|}{\textbf{VOTable}}
996 \tabularnewline
997
998 \hline
999 \textbf{type} &
1000 \textbf{datatype} &
1001 \textbf{arraysize} &
1002 \textbf{xtype}
1003 \tabularnewline
1004
1005 \hline
1006 TIMESTAMP &
1007 char &
1008 n, n*, * &
1009 timestamp
1010 \tabularnewline
1011 \hline
1012 \end{tabular}
1013 \caption{ADQL type mapping for TIMESTAMP}
1014 \label{table:types.datetime.timestamp}
1015 \end{table}
1016
1017 TIMESTAMP literals should be created using the \verb:TIMESTAMP():
1018 constructor, using the syntax defined in the \DALIspec:
1019 \begin{verbatim}
1020 YYYY-MM-DD[’T’hh:mm:ss[.SSS][’Z’]]
1021 \end{verbatim}
1022
1023 The basic comparison operators \verb:=:, \verb:<:, \verb:>:, \verb:<=:, \verb:>=:,
1024 \verb:<>: and \verb:BETWEEN: can all be applied to TIMESTAMP values:
1025 \begin{verbatim}
1026 SELECT
1027 ..
1028 WHERE
1029 obstime > TIMESTAMP('2015-01-01')
1030 OR
1031 obstime
1032 BETWEEN
1033 TIMESTAMP('2014-01-01')
1034 AND
1035 TIMESTAMP('2014-01-02')
1036 \end{verbatim}
1037
1038 Within the database, the details of how TIMESTAMP values are implemented
1039 is platform dependent. The primary requirement is that the results of the
1040 comparison operators on TIMESTAMP values are consistent with respect to
1041 chronological time.
1042
1043 \subsection{Character types}
1044 \label{sec:types.character}
1045
1046 \subsubsection{Character primitives}
1047 \label{sec:types.character.primitive}
1048
1049 The CHAR and VARCHAR datatypes map to the \verb:char: or
1050 \verb:unicodeChar: type defined in the \VOTspec.
1051
1052 The choice of whether CHAR and VARCHAR map to \verb:char: or
1053 \verb:unicodeChar: is implementation dependent and may depend
1054 on the data content.
1055
1056 \begin{table}[thm]\footnotesize
1057 \begin{tabular}
1058 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
1059 \hline
1060
1061 \hline
1062 \multicolumn{1}{|c|}{\textbf{ADQL}} &
1063 \multicolumn{3}{|c|}{\textbf{VOTable}}
1064 \tabularnewline
1065
1066 \hline
1067 \textbf{type} &
1068 \textbf{datatype} &
1069 \textbf{arraysize} &
1070 \textbf{xtype}
1071 \tabularnewline
1072
1073 \hline
1074 CHAR(n) &
1075 char, unicodeChar &
1076 n &
1077 -
1078 \tabularnewline
1079
1080 \hline
1081 VARCHAR(n) &
1082 char, unicodeChar &
1083 n* &
1084 -
1085 \tabularnewline
1086
1087 \hline
1088 \end{tabular}
1089 \caption{ADQL type mapping for character strings}
1090 \label{table:types.character.primitive}
1091 \end{table}
1092
1093 \subsubsection{CLOB}
1094 \label{sec:types.character.clob}
1095
1096 To support strings which are generated by the server when a query is executed
1097 ADQL includes the Character Large OBject (CLOB) datatype,
1098 which behaves as an opaque immutable string of characters.
1099
1100 None of the ADQL operators apply to CLOB values.
1101 However, specific database implementations MAY provide user
1102 defined functions that operate on some CLOB values.
1103
1104 CLOB values are serialized as arrays of characters.
1105
1106 \begin{table}[thm]\footnotesize
1107 \begin{tabular}
1108 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
1109 \hline
1110
1111 \hline
1112 \multicolumn{1}{|c|}{\textbf{ADQL}} &
1113 \multicolumn{3}{|c|}{\textbf{VOTable}}
1114 \tabularnewline
1115
1116 \hline
1117 \textbf{type} &
1118 \textbf{datatype} &
1119 \textbf{arraysize} &
1120 \textbf{xtype}
1121 \tabularnewline
1122
1123 \hline
1124 CLOB &
1125 char, unicodeChar &
1126 n, n*, * &
1127 -
1128 \tabularnewline
1129
1130 \hline
1131 \end{tabular}
1132 \caption{ADQL type mapping for CLOB}
1133 \label{table:types.character.clob}
1134 \end{table}
1135
1136 The details of how CLOB values are handled within a
1137 database is implementation dependent.
1138
1139 An example use case for CLOB is a URL field that is generated on the fly
1140 using one or more fields stored the database.
1141 Although some of the components are stored in the database, the final URL
1142 that appears in the results is not stored in the database.
1143 Hence it would not be possible to apply ADQL functions or operators to the
1144 URL field without special knowledge of the internal database structure.
1145 However, a service implementation could provide user defined functions
1146 that used knowledge of the internal database structure to perform
1147 specific operations on the generated URL field.
1148
1149 \subsection{Binary types}
1150 \label{sec:types.binary}
1151
1152 \subsubsection{Binary primitives}
1153 \label{sec:types.binary.primitive}
1154
1155 The BINARY and VARBINARY datatypes map to the \verb:unsignedByte: type defined
1156 in the \VOTspec.
1157
1158 \begin{table}[thm]\footnotesize
1159 \begin{tabular}
1160 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
1161 \hline
1162
1163 \hline
1164 \multicolumn{1}{|c|}{\textbf{ADQL}} &
1165 \multicolumn{3}{|c|}{\textbf{VOTable}}
1166 \tabularnewline
1167
1168 \hline
1169 \textbf{type} &
1170 \textbf{datatype} &
1171 \textbf{arraysize} &
1172 \textbf{xtype}
1173 \tabularnewline
1174
1175 \hline
1176 BINARY(n) &
1177 unsignedByte &
1178 n &
1179 -
1180 \tabularnewline
1181
1182 \hline
1183 VARBINARY(n) &
1184 unsignedByte &
1185 n* &
1186 -
1187 \tabularnewline
1188
1189 \hline
1190 \end{tabular}
1191 \caption{ADQL type mapping for character strings}
1192 \label{table:types.binary.primitive}
1193 \end{table}
1194
1195 \subsubsection{BLOB}
1196 \label{sec:types.binary.blob}
1197
1198 To support large blocks of binary data such as images,
1199 ADQL includes the Binary Large OBject (BLOB) datatype,
1200 which behaves as an opaque immutable array of bytes.
1201
1202 None of the ADQL operators apply to BLOB values.
1203 However, specific database implementations MAY provide user
1204 defined functions that operate on some BLOB values.
1205
1206 BLOB values are serialized as arrays of \verb:unsignedByte: defined
1207 in the \VOTspec.
1208
1209 \begin{table}[thm]\footnotesize
1210 \begin{tabular}
1211 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
1212 \hline
1213
1214 \hline
1215 \multicolumn{1}{|c|}{\textbf{ADQL}} &
1216 \multicolumn{3}{|c|}{\textbf{VOTable}}
1217 \tabularnewline
1218
1219 \hline
1220 \textbf{type} &
1221 \textbf{datatype} &
1222 \textbf{arraysize} &
1223 \textbf{xtype}
1224 \tabularnewline
1225
1226 \hline
1227 BLOB &
1228 unsignedByte &
1229 n, n*, * &
1230 -
1231 \tabularnewline
1232
1233 \hline
1234 \end{tabular}
1235 \caption{ADQL type mapping for BLOB}
1236 \label{table:types.binary.blob}
1237 \end{table}
1238
1239 The details of how BLOB values are handled within a
1240 database is implementation dependent.
1241
1242 An example use case for BLOB is for storing thumbnail images
1243 in the database alongside the tabular data.
1244 ADQL does not provide functions or operations that operate on
1245 images.
1246 However, a service implementation could provide user defined
1247 functions that use implemetation specific features to perform
1248 operations on the image data.
1249
1250 \subsection{Geometric types}
1251 \label{sec:types.geom}
1252
1253 ADQL provides support for the POINT, CIRCLE and POLYGON geometric
1254 types defined in the \DALIspec.
1255
1256 \subsubsection{POINT}
1257 \label{sec:types.geom.point}
1258
1259 The POINT datatype maps to the corresponding type defined in the
1260 \DALIspec.
1261
1262 POINT values are serialized as arrays of floating point numbers
1263 using the \verb:point: xtype defined in the \DALIspec.
1264
1265 \begin{table}[thm]\footnotesize
1266 \begin{tabular}
1267 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
1268 \hline
1269
1270 \hline
1271 \multicolumn{1}{|c|}{\textbf{ADQL}} &
1272 \multicolumn{3}{|c|}{\textbf{VOTable}}
1273 \tabularnewline
1274
1275 \hline
1276 \textbf{type} &
1277 \textbf{datatype} &
1278 \textbf{arraysize} &
1279 \textbf{xtype}
1280 \tabularnewline
1281
1282 \hline
1283 POINT &
1284 float, double &
1285 2 &
1286 point
1287 \tabularnewline
1288
1289 \hline
1290 \end{tabular}
1291 \caption{ADQL type mapping for POINT}
1292 \label{table:types.geom.point}
1293 \end{table}
1294
1295 POINT literals can be expressed using the \verb:POINT():
1296 constructor defined in \SectionRef{sec:functions.geom.point}.
1297 For example:
1298 \begin{verbatim}
1299 POINT(
1300 12.3,
1301 45.6
1302 )
1303 \end{verbatim}
1304
1305 %References to POINT-typed columns must be valid wherever the ADQL
1306 %\textit{point} nonterminal is allowed.
1307
1308 \subsubsection{CIRCLE}
1309 \label{sec:types.geom.circle}
1310
1311 The CIRCLE datatype maps to the corresponding type defined in the
1312 \DALIspec.
1313
1314 CIRCLE values are serialized as arrays of floating point numbers
1315 using the \verb:circle: xtype defined in the \DALIspec.
1316
1317 \begin{table}[thm]\footnotesize
1318 \begin{tabular}
1319 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
1320 \hline
1321
1322 \hline
1323 \multicolumn{1}{|c|}{\textbf{ADQL}} &
1324 \multicolumn{3}{|c|}{\textbf{VOTable}}
1325 \tabularnewline
1326
1327 \hline
1328 \textbf{type} &
1329 \textbf{datatype} &
1330 \textbf{arraysize} &
1331 \textbf{xtype}
1332 \tabularnewline
1333
1334 \hline
1335 CIRCLE &
1336 float, double &
1337 3 &
1338 circle
1339 \tabularnewline
1340
1341 \hline
1342 \end{tabular}
1343 \caption{ADQL type mapping for CIRCLE}
1344 \label{table:types.geom.circle}
1345 \end{table}
1346
1347 CIRCLE literals can be expressed using the \verb:CIRCLE():
1348 constructor defined in \SectionRef{sec:functions.geom.circle}.
1349 For example:
1350 \begin{verbatim}
1351 CIRCLE(
1352 12.3,
1353 45.6,
1354 0.5
1355 )
1356 \end{verbatim}
1357
1358 \subsubsection{POLYGON}
1359 \label{sec:types.geom.polygon}
1360
1361 The POLYGON datatype maps to the corresponding type defined in the
1362 \DALIspec.
1363
1364 POLYGON values are serialized as arrays of floating point numbers
1365 using the \verb:polygon: xtype defined in the \DALIspec.
1366
1367 \begin{table}[thm]\footnotesize
1368 \begin{tabular}
1369 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
1370 \hline
1371
1372 \hline
1373 \multicolumn{1}{|c|}{\textbf{ADQL}} &
1374 \multicolumn{3}{|c|}{\textbf{VOTable}}
1375 \tabularnewline
1376
1377 \hline
1378 \textbf{type} &
1379 \textbf{datatype} &
1380 \textbf{arraysize} &
1381 \textbf{xtype}
1382 \tabularnewline
1383
1384 \hline
1385 POLYGON &
1386 float, double &
1387 n, *, n* &
1388 polygon
1389 \tabularnewline
1390
1391 \hline
1392 \end{tabular}
1393 \caption{ADQL type mapping for POLYGON}
1394 \label{table:types.geom.polygon}
1395 \end{table}
1396
1397 POLYGON literals can be expressed using the \verb:POLYGON():
1398 constructor defined in \SectionRef{sec:functions.geom.polygon}.
1399 For example:
1400 \begin{verbatim}
1401 POLYGON(
1402 10.0,
1403 -10.5,
1404 20.0,
1405 20.5,
1406 30.0,
1407 30.5
1408 )
1409 \end{verbatim}
1410 \noindent
1411 describes a triangle, whose vertices are (10.0, -10.5), (20.0, 20.5)
1412 and (30.0, 30.5) degrees.
1413
1414 \subsubsection{REGION}
1415 \label{sec:types.geom.region}
1416
1417 The results of an ADQL query may return a complex region
1418 that cannot be expressed by one of the simple geometric types
1419 defined in the preceding sections.
1420
1421 In order to support complex geometric regions,
1422 the results of an ADQL query may use the xtype \verb:region:
1423 to describe a column that contains a STC-S string
1424 serialization.
1425
1426 The grammar for the content of the STC-S string is defined in the \STCSspec.
1427
1428 \begin{table}[thm]\footnotesize
1429 \begin{tabular}
1430 {|p{0.30\textwidth}|p{0.30\textwidth}|p{0.15\textwidth}|p{0.15\textwidth}|}
1431 \hline
1432
1433 \hline
1434 \multicolumn{1}{|c|}{\textbf{ADQL}} &
1435 \multicolumn{3}{|c|}{\textbf{VOTable}}
1436 \tabularnewline
1437
1438 \hline
1439 \textbf{type} &
1440 \textbf{datatype} &
1441 \textbf{arraysize} &
1442 \textbf{xtype}
1443 \tabularnewline
1444
1445 \hline
1446 - &
1447 char &
1448 * &
1449 region
1450 \tabularnewline
1451
1452 \hline
1453 \end{tabular}
1454 \caption{Type mapping for STC-S region}
1455 \label{table:types.geom.region}
1456 \end{table}
1457
1458 \clearpage
1459 \section{Optional components}
1460 \label{sec:optional}
1461
1462 In addition to the core components, the ADQL language also includes support
1463 for optional features and functions.
1464
1465 The following sections define the optional features that are part of the
1466 the ADQL grammar, but are not required in order to meet the standard for
1467 a basic ADQL service.
1468
1469 It is up to each service implementation to declare which optional or
1470 additional features it supports.
1471
1472 If a service does not declare support for an optional feature,
1473 then a client SHOULD assume that the service does NOT support
1474 that feature, and SHOULD NOT make use of that feature in any
1475 ADQL queries that it sends.
1476
1477 \subsection{Service capabilities}
1478 \label{sec:capabilities}
1479
1480 The \TAPRegExtSpec defines an XML schema that a service SHOULD
1481 use to declare which optional features it supports.
1482
1483 In general, each group of language features is identified by a \verb:type:
1484 URI, and each individual feature within the group is identified by the
1485 feature name.
1486
1487 \AppendixRef{sec:features} contains examples of how to declare support
1488 for each of the language features defined in this document using the
1489 XML schema from the \TAPRegExtSpec.
1490
1491 For full details on the XML schema and how it can be used, please refer to
1492 the \TAPRegExtSpec.
1493
1494 \subsection{Geometrical functions}
1495 \label{sec:functions.geom}
1496 \subsubsection{Overview}
1497 \label{sec:functions.geom.overview}
1498
1499 In addition to the mathematical functions, ADQL provides a the following geometrical
1500 functions to enhance the astronomical usage of the language:
1501
1502 \begin{itemize}
1503 \item AREA
1504 \item BOX
1505 \item CENTROID
1506 \item CIRCLE
1507 \item CONTAINS
1508 \item COORD1
1509 \item COORD2
1510 \item COORDSYS
1511 \item DISTANCE
1512 \item INTERSECTS
1513 \item POINT
1514 \item POLYGON
1515 \end{itemize}
1516
1517 \subsubsection{Coordinate limits}
1518 \label{sec:functions.geom.limits}
1519
1520 If the arguments for a geometric function represent spherical coordinates
1521 then the values SHOULD be limited to [0, 360] and [-90, 90],
1522 and the units MUST be in degrees (square degrees for area).
1523
1524 If the arguments for a geometric function represent cartesian coordinates
1525 then there are no inherent limits to the range of values, but
1526 coordinate vectors MUST be normalized.
1527
1528 Details of the mechanism for reporting the out of range arguments are
1529 implementation dependent.
1530
1531 \subsubsection{Datatype functions}
1532 \label{sec:functions.geom.type}
1533
1534 The following functions provide constructors for each of the geometry datatypes.
1535 The semantics of these datatypes are based on the corresponding
1536 concepts from the \STCspec data model.
1537
1538 The geometry datatypes and expressions are part of the core \verb:<value_expression>:
1539 in the ADQL grammar.
1540
1541 \begin{verbatim}
1542 <value_expression> ::=
1543 <numeric_value_expression>
1544 | <string_value_expression>
1545 | <boolean_value_expression>
1546 | <geometry_value_expression>
1547 \end{verbatim}
1548
1549 A \verb:<geometry_value_expression>: does not simply cover the geometry datatype
1550 constructors (POINT, CIRCLE, etc.) but also includes user defined functions and
1551 column values where a geometry datatype is stored in a column.
1552
1553 Therefore, \verb:<geometry_value_expression>: is expanded as:
1554 \begin{verbatim}
1555 <geometry_value_expression> ::=
1556 <value_expression_primary>
1557 | <geometry_value_function>
1558 \end{verbatim}
1559 \noindent
1560 where
1561 \begin{verbatim}
1562 <geometry_value_function> ::=
1563 <box>
1564 | <centroid>
1565 | <circle>
1566 | <point>
1567 | <polygon>
1568 | <user_defined_function>
1569 \end{verbatim}
1570 and \verb:<value_expression_primary>: enables the use of geometric functions
1571 and column references.
1572
1573 \subsubsection{Coordsys}
1574 \label{sec:geom.coordsys.param}
1575
1576 %TODO check on the list ...
1577 For historical reasons, the geometry constructors (BOX, CIRCLE, POINT
1578 and POLYGON) all accept an optional string value as the first argument.
1579 This was originally intended to carry
1580 information on a reference system or other coordinate system metadata.
1581 As of this version of the specification this parameter has been
1582 marked as deprecated. Services are permitted to ignore this parameter and
1583 clients are advised to pass an empty string here. Future versions of this
1584 specification may remove this parameter from the listed functions.
1585
1586 \subsubsection{Predicate functions}
1587 \label{sec:functions.geom.predicate}
1588
1589 Functions CONTAINS and INTERSECTS each accept two geometry datatypes
1590 and return a numeric value of 1 or 0 according to whether the relevant
1591 verb (e.g. contains) is satisfied against the two input geometries;
1592 1 if the condition is met and 0 if it is not.
1593
1594 Each of these functions can be used as a WHERE clause predicate by
1595 comparing the numeric result with zero or one.
1596 For example:
1597 \begin{verbatim}
1598 SELECT
1599 *
1600 FROM
1601 table
1602 WHERE
1603 1 = CONTAINS(
1604 POINT(...),
1605 CIRCLE(...)
1606 )
1607 \end{verbatim}
1608
1609 %REMOVED - speculative, not definitive.
1610 %\noindent
1611 %One would expect later additions to ADQL to add to this range of functions. For
1612 %example, equals, disjoint, touches, crosses, within, overlaps and relate
1613 %are possibilities.
1614
1615 \subsubsection{Utility functions}
1616 \label{sec:functions.geom.utility}
1617
1618 Function COORDSYS extracts the coordinate system string from a given
1619 geometry. To do so it accepts a geometry expression and returns a calculated
1620 string value.
1621
1622 This function has been included as a string value function because it
1623 returns a simple string value.
1624
1625 \begin{verbatim}
1626 <string_value_function> ::=
1627 <string_geometry_function> | <user_defined_function>
1628
1629 <string_geometry_function> ::= <extract_coordsys>
1630
1631 <extract_coordsys> ::=
1632 COORDSYS <left_paren> <geometry_value_expression> <right_paren>
1633 \end{verbatim}
1634
1635 As of this version of the specification the COORDSYS function has
1636 been marked as deprecated. This function may be removed in future versions
1637 of this specification.
1638
1639 Functions like AREA, COORD1, COORD2 and DISTANCE accept a geometry and
1640 return a calculated numeric value.
1641
1642 The specification defines two versions of the DISTANCE function,
1643 one that accepts two geometries, and one that accepts four
1644 separate numeric values, both forms return a numeric value.
1645
1646 The predicate and most of the utility functions are included as numeric
1647 value functions because they return simple numeric values.
1648 Thus:
1649 \begin{verbatim}
1650 <numeric_value_function> ::=
1651 <trig_function>
1652 | <math_function>
1653 | <numeric_geometry_function>
1654 | <user_defined_function>
1655 \end{verbatim}
1656 \noindent
1657 where
1658 \begin{verbatim}
1659 <numeric_geometry_function> ::=
1660 <predicate_geometry_function>
1661 | <non_predicate_geometry_function>
1662 \end{verbatim}
1663 \noindent
1664 and
1665 \begin{verbatim}
1666 <non_predicate_geometry_function> ::=
1667 AREA <left_paren> <geometry_value_expression> <right_paren>
1668 | COORD1 <left_paren> <coord_value> <right_paren>
1669 | COORD2 <left_paren> <coord_value> <right_paren>
1670 | DISTANCE <left_paren>
1671 <coord_value> <comma>
1672 <coord_value>
1673 <right_paren>
1674 | DISTANCE <left_paren>
1675 <numeric_value_expression> <comma>
1676 <numeric_value_expression> <comma>
1677 <numeric_value_expression> <comma>
1678 <numeric_value_expression>
1679 <right_paren>
1680 \end{verbatim}
1681 \noindent
1682 and
1683 \begin{verbatim}
1684 <predicate_geometry_function> ::= <contains> | <intersects>
1685 \end{verbatim}
1686
1687 %\subsubsection{Function definitions}
1688 \clearpage
1689 \label{sec:functions.geom.definitions}
1690
1691 The following sections provide a detailed description for each geometrical
1692 function. In each case, the functionality and usage is described rather
1693 than going into the BNF grammar details as above.
1694
1695 \subsubsection{AREA}
1696 \label{sec:functions.geom.area}
1697 {\footnotesize Language feature :}\\
1698 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1699 {\footnotesize \verb|name: AREA|}\\
1700
1701 The AREA function computes the area, in square degrees, of a given geometry.
1702
1703 For example, an expression to calculate the area of a POLYGON could be
1704 written as follows:
1705 \begin{verbatim}
1706 AREA(
1707 POLYGON(
1708 10.0,
1709 -10.5,
1710 20.0,
1711 20.5,
1712 30.0,
1713 30.5
1714 )
1715 )
1716 \end{verbatim}
1717
1718 The AREA of a single POINT is zero.
1719
1720 The geometry argument may be a literal value, as above, or it may be a
1721 column reference, function or expression that returns a geometric type.
1722 For example:
1723 \begin{verbatim}
1724 AREA(
1725 t1.footprint
1726 )
1727 \end{verbatim}
1728 where \textit{t1.footprint} is a reference to a database column that
1729 contains geometric (POINT, BOX, CIRCLE or POLYGON) values.
1730
1731 \subsubsection{BOX}
1732 \label{sec:functions.geom.box}
1733 {\footnotesize Language feature :}\\
1734 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1735 {\footnotesize \verb|name: BOX|}\\
1736
1737 The BOX function expresses a box on the sky. A BOX is a special case of POLYGON,
1738 defined purely for convenience,
1739 and it corresponds semantically to the equivalent term, Box, defined in
1740 the \STCspec.
1741 %(STC Box, Section 4.5.1.5)
1742
1743 It is specified by a center position and size
1744 (in both axes) defining a cross centered on the center position and
1745 with arms extending, parallel to the coordinate axes at the center position,
1746 for half the respective sizes on either side. The box’s sides are line
1747 segments or great circles intersecting the arms of the cross in its end
1748 points at right angles with the arms.
1749
1750 % Original text in ADQL.
1751 % It is specified by a center position and size
1752 % (in both axes) defining a cross centered on the center position and
1753 % with arms extending, parallel to the coordinate axes at the center position,
1754 % for half the respective sizes on either side. The box’s sides are line
1755 % segments or great circles intersecting the arms of the cross in its end
1756 % points at right angles with the arms.
1757
1758 % Text from STC-20071030
1759 % A Box is a special case of a Polygon, defined purely for convenience.
1760 % It is specified by a center position and size (in both coordinates)
1761 % defining a cross centered on the center position and with arms
1762 % extending, parallel to the coordinate axes at the center position,
1763 % for half the respective sizes on either side.
1764 % The box’s sides are line segments or great circles intersecting the
1765 % arms of the cross in its end points at right angles with the arms.
1766
1767 The function arguments specify the center position and the width and height,
1768 where:
1769 \begin{itemize}
1770 \item the center position is given by a pair of numeric coordinates
1771 in degrees, or a single geometric POINT
1772 \item the width and height are given by numeric values in degrees
1773 \item the center position and the width and height MUST be within the ranges defined in
1774 \SectionRef{sec:functions.geom.limits}.
1775 \end{itemize}
1776
1777 For example, a BOX of ten degrees centered on a position
1778 (25.4, -20.0) in degrees could be written as follows:
1779 \begin{verbatim}
1780 BOX(
1781 25.4,
1782 -20.0,
1783 10.0,
1784 10.0
1785 )
1786 \end{verbatim}
1787
1788 Alternatively, the center position could be expressed as a POINT:
1789 \begin{verbatim}
1790 BOX(
1791 POINT(
1792 25.4,
1793 -20.0
1794 ),
1795 10.0,
1796 10.0
1797 )
1798 \end{verbatim}
1799
1800 The function arguments may be literal values, as above, or they may be
1801 column references, functions or expressions that returns the appropriate
1802 datatypes.
1803 For example:
1804 \begin{verbatim}
1805 BOX(
1806 t1.center,
1807 t1.width,
1808 t1.height
1809 )
1810 \end{verbatim}
1811 where \textit{t1.center}, \textit{t1.width} and \textit{t1.height}
1812 are references to database columns that contain POINT, DOUBLE
1813 and DOUBLE values respectively.
1814 %TODO - ObsCore example
1815
1816 %coordsys param
1817 For historical reasons, the BOX function accepts an optional string
1818 value as the first argument.
1819 As of this version of the specification this parameter has been
1820 marked as deprecated.
1821 Future versions of this specification may remove this parameter
1822 \SectionSee{sec:geom.coordsys.param}.
1823
1824 \subsubsection{CENTROID}
1825 \label{sec:functions.geom.centroid}
1826 {\footnotesize Language feature :}\\
1827 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1828 {\footnotesize \verb|name: CENTROID|}\\
1829
1830 The CENTROID function computes the centroid of a given geometry and returns a POINT.
1831
1832 For example, an expression to calculate the centroid of a POLYGON could
1833 be written as follows :
1834 \begin{verbatim}
1835 CENTROID(
1836 POLYGON(
1837 10.0,
1838 -10.5,
1839 20.0,
1840 20.5,
1841 30.0,
1842 30.5
1843 )
1844 )
1845 \end{verbatim}
1846
1847 The CENTROID of a single POINT is that POINT.
1848
1849 The geometry argument may be a literal value, as above, or it may be a
1850 column reference, function or expression that returns a geometric type.
1851 For example:
1852 \begin{verbatim}
1853 CENTROID(
1854 t1.footprint
1855 )
1856 \end{verbatim}
1857 where \textit{t1.footprint} is a reference to a database column that
1858 contains geometric (POINT, BOX, CIRCLE or POLYGON) values.
1859 %TODO - ObsCore example
1860
1861 \subsubsection{CIRCLE}
1862 \label{sec:functions.geom.circle}
1863 {\footnotesize Language feature :}\\
1864 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1865 {\footnotesize \verb|name: CIRCLE|}\\
1866
1867 The CIRCLE function expresses a circular region on the sky (a cone in space),
1868 and it corresponds semantically to the equivalent term, Circle, defined in
1869 the \STCspec.
1870 %(STC Circle, Section 4.5.1.2)
1871
1872 The function arguments specify the center position and the radius, where:
1873 \begin{itemize}
1874 \item the center position is given by a pair of numeric coordinates
1875 in degrees, or a single geometric POINT
1876 \item the radius is a numeric value in degrees
1877 \item the center position and the radius MUST be within the ranges defined in
1878 \SectionRef{sec:functions.geom.limits}.
1879 \end{itemize}
1880
1881 For example, a CIRCLE of ten degrees radius centered on position
1882 (25.4, -20.0) in degrees could be written as follows:
1883 \begin{verbatim}
1884 CIRCLE(
1885 25.4,
1886 -20.0,
1887 10.0
1888 )
1889 \end{verbatim}
1890
1891 Alternatively, the center position may be expressed as a POINT:
1892 \begin{verbatim}
1893 CIRCLE(
1894 POINT(
1895 25.4,
1896 -20.0,
1897 ),
1898 10.0
1899 )
1900 \end{verbatim}
1901
1902 The position argument may be a literal value, as above, or it may be a
1903 column reference, function or expression that returns a geometric type.
1904 For example:
1905 \begin{verbatim}
1906 CIRCLE(
1907 t1.center,
1908 t1.radius
1909 )
1910 \end{verbatim}
1911 where \textit{t1.center} and \textit{t1.radius} are references to
1912 database columns that contain POINT and DOUBLE values respectively.
1913 %TODO - ObsCore example
1914
1915 %coordsys param
1916 For historical reasons, the CIRCLE function accepts an optional string
1917 value as the first argument.
1918 As of this version of the specification this parameter has been
1919 marked as deprecated.
1920 Future versions of this specification may remove this parameter
1921 \SectionSee{sec:geom.coordsys.param}.
1922
1923 \subsubsection{CONTAINS}
1924 \label{sec:functions.geom.contains}
1925 {\footnotesize Language feature :}\\
1926 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
1927 {\footnotesize \verb|name: CONTAINS|}\\
1928
1929 The CONTAINS function determines if a geometry is wholly contained within
1930 another. This is most commonly used to express a "point-in-shape" condition.
1931
1932 For example, an expression to determine whether the point (25.0, -19.5) degrees
1933 is within a circle of ten degrees radius centered on position (25.4, -20.0) degrees,
1934 could be written as follows:
1935 \begin{verbatim}
1936 CONTAINS(
1937 POINT(
1938 25.0,
1939 -19.5
1940 ),
1941 CIRCLE(
1942 25.4,
1943 -20.0,
1944 10.0
1945 )
1946 )
1947 \end{verbatim}
1948
1949 The CONTAINS function is not symmetric in the meaning of the arguments.
1950
1951 The CONTAINS function returns the numeric value 1 if the first argument
1952 is in, or on, the boundary of the second argument and the numeric value 0
1953 if it is not.
1954
1955 When used as a predicate in the WHERE clause of a query, the numeric return
1956 value must be compared to the numeric values 0 or 1 to form a SQL predicate:
1957 \begin{verbatim}
1958 WHERE
1959 1 = CONTAINS(
1960 POINT(
1961 25.0,
1962 -19.5
1963 ),
1964 CIRCLE(
1965 25.4,
1966 -20.0,
1967 10.0
1968 )
1969 )
1970 \end{verbatim}
1971 \noindent
1972 for "does contain" and
1973 \begin{verbatim}
1974 WHERE
1975 0 = CONTAINS(
1976 POINT(
1977 25.0,
1978 -19.5
1979 ),
1980 CIRCLE(
1981 25.4,
1982 -20.0,
1983 10.0
1984 )
1985 )
1986 \end{verbatim}
1987 \noindent
1988 for "does not contain".
1989
1990 %TODO - CONTAINS(thing, POINT) ?
1991
1992 The geometric arguments for CONTAINS may be literal values, as above,
1993 or they may be column references, functions or expressions that return
1994 geometric values.
1995 For example:
1996 \begin{verbatim}
1997 WHERE
1998 0 = CONTAINS(
1999 t1.center,
2000 t2.footprint
2001 )
2002 \end{verbatim}
2003 where \textit{t1.center} and \textit{t2.footprint} are references to
2004 database columns that contain POINT and geometric (BOX, CIRCLE or POLYGON)
2005 values respectively.
2006 %TODO - ObsCore example
2007
2008 %coordsys trans
2009 If the geometric arguments are expressed in different coordinate systems,
2010 the CONTAINS function is responsible for converting one, or both, of the
2011 arguments into a different coordinate system.
2012 If the CONTAINS function cannot perform the required conversion then
2013 it SHOULD throw an error.
2014 Details of the mechanism for reporting the error condition are
2015 implementation dependent.
2016
2017 \subsubsection{COORD1}
2018 \label{sec:functions.geom.coord1}
2019 {\footnotesize Language feature :}\\
2020 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
2021 {\footnotesize \verb|name: COORD1|}\\
2022
2023 The COORD1 function extracts the first coordinate value, in degrees, of a given
2024 POINT \SectionSee{sec:functions.geom.point} or column reference.
2025
2026 For example, the right ascension of a point with position (25, -19.5) in
2027 degrees would be obtained using the following expression:
2028 \begin{verbatim}
2029 COORD1(
2030 POINT(
2031 25.0,
2032 -19.5
2033 )
2034 )
2035 \end{verbatim}
2036 \noindent
2037 which would return a numeric value of 25.0 degrees.
2038
2039 For example:
2040 \begin{verbatim}
2041 COORD1(
2042 t.center
2043 )
2044 \end{verbatim}
2045 \noindent
2046 where \textit{t.center} is a reference to a column that contans POINT values.
2047
2048 \subsubsection{COORD2}
2049 \label{sec:functions.geom.coord2}
2050 {\footnotesize Language feature :}\\
2051 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
2052 {\footnotesize \verb|name: COORD2|}\\
2053
2054 The COORD2 function extracts the second coordinate value, in degrees, of a given
2055 POINT \SectionSee{sec:functions.geom.point} or column reference.
2056
2057 For example, the declination of a point with position (25, -19.5) in degrees,
2058 could be obtained using the following expression:
2059 \begin{verbatim}
2060 COORD2(
2061 POINT(
2062 25.0,
2063 -19.5
2064 )
2065 )
2066 \end{verbatim}
2067 \noindent
2068 which would return a numeric value of -19.5 degrees.
2069
2070 The COORD2 function may be applied to any expression that returns a
2071 geometric POINT value.
2072 For example:
2073 \begin{verbatim}
2074 COORD2(
2075 t.center
2076 )
2077 \end{verbatim}
2078 \noindent
2079 where \textit{t.center} is a reference to a column that contans POINT values.
2080
2081 \subsubsection{COORDSYS}
2082 \label{sec:functions.geom.coordsys}
2083 {\footnotesize Language feature :}\\
2084 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
2085 {\footnotesize \verb|name: COORDSYS|}\\
2086
2087 As of this version of the specification the COORDSYS function has
2088 been marked as deprecated. This function may be removed in future versions
2089 of this specification.
2090 Details of the coordinate system for a database column are available as part of
2091 the service metadata, available via the \verb:TAP_SCHEMA: tables defined in the
2092 \TAPspec and the \verb:/tables: webservice response defined in the \VOSIspec.
2093
2094 %As described in \SectionRef{sec:functions.geom.overview}, the allowed return values must be defined
2095 %by any service making use of ADQL, and a list of standard coordinate system
2096 %literals can be found in the STC specification.
2097 % STC-reference 'STC specification [3]'
2098
2099 The COORDSYS function returns the formal name of the coordinate system for
2100 a given geometry as a string.
2101
2102 The following example would return the coordinate system of a POINT literal:
2103 \begin{verbatim}
2104 COORDSYS(
2105 POINT(
2106 25.0,
2107 -19.5
2108 )
2109 )
2110 \end{verbatim}
2111 \noindent
2112 which would return a string value representing the coordinate system used
2113 to create the POINT.
2114
2115 The COORDSYS function may be applied to any expression that returns a
2116 geometric datatype. For example:
2117 \begin{verbatim}
2118 COORDSYS(
2119 t.footprint
2120 )
2121 \end{verbatim}
2122 \noindent
2123 where \textit{t.footprint} is a reference to a database column that
2124 contains geometric (POINT, BOX, CIRCLE or POLYGON) values.
2125
2126 \subsubsection{DISTANCE}
2127 \label{sec:functions.geom.distance}
2128 {\footnotesize Language feature :}\\
2129 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
2130 {\footnotesize \verb|name: DISTANCE|}\\
2131
2132 The DISTANCE function computes the arc length along a great circle between two
2133 points and returns a numeric value expression in degrees.
2134
2135 The specification defines two versions of the DISTANCE function, one that
2136 accepts two POINT values, and a second that accepts four separate numeric
2137 values.
2138
2139 If an ADQL service implementation declares support for DISTANCE,
2140 then it must implement both the two parameter and four parameter
2141 forms of the function.
2142
2143 For example, an expression calculating the distance between two points of
2144 coordinates (25,-19.5) and (25.4,-20) could be written as follows:
2145 \begin{verbatim}
2146 DISTANCE(
2147 POINT(
2148 25.0,
2149 -19.5
2150 ),
2151 POINT(
2152 25.4,
2153 -20.0
2154 )
2155 )
2156 \end{verbatim}
2157 \noindent
2158 where all numeric values and the returned arc length are in degrees.
2159
2160 The equivalent call to the four parameter form of the function would be:
2161 \begin{verbatim}
2162 DISTANCE(
2163 25.0,
2164 -19.5,
2165 25.4,
2166 -20.0
2167 )
2168 \end{verbatim}
2169
2170 The DISTANCE function may be applied to any expression that returns a
2171 geometric POINT value.
2172 For example, the distance between to points stored in the database could
2173 be calculated as follows:
2174 \begin{verbatim}
2175 DISTANCE(
2176 t1.target,
2177 t2.reference
2178 )
2179 \end{verbatim}
2180 \noindent
2181 where \textit{t1.target} and \textit{t2.reference} are references to
2182 database columns that contain POINT values.
2183
2184 %coordsys trans
2185 If the geometric arguments are expressed in different coordinate systems,
2186 the DISTANCE function is responsible for converting one, or both, of the
2187 arguments into a different coordinate system.
2188 If the DISTANCE function cannot perform the required conversion then
2189 it SHOULD throw an error.
2190 Details of the mechanism for reporting the error condition are
2191 implementation dependent.
2192
2193 It is assumed that the arguments for the four numeric parameter form all
2194 use the same coordinate system.
2195
2196 \subsubsection{INTERSECTS}
2197 \label{sec:functions.geom.intersects}
2198 {\footnotesize Language feature :}\\
2199 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
2200 {\footnotesize \verb|name: INTERSECTS|}\\
2201
2202 The INTERSECTS function determines if two geometry values overlap. This is
2203 most commonly used to express a "shape-vs-shape" intersection test.
2204
2205 For example, an expression to determine whether a circle of one degree radius
2206 centered on position (25.4, -20.0) degrees overlaps with a box of ten degrees
2207 centered on position (20.0, -15.0) degrees, could be written as follows:
2208 \begin{verbatim}
2209 INTERSECTS(
2210 CIRCLE(
2211 25.4,
2212 -20.0,
2213 1
2214 ),
2215 BOX(
2216 20.0,
2217 -15.0,
2218 10,
2219 10
2220 )
2221 )
2222 \end{verbatim}
2223 \noindent
2224 where the INTERSECTS function returns the numeric value 1 if the two arguments
2225 overlap and 0 if they do not.
2226
2227 When used as a predicate in the WHERE clause of a query, the numeric return
2228 value must be compared to the numeric values 0 or 1 to form a SQL predicate:
2229 \begin{verbatim}
2230 WHERE
2231 1 = INTERSECTS(
2232 CIRCLE(
2233 25.4,
2234 -20.0,
2235 1
2236 ),
2237 BOX(
2238 20.0,
2239 -15.0,
2240 10,
2241 10
2242 )
2243 )
2244 \end{verbatim}
2245 \noindent
2246 for "does intersect" and
2247 \begin{verbatim}
2248 WHERE
2249 0 = INTERSECTS(
2250 CIRCLE(
2251 25.4,
2252 -20.0,
2253 1
2254 ),
2255 BOX(
2256 20.0,
2257 -15.0,
2258 10,
2259 10
2260 )
2261 )
2262 \end{verbatim}
2263 \noindent
2264 for "does not intersect".
2265
2266 The geometric arguments for INTERSECTS may be literal values, as above,
2267 or they may be column references, functions or expressions that return
2268 geometric values.
2269 For example:
2270 \begin{verbatim}
2271 WHERE
2272 0 = INTERSECTS(
2273 t1.target,
2274 t2.footprint
2275 )
2276 \end{verbatim}
2277 where \textit{t1.target} and \textit{t2.footprint} are references to
2278 database columns that contain geometric (BOX, CIRCLE or POLYGON) values.
2279
2280 %TODO
2281 The arguments to INTERSECTS SHOULD be geometric expressions evaluating to
2282 either BOX, CIRCLE or POLYGON.
2283 Previous versions of this
2284 specification also allowed POINT values and required server implementations to
2285 interpret the expression as a CONTAINS with the POINT moved into the first position.
2286 Server implementations SHOULD still implement that behaviour, but clients
2287 SHOULD NOT expect it.
2288 This behaviour will be dropped in the next major version of this specification.
2289
2290 %coordsys trans
2291 If the geometric arguments are expressed in different coordinate systems,
2292 the INTERSECTS function is responsible for converting one, or both, of the
2293 arguments into a different coordinate system.
2294 If the INTERSECTS function cannot perform the required conversion then
2295 it SHOULD throw an error.
2296 Details of the mechanism for reporting the error condition are
2297 implementation dependent.
2298
2299 \subsubsection{POINT}
2300 \label{sec:functions.geom.point}
2301 {\footnotesize Language feature :}\\
2302 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
2303 {\footnotesize \verb|name: POINT|}\\
2304
2305 The POINT function expresses a single location on the sky,
2306 and it corresponds semantically to the equivalent term, SpatialCoord, defined in
2307 the \STCspec.
2308 %(STC SpatialCoord, Section 4.4.2.2)
2309
2310 The function arguments specify the position, where:
2311 \begin{itemize}
2312 \item the position is given by a pair of numeric coordinates in degrees
2313 \item the numeric coordinates MUST be within the ranges defined in
2314 \SectionRef{sec:functions.geom.limits}.
2315 \end{itemize}
2316
2317 For example, a function expressing a point with right ascension of 25 degrees
2318 and declination of -19.5 degrees would be written as follows:
2319 \begin{verbatim}
2320 POINT(
2321 25.0,
2322 -19.5
2323 )
2324 \end{verbatim}
2325 \noindent
2326 where numeric values are in degrees.
2327
2328 The coordinates for POINT may be literal values, as above,
2329 or they may be column references, functions or expressions that return
2330 numeric values.
2331 For example:
2332 \begin{verbatim}
2333 POINT(
2334 t.ra,
2335 t.dec
2336 )
2337 \end{verbatim}
2338 \noindent
2339 where \textit{t.ra} and \textit{t.dec} are references to database
2340 columns that contain numeric values.
2341 %TODO - ObsCore example
2342
2343 %coordsys param
2344 For historical reasons, the POINT function accepts an optional string
2345 value as the first argument.
2346 As of this version of the specification this parameter has been
2347 marked as deprecated.
2348 Future versions of this specification may remove this parameter
2349 \SectionSee{sec:geom.coordsys.param}.
2350
2351 \subsubsection{POLYGON}
2352 \label{sec:functions.geom.polygon}
2353 {\footnotesize Language feature :}\\
2354 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
2355 {\footnotesize \verb|name: POLYGON|}\\
2356
2357 The POLYGON function expresses a region on the sky with boundaries denoted by great
2358 circles passing through specified coordinates. It corresponds semantically
2359 to the STC Polygon.
2360 %(STC Polygon, Section 4.5.1.4)
2361
2362 A polygon is described by a list of vertices in a single coordinate system, with
2363 each vertex connected to the next along a great circle and the last vertex
2364 implicitly connected to the first vertex.
2365
2366 The function arguments specify three or more vertices, where:
2367 \begin{itemize}
2368 \item the position of the vertices are given as a sequence of
2369 numeric coordinates in degrees, or as a sequence of geometric POINTs
2370 \item the numeric coordinates MUST be within the ranges defined in
2371 \SectionRef{sec:functions.geom.limits}
2372 \end{itemize}
2373
2374 For example, a function expressing a triangle, whose vertices are (10.0,
2375 -10.5), (20.0, 20.5) and (30.0,30.5) in degrees would be written
2376 as follows:
2377 \begin{verbatim}
2378 POLYGON(
2379 10.0,
2380 -10.5,
2381 20.0,
2382 20.5,
2383 30.0,
2384 30.5
2385 )
2386 \end{verbatim}
2387 \noindent
2388 where all numeric values are in degrees.
2389
2390 The coordinates for the POLYGON vertices may be literal values, as above,
2391 or they may be column references, functions or expressions that return
2392 numeric values.
2393 For example:
2394 \begin{verbatim}
2395 POLYGON(
2396 t1.ra,
2397 t1.dec + 5,
2398 t1.ra - 5,
2399 t1.dec - 5,
2400 t1.ra - 5,
2401 t1.dec + 5,
2402 )
2403 \end{verbatim}
2404 \noindent
2405 where \textit{t1.ra} and \textit{t1.dec} are references to database columns
2406 that contain numeric values.
2407 %TODO - ObsCore example
2408
2409 Alternatively, the coordinates for the POLYGON vertices may be column references,
2410 functions or expressions that return POINT values.
2411 For example:
2412 \begin{verbatim}
2413 POLYGON(
2414 t2.toppoint,
2415 t2.bottomleft,
2416 t2.bottomright
2417 )
2418 \end{verbatim}
2419 \noindent
2420 where \textit{t2.toppoint}, \textit{t2.bottomleft} and \textit{t2.bottomright}
2421 are references to database columns that contain POINT values.
2422 %TODO - ObsCore example
2423
2424 The coordinates for the vertices MUST all be expressed in the same datatype.
2425 The POLYGON function does not support a mixture of numeric and POINT
2426 arguments.
2427
2428 %coordsys param
2429 For historical reasons, the POLYGON function accepts an optional string
2430 value as the first argument.
2431 As of this version of the specification this parameter has been
2432 marked as deprecated.
2433 Future versions of this specification may remove this parameter
2434 \SectionSee{sec:geom.coordsys.param}.
2435
2436 \subsection{User defined functions}
2437 \label{sec:user.functions}
2438 \subsubsection{Overview}
2439
2440 ADQL also provides a place holder to define user specific functions. The grammar
2441 definition for user defined functions includes a variable list of parameters.
2442
2443 \begin{verbatim}
2444 <user_defined_function> ::=
2445 <user_defined_function_name> <left_paren>
2446 [
2447 <value_expression>
2448 [
2449 {
2450 <comma> <value_expression>
2451 }...
2452 ]
2453 ]
2454 <right_paren>
2455 \end{verbatim}
2456
2457 In order to avoid name conflicts, user defined function names SHOULD include
2458 a prefix which indicates the name of the institute or project which created
2459 the function.
2460
2461 For example, the names of \verb:align: and \verb:convert: functions developed
2462 by the Wide Field Astronomy Unit (WFAU) could be prefixed as follows:
2463 \begin{verbatim}
2464 wfau_align()
2465 wfau_convert()
2466 \end{verbatim}
2467
2468 This enables users to distinguish between functions with similar names developed
2469 by a different service provider, e.g. the German Astrophysical Virtual
2470 Observatory (GAVO):
2471 \begin{verbatim}
2472 gavo_align()
2473 gavo_convert()
2474 \end{verbatim}
2475
2476 The \verb:ivo: prefix is reserved for functions that have been defined
2477 in an IVOA specification. For example the \RegTAPspec defines the following
2478 functions:
2479 \begin{verbatim}
2480 ivo_nocasematch()
2481 ivo_hasword()
2482 ivo_hashlist_has()
2483 ivo_string_agg()
2484 \end{verbatim}
2485
2486 %If metadata on a user defined function is available, this should be used. For
2487 %example function names and cardinality of arguments should be checked against
2488 %metadata where available.
2489
2490 \subsubsection{Metadata}
2491 \label{sec:user.metadata}
2492
2493 The URI for identifying the language feature for a user defined function
2494 is defined as part of the \TAPRegExtSpec.
2495
2496 \begin{verbatim}
2497 ivo://ivoa.net/std/TAPRegExt#features-udf
2498 \end{verbatim}
2499
2500 For user defined functions, the \verb:form: element of the language feature
2501 declaration must contain the signature of the function, written to match
2502 the signature nonterminal in the following grammar:
2503 \begin{verbatim}
2504 signature ::= <funcname> <arglist> "->" <type_name>
2505 funcname ::= <regular_identifier>
2506 arglist ::= "(" <arg> { "," <arg> } ")"
2507 arg ::= <regular_identifier> <type_name>
2508 \end{verbatim}
2509
2510 For example, the following fragment declares a user defined function that
2511 takes two \verb:TEXT: parameters and returns an integer, zero or one,
2512 depending on the regular expression pattern matching:
2513 \begin{verbatim}
2514 <languageFeatures type="ivo://ivoa.net/std/TAPRegExt#features-udf">
2515 <feature>
2516 <form>match(pattern TEXT, string TEXT) -> INTEGER</form>
2517 <description>
2518 match returns 1 if the POSIX regular expression pattern
2519 matches anything in string, 0 otherwise.
2520 </description>
2521 </feature>
2522 </languageFeatures>
2523 \end{verbatim}
2524
2525 See the \TAPRegExtSpec for full details on how to use the
2526 XML schema to declare user defined functions.
2527
2528 \subsection{String functions and operators}
2529 \label{sec:string.functions}
2530
2531 An ADQL service implementation MAY include support for the following optional
2532 string manipulation and comparison operators:
2533
2534 \begin{itemize}
2535 \item \verb:LOWER(): Lower case conversion
2536 \item \verb:ILIKE: Case-insensitive comparison.
2537 \end{itemize}
2538
2539 \subsubsection{LOWER}
2540 \label{sec:string.functions.lower}
2541 {\footnotesize Language feature :}\\
2542 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-string|}\\
2543 {\footnotesize \verb|name: LOWER|}\\
2544
2545 The LOWER function converts its string parameter to lower case.
2546
2547 Since case folding is a nontrivial operation in a multi-encoding world,
2548 ADQL requires standard behaviour for the ASCII characters, and recommends
2549 following algorithm R2 described in Section 3.13, "Default Case Algorithms"
2550 of \citet{std:UNICODE} for characters outside the ASCII set.
2551
2552 \begin{verbatim}
2553 LOWER('Francis Albert Augustus Charles Emmanuel')
2554 =>
2555 francis albert augustus charles emmanuel
2556 \end{verbatim}
2557
2558 \subsubsection{ILIKE}
2559 \label{sec:string.functions.ilike}
2560 {\footnotesize Language feature :}\\
2561 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-string|}\\
2562 {\footnotesize \verb|name: ILIKE|}\\
2563
2564 The ILIKE string comparison operator performs a case-insensitive comparison
2565 of its string operands.
2566
2567 \begin{verbatim}
2568 'Francis' LIKE 'francis' => False
2569
2570 'Francis' ILIKE 'francis' => True
2571 \end{verbatim}
2572
2573 Since case folding is a nontrivial operation in a multi-encoding world,
2574 ADQL requires standard behaviour for the ASCII characters, and recommends
2575 following algorithm R2 described in Section 3.13, "Default Case Algorithms"
2576 of \citet{std:UNICODE} for characters outside the ASCII set.
2577
2578 \subsection{Set operators}
2579 \label{sec:set.operators}
2580
2581 An ADQL service implementation MAY include support for the following optional
2582 set operators:
2583
2584 \begin{itemize}
2585 \item \verb:UNION:
2586 \item \verb:EXCEPT:
2587 \item \verb:INTERSECT:
2588 \end{itemize}
2589
2590 \subsubsection{UNION}
2591 {\footnotesize Language feature :}\\
2592 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-sets|}\\
2593 {\footnotesize \verb|name: UNION|}\\
2594
2595 The UNION operator combines the results of two queries, accepting rows from
2596 both the first and second set of results,
2597 removing duplicate rows unless UNION ALL is used.
2598
2599 For a UNION operation to be valid in ADQL, the following criteria MUST be met:
2600 \begin{itemize}
2601 \item the two queries MUST result in the same number of columns
2602 \item the columns in the operands MUST have the same datatypes.
2603 \end{itemize}
2604
2605 In addition, the following criteria SHOULD be met:
2606 \begin{itemize}
2607 \item the columns in the operands SHOULD have the same metadata, e.g. units, UCD, etc.
2608 \item the metadata for the results SHOULD be generated from the left-hand operand.
2609 \end{itemize}
2610
2611 Note that the comparison used for removing duplicates is based purely on the
2612 column value and does not take into account the units.
2613 This means that a row with a numeric value of \verb:2: and units of \verb:m:
2614 and a row with a numeric value of \verb:2: and units of \verb:km: will be
2615 considered equal, despite the difference in units.
2616 \begin{verbatim}
2617 2m = 2km
2618 \end{verbatim}
2619
2620 \subsubsection{EXCEPT}
2621 {\footnotesize Language feature :}\\
2622 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-sets|}\\
2623 {\footnotesize \verb|name: EXCEPT|}\\
2624
2625 The EXCEPT operator combines the results of two queries, accepting rows that are
2626 in the first set of results but are not in the second,
2627 removing duplicate rows unless EXCEPT ALL is used.
2628
2629 For an EXCEPT operation to be valid in ADQL, the following criteria MUST be met:
2630
2631 \begin{itemize}
2632 \item the two queries MUST result in the same number of columns
2633 \item the columns in the operands MUST have the same datatypes.
2634 \end{itemize}
2635
2636 In addition, the following criteria SHOULD be met:
2637 \begin{itemize}
2638 \item the columns in the operands SHOULD have the same metadata, e.g. units, UCD, etc.
2639 \item the metadata for the results MUST be generated from the left-hand operand.
2640 \end{itemize}
2641
2642 Note that the comparison used for removing duplicates is based purely on the
2643 column value and does not take into account the units.
2644 This means that a row with a numeric value of \verb:2: and units of \verb:m:
2645 and a row with a numeric value of \verb:2: and units of \verb:km: will be
2646 considered equal, despite the difference in units.
2647 \begin{verbatim}
2648 2m = 2km
2649 \end{verbatim}
2650
2651 \subsubsection{INTERSECT}
2652 {\footnotesize Language feature :}\\
2653 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-sets|}\\
2654 {\footnotesize \verb|name: INTERSECT|}\\
2655
2656 The INTERSECT operator combines the results of two queries, accepting rows that are
2657 in the first set of results but are not in the second,
2658 removing duplicate rows unless INTERSECT ALL is used.
2659
2660 For an INTERSECT operation to be valid in ADQL, the following criteria MUST be met:
2661
2662 \begin{itemize}
2663 \item the two queries MUST result in the same number of columns
2664 \item the columns in the operands MUST have the same datatypes.
2665 \end{itemize}
2666
2667 In addition, the following criteria SHOULD be met:
2668 \begin{itemize}
2669 \item the columns in the operands SHOULD have the same metadata, e.g. units, UCD, etc.
2670 \item the metadata for the results MUST be generated from the left-hand operand.
2671 \end{itemize}
2672
2673 Note that the comparison used for removing duplicates is based purely on the
2674 column value and does not take into account the units.
2675 This means that a row with a numeric value of \verb:2: and units of \verb:m:
2676 and a row with a numeric value of \verb:2: and units of \verb:km: will be
2677 considered equal, despite the difference in units.
2678 \begin{verbatim}
2679 2m = 2km
2680 \end{verbatim}
2681
2682 \subsection{Common table expressions}
2683 \label{sec:common-table}
2684
2685 An ADQL service implementation MAY include support for the following optional
2686 support for common table expressions:
2687
2688 \begin{itemize}
2689 \item \verb:WITH:
2690 \end{itemize}
2691
2692 \subsubsection{WITH}
2693 {\footnotesize Language feature :}\\
2694 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-common-table|}\\
2695 {\footnotesize \verb|name: WITH|}\\
2696
2697 The WITH operator creates a temporary named result set that can be referred
2698 to elsewhere in the main query.
2699
2700 Using a common table expression can make complex queries easier to understand
2701 by factoring subqueries out of the main SQL statement.
2702
2703 For example, the following query with a nested subquery:
2704 \begin{verbatim}
2705 SELECT
2706 ra,
2707 dec
2708 FROM
2709 (
2710 SELECT
2711 *
2712 FROM
2713 alpha_source
2714 WHERE
2715 id % 10 = 0
2716 )
2717 WHERE
2718 ra > 10
2719 AND
2720 ra < 20
2721 \end{verbatim}
2722 \noindent
2723 can be refactored as a named WITH query and a simpler main query:
2724 \begin{verbatim}
2725 WITH alpha_subset AS
2726 (
2727 SELECT
2728 *
2729 FROM
2730 alpha_source
2731 WHERE
2732 id % 10 = 0
2733 )
2734
2735 SELECT
2736 ra,
2737 dec
2738 FROM
2739 alpha_subset
2740 WHERE
2741 ra > 10
2742 AND
2743 ra < 20
2744 \end{verbatim}
2745
2746 The current version of ADQL does not support recursive common table expressions.
2747
2748 \subsection{Type operations}
2749 \label{sec:type}
2750
2751 An ADQL service implementation MAY include support for the following optional
2752 type conversion functions:
2753
2754 \begin{itemize}
2755 \item \verb:CAST():
2756 \end{itemize}
2757
2758 \subsubsection{CAST}
2759 \label{sec:type.cast}
2760 {\footnotesize Language feature :}\\
2761 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-type|}\\
2762 {\footnotesize \verb|name: CAST|}\\
2763
2764 The \verb:CAST(): function returns the value of the first argument converted
2765 to the datatype specified by the second argument.
2766
2767 The ADQL \verb:CAST(): function does not replicate the full functionality
2768 and range of types supported by common RDBMS implementations of CAST.
2769
2770 The ADQL \verb:CAST(): function only supports type conversion between the
2771 standard numeric datatypes. The \verb:CAST(): function does not support
2772 casting to or from the character, binary, datetime or geometric datatypes.
2773
2774 The rounding mechanism used when converting from floating point values
2775 (REAL or DOUBLE) to integer values (SHORTINT, INTEGER or BIGINT) is
2776 implementation dependent.
2777
2778 When converting a numeric value to a datatype that is too small to represent
2779 the value, this SHOULD be treated as an error.
2780 Details of the mechanism for reporting the error condition are
2781 implementation dependent.
2782
2783 \subsection{Unit operations}
2784 \label{sec:unit}
2785
2786 An ADQL service implementation MAY include support for the following optional
2787 unit conversion functions:
2788
2789 \begin{itemize}
2790 \item \verb:IN_UNIT():
2791 \end{itemize}
2792
2793 \subsubsection{IN\_UNIT}
2794 \label{sec:unit.cast}
2795 {\footnotesize Language feature :}\\
2796 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-unit|}\\
2797 {\footnotesize \verb|name: IN_UNIT|}\\
2798
2799 The \verb:IN_UNIT(): function returns the value of the first argument
2800 transformed into the units defined by the second argument.
2801
2802 The second argument MUST be a string literal containing a valid unit
2803 description using the formatting defined in the \VOUnitSpec.
2804
2805 The system SHOULD report an error in response to the following conditions:
2806 \begin{itemize}
2807 \item if the second argument is not a valid unit description
2808 \item if the system is not able to convert the value into the requested units.
2809 \end{itemize}
2810
2811 Details of the mechanism for reporting the error condition are
2812 implementation dependent.
2813
2814 \subsection{Bitwise operators}
2815 \label{sec:bitwise}
2816
2817 An ADQL service implementation MAY include support for the following optional
2818 bitwise operators:
2819
2820 \begin{itemize}
2821 \item not \verb:~ x:
2822 \item and \verb:x & y:
2823 \item or \verb:x | y:
2824 \item xor \verb:x ^ y:
2825 \end{itemize}
2826
2827 \subsubsection{Bit AND}
2828 \label{sec:bitwise.and}
2829 {\footnotesize Language feature :}\\
2830 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-bitwise|}\\
2831 {\footnotesize \verb|name: BIT_AND|}\\
2832
2833 The ampersand (\verb:&:) operator performs a bitwise AND operation
2834 on two integer operands.
2835
2836 \begin{verbatim}
2837 x & y
2838 \end{verbatim}
2839
2840 The bitwise AND operation is only valid for integer numeric values,
2841 SMALLINT, INTEGER or BIGINT.
2842 If the operands are not integer values, then the result of the bitwise
2843 AND operation is undefined.
2844
2845 \subsubsection{Bit OR}
2846 \label{sec:bitwise.or}
2847 {\footnotesize Language feature :}\\
2848 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-bitwise|}\\
2849 {\footnotesize \verb|name: BIT_OR|}\\
2850
2851 The vertical bar (\verb:|:) operator performs a bitwise OR operation
2852 on two integer operands.
2853
2854 \begin{verbatim}
2855 x | y
2856 \end{verbatim}
2857
2858 The bitwise OR operation is only valid for integer numeric values,
2859 SMALLINT, INTEGER or BIGINT.
2860 If the operands are not integer values, then the result of the bitwise OR
2861 operation is undefined.
2862
2863 \subsubsection{Bit XOR}
2864 \label{sec:bitwise.xor}
2865 {\footnotesize Language feature :}\\
2866 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-bitwise|}\\
2867 {\footnotesize \verb|name: BIT_XOR|}\\
2868
2869 The circumflex (\verb:^:) operator performs a bitwise XOR (exclusive or)
2870 operation on two integer operands.
2871
2872 \begin{verbatim}
2873 x ^ y
2874 \end{verbatim}
2875
2876 The bitwise XOR operation is only valid for integer numeric values,
2877 SMALLINT, INTEGER or BIGINT.
2878 If the operands are not integer values, then the result of the bitwise
2879 XOR operation is undefined.
2880
2881 \subsubsection{Bit NOT}
2882 \label{sec:bitwise.not}
2883 {\footnotesize Language feature :}\\
2884 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-bitwise|}\\
2885 {\footnotesize \verb|name: BIT_NOT|}\\
2886
2887 The tilde (\verb:~:) operator performs a bitwise NOT operation on an integer operand.
2888
2889 \begin{verbatim}
2890 ~ x
2891 \end{verbatim}
2892
2893 The bitwise NOT operation is only valid for integer numeric values,
2894 SMALLINT, INTEGER or BIGINT.
2895 If the operand is not an integer value, then the result of the bitwise
2896 NOT operation is undefined.
2897
2898 \subsection{Cardinality}
2899 \label{sec:cardinality}
2900
2901 An ADQL service implementation MAY include support for the following optional
2902 clauses to modify the cardinality of query results:
2903
2904 \begin{itemize}
2905 \item \verb:OFFSET:
2906 \end{itemize}
2907
2908 \subsubsection{OFFSET}
2909 \label{sec:offset}
2910
2911 {\footnotesize Language feature :}\\
2912 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-offset|}\\
2913 {\footnotesize \verb|name: OFFSET|}\\
2914
2915 An ADQL service implementation MAY include support for the OFFSET clause
2916 which limits the number of rows returned by removing a specified number
2917 of rows from the beginning of the result set.
2918
2919 If a query contains both an ORDER BY clause and an OFFSET clause,
2920 then the ORDER BY is applied before the specified number of
2921 rows are dropped by the OFFSET clause.
2922
2923 If the total number of rows is less than the value
2924 specified by the OFFSET clause, then the result set is empty.
2925
2926 If a query contains both an OFFSET clause and a TOP clause,
2927 then the OFFSET clause is applied first, dropping the specified
2928 number of rows from the beginning of the result set before the
2929 TOP clause is applied to limit the number of rows returned.
2930
2931 \newpage
2932 \appendix
2933 \section{BNF grammar}
2934 \label{sec:grammar}
2935
2936 \begin{verbatim}
2937
2938 <ADQL_language_character> ::=
2939 <simple_Latin_letter>
2940 | <digit>
2941 | <SQL_special_character>
2942
2943 <ADQL_reserved_word> ::=
2944 ABS
2945 | ACOS
2946 | AREA
2947 | ASIN
2948 | ATAN
2949 | ATAN2
2950 | BIT_AND
2951 | BIT_NOT
2952 | BIT_OR
2953 | BIT_XOR
2954 | BOX
2955 | CEILING
2956 | CENTROID
2957 | CIRCLE
2958 | CONTAINS
2959 | COORD1
2960 | COORD2
2961 | COORDSYS
2962 | COS
2963 | DEGREES
2964 | DISTANCE
2965 | EXP
2966 | FLOOR
2967 | ILIKE
2968 | INTERSECTS
2969 | IN_UNIT
2970 | LOG
2971 | LOG10
2972 | MOD
2973 | PI
2974 | POINT
2975 | POLYGON
2976 | POWER
2977 | RADIANS
2978 | REGION
2979 | RAND
2980 | ROUND
2981 | SIN
2982 | SQRT
2983 | TOP
2984 | TAN
2985 | TRUNCATE
2986
2987 <SQL_embedded_language_character> ::=
2988 <left_bracket> | <right_bracket>
2989
2990 <SQL_reserved_word> ::=
2991 ABSOLUTE | ACTION | ADD | ALL
2992 | ALLOCATE | ALTER | AND
2993 | ANY | ARE
2994 | AS | ASC
2995 | ASSERTION | AT
2996 | AUTHORIZATION | AVG
2997 | BEGIN | BETWEEN | BIT | BIT_LENGTH
2998 | BOTH | BY
2999 | CASCADE | CASCADED | CASE | CAST
3000 | CATALOG
3001 | CHAR | CHARACTER | CHAR_LENGTH
3002 | CHARACTER_LENGTH | CHECK | CLOSE | COALESCE
3003 | COLLATE | COLLATION
3004 | COLUMN | COMMIT
3005 | CONNECT
3006 | CONNECTION | CONSTRAINT
3007 | CONSTRAINTS | CONTINUE
3008 | CONVERT | CORRESPONDING | COUNT | CREATE | CROSS
3009 | CURRENT
3010 | CURRENT_DATE | CURRENT_TIME
3011 | CURRENT_TIMESTAMP | CURRENT_USER | CURSOR
3012 | DATE | DAY | DEALLOCATE
3013 | DECIMAL | DECLARE | DEFAULT | DEFERRABLE
3014 | DEFERRED | DELETE | DESC | DESCRIBE | DESCRIPTOR
3015 | DIAGNOSTICS
3016 | DISCONNECT | DISTINCT | DOMAIN | DOUBLE | DROP
3017 | ELSE | END | END-EXEC | ESCAPE
3018 | EXCEPT | EXCEPTION
3019 | EXEC | EXECUTE | EXISTS
3020 | EXTERNAL | EXTRACT
3021 | FALSE | FETCH | FIRST | FLOAT | FOR
3022 | FOREIGN | FOUND | FROM | FULL
3023 | GET | GLOBAL | GO | GOTO
3024 | GRANT | GROUP
3025 | HAVING | HOUR
3026 | IDENTITY | IMMEDIATE | IN | INDICATOR
3027 | INITIALLY | INNER | INPUT
3028 | INSENSITIVE | INSERT | INT | INTEGER | INTERSECT
3029 | INTERVAL | INTO | IS
3030 | ISOLATION
3031 | JOIN
3032 | KEY
3033 | LANGUAGE | LAST | LEADING | LEFT
3034 | LEVEL | LIKE | ILIKE | LOCAL | LOWER
3035 | MATCH | MAX | MIN | MINUTE | MODULE
3036 | MONTH
3037 | NAMES | NATIONAL | NATURAL | NCHAR | NEXT | NO
3038 | NOT | NULL
3039 | NULLIF | NUMERIC
3040 | OCTET_LENGTH | OF
3041 | ON | ONLY | OPEN | OPTION | OR
3042 | ORDER | OUTER
3043 | OUTPUT | OVERLAPS
3044 | PAD | PARTIAL | POSITION | PRECISION | PREPARE
3045 | PRESERVE | PRIMARY
3046 | PRIOR | PRIVILEGES | PROCEDURE | PUBLIC
3047 | READ | REAL | REFERENCES | RELATIVE | RESTRICT
3048 | REVOKE | RIGHT
3049 | ROLLBACK | ROWS
3050 | SCHEMA | SCROLL | SECOND | SECTION
3051 | SELECT
3052 | SESSION | SESSION_USER | SET
3053 | SIZE | SMALLINT | SOME | SPACE | SQL | SQLCODE
3054 | SQLERROR | SQLSTATE
3055 | SUBSTRING | SUM | SYSTEM_USER
3056 | TABLE | TEMPORARY
3057 | THEN | TIME | TIMESTAMP
3058 | TIMEZONE_HOUR | TIMEZONE_MINUTE
3059 | TO | TRAILING | TRANSACTION
3060 | TRANSLATE | TRANSLATION | TRIM | TRUE
3061 | UNION | UNIQUE | UNKNOWN | UPDATE | UPPER | USAGE
3062 | USER | USING
3063 | VALUE | VALUES | VARCHAR | VARYING | VIEW
3064 | WHEN | WHENEVER | WHERE | WITH | WORK | WRITE
3065 | YEAR
3066 | ZONE
3067
3068 <SQL_special_character> ::=
3069 <space>
3070 | <double_quote>
3071 | <percent>
3072 | <ampersand>
3073 | <quote>
3074 | <left_paren>
3075 | <right_paren>
3076 | <asterisk>
3077 | <plus_sign>
3078 | <comma>
3079 | <minus_sign>
3080 | <period>
3081 | <solidus>
3082 | <colon>
3083 | <semicolon>
3084 | <less_than_operator>
3085 | <equals_operator>
3086 | <greater_than_operator>
3087 | <question_mark>
3088 | <underscore>
3089 | <vertical_bar>
3090
3091 <ampersand> ::= &
3092
3093 <approximate_numeric_literal> ::= <mantissa>E<exponent>
3094
3095 <area> ::= AREA <left_paren> <geometry_value_expression> <right_paren>
3096
3097 <as_clause> ::= [ AS ] <column_name>
3098
3099 <asterisk> ::= *
3100
3101 <between_predicate> ::=
3102 <value_expression> [ NOT ] BETWEEN
3103 <value_expression> AND <value_expression>
3104
3105 <bitwise_expression> ::=
3106 <bitwise_not> <numeric_value_expression>
3107 | <numeric_value_expression> <bitwise_and> <numeric_value_expression>
3108 | <numeric_value_expression> <bitwise_or> <numeric_value_expression>
3109 | <numeric_value_expression> <bitwise_xor> <numeric_value_expression>
3110
3111 <bitwise_and> ::= <ampersand>
3112 <bitwise_not> ::= <tilde>
3113 <bitwise_or> ::= <vertical_bar>
3114 <bitwise_xor> ::= <circumflex>
3115
3116 <boolean_factor> ::= [ NOT ] <boolean_primary>
3117
3118 <boolean_function> ::=
3119
3120 <boolean_literal> ::= True | False
3121
3122 <boolean_primary> ::=
3123 <left_paren> <search_condition> <right_paren>
3124 | <predicate>
3125 | <boolean_value_expression>
3126
3127 <boolean_term> ::=
3128 <boolean_factor>
3129 | <boolean_term> AND <boolean_factor>
3130
3131 <boolean_value_expression> ::=
3132 <boolean_literal>
3133 | <boolean_function>
3134 | <user_defined_function>
3135
3136 <box> ::=
3137 BOX <left_paren>
3138 [ <coord_sys> <comma> ]
3139 <coordinates>
3140 <comma> <numeric_value_expression>
3141 <comma> <numeric_value_expression>
3142 <right_paren>
3143
3144 <catalog_name> ::= <identifier>
3145
3146 <centroid> ::=
3147 CENTROID <left_paren>
3148 <geometry_value_expression>
3149 <right_paren>
3150
3151 <character_factor> ::= <character_primary>
3152
3153 <character_primary> ::=
3154 <value_expression_primary>
3155 | <string_value_function>
3156
3157 <character_representation> ::= <nonquote_character> | <quote_symbol>
3158
3159 <character_string_literal> ::=
3160 <quote> [ <character_representation>... ] <quote>
3161
3162 <character_value_expression> ::= <concatenation> | <character_factor>
3163
3164 <circle> ::=
3165 CIRCLE <left_paren>
3166 [ <coord_sys> <comma> ]
3167 <coordinates>
3168 <comma> <radius>
3169 <right_paren>
3170
3171 <circumflex> ::= ^
3172
3173 <colon> ::= :
3174
3175 <column_name> ::= <identifier>
3176
3177 <column_name_list> ::= <column_name> [ { <comma> <column_name> }... ]
3178
3179 <column_reference> ::= [ <qualifier> <period> ] <column_name>
3180
3181 <comma> ::= ,
3182
3183 <comment> ::= <comment_introducer> [ <comment_character>... ] <newline>
3184
3185 <comment_character> ::= <nonquote_character> | <quote>
3186
3187 <comment_introducer> ::= <minus_sign><minus_sign> [<minus_sign>...]
3188
3189 <comp_op> ::=
3190 <equals_operator>
3191 | <not_equals_operator>
3192 | <less_than_operator>
3193 | <greater_than_operator>
3194 | <less_than_or_equals_operator>
3195 | <greater_than_or_equals_operator>
3196
3197 <comparison_predicate> ::=
3198 <value_expression> <comp_op> <value_expression>
3199
3200 <concatenation> ::=
3201 <character_value_expression>
3202 <concatenation_operator>
3203 <character_factor>
3204
3205 <concatenation_operator> ::= ||
3206
3207 <contains> ::=
3208 CONTAINS <left_paren>
3209 <geometry_value_expression> <comma> <geometry_value_expression>
3210 <right_paren>
3211
3212 <coord1> ::= COORD1 <left_paren> <coord_value> <right_paren>
3213
3214 <coord2> ::= COORD2 <left_paren> <coord_value> <right_paren>
3215
3216 <coord_sys> ::= <string_value_expression>
3217
3218 <coord_value> ::= <point> | <column_reference>
3219
3220 <coordinate1> ::= <numeric_value_expression>
3221
3222 <coordinate2> ::= <numeric_value_expression>
3223
3224 <coordinates> ::= <coordinate1> <comma> <coordinate2>
3225
3226 <correlation_name> ::= <identifier>
3227
3228 <correlation_specification> ::= [ AS ] <correlation_name>
3229
3230 <default_function_prefix> ::=
3231
3232 <delimited_identifier> ::=
3233 <double_quote> <delimited_identifier_body> <double_quote>
3234
3235 <delimited_identifier_body> ::= <delimited_identifier_part>...
3236
3237 <delimited_identifier_part> ::=
3238 <nondoublequote_character> | <double_quote_symbol>
3239
3240 <delimiter_token> ::=
3241 <character_string_literal>
3242 | <delimited_identifier>
3243 | <SQL_special_character>
3244 | <not_equals_operator>
3245 | <greater_than_or_equals_operator>
3246 | <less_than_or_equals_operator>
3247 | <concatenation_operator>
3248 | <double_period>
3249 | <left_bracket>
3250 | <right_bracket>
3251
3252 <derived_column> ::= <value_expression> [ <as_clause> ]
3253
3254 <derived_table> ::= <table_subquery>
3255
3256 <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
3257
3258 <distance_function> ::=
3259 DISTANCE <left_paren>
3260 <coord_value> <comma>
3261 <coord_value>
3262 <right_paren>
3263 | DISTANCE <left_paren>
3264 <numeric_value_expression> <comma>
3265 <numeric_value_expression> <comma>
3266 <numeric_value_expression> <comma>
3267 <numeric_value_expression>
3268 <right_paren>
3269
3270 <double_period> ::= ..
3271
3272 <double_quote> ::= "
3273
3274 <double_quote_symbol> ::= <double_quote><double_quote>
3275
3276 <equals_operator> ::= =
3277
3278 <exact_numeric_literal> ::=
3279 <unsigned_decimal> [ <period> [ <unsigned_decimal> ] ]
3280 | <period> <unsigned_decimal>
3281
3282 <exists_predicate> ::= EXISTS <table_subquery>
3283
3284 <exponent> ::= <signed_integer>
3285
3286 <extract_coordsys> ::=
3287 COORDSYS <left_paren>
3288 <geometry_value_expression>
3289 <right_paren>
3290
3291 <factor> ::= [ <sign> ] <numeric_primary>
3292
3293 <from_clause> ::=
3294 FROM <table_reference>
3295 [ { <comma> <table_reference> }... ]
3296
3297 <general_literal> ::= <character_string_literal>
3298
3299 <general_set_function> ::=
3300 <set_function_type> <left_paren>
3301 [ <set_quantifier> ] <value_expression>
3302 <right_paren>
3303
3304 <geometry_value_expression> ::=
3305 <value_expression_primary > | <geometry_value_function>
3306
3307 <geometry_value_function> ::=
3308 <box>
3309 | <centroid>
3310 | <circle>
3311 | <point>
3312 | <polygon>
3313 | <user_defined_function>
3314
3315 <greater_than_operator> ::= >
3316
3317 <greater_than_or_equals_operator> ::= >=
3318
3319 <group_by_clause> ::= GROUP BY <group_by_term_list>
3320
3321 <group_by_term> ::=
3322 <column_reference>
3323 | <value_expression>
3324
3325 <group_by_term_list> ::=
3326 <group_by_term>
3327 [ { <comma> <group_by_term> }... ]
3328
3329 <having_clause> ::= HAVING <search_condition>
3330
3331 <hex_digit> ::= <digit> | a | b | c | d | e | f | A | B | C | D | E | F
3332
3333 <identifier> ::= <regular_identifier> | <delimited_identifier>
3334
3335 <in_predicate> ::=
3336 <value_expression> [ NOT ] IN <in_predicate_value>
3337
3338 <in_predicate_value> ::=
3339 <table_subquery> | <left_paren> <in_value_list> <right_paren>
3340
3341 <in_value_list> ::=
3342 <value_expression> { <comma> <value_expression> } ...
3343
3344 <intersects > ::=
3345 INTERSECTS <left_paren>
3346 <geometry_value_expression> <comma> <geometry_value_expression>
3347 <right_paren>
3348
3349 <join_column_list> ::= <column_name_list>
3350
3351 <join_condition> ::= ON <search_condition>
3352
3353 <join_specification> ::= <join_condition> | <named_columns_join>
3354
3355 <join_type> ::=
3356 INNER | <outer_join_type> [ OUTER ]
3357
3358 <joined_table> ::=
3359 <qualified_join> | <left_paren> <joined_table> <right_paren>
3360
3361 <keyword> ::= <SQL_reserved_word> | <ADQL_reserved_word>
3362
3363 <left_bracket> ::= [
3364
3365 <left_paren> ::= (
3366
3367 <less_than_operator> ::= <
3368
3369 <less_than_or_equals_operator> ::= <=
3370
3371 <like_predicate> ::=
3372 <match_value> [ NOT ] LIKE <pattern>
3373 | <match_value> [ NOT ] ILIKE <pattern>
3374
3375 <mantissa> ::= <exact_numeric_literal>
3376
3377 <match_value> ::= <character_value_expression>
3378
3379 <math_function> ::=
3380 ABS <left_paren> <numeric_value_expression> <right_paren>
3381 | CEILING <left_paren> <numeric_value_expression> <right_paren>
3382 | DEGREES <left_paren> <numeric_value_expression> <right_paren>
3383 | EXP <left_paren> <numeric_value_expression> <right_paren>
3384 | FLOOR <left_paren> <numeric_value_expression> <right_paren>
3385 | LOG <left_paren> <numeric_value_expression> <right_paren>
3386 | LOG10 <left_paren> <numeric_value_expression> <right_paren>
3387 | MOD <left_paren>
3388 <numeric_value_expression> <comma> <numeric_value_expression>
3389 <right_paren>
3390 | PI <left_paren><right_paren>
3391 | POWER <left_paren>
3392 <numeric_value_expression> <comma> <numeric_value_expression>
3393 <right_paren>
3394 | RADIANS <left_paren> <numeric_value_expression> <right_paren>
3395 | RAND <left_paren> [ <unsigned_decimal> ] <right_paren>
3396 | ROUND <left_paren>
3397 <numeric_value_expression> [ <comma> <signed_integer>]
3398 <right_paren>
3399 | SQRT <left_paren> <numeric_value_expression> <right_paren>
3400 | TRUNCATE <left_paren>
3401 <numeric_value_expression>
3402 [ <comma> <signed_integer>]
3403 <right_paren>
3404
3405 <minus_sign> ::= -
3406
3407 <named_columns_join> ::=
3408 USING <left_paren>
3409 <join_column_list>
3410 <right_paren>
3411
3412 <newline> ::=
3413
3414 <non_predicate_geometry_function> ::=
3415 <area>
3416 | <coord1>
3417 | <coord2>
3418 | <distance>
3419
3420 <nondelimiter_token> ::=
3421 <regular_identifier>
3422 | <keyword>
3423 | <unsigned_numeric_literal>
3424
3425 <nondoublequote_character> ::=
3426
3427 <nonquote_character> ::=
3428
3429 <not_equals_operator> ::= <not_equals_operator1> | <not_equals_operator2>
3430
3431 <not_equals_operator1> ::= <>
3432
3433 <not_equals_operator2> ::= !=
3434
3435 <non_join_query_expression> ::=
3436 <non_join_query_term>
3437 | <query_expression> UNION [ ALL ] <query_term>
3438 | <query_expression> EXCEPT [ ALL ] <query_term>
3439
3440 <non_join_query_primary> ::=
3441 <query_specification>
3442 | <left_paren> <non_join_query_expression> <right_paren>
3443
3444 <non_join_query_term> ::=
3445 <non_join_query_primary>
3446 | <query_term> INTERSECT [ ALL ] <query_expression>
3447
3448 <null_predicate> ::= <column_reference> IS [ NOT ] NULL
3449
3450 <numeric_geometry_function> ::=
3451 <predicate_geometry_function> | <non_predicate_geometry_function>
3452
3453 <numeric_primary> ::=
3454 <value_expression_primary>
3455 | <numeric_value_function>
3456
3457 <numeric_value_expression> ::=
3458 <term>
3459 | <bitwise_expression>
3460 | <numeric_value_expression> <plus_sign> <term>
3461 | <numeric_value_expression> <minus_sign> <term>
3462
3463 <numeric_value_function> ::=
3464 <trig_function>
3465 | <math_function>
3466 | <numeric_geometry_function >
3467 | <user_defined_function>
3468
3469 <offset_clause> ::= OFFSET <unsigned_decimal>
3470
3471 <order_by_clause> ::= ORDER BY <order_by_term_list>
3472
3473 <order_by_direction> ::= ASC | DESC
3474
3475 <order_by_expression> ::=
3476 <unsigned_decimal>
3477 | <column_reference>
3478 | <value_expression>
3479
3480 <order_by_term> ::=
3481 <order_by_expression> [ <order_by_direction> ]
3482
3483 <order_by_term_list> ::=
3484 <order_by_term> [ { <comma> <order_by_term> }... ]
3485
3486 <outer_join_type> ::= LEFT | RIGHT | FULL
3487
3488 <pattern> ::= <character_value_expression>
3489
3490 <percent> ::= %
3491
3492 <period> ::= .
3493
3494 <plus_sign> ::= +
3495
3496 <point> ::=
3497 POINT <left_paren>
3498 [ <coord_sys> <comma> ]
3499 <coordinates>
3500 <right_paren>
3501
3502 <polygon> ::=
3503 POLYGON <left_paren>
3504 [ <coord_sys> <comma> ]
3505 <coordinates>
3506 <comma> <coordinates>
3507 { <comma> <coordinates> } ?
3508 <right_paren>
3509
3510 <predicate> ::=
3511 <comparison_predicate>
3512 | <between_predicate>
3513 | <in_predicate>
3514 | <like_predicate>
3515 | <null_predicate>
3516 | <exists_predicate>
3517
3518 <predicate_geometry_function> ::= <contains> | <intersects>
3519
3520 <qualified_join> ::=
3521 <table_reference> [ NATURAL ] [ <join_type> ] JOIN
3522 <table_reference> [ <join_specification> ]
3523
3524 <qualifier> ::= <table_name> | <correlation_name>
3525
3526 <query_expression> ::=
3527 <non_join_query_expression>
3528 | <joined_table>
3529
3530 <query_term> ::=
3531 <non_join_query_term>
3532 | <joined_table>
3533
3534 <query_name> ::= <identifier>
3535
3536 <query_specification> :=
3537 WITH <with_query> [, ...]
3538 <select_query>
3539
3540 <question_mark> ::= ?
3541
3542 <quote> ::= '
3543
3544 <quote_symbol> ::= <quote> <quote>
3545
3546 <radius> ::= <numeric_value_expression>
3547
3548 <regular_identifier> ::=
3549 <simple_Latin_letter>...
3550 [ { <digit> | <simple_Latin_letter> | <underscore> }... ]
3551
3552 <right_bracket> ::= ]
3553
3554 <right_paren> ::= )
3555
3556 <schema_name> ::= [ <catalog_name> <period> ] <unqualified_schema name>
3557
3558 <search_condition> ::=
3559 <boolean_term>
3560 | <search_condition> OR <boolean_term>
3561
3562 <select_list> ::=
3563 <asterisk>
3564 | <select_sublist> [ { <comma> <select_sublist> }... ]
3565
3566 <select_query> ::=
3567 SELECT
3568 [ <set_quantifier> ]
3569 [ <set_limit> ]
3570 <select_list>
3571 <table_expression>
3572
3573 <select_sublist> ::= <derived_column> | <qualifier> <period> <asterisk>
3574
3575 <semicolon> ::= ;
3576
3577 <set_function_specification> ::=
3578 COUNT <left_paren> <asterisk> <right_paren>
3579 | <general_set_function>
3580
3581 <set_function_type> ::= AVG | MAX | MIN | SUM | COUNT
3582
3583 <set_limit> ::= TOP <unsigned_decimal>
3584
3585 <set_quantifier> ::= DISTINCT | ALL
3586
3587 <sign> ::= <plus_sign> | <minus_sign>
3588
3589 <signed_integer> ::= [ <sign> ] <unsigned_decimal>
3590
3591 <simple_Latin_letter> ::=
3592 <simple_Latin_upper_case_letter>
3593 | <simple_Latin_lower_case_letter>
3594
3595 <simple_Latin_lower_case_letter> ::=
3596 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
3597
3598 <simple_Latin_upper_case_letter> ::=
3599 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
3600
3601 <solidus> ::= /
3602
3603 <space> ::=
3604
3605 <string_geometry_function> ::= <extract_coordsys>
3606
3607 <string_value_expression> ::= <character_value_expression>
3608
3609 <string_value_function> ::=
3610 <string_geometry_function> | <user_defined_function>
3611
3612 <subquery> ::= <left_paren> <query_expression> <right_paren>
3613
3614 <table_expression> ::=
3615 <from_clause>
3616 [ <where_clause> ]
3617 [ <group_by_clause> ]
3618 [ <having_clause> ]
3619 [ <order_by_clause> ]
3620 [ <offset_clause> ]
3621
3622 <table_name> ::= [ <schema_name> <period> ] <identifier>
3623
3624 <table_reference> ::=
3625 <table_name> [ <correlation_specification> ]
3626 | <derived_table> <correlation_specification>
3627 | <joined_table>
3628
3629 <table_subquery> ::= <subquery>
3630
3631 <term> ::=
3632 <factor>
3633 | <term> <asterisk> <factor>
3634 | <term> <solidus> <factor>
3635
3636 <tilde> ::= ~
3637
3638 <token> ::=
3639 <nondelimiter_token> | <delimiter_token>
3640
3641 <trig_function> ::=
3642 ACOS <left_paren> <numeric_value_expression> <right_paren>
3643 | ASIN <left_paren> <numeric_value_expression> <right_paren>
3644 | ATAN <left_paren> <numeric_value_expression> <right_paren>
3645 | ATAN2 <left_paren>
3646 <numeric_value_expression> <comma> <numeric_value_expression>
3647 <right_paren>
3648 | COS <left_paren> <numeric_value_expression> <right_paren>
3649 | COT <left_paren> <numeric_value_expression> <right_paren>
3650 | SIN <left_paren> <numeric_value_expression> <right_paren>
3651 | TAN <left_paren> <numeric_value_expression> <right_paren>
3652
3653 <underscore> ::= _
3654
3655 <unqualified_schema name> ::= <identifier>
3656
3657 <unsigned_decimal> ::= <digit>...
3658
3659 <unsigned_hexadecimal> ::= 0x<hex_digit>...
3660
3661 <unsigned_literal> ::=
3662 <unsigned_numeric_literal>
3663 | <general_literal>
3664
3665 <unsigned_numeric_literal> ::=
3666 <exact_numeric_literal>
3667 | <approximate_numeric_literal>
3668 | <unsigned_hexadecimal>
3669
3670 <unsigned_value_specification> ::= <unsigned_literal>
3671
3672 <user_defined_function> ::=
3673 <user_defined_function_name> <left_paren>
3674 [
3675 <user_defined_function_param>
3676 [
3677 {
3678 <comma> <user_defined_function_param>
3679 }...
3680 ]
3681 ]
3682 <right_paren>
3683
3684 <user_defined_function_name> ::=
3685 [ <default_function_prefix> ] <regular_identifier>
3686
3687 <user_defined_function_param> ::= <value_expression>
3688
3689 <value_expression> ::=
3690 <numeric_value_expression>
3691 | <string_value_expression>
3692 | <boolean_value_expression>
3693 | <geometry_value_expression>
3694
3695 <value_expression_primary> ::=
3696 <unsigned_value_specification>
3697 | <column_reference>
3698 | <set_function_specification>
3699 | <left_paren> <value_expression> <right_paren>
3700
3701 <vertical_bar> ::= |
3702
3703 <where_clause> ::= WHERE <search_condition>
3704
3705 <with_query> :=
3706 <query_name>
3707 [ (<column_name> [,...]) ] AS (<query_specification>)
3708
3709 \end{verbatim}
3710
3711 \newpage
3712 \section{Language feature support}
3713 \label{sec:features}
3714
3715 In the \TAPRegExtSpec XML schema, each group of features is
3716 described by a \verb:languageFeatures: element, which has a \verb:type:
3717 URI that identifies the group, and contains a \verb:form: element for each
3718 individual feature from the group that the service supports.
3719
3720 For example, the following XML fragment describes a service that supports the
3721 \verb:POINT: and \verb:CIRCLE: functions from the set of geometrical functions,
3722
3723 {\footnotesize Language feature :}\\
3724 {\footnotesize \verb|type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo|}\\
3725 {\footnotesize \verb|name: POINT, CIRCLE|}\\
3726
3727 \begin{verbatim}
3728 <languageFeatures
3729 type="ivo://ivoa.net/std/TAPRegExt#features-adql-geo"
3730 >
3731 <feature>
3732 <form>POINT</form>
3733 </feature>
3734 <feature>
3735 <form>CIRCLE</form>
3736 </feature>
3737 </languageFeatures>
3738 \end{verbatim}
3739
3740 \newpage
3741 \section{Changes from previous versions}
3742 \label{sec:changes}
3743
3744 \begin{itemize}
3745 \item Changes from WD-ADQL-2.1-20160502
3746 \begin{itemize}
3747 \item Removed hard coded version number\\
3748 (svn version r4544, 23 Oct 2017)
3749 \item Restored coordsys param for now\\
3750 (svn version r4543, 23 Oct 2017)
3751 \item Updates to CAST and IN\_UNIT\\
3752 (svn version r4539, 18 Oct 2017)
3753 \item Updates to UNION, EXCEPT, INTERSECT and WITH\\
3754 (svn version r4538, 18 Oct 2017)
3755 \item Updates to user defined functions\\
3756 (svn version r4537, 18 Oct 2017)
3757 \item Updates to DISTANCE, POINT and POLYGON\\
3758 (svn version r4536, 18 Oct 2017)
3759 \item Proof reading typos and readability fixes\\
3760 (svn version r4527, 16 Oct 2017)
3761 \item Updates to COORDSYS\\
3762 (svn version r4522, 16 Oct 2017)
3763 \item Removed (commented) text describing coordsys argument\\
3764 (svn version r4521, 16 Oct 2017)
3765 \item Updates to COORD1, COORD2 and COORDSYS\\
3766 (svn version r4520, 16 Oct 2017)
3767 \item Updates to AREA, BOX, CENTROID, CIRCLE and CONTAINS\\
3768 (svn version r4519, 13 Oct 2017)
3769 \item Removed old section about 'Geometry in the SELECT clause'\\
3770 (svn version r4481, 10 Oct 2017)
3771 \item Updated text for AREA and BOX\\
3772 (svn version r4480, 10 Oct 2017)
3773 \item Updated text for AREA\\
3774 (svn version r4469, 09 Oct 2017)
3775 \item Cleaned up text describing ranges for coordinates\\
3776 (svn version r4467, 09 Oct 2017)
3777 \item Removed REGION\\
3778 (svn version r4466, 09 Oct 2017)
3779 \item Removed reference to ADQL from section titles in the ADQL document\\
3780 (svn version r4465, 09 Oct 2017)
3781 \item Expanded the datatypes to add sub-section for each xtype\\
3782 (svn version r4353, 19 Sep 2017)
3783 \item Removed restriction on nested JOINs\\
3784 (svn version r4283, 12 Sep 2017)
3785 \item Added subversion properties\\
3786 (svn version r4282, 12 Sep 2017)
3787 \item Updated types and xtypes to match DALI\\
3788 (svn version r4281, 12 Sep 2017)
3789 \item Clarify text for SELECT and subqueries\\
3790 (svn version r4256, 12 Sep 2017)
3791 \item Improved wording for keywords and identifiers\\
3792 (svn version r4242, 11 Sep 2017)
3793 \item Fixed section references\\
3794 (svn version r3637, 18 Oct 2016)
3795 \item Fixed typo in definition of MOD\\
3796 (svn version 3456)
3797 \item Fixed section references\\
3798 (svn version 3637)
3799 \item Improved wording for keywords and identifiers\\
3800 (svn version 4242)
3801 \end{itemize}
3802
3803 \item Changes from ADQL-20150601
3804 \begin{itemize}
3805 \item Added boolean type\\
3806 (svn version 3364)
3807 \item Removed bitwise functions and updated the operators\\
3808 (svn version 3365)
3809 \item Changed 'hierarchical queries' to 'common table expressions'\\
3810 (svn version 3366)
3811 \item Added OFFSET clause\\
3812 (svn version 3367)
3813 \item Added four parameter DISTANCE\\
3814 (svn version 3370)
3815 \item Added hexadecimal literals\\
3816 (svn version 3374)
3817 \end{itemize}
3818
3819 \item Changes from \citet{note:TAPNotes}
3820 \begin{itemize}
3821 \item 2.1.1. The Separator Nonterminal
3822 \item 2.1.2. Type System
3823 \item 2.1.4. Empty Coordinate Systems
3824 \item 2.1.5. Explanation of optional features
3825 \item 2.2.2. No Type-based Decay of INTERSECTS
3826 \item 2.2.3. Generalized User Defined Functions
3827 \item 2.2.4. Case-insensitive String Comparisons
3828 \item 2.2.5. Set Operators
3829 \item 2.2.6. Boolean Type
3830 \item 2.2.7. Casting to Unit
3831 \item 2.2.10. Bitwise operators
3832 \item 2.2.10. Hexadecimal literals
3833 \item 2.2.11. CAST operator
3834 \item 2.NN WITH
3835 \end{itemize}
3836
3837 \item Created [Optional components] section.
3838 \item Moved [Geometrical Functions] into [Optional components].
3839 \item Added [Language feature] information.
3840
3841 \end{itemize}
3842
3843 \bibliography{ivoatex/ivoabib}
3844
3845 \end{document}
3846

Properties

Name Value
svn:keywords Date Rev URL

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