Complete ADQL 2.0 grammar in PEG
Marco Molinaro
molinaro at oats.inaf.it
Fri Jan 12 09:01:41 CET 2018
Hi Walter,
thank you for the PEG version and attached tests.
It's a bit say saying hallo to you from the VO perspective.
Best wishes for your new job!
Marco
2018-01-12 6:49 GMT+01:00 Walter Landry <wlandry at caltech.edu>:
> Hi Everyone,
>
> There was interest in the past in a PEG version of the ADQL grammar.
> I am attaching a PEG version of ADQL 2.0 and two sets of tests. As
> discussed earlier, I had to make a number of modifications to the BNF
> to get an equivalent PEG grammar. My tests are extensive but not
> comprehensive. So I am somewhat confident, but not completely
> confident, that it parses everything according to the standard.
>
> To test it yourself, you can paste adql.peg into box 1 in
>
> https://pegjs.org/online
>
> and adql_tests_pass.sql into box 2. That will give you immediate
> feedback on whether the query parses.
>
> Normally, I would say that if you see any problems, please let me
> know. However, my last day at IRSA is today, after which I will be
> moving to a position as a Research Software Engineer in quantum field
> theory. If you need to contact me, my email address will not change.
>
> I have had a great time going to the IVOA meetings and seeing so many
> friendly, familiar faces. I wish you all the best in working out
> these standards.
>
> Cheers,
> Walter Landry
>
> /// A PEG grammar suitable for directly putting into PEGjs.
>
> queries = (space* query space* ';')* space*
>
> unqualified_schema_name = identifier
>
> correlation_name = identifier
> correlation_specification = ("AS"i space)? space* correlation_name
>
> /// The spec says to have correlation_name as an alternate, but
> /// table_name matches everything that correlation name matches,
> /// so correlation_name will never match.
> qualifier = table_name
>
> /// I have to manually expand the possibilities in
> /// column_reference and table name, because a catalog.schema can
> /// match against schema.table or table.column, gobbling up the
> /// table or column name and making the parse fail.
> table_name =
> (catalog_name '.' unqualified_schema_name '.' identifier)
> / (unqualified_schema_name '.' identifier)
> / identifier
>
> column_reference =
> (catalog_name '.' unqualified_schema_name
> '.' identifier '.' identifier)
> / (unqualified_schema_name '.' identifier '.' identifier)
> / (identifier '.' identifier)
> / identifier
> catalog_name = identifier
>
> /// This puts term and numeric_value_expression on the left, not
> /// right. Otherwise the rule greedily recurses on itself and
> /// runs out of stack space. With that reordering, the first term
> /// is always the same and the second part becomes optional.
> term = factor (space* ('*' / '/') space* term)?
> numeric_value_expression =
> term (space* ('+' / '-') space* numeric_value_expression)?
>
> string_value_function = string_geometry_function / user_defined_function
>
> string_geometry_function = extract_coordsys
> extract_coordsys = 'COORDSYS'i space* '(' space* geometry_value_expression
> space* ')'
>
> /// Flipped the order here because a value_expression_primary can
> /// match a function name that should be matched by
> /// string_value_function
> character_primary = string_value_function / value_expression_primary
> character_factor = character_primary
> concatenation_operator = '||'
> /// Flip the order of character_factor and
> /// character_value_expression to prevent recursion.
> character_value_expression = character_factor
> (space* concatenation_operator space* character_value_expression)?
>
> string_value_expression = character_value_expression
>
> concatenation_expression = character_factor space* concatenation_operator
> space* character_value_expression
>
> /// This expression first checks for a concatenation operator.
> /// Otherwise a numeric_value_expression would match the first
> /// half of a concatenation, meaning the concatenation operator
> /// would cause the parse to fail. We can not put
> /// string_value_expression first, because that would partially
> /// match arithmetic. For 'a+b', it matches 'a' but not the '+'.
>
> value_expression = concatenation_expression
> / numeric_value_expression / string_value_expression /
> geometry_value_expression
>
> column_name = identifier
>
> as = value_expression space* ('AS'i space+)? column_name
>
> select_non_as_item = (qualifier '.*') / value_expression
> select_item = as / select_non_as_item
> select_list = select_item (space* ',' space* select_item)*
>
> columns = '*' / select_list
> set_function_type = 'AVG'i / 'MAX'i / 'MIN'i
> / 'SUM'i / 'COUNT'i
> set_quantifier = ('DISTINCT'i / 'ALL'i)
> &space
> /// The '&nonidentifier_character' after set_function_type allows us
> /// to fail immediately if we get a set_function that does not have a
> /// parenthesis.
> general_set_function =
> set_function_type &nonidentifier_character
> space* '('
> (space* set_quantifier)?
> space* value_expression space* ')'
>
> set_function_specification
> = ('COUNT'i space* '(' space* '*' space* ')')
> / general_set_function
>
> when_operand = value_expression
> result_expression = value_expression
> result = result_expression / 'NULL'i
> simple_when_clause = 'WHEN'i
> space+ when_operand
> space+ 'THEN'i
> space+ result
> else_clause = 'ELSE'i space+ result
>
> value_expression_primary =
> unsigned_value_specification
> / column_reference
> / set_function_specification
> / ('(' value_expression ')')
>
> trig_function = (('ACOS'i
> / 'ASIN'i
> / 'ATAN'i
> / 'COS'i
> / 'COT'i
> / 'SIN'i
> / 'TAN'i)
> &nonidentifier_character)
> space* '('
> space* numeric_value_expression
> space* ')'
> / ('ATAN2'i &nonidentifier_character space* '('
> space* numeric_value_expression space* ','
> space* numeric_value_expression space* ')')
>
> math_function = (('ABS'i
> / 'CEILING'i
> / 'DEGREES'i
> / 'EXP'i
> / 'FLOOR'i
> / 'LOG10'i
> / 'LOG'i
> / 'RADIANS'i
> / 'SQRT'i)
> &nonidentifier_character
> space* '('
> space* numeric_value_expression
> space* ')')
> / (('MOD'i / 'POWER'i)
> &nonidentifier_character
> space* '('
> space* numeric_value_expression
> space* ','
> space* numeric_value_expression
> space* ')')
> / ('PI'i &nonidentifier_character
> space* '(' space* ')')
> / ('RAND'i &nonidentifier_character
> space* '(' numeric_value_expression? ')')
> / (('ROUND'i / 'TRUNCATE'i) &nonidentifier_character
> space* '('
> space* numeric_value_expression
> (space* ',' space* signed_integer)?
> space* ')')
>
> /// default_function_prefix is a bit useless since it is optional.
> default_function_prefix = 'udf_'
>
> user_defined_function_name = default_function_prefix?
> regular_identifier
>
> user_defined_function_param = value_expression
>
> user_defined_function = user_defined_function_name
> space* '('
> (space* user_defined_function_param
> (space* ',' space* user_defined_function_param)*)?
> space* ')'
>
> numeric_value_function = trig_function / math_function
> / user_defined_function / numeric_geometry_function
> /// Flipped the order here, because a value_expression can match a
> /// function name.
> numeric_primary = numeric_value_function / value_expression_primary
> factor = sign? numeric_primary
> coord_sys = string_value_expression
>
> column_or_number = column_reference / double
>
> coordinates = numeric_value_expression space* ',' space*
> numeric_value_expression
> point = 'POINT'i
> space* '('
> space* coord_sys
> space* ','
> space* coordinates
> space* ')'
> point_or_column = point / column_reference
>
> circle = 'CIRCLE'i
> space* '(' space*
> coord_sys
> space* ','
> space* coordinates
> space* ','
> space* numeric_value_expression
> space* ')'
> box = 'BOX'i
> space* '('
> space* coord_sys
> space* ','
> space* coordinates
> space* ','
> space* numeric_value_expression
> space* ','
> space* numeric_value_expression
> space* ')'
>
> centroid = 'CENTROID'i space* '(' space* geometry_value_expression space*
> ')'
> region = 'REGION'i space* '(' space* string_value_expression space* ')'
>
> coord_list = coordinates (space* ',' space* coordinates)*
> polygon = 'POLYGON'i space*
> '(' space* coord_sys
> space* ','
> space* coord_list
> space* ')'
>
> geometry_value_expression = value_expression_primary /
> geometry_value_function
> geometry_value_function = point / circle / box / polygon / centroid /
> region
>
> area = 'AREA'i space* '(' space* geometry_value_expression space* ')'
> coord1 = 'COORD1'i space* '(' space* coord_value space* ')'
> coord2 = 'COORD2'i space* '(' space* coord_value space* ')'
> distance = 'DISTANCE'i space* '(' space* coord_value space* ',' space*
> coord_value space* ')'
> coord_value = point / column_reference
>
> non_predicate_geometry_function = area / coord1 / coord2 / distance
>
> contains = 'CONTAINS'i
> space* '('
> space* geometry_value_expression
> space* ','
> space* geometry_value_expression
> space* ')'
> intersects = 'INTERSECTS'i
> space* '('
> space* geometry_value_expression
> space* ','
> space* geometry_value_expression space* ')'
>
> predicate_geometry_function = contains / intersects
>
> numeric_geometry_function = predicate_geometry_function /
> non_predicate_geometry_function
>
> keyword = (ADQL_reserved_word / SQL_reserved_word) &(!identifier_character)
>
> simple_Latin_letter = [a-zA-Z]
> identifier_character = digit / simple_Latin_letter / '_'
> /// nonidentifier_character is to signal that, for example, in an
> /// AND, clause, AND is followed by something that is not an
> /// identifier (e.g. a space or parentheses).
> nonidentifier_character = &(!identifier_character) char
> all_identifiers = simple_Latin_letter identifier_character*
> regular_identifier = &(!keyword) all_identifiers
>
> nondoublequote_character = &(!'"') char
> delimited_identifier_part = nondoublequote_character / '""'
> delimited_identifier_body = delimited_identifier_part+
> delimited_identifier = '"' delimited_identifier_body '"'
>
> identifier = regular_identifier / delimited_identifier
>
> join_specification = join_condition / named_columns_join
>
> join_condition = 'ON'i space+ search_condition
>
> named_columns_join = 'USING'i space+ '('
> space* join_column_list space* ')'
>
> join_column_list = column_name_list
>
> column_name_list = column_name (space* ',' space* column_name)*
>
> table_correlation = table_name (space* correlation_specification)?
>
> /// Joins are a bit circuitous because of the possibility of
> /// parentheses, but I think this is the same as the BNF
>
> outer_join = ('LEFT'i / 'RIGHT'i / 'FULL'i) &space
> (space* 'OUTER'i &space)?
>
> join_type = 'INNER'i / outer_join
>
> join_suffix =
> ('NATURAL'i space+)?
> (join_type space+)?
> 'JOIN'i space+
> table_reference (space* join_specification)?
>
> qualified_join = (('(' space* joined_table space* ')')
> / table_correlation)
> (space* join_suffix)+
>
> joined_table = qualified_join / ('(' space* joined_table space* ')')
>
> digit = [0-9]
> unsigned_integer = digit+
> exact_numeric_literal = (unsigned_integer ('.' unsigned_integer)?)
> / ('.' unsigned_integer)
>
> sign = '+' / '-'
> signed_integer = sign? unsigned_integer
> mantissa = exact_numeric_literal
> exponent = signed_integer
> approximate_numeric_literal = mantissa ('E' / 'e') exponent
> unsigned_numeric_literal = approximate_numeric_literal /
> exact_numeric_literal
>
> double = sign? unsigned_numeric_literal
>
> quote = '\''
> literal_space = ' '
> newline = '\n'
> tab = '\t'
> minus_sign = '-'
> char = .
>
> nonquote_character = &(!quote) char
> character_representation = nonquote_character / '\'\''
>
> comment_introducer = minus_sign minus_sign+
> comment_character = &(!newline) char
> comment = comment_introducer comment_character* newline
>
> separator = comment / literal_space / newline
> space = separator
>
> /// String literals are implicitly concatenated when placed next to each
> other
> character_string_literal= quote character_representation* quote
> (separator+ quote character_representation* quote)*
>
> general_literal = character_string_literal
> unsigned_literal = unsigned_numeric_literal / general_literal
> unsigned_value_specification = unsigned_literal
> derived_correlation = subquery space* correlation_specification
>
> table_reference = joined_table / table_correlation / derived_correlation
>
> from_clause = 'FROM'i space+
> table_reference (space* ',' space* table_reference)*
>
> comparison_predicate = value_expression
> space* ('=' / '!=' / '<>' / '<=' / '>=' / '<' / '>')
> space* value_expression
>
> between_predicate = value_expression &space
> (space* 'NOT'i &space)?
> space* 'BETWEEN'i &space
> space* value_expression &space
> space* 'AND'i &space
> space* value_expression
>
> in_predicate = value_expression &space
> (space* 'NOT'i &space)?
> space* 'IN'i &space
> space* (subquery
> / ('(' space* (value_expression (space* ',' space*
> value_expression)*)
> space* ')'))
>
> null_predicate = value_expression
> space* ('IS'i &space)
> (space* 'NOT'i &space)?
> space* 'NULL'i
>
> match_value = character_value_expression
> pattern = character_value_expression
>
> like_predicate = match_value
> (space* 'NOT'i &space)?
> space* 'LIKE'i space+ pattern
>
> exists_predicate= 'EXISTS'i space+ subquery
>
> predicate = comparison_predicate / between_predicate / in_predicate
> / null_predicate / like_predicate / exists_predicate
>
> where = 'WHERE'i space+ search_condition
>
> grouping_column_reference = column_reference
> grouping_column_reference_list = grouping_column_reference
> (space* ',' space* column_reference)*
> group_by_clause = 'GROUP'i &space
> space* 'BY'i &space
> space* grouping_column_reference_list
>
> having_clause = 'HAVING'i &space
> space* search_condition
>
> sort_key = column_name / unsigned_integer
> ordering_specification = 'ASC'i / 'DESC'i
> /// I have the vague feeling that there are cases where there are
> /// no spaces between the sort_key and ordering_specification, but
> /// I can not think of any.
> sort_specification = sort_key (space+ ordering_specification)?
> sort_specification_list = sort_specification
> (space* ',' space* sort_specification)*
> order_by_clause = 'ORDER'i &space
> space* 'BY'i &space
> space* sort_specification_list
>
> query = 'SELECT'i
> (space+ set_quantifier)?
> (space+ 'TOP'i space+ unsigned_integer)?
> space+ columns
> space+ from_clause
> (space+ where)?
> (space+ group_by_clause)?
> (space+ having_clause)?
> (space+ order_by_clause)?
>
> subquery = '(' space* (query / joined_table) space* ')'
>
> /// Reverse sort to avoid early matches.
> ADQL_reserved_word = 'TRUNCATE'i / 'TOP'i
> / 'SQRT'i / 'SIN'i
> / 'ROUND'i / 'REGION'i
> / 'RAND'i / 'RADIANS'i
> / 'POWER'i / 'POLYGON'i
> / 'POINT'i / 'PI'i
> / 'MOD'i / 'LOG10'i
> / 'LOG'i / 'INTERSECTS'i
> / 'FLOOR'i / 'EXP'i
> / 'DISTANCE'i
> / 'DEGREES'i / 'COS'i
> / 'COORDSYS'i / 'COORD2'i
> / 'COORD1'i / 'CONTAINS'i
> / 'CIRCLE'i / 'CENTROID'i
> / 'CEILING'i / 'BOX'i
> / 'ATAN2'i / 'ATAN'i
> / 'ASIN'i / 'AREA'i
> / 'ACOS'i / 'ABS'i
>
> /// Split up SQL_reserved_word to help memory usage and compile times.
> SQL_reserved_word_00 = 'ZONE'i / 'YEAR'i
> / 'WRITE'i / 'WORK'i
> / 'WITH'i / 'WHERE'i
> / 'WHENEVER'i / 'WHEN'i
> / 'VIEW'i / 'VARYING'i
> / 'VARCHAR'i
> / 'VALUES'i / 'VALUE'i
> / 'USING'i / 'USER'i
> / 'USAGE'i
>
> SQL_reserved_word_01
> = 'UPPER'i / 'UPDATE'i
> / 'UNKNOWN'i / 'UNIQUE'i
> / 'UNION'i / 'TRUE'i
> / 'TRIM'i / 'TRANSLATION'i
> / 'TRANSLATE'i / 'TRANSACTION'i
> / 'TRAILING'i / 'TO'i
> / 'TIMEZONE_MINUTE'i / 'TIMEZONE_HOUR'i
> / 'TIMESTAMP'i
>
> SQL_reserved_word_02
> = 'TIME'i / 'THEN'i
> / 'TEMPORARY'i / 'TABLE'i
> / 'SYSTEM_USER'i / 'SUM'i
> / 'SUBSTRING'i / 'SQLSTATE'i
> / 'SQLERROR'i / 'SQLCODE'i
> / 'SQL'i / 'SPACE'i
> / 'SOME'i / 'SMALLINT'i
> / 'SIZE'i / 'SET'i
>
> SQL_reserved_word_03
> = 'SESSION_USER'i / 'SESSION'i
> / 'SELECT'i / 'SECTION'i
> / 'SECOND'i / 'SCROLL'i
> / 'SCHEMA'i / 'ROWS'i
> / 'ROLLBACK'i / 'RIGHT'i
> / 'REVOKE'i / 'RESTRICT'i
> / 'RELATIVE'i / 'REFERENCES'i
> / 'REAL'i / 'READ'i
>
> SQL_reserved_word_10
> = 'PUBLIC'i / 'PROCEDURE'i
> / 'PRIVILEGES'i / 'PRIOR'i
> / 'PRIMARY'i / 'PRESERVE'i
> / 'PREPARE'i / 'PRECISION'i
> / 'POSITION'i / 'PARTIAL'i
> / 'PAD'i / 'OVERLAPS'i
> / 'OUTPUT'i / 'OUTER'i
> / 'ORDER'i / 'OR'i
>
> SQL_reserved_word_11
> = 'OPTION'i / 'OPEN'i
> / 'ONLY'i / 'ON'i / 'OF'i
> / 'OCTET_LENGTH'i / 'NUMERIC'i
> / 'NULLIF'i / 'NULL'i
> / 'NOT'i / 'NO'i
> / 'NEXT'i / 'NCHAR'i
> / 'NATURAL'i / 'NATIONAL'i
>
> SQL_reserved_word_12 = 'NAMES'i / 'MONTH'i
> / 'MODULE'i / 'MINUTE'i
> / 'MIN'i / 'MAX'i
> / 'MATCH'i / 'LOWER'i
> / 'LOCAL'i / 'LIKE'i
> / 'LEVEL'i / 'LEFT'i
> / 'LEADING'i / 'LAST'i
> / 'LANGUAGE'i / 'KEY'i
>
> SQL_reserved_word_13
> = 'JOIN'i / 'ISOLATION'i
> / 'IS'i / 'INTO'i
> / 'INTERVAL'i / 'INTERSECT'i
> / 'INTEGER'i / 'INT'i
> / 'INSERT'i / 'INSENSITIVE'i
> / 'INPUT'i / 'INNER'i
> / 'INITIALLY'i / 'INDICATOR'i
> / 'IN'i / 'IMMEDIATE'i
>
> SQL_reserved_word_20 = 'IDENTITY'i / 'HOUR'i
> / 'HAVING'i / 'GROUP'i
> / 'GRANT'i / 'GOTO'i
> / 'GO'i / 'GLOBAL'i
> / 'GET'i / 'FULL'i
> / 'FROM'i / 'FOUND'i
> / 'FOREIGN'i / 'FOR'i
> / 'FLOAT'i / 'FIRST'i
>
> SQL_reserved_word_21
> = 'FETCH'i / 'FALSE'i
> / 'EXTRACT'i / 'EXTERNAL'i
> / 'EXISTS'i / 'EXECUTE'i
> / 'EXEC'i / 'EXCEPTION'i
> / 'EXCEPT'i / 'ESCAPE'i
> / 'END-EXEC'i / 'END'i
> / 'ELSE'i / 'DROP'i
> / 'DOUBLE'i / 'DOMAIN'i
>
> SQL_reserved_word_22
> = 'DISTINCT'i / 'DISCONNECT'i
> / 'DIAGNOSTICS'i / 'DESCRIPTOR'i
> / 'DESCRIBE'i / 'DESC'i
> / 'DELETE'i / 'DEFERRED'i
> / 'DEFERRABLE'i / 'DEFAULT'i
> / 'DECLARE'i / 'DECIMAL'i
> / 'DEALLOCATE'i / 'DAY'i
> / 'DATE'i / 'CURSOR'i
>
> SQL_reserved_word_23
> = 'CURRENT_USER'i / 'CURRENT_TIMESTAMP'i
> / 'CURRENT_TIME'i / 'CURRENT_DATE'i
> / 'CURRENT'i / 'CROSS'i
> / 'CREATE'i / 'COUNT'i
> / 'CORRESPONDING'i / 'CONVERT'i
> / 'CONTINUE'i / 'CONSTRAINTS'i
> / 'CONSTRAINT'i / 'CONNECTION'i
> / 'CONNECT'i / 'COMMIT'i
>
> SQL_reserved_word_30
> = 'COLUMN'i / 'COLLATION'i
> / 'COLLATE'i / 'COALESCE'i
> / 'CLOSE'i / 'CHECK'i
> / 'CHAR_LENGTH'i / 'CHARACTER_LENGTH'i
> / 'CHARACTER'i / 'CHAR'i
> / 'CATALOG'i / 'CAST'i
> / 'CASE'i / 'CASCADED'i
> / 'CASCADE'i / 'BY'i
>
> SQL_reserved_word_31 = 'BOTH'i / 'BIT_LENGTH'i
> / 'BIT'i / 'BETWEEN'i
> / 'BEGIN'i / 'AVG'i
> / 'AUTHORIZATION'i
> / 'AT'i / 'ASSERTION'i
> / 'ASC'i / 'AS'i
> / 'ARE'i / 'ANY'i
> / 'AND'i / 'ALTER'i
> / 'ALLOCATE'i
>
> SQL_reserved_word_32 = 'ALL'i / 'ADD'i
> / 'ACTION'i
> / 'ABSOLUTE'i
>
> SQL_reserved_word_0 = SQL_reserved_word_00 / SQL_reserved_word_01
> / SQL_reserved_word_02 / SQL_reserved_word_03
>
> SQL_reserved_word_1 = SQL_reserved_word_10 / SQL_reserved_word_11
> / SQL_reserved_word_12 / SQL_reserved_word_13
>
> SQL_reserved_word_2 = SQL_reserved_word_20 / SQL_reserved_word_21
> / SQL_reserved_word_22 / SQL_reserved_word_23
>
> SQL_reserved_word_3 = SQL_reserved_word_30 / SQL_reserved_word_31
> / SQL_reserved_word_32
>
> SQL_reserved_word = SQL_reserved_word_0 / SQL_reserved_word_1
> / SQL_reserved_word_2 / SQL_reserved_word_3
>
> boolean_literal = 'True'i / 'False'i
>
> boolean_value_expression = boolean_literal / user_defined_function
>
> boolean_primary = predicate
> / ('(' space* search_condition space* ')')
> / boolean_value_expression
>
> boolean_factor = ('NOT'i &space)? space* boolean_primary
>
> boolean_term = boolean_factor
> space+ ('AND'i / 'OR'i)
> space+ search_condition
>
> search_condition = boolean_term / boolean_factor
>
> Select ra1,dec2,flux From mytable Where Contains(Point('j2000',ra,dec),Circle('J2000',+10
> , -20,-1))= 1;
> Select ra1,dec2,flux From mytable Where 1=Contains(Point('j2000',ra,dec),Circle('J2000',+10
> , -20,-1));
> Select ra1,dec2,flux From mytable Where 1=Contains(pos,Circle('J2000',+10
> , -20,-1));
> Select ra1,dec2,flux From mytable Where Intersects(pos,Circle('J2000',+10
> , -20,-1))= 1;
> Select ra1,dec2,flux From mytable Where 1=INTERSECTs(pos,Circle('J2000',+10
> , -20,-1));
> Select ra1,dec2,flux From mytable Where Contains(Point('galactic',ra,dec),Circle('galactic',+10
> , -20,-1))= 1;
> Select ra1,dec2,flux From mytable Where Contains(Point('ICRS',ra,dec),Circle('icrs',+10
> , -20,-1))= 1;
> SELECT ra1 As rara, dec2, flux FROM mytable WHERE CONTAINS(POINT('J2000
> Geocenter',ra,dec),CIRCLE('J2000 Geocenter',+10 , -20,-1))= 1;
> SELECT ra1 As rara, dec2, flux FROM mytable WHERE CONTAINS(POINT('J2000
> Geocenter',ra,dec),BOX('J2000',+10 , -20,1,2))= 1;
> SELECT ra1 As rara, dec2, flux FROM mytable WHERE CONTAINS(POINT('J2000
> Geocenter',ra,dec),POLYGON('J2000',0,1,2,3,4,5,6,7,8,9,10,11,12,13))= 1;
> SELECT * FROM mytable WHERE CONTAINS(POINT('J2000',
> mytable.ra,dec),CIRCLE('J2000',+10 , -20,-1)) = 1;
> SELECT * FROM my_table WHERE 1=CONTAINS(POINT('J2000',ra,
> my_table.dec),CIRCLE('J2000',+10 , -20,-1));
> SELECT * FROM my_table1 WHERE 1= CONTAINS(POINT('J2000',my_
> table1.ra,dec),CIRCLE('J2000',+10 , -20,-1));
> SELECT * FROM my_table1 WHERE 1= CONTAINS(POINT('J2000',my_
> table1.ra,dec),CIRCLE('J2000',+10 , -20,-1)) And (x<1);
> SELECT * FROM my_table1 WHERE (x!=3) and 1= CONTAINS(POINT('J2000',my_
> table1.ra,dec),CIRCLE('J2000',+10 , -20,-1));
> SELECT * FROM my_table1 WHERE x!=3;
> SELECT * FROM my_table1;
> SELECT * FROM my_table1 where x>2;
> SELECT * FROM my_table1 where x>2 AND x<4;
> SELECT * FROM my_table1 WHERE 1= CONTAINS(POINT('J2000',my_
> table1.ra,dec),CIRCLE('J2000',+10 , -20,-1)) And (x<1 And x>2 Or y < 3 Or
> y >5);
> SELECT * FROM my_table1 WHERE 1= CONTAINS(POINT('J2000',my_
> table1.ra,dec),CIRCLE('J2000',+10 , -20,-1)) And (x<1 And x>2);
> SELECT * FROM my_table1 where (x>2 AND x<4) Or (x>6 and x<10);
> SELECT * FROM my_table1 where (x Between 2 AND 4) Or (x>6 and x<10);
> SELECT * FROM my_table1 where x is null;
> SELECT * FROM my_table1 where x is not null;
> SELECT * FROM fp_psc WHERE CONTAINS(POINT('J2000',ccc.fp_psc.ra,
> aaa.ccc.fp_pscdec),CIRCLE('J2000',1,1,0.08))=1 and (ra between 0.95 and
> 1.05);
> SELECT * FROM my_table1 where x in (select y from my_table2);
> SELECT * FROM my_table1 where x in (10,20,30);
> SELECT * FROM my_table1 where x not in (10,20,30);
> SELECT All * FROM my_table1;
> SELECT Distinct * FROM my_table1;
> SELECT Top 14223 * FROM my_table1;
> SELECT * FROM my_table1 where 38*(x+1-3)>2/4;
> SELECT ra+dec as ra_dec FROM my_table1;
> SELECT ra+dec FROM my_table1;
> SELECT my_tablel1.* FROM my_table1;
> SELECT sin(dec),cos(dec),tan(dec),cot(dec),asin(dec),acos(dec),atan(dec),atan2(ra,dec)
> FROM my_table1;
> SELECT abs(ra),ceiling(ra), degrees(ra),exp(ra),floor(ra),
> log(ra),log10(ra),radians(ra),sqrt(ra) FROM my_table1;
> SELECT mod(ra, dec),power( ra,dec ),pi(),rand(ra),rand(),round(
> ra,10),round(ra),truncate(ra),truncate(ra,10) FROM my_table1;
> SELECT modern() FROM my_table1;
> SELECT my_modern_function(ra,dec) FROM my_table1;
> SELECT my_modern_function(ra,dec), modern() FROM my_table1;
> SELECT my_modern_function(ra,dec) || modern() FROM my_table1;
> select 'a b c','a','a ''bv' from b;
> select "a b","a "" b" from b;
> select 'a' 'b' from b;
> select 'a' --This is a useful comment
> 'b' from b;
> select (a), sum(a), max(all a), min(distinct a), count(a), avg ( a )
> ,min(a) from a;
> select max(all a) from a;
> select count ( * ) from a;
> select count(*) from a;
> select count(a) from a;
> select count(all a) from a;
> select count(distinct a) from a;
> select sum(a) from a;
> select sum(all a) from a;
> select sum(DISTINCT a) from a;
> select sum(DISTINCTa) from a;
> select single from a;
> select a,b from a group by a;
> select a,b from a group by a having x>2;
> SELECT * FROM my_table1 order by x;
> SELECT * FROM my_table1 order by x asc;
> SELECT * FROM my_table1 order by x desc;
> select b from a where x<1 order by a;
> SELECT * FROM my_table1 where x like y;
> SELECT * FROM my_table1 where x like y || z;
> SELECT * FROM my_table1 where x not like my_sin(x);
> SELECT * FROM my_table1 where x like my_sin(x) || x;
> SELECT * FROM my_table1 where exists (select a from b);
> SELECT * FROM my_table WHERE (mjd>=55550.0 and mjd<=65650.5);
> SELECT my_table1.*,'table' from my_table1;
> SELECT myschema.mytable.* from myschema.mytable;
> SELECT * from mycatalog.myschema.mytable;
> SELECT * from myschema.mytable;
> SELECT mycatalog.myschema.mytable.* from mycatalog.myschema.mytable;
> SELECT * from myschema.mytable as mine;
> SELECT * from myschema.mytable mine;
> SELECT * from myschema.mytable, yourscheme.yourtable mine;
> SELECT * from TAP_UPLOAD.mytable;
> SELECT * from tapmod.mytable;
> SELECT TAP_UPLOAD.mytable.b from TAP_UPLOAD.mytable;
> SELECT TAP_UPLOAD.mytable.* from TAP_UPLOAD.mytable;
> SELECT TAP_UPLOAD.mytable.*, fp_psc.* from TAP_UPLOAD.mytable, fp_psc;
> SELECT * FROM my_table1 WHERE 1= CONTAINS(POINT('J2000',TAP_
> UPLOAD.mytable.ra,TAP_UPLOAD.mytable.dec),CIRCLE('J2000',+10 , -20,-1)) ;
> SELECT * FROM my_table1 WHERE 1= CONTAINS(POINT('J2000',TAP_
> UPLOAD.mytable.ra,TAP_UPLOAD.mytable.dec),CIRCLE('J2000',
> TAP_UPLOAD.mytable.ra,TAP_UPLOAD.mytable.dec,-1)) ;
> SELECT alligator from a;
> SELECT tophat from a;
> SELECT max(alligator) from a;
> SELECT maximus from a;
> SELECT ast from a;
> Select fromage from fromming;
> Select fromage from fromming ast;
> select alligator from table1 join table2;
> select alligator from table1 natural join table2;
> select alligator from table1 natural left outer join table2;
> select alligator from table1 right outer join table2;
> select alligator from table1 right join table2;
> select alligator from table1 full outer join table2;
> select alligator from table1 natural inner join table2;
> select alligator from table1 inner join table2;
> select alligator from (table1 join table2);
> select alligator from table1 join (table2 join (table3 join table4));
> select alligator from table1 join table2 on a=b;
> select alligator from table1 t1 join table2 t2 on a=b join table3 t3 on
> c=d join table4 t4 on e=f join table5 t5 on g=h;
> select alligator from (table1 t1 join table2 t2 on a=b) join table3 t3 on
> c=d;
> select alligator from table1 join table2 using (a,b, c );
> select TOP 14223 caom.observation.*,caom.plane.
> *,caom.artifact.*,caom.part.*,caom.chunk.* FROM caom.observation join
> caom.plane on caom.observation.obsid = caom.plane.obsid join caom.artifact
> on caom.plane.planeid = caom.artifact.planeid join caom.part on
> caom.artifact.artifactid = caom.part.artifactid join caom.chunk on
> caom.part.partid = caom.chunk.partid;
> select TOP 14223 caom.observation.*,caom.plane.
> *,caom.artifact.*,caom.part.*,caom.chunk.* FROM (((caom.observation join
> caom.plane on caom.observation.obsid = caom.plane.obsid) join caom.artifact
> on caom.plane.planeid = caom.artifact.planeid) join caom.part on
> caom.artifact.artifactid = caom.part.artifactid) join caom.chunk on
> caom.part.partid = caom.chunk.partid;
> select alligator from (select a from b) as c;
> select fp_psc.* from fp_psc, TAP_UPLOAD.mytable where
> 1=contains(fp_psc.ra_dec, circle('j2000',TAP_UPLOAD.
> mytable.ra,TAP_UPLOAD.mytable.dec,0.001));
> select fp_psc.* from fp_psc, TAP_UPLOAD.mytable where
> 1=contains(fp_psc.ra_dec, circle('j2000',TAP_UPLOAD.
> mytable.ra,TAP_UPLOAD.mytable.dec,TAP_UPLOAD.mytable.r));
> select fp_psc.* from fp_psc, TAP_UPLOAD.mytable where
> 1=contains(fp_psc.ra_dec, circle('j2000',10,TAP_UPLOAD.
> mytable.dec,TAP_UPLOAD.mytable.r));
> select fp_psc.* from fp_psc, TAP_UPLOAD.mytable where
> 1=contains(fp_psc.ra_dec, circle('j2000',TAP_UPLOAD.
> mytable.ra,10,TAP_UPLOAD.mytable.r));
> select (fp_psc.x * TAP_UPLOAD.mytable.x) as dx from fp_psc,
> TAP_UPLOAD.mytable;
> select (TAP_UPLOAD.mytable.x - fp_psc.x) as dx from TAP_UPLOAD.mytable,
> fp_psc;
> select TAP_UPLOAD.mytable.x - TAP_UPLOAD.mytable.y as dx from
> TAP_UPLOAD.mytable, fp_psc;
> select (fp_psc.x - fp_psc.y) + (TAP_UPLOAD.mytable.x - fp_psc.x) as dxy
> from fp_psc;
> select fp_psc.x - TAP_UPLOAD.mytable.x from fp_psc, TAP_UPLOAD.mytable;
> select TAP_UPLOAD.mytable.x - fp_psc.x from fp_psc, TAP_UPLOAD.mytable;
> select * from herschel.observations where ST_Covers(poly,ST_Point(10,10))='t'
> or ST_Distance(poly,ST_Point(10,10)) <= 0.0;
> select herschel.observations.*, ST_Distance(ST_Centroid(poly),
> ST_Point(84.912154,-69.652608)) as dist_to_centroid from
> herschel.observations where ST_Covers(poly,ST_Point(10,10))='t' or
> ST_Distance(poly,ST_Point(10,10)) <= 0.0;
> select * from a where (1=0) AND (2=1) ORDER BY cntr ASC;
> select '{a,b}' from c;
> select ARRAYNOT from c;
> select * from twomass.full_images where (ST_DWithin(twomass.full_images.poly,ST_Point(1,
> 1),0*111194.68229846345,'f'));
> SELECT schema_name as schemas FROM (select schema_name, min(table_index)
> as schema_index from TAP_SCHEMA.tables group by schema_name order by
> schema_index) as temp_schemas group by schemas order by schema_index;
> select schema_name from (select schema_name, min(table_index) as
> schema_index from TAP_SCHEMA.tables group by schema_name order by
> schema_index) temp_schemas;
> SELECT TAP_UPLOAD.pos.cntr as in_row_id FROM wise.wise_allwise_p3am_cdd,
> TAP_UPLOAD.pos WHERE (ST_Intersects(TAP_UPLOAD.pos.
> poly,wise.wise_allwise_p3am_cdd.poly)) ORDER BY in_row_id ASC,
> dist_to_bounds ASC;
> select CASEFULL from b;
>
>
> POINT('foo',10 20);
> POINT('foo',1.0, 20);
> POINT('foo',10 ,-2.0);
> SELECTTOP 100 * FROM my_table1;
> SELECT TOP100 * FROM my_table1;
> SELECT TOP 100* FROM my_table1;
> SELECT TOP hundred * FROM my_table1;
> SELECT DISTINCT sin FROM my_table1;
> SELECT TOP 100 FROM my_table1;
> SELECT * FROM my_table1 where x in(10,20,30);
> SELECT * FROM my_table1 where x not in(10,20,30);
> SELECT FROM my_table1 WHERE CONTAINS(POINT('J2000',10 ,
> 20),CIRCLE('J2000',10 , 20,1));
> SELECT * FROM WHERE CONTAINS(POINT('J2000',10 , 20),CIRCLE('J2000',10 ,
> 20,1));
> 1= CONTAINS(POINT('J2000',my_table1.ra,dec),CIRCLE('J2000',+10 , -20,-1))
> And x<1 And x>2 Or y < 3 Or y >5SELECT *,ra FROM my_table1;
> SELECT my_tablel1.* as ra_dec FROM my_table1;
> select sum(a,b) from a;
> select sum from a;
> select sum( from a;
> select abs(a,b) from a;
> select sin from a;
> select sin() from a;
> select sin( from a;
> select atan2 from a;
> select atan2() from a;
> select atan2(a) from a;
> select atan2(a,) from a;
> select atan2(a,b from a;
> select round from a;
> select round() from a;
> select round(a,) from a;
> select round(a,b from a;
> select round(a,10 from a;
> select count(* from a;
> select b from a where x<1 groupby a;
> select b from a where x<1 group bya;
> select b from a where x<1 orderby a;
> select b from a where x<1 order bya;
> select b from a where x<1 havinga;
> select a asb from a group by a;
> Select fromage ast from fromming;
> SELECT * FROM my_table1 where (x notBetween 2 AND 4);
> SELECT * FROM my_table1 where x notin (10,20,30);
> SELECT * FROM mytable WHERECONTAINS(POINT('J2000',
> mytable.ra,dec),CIRCLE('J2000',+10 , -20,-1)) = 1;
> SELECT * FROM mytable as my WHERECONTAINS(POINT('J2000',
> mytable.ra,dec),CIRCLE('J2000',+10 , -20,-1)) = 1;
> SELECT * from TAP_UPLOAD.wrong_table;
> SELECT TAP_UPLOAD.* from TAP_UPLOAD.mytable;
> SELECT TAP_UPLOAD.wrong_table from TAP_UPLOAD.mytable;
> select CASE foo WHENever THEN 'c' END from b;
> select alligator from (table1);
>
> POINT('foo',10 20);
> POINT('foo',1.0, 20);
> POINT('foo',10 ,-2.0);
> SELECTTOP 100 * FROM my_table1;
> SELECT TOP100 * FROM my_table1;
> SELECT TOP 100* FROM my_table1;
> SELECT TOP hundred * FROM my_table1;
> SELECT DISTINCT sin FROM my_table1;
> SELECT TOP 100 FROM my_table1;
> SELECT * FROM my_table1 where x in(10,20,30);
> SELECT * FROM my_table1 where x not in(10,20,30);
> SELECT FROM my_table1 WHERE CONTAINS(POINT('J2000',10 ,
> 20),CIRCLE('J2000',10 , 20,1));
> SELECT * FROM WHERE CONTAINS(POINT('J2000',10 , 20),CIRCLE('J2000',10 ,
> 20,1));
> 1= CONTAINS(POINT('J2000',my_table1.ra,dec),CIRCLE('J2000',+10 , -20,-1))
> And x<1 And x>2 Or y < 3 Or y >5SELECT *,ra FROM my_table1;
> SELECT my_tablel1.* as ra_dec FROM my_table1;
> select sum(a,b) from a;
> select sum from a;
> select sum( from a;
> select abs(a,b) from a;
> select sin from a;
> select sin() from a;
> select sin( from a;
> select atan2 from a;
> select atan2() from a;
> select atan2(a) from a;
> select atan2(a,) from a;
> select atan2(a,b from a;
> select round from a;
> select round() from a;
> select round(a,) from a;
> select round(a,b from a;
> select round(a,10 from a;
> select count(* from a;
> select b from a where x<1 groupby a;
> select b from a where x<1 group bya;
> select b from a where x<1 orderby a;
> select b from a where x<1 order bya;
> select b from a where x<1 havinga;
> select a asb from a group by a;
> Select fromage ast from fromming;
> SELECT * FROM my_table1 where (x notBetween 2 AND 4);
> SELECT * FROM my_table1 where x notin (10,20,30);
> SELECT * FROM mytable WHERECONTAINS(POINT('J2000',
> mytable.ra,dec),CIRCLE('J2000',+10 , -20,-1)) = 1;
> SELECT * FROM mytable as my WHERECONTAINS(POINT('J2000',
> mytable.ra,dec),CIRCLE('J2000',+10 , -20,-1)) = 1;
> SELECT * from TAP_UPLOAD.wrong_table;
> SELECT TAP_UPLOAD.* from TAP_UPLOAD.mytable;
> SELECT TAP_UPLOAD.wrong_table from TAP_UPLOAD.mytable;
> select CASE foo WHENever THEN 'c' END from b;
> select alligator from (table1);
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ivoa.net/pipermail/dal/attachments/20180112/1dbca666/attachment-0001.html>
More information about the dal
mailing list