Latest ADQL Document version

From: Pedro Osuna <Pedro.Osuna-at-sciops.esa.int>
Date: Wed, 19 Mar 2008 10:50:59 +0100

Dear all,

please find attached the latest version of the ADQL document.

This version will be distributed to the overall VOQL group next Tuesday, after the Easter Holidays, and is intended to be the final version for an eventual Proposed Recommendation.

It includes a vast amount of work done on the Region definition mainly by Pat Dowler, Jeff Lusted, Alex Szalay and Inaki Ortiz. I just wanted to publicly thank them for this huge effort.

Please read the document in detail, and let us know if you have any comment. In any case, after it goes to the VOQL group we will still be able to do minor changes on it.

Thanks very much.
Cheers,
P.

BNF

---

This file contains a BNF definition of ADQL, the Astronomical Data Query Language. The elements are arranged in alphabetical order. ADQL is based upon a subset of SQL92 with extensions for astronomical usage.

The definition reflects the draft ADQL spec version v1.5-20080318 The select statement is found at <query_specification> For region and associated constructs, begin at <system_defined_function> User defined function is found at <user_defined_function> See SQL92 for a similar page describing SQL92 in full.

<ADQL_language_character> ::=

       <simple_Latin_letter>
     | <digit>
     | <SQL_special_character>



   <ADQL_reserved_word> ::=

       ABS
     | ACOS
     | AREA
     | ASIN
     | ATAN
     | ATAN2
     | CEILING
     | CENTROID
     | CIRCLE
     | CONTAINS
     | COS
     | DEGREES
     | DISTANCE
     | EXP
     | FLOOR
     | INTERSECTS
     | LATITUDE
     | LOG
     | LOG10
     | LONGITUDE
     | MODE
     | PI
     | POINT
     | POLYGON
     | POWER
     | RADIANS
     | RECTANGLE
     | REGION
     | RAND
     | ROUND
     | SIN
     | SQUARE
     | SQRT
     | TOP
     | TAN
     | TRUNCATE
     !! ADQL Note: GAL and ICRS removed. UDF also removed.



   <SQL_embedded_language_character> ::=

       <left_bracket>
     | <right_bracket>



   <SQL_reserved_word> ::=

      ABSOLUTE | ACTION | ADD | ALL
    | ALLOCATE | ALTER | AND
    | ANY | ARE
    | AS | ASC
    | ASSERTION | AT
    | AUTHORIZATION | AVG
    | BEGIN | BETWEEN | BIT | BIT_LENGTH
    | BOTH | BY
    | CASCADE | CASCADED | CASE | CAST
    | CATALOG
    | CHAR | CHARACTER | CHAR_LENGTH
    | CHARACTER_LENGTH | CHECK | CLOSE | COALESCE
    | COLLATE | COLLATION
    | COLUMN | COMMIT
    | CONNECT
    | CONNECTION | CONSTRAINT
    | CONSTRAINTS | CONTINUE
    | CONVERT | CORRESPONDING | COUNT | CREATE | CROSS
    | CURRENT
    | CURRENT_DATE | CURRENT_TIME
    | CURRENT_TIMESTAMP | CURRENT_USER | CURSOR
    | DATE | DAY | DEALLOCATE
    | DECIMAL | DECLARE | DEFAULT | DEFERRABLE
    | DEFERRED | DELETE | DESC | DESCRIBE | DESCRIPTOR
    | DIAGNOSTICS
    | DISCONNECT | DISTINCT | DOMAIN | DOUBLE | DROP
    | ELSE | END | END-EXEC | ESCAPE
    | EXCEPT | EXCEPTION
    | EXEC | EXECUTE | EXISTS
    | EXTERNAL | EXTRACT
    | FALSE | FETCH | FIRST | FLOAT | FOR
    | FOREIGN | FOUND | FROM | FULL
    | GET | GLOBAL | GO | GOTO
    | GRANT | GROUP
    | HAVING | HOUR
    | IDENTITY | IMMEDIATE | IN | INDICATOR
    | INITIALLY | INNER | INPUT
    | INSENSITIVE | INSERT | INT | INTEGER | INTERSECT
    | INTERVAL | INTO | IS
    | ISOLATION
    | JOIN
    | KEY
    | LANGUAGE | LAST | LEADING | LEFT
    | LEVEL | LIKE | LOCAL | LOWER
    | MATCH | MAX | MIN | MINUTE | MODULE
    | MONTH
    | NAMES | NATIONAL | NATURAL | NCHAR | NEXT | NO
    | NOT | NULL
    | NULLIF | NUMERIC
    | OCTET_LENGTH | OF
    | ON | ONLY | OPEN | OPTION | OR
    | ORDER | OUTER
    | OUTPUT | OVERLAPS
    | PAD | PARTIAL | POSITION | PRECISION | PREPARE
    | PRESERVE | PRIMARY
    | PRIOR | PRIVILEGES | PROCEDURE | PUBLIC
    | READ | REAL | REFERENCES | RELATIVE | RESTRICT
    | REVOKE | RIGHT
    | ROLLBACK | ROWS
    | SCHEMA | SCROLL | SECOND | SECTION
    | SELECT
    | SESSION | SESSION_USER | SET
    | SIZE | SMALLINT | SOME | SPACE | SQL | SQLCODE
    | SQLERROR | SQLSTATE
    | SUBSTRING | SUM | SYSTEM_USER
    | TABLE | TEMPORARY
    | THEN | TIME | TIMESTAMP
    | TIMEZONE_HOUR | TIMEZONE_MINUTE
    | TO | TRAILING | TRANSACTION
    | TRANSLATE | TRANSLATION | TRIM | TRUE
    | UNION | UNIQUE | UNKNOWN | UPDATE | UPPER | USAGE
    | USER | USING
    | VALUE | VALUES | VARCHAR | VARYING | VIEW
    | WHEN | WHENEVER | WHERE | WITH | WORK | WRITE

    | YEAR
    | ZONE
