PL/SQL improper use of TIMEZONE token

[danlyons-home]

Background:

During the course of going through some queries, I ran into some parsing issues calling out an unexpected TIMEZONE string from statements inside the body of a CTE similar to the following:

   SELECT 0 AS ID, 'AMERICA/DENVER' AS TIMEZONE FROM DUAL UNION ALL
   SELECT 1 AS ID, 'ETC/GMT+12' AS TIMEZONE FROM DUAL UNION ALL
   SELECT 2 AS ID, 'ETC/GMT+11' AS TIMEZONE FROM DUAL UNION ALL

I'll spare you the nightmares arising from knowing why such a CTE need exist in this particular query.

Problem:

While troubleshooting the issue, I searched the lexer/parser files for references to TIMEZONE and found two matches: https://github.com/antlr/grammars-v4/blob/d939aad7310b6a214d6ade30afc90eb28f9fe809/sql/plsql/PlSqlParser.g4#L4424-L4426 https://github.com/antlr/grammars-v4/blob/d939aad7310b6a214d6ade30afc90eb28f9fe809/sql/plsql/PlSqlParser.g4#L6334

However, neither of these use TIMEZONE as a single, compound word.

The set_time_zone_clause uses TIME_ZONE: Oracle set_time_zone_clause documentation As such, it should probably be the following:

 set_time_zone_clause 
---     : SET TIMEZONE EQUALS_OP CHAR_STRING 
+++     : SET TIME_ZONE EQUALS_OP CHAR_STRING
     ; 

The TIMESTAMP type declaration uses TIME ZONE: Oracle TIMESTAMP WITH TIME ZONE Data Type documentation Similarly, this should probably be the following:

---    | TIMESTAMP (WITH TIMEZONE)?
+++    | TIMESTAMP (WITH TIME ZONE)?

[danlyons-home]

Those two appear to be the only references for the TIMEZONE token in the lexer definitions: https://github.com/antlr/grammars-v4/blob/d939aad7310b6a214d6ade30afc90eb28f9fe809/sql/plsql/PlSqlLexer.g4#L2043

Removing it may or may not be worthwhile.