Normalisation Features

The parser normalizes language features using the following methods:

Alias token AS added

The AS token is always added to a column alias.

Parentheses added:

In the following cases parentheses are added to ensure the semantic meaning:

• surrounding the subquery itself and each statement (ALTER USER, CREATE TABLE, CREATE USER, DELETE, GRANT, INSERT, REVOKE, TRUNCATE TABLE, UPDATE) inside the subquery:

  all_or_any_predicate -> scalar_exp COMPARISON any_all_some subquery

• surrounding subqueries included in the following terms: query_exp, query_spec, query_term, scalar_exp and scalar_sub_exp:

  all_or_any_predicate -> scalar_exp COMPARISON any_all_some subquery
  
  between_predicate -> scalar_exp     BETWEEN scalar_exp AND scalar_exp
  
  in_predicate -> scalar_exp not_in '(' subquery ')'
  in_predicate -> scalar_exp     IN '(' subquery ')'
  in_predicate -> scalar_exp not_in '(' scalar_exp_commalist ')'
  in_predicate -> scalar_exp     IN '(' scalar_exp_commalist ')'
  
  join_ref -> '(' query_exp ')' AS NAME
  
  like_predicate -> scalar_exp not_like scalar_exp opt_escape
  like_predicate -> scalar_exp     LIKE scalar_exp opt_escape
  
  ordering_spec -> scalar_exp opt_asc_desc     [see: ordering_spec_commalist - order_by_clause]
  
  query_exp -> query_exp UNION     query_term
  query_exp -> query_exp UNION ALL query_term
  query_exp -> query_exp INTERSECT query_term
  query_exp -> query_exp MINUS     query_term
  
  scalar_opt_as_exp -> scalar_exp AS NAME
  
  scalar_sub_exp -> scalar_sub_exp '+'    scalar_sub_exp
  scalar_sub_exp -> scalar_sub_exp '-'    scalar_sub_exp
  scalar_sub_exp -> scalar_sub_exp '*'    scalar_sub_exp
  scalar_sub_exp -> scalar_sub_exp '/'    scalar_sub_exp
  scalar_sub_exp -> scalar_sub_exp 'div'  scalar_sub_exp
  scalar_sub_exp -> unary_add_or_subtract scalar_sub_exp
  
  table_ref -> '(' query_exp ')' NAME
  
  test_for_null -> scalar_exp is_not_null
  test_for_null -> scalar_exp is_null

• surrounding subqueries representing from_column terms:

  from_clause -> FROM from_column_commalist

• surrounding subqueries representing function arguments and each statement (ALTER USER, CREATE TABLE, CREATE USER, DELETE, GRANT, INSERT, REVOKE, TRUNCATE TABLE, UPDATE) inside the function arguments

  function_ref -> NAME '.' NAME '.' NAME '(' fun_args ')'
  function_ref -> NAME '.' NAME '(' fun_args ')'
  function_ref -> NAME '(' fun_args ')'
  function_ref -> FUNS '(' fun_args ')'
  function_ref -> FUNS '(' DISTINCT column_ref ')'
  function_ref -> FUNS '(' ALL      scalar_exp ')'

• surrounding the whole set operator:

  query_exp -> query_exp UNION     query_term
  query_exp -> query_exp UNION ALL query_term
  query_exp -> query_exp INTERSECT query_term
  query_exp -> query_exp MINUS     query_term

• surrounding subqueries inside the selection term:

  query_spec -> SELECT opt_hint opt_all_distinct selection opt_into table_exp

• surrounding binary operations to secure the precedence rules:

  scalar_sub_exp -> scalar_sub_exp '+' scalar_sub_exp
  scalar_sub_exp -> scalar_sub_exp '-' scalar_sub_exp
  scalar_sub_exp -> scalar_sub_exp '*' scalar_sub_exp
  scalar_sub_exp -> scalar_sub_exp '/' scalar_sub_exp
  
  search_condition -> search_condition OR  search_condition
  search_condition -> search_condition AND search_condition

Parentheses removed:

• The following parentheses are not necessary and therefore removed:

  case_when_exp -> '(' case_when_exp ')'
  
  from_column -> '(' join_clause ')'
  
  fun_arg -> '(' fun_arg ')'
  
  query_partition_clause -> PARTITION BY '(' scalar_exp_commalist ')'
  
  query_term -> '(' query_exp ')'
  
  scalar_sub_exp -> '(' scalar_sub_exp ')'

Predicate negation

• IS NOT NULL

  scalar_exp IS NOT NULL

  becomes:

  NOT ( scalar_exp IS NULL )

• NOT BETWEEN

  scalar_exp NOT BETWEEN scalar_exp AND scalar_exp

  becomes:

  NOT ( scalar_exp BETWEEN scalar_exp AND scalar_exp )

• NOT IN

  scalar_exp NOT IN '(' subquery ')'
  scalar_exp NOT IN '(' scalar_exp_commalist ')'

  becomes:

  NOT ( scalar_exp IN '(' subquery ')' )
  NOT ( scalar_exp IN '(' scalar_exp_commalist ')' )

• NOT LIKE

  scalar_exp NOT LIKE scalar_exp opt_escape

  becomes:

  NOT ( scalar_exp LIKE scalar_exp opt_escape )