<SQL_special_character> ::=

      <space>
    | <double_quote>
    | <percent>
    | <ampersand>
    | <quote>
    | <left_paren>
    | <right_paren>
    | <asterisk>
    | <plus_sign>
    | <comma>
    | <minus_sign>
    | <period>
    | <solidus>
    | <colon>
    | <semicolon>
    | <less_than_operator>
    | <equals_operator>
    | <greater_than_operator>
    | <question_mark>
    | <underscore>
    | <vertical_bar>

  <ampersand> ::= &

<approximate_numeric_literal> ::= <mantissa>E<exponent>

<area> ::= AREA <left_paren> <geometry_value_expression>
<right_paren>

<as_clause> ::= [ AS ] <column_name>

  <asterisk> ::= *

  <between_predicate> ::=

      <value_expression> [ NOT ] BETWEEN
      <value_expression> AND <value_expression>

    <boolean_factor> ::= [ NOT ] <boolean_primary>

<boolean_primary> ::=

      | <left_paren> <search_condition> <right_paren>
        <predicate>



   <boolean_term> ::=

       <boolean_factor>
     | <boolean_term> AND <boolean_factor>

  <catalog_name> ::= <identifier>

<centroid> ::= CENTROID <left_paren> <geometry_expression>
<right_paren>

<character_factor> ::= <character_primary>
   !! For information only, SQL92 supports <collate_clause>

<character_primary> ::=

       <value_expression_primary>
     | <user_defined_function>

   !! For information, SQL92 has support for <string_value_function>

  <character_representation> ::= <nonquote_character> | <quote_symbol>   !! A way of allowing a single quote to be escaped by a single quote.

<character_string_literal> ::=

      <quote> [ <character_representation>... ] <quote>
      [ { <separator>... <quote> [ <character_representation>... ]  

<quote> }... ]

<character_value_expression> ::= <concatenation> | <character_factor>

    <circle> ::=

       CIRCLE <left_paren> <coord_sys>
                   <comma> <coordinates>
                   <comma> <radius> <right_paren>

  <colon> ::= :

  <column_name> ::= <identifier>

  <column_name_list> ::= <column_name> [ { <comma> <column_name> }... ]

