DevNotes #1 - Quoted Identifiers

A few days ago, I discovered that ZPA was not handling quoted identifiers properly. Specifically, the component responsible for creating the symbol table was not correctly identifying and counting the usage of quoted identifiers. This led to inaccurate results, which could affect the quality of the code analysis.

In Oracle SQL, "quoted user-defined identifiers" are identifiers enclosed in double quotes. Quoted identifiers are case-sensitive, which means that "My Table" and "my table" are considered different identifiers. This is different from unquoted identifiers, which are case-insensitive (where uppercase and lowercase letters do not matter).

However, there's a catch: if a quoted identifier, without its double quotes, is a valid ordinary user-defined identifier (it begins with a letter and contains only letters, digits, $, #, and _) and is in uppercase, then it is treated as an unquoted identifier. The definition seems long, but in practice, this means that MY_TABLE, my_table, and "MY_TABLE" are considered the same identifier.

Therefore, let's consider the following example:

DECLARE
  "HELLO" VARCHAR2(15) := 'UPPERCASE';
  "Hello" VARCHAR2(15) := 'Initial Capital';
  "hello" VARCHAR2(15) := 'lowercase';
BEGIN
  DBMS_OUTPUT.PUT_LINE("HELLO");
  DBMS_OUTPUT.PUT_LINE("Hello");
  DBMS_OUTPUT.PUT_LINE("hello");
  DBMS_OUTPUT.PUT_LINE(hello);
END;

In this example, we have three variables declared with quoted identifiers: "HELLO", "Hello", and "hello". All three are considered different identifiers because they have different casing.

The last line with DBMS_OUTPUT.PUT_LINE(hello); is interesting because the identifier is unquoted. This hello refers to the variable declared with the quoted identifier "HELLO". Since the quoted identifier "HELLO" fits the situation described earlier, it's treated as if it were unquoted and is case-insensitive.

While working on ZPA, I noticed that the symbol table was not handling this type of identifier correctly, causing false positives in the rules UnusedVariable, UnusedCursor, UnusedParameter, and VariableHiding.

Testing this code snippet with ZPA Toolkit 3.5.1, we can see that the three variables declared with quoted identifiers are incorrectly reported as usages of "HELLO" and the last unquoted reference is not even considered.

After the fixes, ZPA correctly handles all situations: