Connected token "NOT EXISTS" leads to syntax error

Detail name	Value
Changelog Number	12313
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 7.1.2
Resolution Date	2021-10-20

Background

Connected tokens like "NOT EXISTS" lead to a syntax error, if part of the token is used as the start of an identifier.

Example

If an identifier EXISTS_XXX is used without connected token NOT, the statement works as expected and returns TRUE.

SELECT * FROM (SELECT TRUE AS EXISTS_XXX FROM DUAL)  where  EXISTS_XXX;

If the identifier EXISTS_XXX is used in combination with the connected token NOT, the statement raises a syntax error.

SELECT * FROM (SELECT TRUE AS EXISTS_XXX FROM DUAL)  where NOT EXISTS_XXX;

Error: [42000] syntax error, unexpected $undefined, expecting SELECT_ or TABLE_ or VALUES_ or '('

 
These connected tokens with "NOT ... " are affected:

NOT INSTANTIABLE
NOT FINAL
NOT FOUND
NOT DEFFERABLE
NOT EXISTS
NOT CASESPECIFIC
NOT CS

Generally, this problem is not limited only to "NOT ...", but to all connected tokens, if used in a valid context in the statement.

Workaround

1. Rename the identifier so that it does not start with a keyword.
2. Use double quotes for the identifier to split connected token explicitly: instead of NOT EXISTS_XXX use NOT "EXISTS_XXX".

Fix

The above-mentioned combination of connected tokens and identifiers can be used without raising an error.