<column_reference> ::= [ <qualifier> <period> ] <column_name>

  <comma> ::= ,

  <comment> ::= <comment_introducer> [ <comment_character>... ] <newline>

  <comment_character> ::= <nonquote_character> | <quote>

  <comment_introducer> ::= <minus_sign><minus_sign> [<minus_sign>...]

<comp_op> ::=

       <equals_operator>
     | <not_equals_operator>
     | <less_than_operator>
     | <greater_than_operator>
     | <less_than_or_equals_operator>
     | <greater_than_or_equals_operator>



   <comparison_predicate> ::=

       <value_expression> <comp_op> <value_expression>

<concatenation> ::= <character_value_expression>
<concatenation_operator> <character_factor>

  <concatenation_operator> ::= ||

    <contains_function> ::= CONTAINS <left_paren> <geometry_value_expression> <comma> <geometry_value_expression> <right_paren>

    <coord_lat> ::= <numeric_value_expression>     !! generic latitude

    <coord_lon> ::= <numeric_value_expression>     !! generic longitude

<coord_sys> ::= <string_value_expression>

    <coordinates> ::= <coord_lon> <comma> <coord_lat>

  <correlation_name> ::= <identifier>

  <correlation_specification> ::= [ AS ] <correlation_name>

    <default_function_prefix> ::=
    !! The prefix is set by default to "udf_".     !! It should be possible to change the default prefix to accommodate local preferences.

<delimited_identifier> ::= <double_quote>
<delimited_identifier_body> <double_quote>

   !! These diagrams are somewhat difficult to follow through and understand immediately.

   !! The idea is to allow any identifier to be quoted by enclosing within double quotes.

   !! This can allow the use of reserved words as identifiers, eg: "DATE" as a column name.

   !! The identifier can also contain double quotes as long as they are escaped by double quotes.

   !! For example: "Table""X" will be identifier Table"X

  <delimited_identifier_body> ::= <delimited_identifier_part>...

  <delimited_identifier_part> ::= <nondoublequote_character> | <double_quote_symbol>

<delimiter_token> ::=

         <character_string_literal>
     |   <delimited_identifier>
     |   <SQL_special_character>
     |   <not_equals_operator>
     |   <greater_than_or_equals_operator>
     |   <less_than_or_equals_operator>
     |   <concatenation_operator>
     |   <double_period>
     |   <left_bracket>
     |   <right_bracket>

<derived_column> ::= <value_expression> [ <as_clause> ]

  <derived_table> ::= <table_subquery>

  <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

<distance_function> ::=

      DISTANCE <left_paren> <point> <comma> <point> <right_paren>

  <double_period> ::= ..

  <double_quote> ::= "

  <double_quote_symbol> ::= <double_quote><double_quote>

  <equals_operator> ::= =

<exact_numeric_literal> ::=

       <unsigned_integer> [ <period> [ <unsigned_integer> ] ]
     | <period> <unsigned_integer>

  <exists_predicate> ::= EXISTS <table_subquery>

  <exponent> ::= <signed_integer>

  <factor> ::= [ <sign> ] <numeric_primary>

<from_clause> ::= FROM <table_reference>

      [ { <comma> <table_reference> }... ]

<general_literal> ::= <character_string_literal>

   !! ADQL Note:
   !! For information, SQL92 has <general_literal>, and also supports
   !! <national_character_string_literal>, <bit_string_literal>,
   !! <hex_string_literal>, <datetime_literal> and <interval_literal>
   !! Good to retain this as a place-holder, bearing in mind
   !! that we might need <bit_string_literal> and <hex_string_literal>
   !! at some point in the future?



   <general_set_function> ::=

      <set_function_type> <left_paren> [ <set_quantifier> ]  

<value_expression> <right_paren>

    <geometry_expression> ::= <point> | <circle> | <rectangle> | <polygon> | <region>

    <geometry_value> ::= <column_reference>

<geometry_value_expression> ::= <geometry_expression> |
<geometry_value> | <centroid>

  <greater_than_operator> ::= >

  <greater_than_or_equals_operator> ::= >=

<group_by_clause> ::= GROUP BY <grouping_column_reference_list>

