From the Archives: Keywords and Upgrades
In this post, originally written by Glenn Paulley and posted to sybase.com in April of 2010, Glenn talks about how keywords are recognized by the SQL Anywhere parser.
Each new SQL Anywhere release brings additional SQL functionality, some resulting from enhancements to the SQL standard, and some resulting from our own innovations to the product. In the forthcoming Version 12, for instance, SQL Anywhere will support the “distinct predicate” from the SQL/2008 standard that has the syntax
X IS [ NOT ] DISTINCT FROM Y which permits one to compare two expression values and treat NULLs as comparing equal. The distinct predicate can be useful in situations, particularly involving stored procedure parameters, where one doesn’t want to have to specify a different SQL construction when an expression can be NULL. (Aside: while the distinct predicate may have some utility, at the same time, this deviation from SQL’s “normal” 3-valued logic arguably increases the already confusing issues surrounding NULLs that have been debated at length elsewhere.)
In the case of the distinct predicate, its syntax permits straightforward adoption and implementation because its syntax – arguably somewhat clumsy – at least doesn’t contain any new reserved words. Unfortunately, in SQL it is more often the rule that additional functionality requires the use of additional keywords, and occasionally these keywords must become reserved words in order to avoid ambiguities when parsing the statement. With SQL Anywhere server’s custom-built implementation of YACC, we have long-offered the “
non_keywords” connection option, which permits a user or application to turn off a specific keyword so that it can be used as an identifier. For example, one would be able to specify:
SET OPTION non_keywords = 'TRUNCATE, SYNCHRONIZE';
In the Version 12 release of SQL Anywhere, we’ve taken this flexibility one step further, and we now support an additional connection option, “
reserved_keywords“. The motivation behind this new option is to make server upgrades easier out-of-the-box by automatically excluding keywords from the SQL grammar when the likelihood of conflicts with customer applications is high. As a concrete example, SQL Anywhere 12 supports the
OFFSET clauses familiar to those who develop MySQL applications.
OFFSET are nearly identical in functionality to
SELECT TOP ... START AT. However, introducing
LIMIT to the SQL grammar would require it to be a reserved keyword, potentially breaking existing applications that use “limit” as an identifier. Consequently, what we’ve done is introduce support for
LIMIT but to have it disabled by default. To enable the use of the
LIMITclause, one can enable its status as a keyword via:
SET OPTION PUBLIC.reserved_keywords = 'LIMIT';
In the server, whether a word is identified as a keyword is determined by the following (in order of precedence):
- It appears in the SQL Anywhere list of reserved words.
- It has been turned on with the
- It has been turned off using the
reserved_keywords option offers additional flexibility in how we offer support for SQL language extensions, and our intent is to utilize this option as we offer additional SQL functionality in forthcoming releases of SQL Anywhere.