<grouping_column_reference> ::= <column_reference>
   !! For information, SQL92 supports a <collate_clause>

<grouping_column_reference_list> ::=

       <grouping_column_reference> [ { <comma> <grouping_column_reference> }... ]

  <having_clause> ::= HAVING <search_condition>

  <identifier> ::= <regular_identifier> | <delimited_identifier>

<in_predicate> ::=

       <value_expression> [ NOT ] IN <in_predicate_value>

<in_predicate_value> ::=

      <table_subquery> | <left_paren> <in_value_list> <right_paren>

<in_value_list> ::=

       <value_expression> { <comma> <value_expression> } ...

    <intersects_function> ::= INTERSECTS <left_paren> <geometry_value_expression> <comma> <geometry_value_expression> <right_paren>

  <join_column_list> ::= <column_name_list>

  <join_condition> ::= ON <search_condition>

<join_specification> ::= <join_condition> | <named_columns_join>

<join_type> ::=

      INNER
    | <outer_join_type> [ OUTER ]
   !! For information, SQL92 also supports UNION

<joined_table> ::=

       <qualified_join>
     | <left_paren> <joined_table> <right_paren>

  <keyword> ::= <SQL_reserved_word> | <ADQL_reserved_word>

<latitude> ::= LATITUDE <left_paren> <point> <right_paren>

  <left_bracket> ::= [

  <left_paren> ::= (

  <less_than_operator> ::= <

  <less_than_or_equals_operator> ::= <=

<like_predicate> ::=

       <match_value> [ NOT ] LIKE <pattern>

<longitude> ::= LONGITUDE <left_paren> <point> <right_paren>

  <mantissa> ::= <exact_numeric_literal>

  <match_value> ::= <character_value_expression>

<math_function> ::=

       ABS <left_paren> <numeric_value_expression> <right_paren>
	  | CEILING <left_paren> <numeric_value_expression> <right_paren>
	  | DEGREES <left_paren> <numeric_value_expression> <right_paren>
	  | EXP <left_paren> <numeric_value_expression> <right_paren>
	  | FLOOR <left_paren> <numeric_value_expression> <right_paren>
	  | LOG <left_paren> <numeric_value_expression> <right_paren>
	  | PI <left_paren><right_paren>
	  | POWER <left_paren> <numeric_value_expression> <comma>  
<unsigned_integer> <right_paren>
	  | RADIANS <left_paren> <numeric_value_expression> <right_paren>
	  | SQUARE <left_paren> <numeric_value_expression> <right_paren>
	  | SQRT <left_paren> <numeric_value_expression> <right_paren>
	  | LOG10 <left_paren> <numeric_value_expression> <right_paren>
	  | RAND <left_paren> [ <numeric_value_expression> ] <right_paren>
	  | ROUND <left_paren> <numeric_value_expression> <right_paren>
	  | TRUNCATE <left_paren> <numeric_value_expression> <right_paren>

  <minus_sign> ::= -

  <named_columns_join> ::= USING <left_paren> <join_column_list> <right_paren>

<newline> ::=

   !! Implementation defined end of line indicator

<nondelimiter_token> ::=

       <regular_identifier>
     | <keyword>
     | <unsigned_numeric_literal>

    !! For information, SQL92 has some others.     !! (Jeff: Not sure I understand how this is used.)

    <nondoublequote_character> ::=
    !! See SQL92 syntax rules. But basically what it says.

<nonquote_character> ::=

   !! One ASCII character but not a single quote

  <not_equals_operator> ::= <not_equals_operator1> | <not_equals_operator2>

  <not_equals_operator1> ::= <>

  <not_equals_operator2> ::= !=

<null_predicate> ::= <column_reference> IS [ NOT ] NULL

<numeric_primary> ::=

       <value_expression_primary>
     | <numeric_value_function>



   <numeric_value_expression> ::=

       <term>
     | <numeric_value_expression> <plus_sign> <term>
     | <numeric_value_expression> <minus_sign> <term>



   <numeric_value_function> ::=

      <trig_function>
    | <math_function>	

    | <user_defined_function>
    | <system_defined_function>
   !! For information, SQ92 also supports <position_expression>,    !! <extract_expression> and <length_expression>

<order_by_clause> ::= ORDER BY <sort_specification_list>

  <ordering_specification> ::= ASC | DESC

  <outer_join_type> ::= LEFT | RIGHT | FULL

<pattern> ::= <character_value_expression>

  <percent> ::= %

  <period> ::= .

  <plus_sign> ::= +

    <point> ::= POINT <left_paren> <coord_sys> <comma> <coordinates> <right_paren>

    <polygon> ::=

       POLYGON <left_paren> <coord_sys>
                    <comma> <coordinates>
                    <comma> <coordinates>
                  { <comma> <coordinates> } ?
               <right_paren>



   <predicate> ::=

      <comparison_predicate>
    | <between_predicate>
    | <in_predicate>
    | <like_predicate>

    | <null_predicate>
    | <exists_predicate>

   !! For information, SQL92 also supports the following predicates:
   !! <quantified_comparison_predicate>, <unique_predicate>,
   !! <match_predicate> and <overlaps_predicate>



   <qualified_join> ::=

       <table_reference> [ NATURAL ] [ <join_type> ] JOIN
       <table_reference> [ <join_specification> ]

<qualifier> ::= <table_name> | <correlation_name>

    <query_expression> ::=

        <query_specification>
      | <joined_table>

    !! ADQL Note: SQL92 uses <non_join_query_expression> rather than <query_specification>

<query_specification> ::=

       SELECT [ <set_quantifier> ] [ <set_limit> ] <select_list> <table_expression>

  <question_mark> ::= ?

  <quote> ::= '

  <quote_symbol> ::= <quote> <quote>

  <radius> ::= <numeric_value_expression>

    <rectangle> ::=

       RECTANGLE <left_paren> <coord_sys>
                      <comma> <coordinates> <comma> <coordinates>
                 <right_paren>

  <region> ::= REGION <left_paren> <string_value_expression> <right_paren>

    <region_function> ::= <contains_function> | <intersects_function>     !! Other functions could fit here.
    !! For example: Equals, Disjoint, Touches, Crosses, Within, Overlaps, Relate

<regular_identifier> ::=

       <simple_Latin_letter>...
       [ { <digit> | <simple_Latin_letter> | <underscore> }... ]

  <right_bracket> ::= ]

  <right_paren> ::= )

     <schema_name> ::= [ <catalog_name> <period> ] <unqualified_schema name>

<search_condition> ::=

       <boolean_term>
     | <search_condition> OR <boolean_term>



   <select_list> ::=

       <asterisk>
     | <select_sublist> [ { <comma> <select_sublist> }... ]

<select_sublist> ::= <derived_column> | <qualifier> <period>
<asterisk>

  <semicolon> ::= ;

  <separator> ::= { <comment> | <space> | <newline> }...

<set_function_specification> ::=

       COUNT <left_paren> <asterisk> <right_paren>
     | <general_set_function>

  <set_function_type> ::= AVG | MAX | MIN | SUM | COUNT

<set_limit> ::= TOP <unsigned_integer>

  <set_quantifier> ::= DISTINCT | ALL

  <sign> ::= <plus_sign> | <minus_sign>

  <signed_integer> ::= [ <sign> ] <unsigned_integer>

<simple_Latin_letter> ::=

       <simple_Latin_upper_case_letter>
     | <simple_Latin_lower_case_letter>



   <simple_Latin_lower_case_letter> ::=

       a | b | c | d | e | f | g | h | i | j | k | l | m | n | o
     | p | q | r | s | t | u | v | w | x | y | z



   <simple_Latin_upper_case_letter> ::=

       A | B | C | D | E | F | G | H | I | J | K | L | M | N | O
     | P | Q | R | S | T | U | V | W | X | Y | Z

  <solidus> ::= /

    <sort_key> ::= <column_name> | <unsigned_integer>     !! For information, the use of <unsigned_integer> in <sort_key>is a deprecated feature of SQL92

<sort_specification> ::=

       <sort_key> [ <ordering_specification> ]    !! For information, SQL92 also supports <collate_clause>

<sort_specification_list> ::=

       <sort_specification> [ { <comma> <sort_specification> }... ]

<space> ::=

   !! Imagine there is a space character here

<string_value_expression> ::=

       <character_value_expression>
   !! For information, SQL92 also supports <bit_value_expression>

     <subquery> ::= <left_paren> <query_expression> <right_paren>

<system_defined_function> ::=

      <distance_function>
    | <region_function>
    | <longitude>
    | <latitude>
    | <area>



   <table_expression> ::=

       <from_clause>
       [ <where_clause> ]
       [ <group_by_clause> ]
       [ <having_clause> ]
       [ <order_by_clause> ]

<table_name> ::= [ <schema_name> <period> ] <identifier>

<table_reference> ::=

      <table_name> [ <correlation_specification> ]
    | <derived_table> <correlation_specification>
    | <joined_table>

<table_subquery> ::= <subquery>

<term> ::=

       <factor>
     | <term> <asterisk> <factor>
     | <term> <solidus> <factor>



   <token> ::=

       <nondelimiter_token>
     | <delimiter_token>



   <trig_function> ::=

       ACOS <left_paren> <numeric_value_expression> <right_paren>
     | ASIN <left_paren> <numeric_value_expression> <right_paren>
     | ATAN <left_paren> <numeric_value_expression> <right_paren>
     | ATAN2 <left_paren> <numeric_value_expression> <comma>  
<numeric_value_expression> <right_paren>
     | COS <left_paren> <numeric_value_expression> <right_paren>
     | COT <left_paren> <numeric_value_expression> <right_paren>
     | SIN <left_paren> <numeric_value_expression> <right_paren>
     | TAN <left_paren> <numeric_value_expression> <right_paren>

  <underscore> ::= _

  <unqualified_schema name> ::= <identifier>

  <unsigned_integer> ::= <digit>...

  <unsigned_literal> ::= <unsigned_numeric_literal> | <general_literal>

<unsigned_numeric_literal> ::=

      <exact_numeric_literal>
    | <approximate_numeric_literal>

<unsigned_value_specification> ::= <unsigned_literal>

<user_defined_function> ::=

       <user_defined_function_name>
           <left_paren>
               [ <user_defined_function_param> [ { <comma>  
<user_defined_function_param> }... ] ]
           <right_paren>

   !! ADQL Note:
   !! If metadata on a user defined function is available, this should be used.

   !! For example, function names and cardinality of arguments should be checked.

    <user_defined_function_name> ::=

        [ <default_function_prefix> ] <regular_identifier>     !! Function names should be checked against metadata where available.

<user_defined_function_param> ::= <value_expression>

<value_expression> ::=

       <numeric_value_expression>
     | <string_value_expression>
     | <geometry_value_expression>

   !! For information only, SQL92 supports <datetime_value_expression> and <interval_value_expression>

<value_expression_primary> ::=

       <unsigned_value_specification>
     | <column_reference>
     | <set_function_specification>
     | <left_paren> <value_expression> <right_paren>

   !! For information, SQL92 supports <scalar_subquery>,    !! <case_expression> and <cast_specification>

  <vertical_bar> ::= |

  <where_clause> ::= WHERE <search_condition>

---

Pedro Osuna Alcalaya

European Space Agency (ESA)
European Space Astronomy Centre (ESAC)
Science Operations Department (SCI-O)
Science Archives and Computer Support Engineering Unit (SCI-OE) e-mail: Pedro.Osuna-at-esa.int
Tel + 34 91 813 13 14 Fax: +34 91 813 11 72

---

European Space Astronomy Centre (ESAC)
P.O. Box 78
E-28691 Villanueva de la Cañada
MADRID - SPAIN

---

This message and any attachments are intended for the use of the addressee or addressees only. The unauthorised disclosure, use, dissemination or copying (either in whole or in part) of its content is prohibited. If you received this message in error, please delete it from your system and notify the sender. E-mails can be altered and their integrity cannot be guaranteed. ESA shall not be liable for any e-mail if modified.

---

Received on 2008-03-19Z10:51:16