SQL Expression Analyzer intrinsic limitations

Unsupported features

• Control lineage is not available for certain expressions inside operations.

The SQL99 syntax includes reserved keywords (e.g. CREATE that cannot be used as a table name), and non reserved keywords. Some of these keywords are used for compound terms like "order by", "group by", "union join". All SQL99 keywords are properly parsed and will not fail the expression analyzer used in bridges. However, some keywords have a direct data flow impact on lineage, and some do not. The following list provide the details:

• SQL99 Reserved Key Words
• with direct data flow lineage impact:  ADD, ALL, ALTER, AND, ANY, AS, ASC, BEGIN, BOTH, BY, CALL, CASE, CAST, CHAR, CHARACTER, CONSTRAINT, CREATE, CURRENT, CURSOR, DATE, DAY, DEC, DECIMAL, DEFAULT, DELETE, DESC, DISTINCT, DOUBLE, DROP, ELSE, END, EXCEPT, FALSE, FLOAT, FOR, FOREIGN, FROM, FULL, FUNCTION, GRANT, GROUP, HAVING, HOUR, IN, INNER, INSERT, INT, INTEGER, INTERSECT, INTERVAL, INTO, IS, JOIN, KEY, LEADING, LEFT, MINUTE, MONTH, NATURAL, NO, NOT, NULL, NUMERIC, ON, OR, ORDER, OUT, OUTER, PRECISION, PRIMARY, PROCEDURE, RECURSIVE, REFERENCES, RETURN, REVOKE, RIGHT, ROW, ROWS, SECOND, SELECT, SET, SMALLINT, TABLE, THEN, TIME, TIMESTAMP, TO, TRAILING, TRUE, UNION, UNIQUE, UPDATE, USING, VALUES, VARCHAR, VARYING, VIEW, WHEN, WHERE, WITH, YEAR
• with no lineage impact:  ABSOLUTE, ACTION, ADMIN, AFTER, AGGREGATE, ALIAS, ALLOCATE, ARE, ARRAY, ASSERTION, AT, AUTHORIZATION, BEFORE, BINARY, BIT, BLOB, BOOLEAN, BREADTH, CASCADE, CASCADED, CATALOG, CHECK, CLASS, CLOB, CLOSE, COLLATE, COLLATION, COLUMN, COMMIT, COMPLETION, CONNECT, CONNECTION, CONSTRAINTS, CONSTRUCTOR, CONTINUE, CORRESPONDING, CROSS, CUBE, CURRENT_DATE, CURRENT_PATH, CURRENT_ROLE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, CYCLE, DATA, DEALLOCATE, DECLARE, DEFERRABLE, DEFERRED, DEPTH, DEREF, DESCRIBE, DESCRIPTOR, DESTROY, DESTRUCTOR, DETERMINISTIC, DICTIONARY, DIAGNOSTICS, DISCONNECT, DOMAIN, DYNAMIC, EACH, END-EXEC, EQUALS, ESCAPE, EVERY, EXCEPTION, EXEC, EXECUTE, EXTERNAL, FETCH, FIRST, FOUND, FREE, GENERAL, GET, GLOBAL, GO, GOTO, GROUPING, HOST, IDENTITY, IGNORE, IMMEDIATE, INDICATOR, INITIALIZE, INITIALLY, INOUT, INPUT, ISOLATION, ITERATE, LANGUAGE, LARGE, LAST, LATERAL, LESS, LEVEL, LIKE, LIMIT, LOCAL, LOCALTIME, LOCALTIMESTAMP, LOCATOR, MAP, MATCH, MODIFIES, MODIFY, MODULE, NAMES, NATIONAL, NCHAR, NCLOB, NEW, NEXT, NONE, OBJECT, OF, OFF, OLD, ONLY, OPEN, OPERATION, OPTION, ORDINALITY, OUTPUT, PAD, PARAMETER, PARAMETERS, PARTIAL, PATH, POSTFIX, PREFIX, PREORDER, PREPARE, PRESERVE, PRIOR, PRIVILEGES, PUBLIC, READ, READS, REAL, REF, REFERENCING, RELATIVE, RESTRICT, RESULT, RETURNS, ROLE, ROLLBACK, ROLLUP, ROUTINE, SAVEPOINT, SCHEMA, SCROLL, SCOPE, SEARCH, SECTION, SEQUENCE, SESSION, SESSION_USER, SETS, SIZE, SOME, SPACE, SPECIFIC, SPECIFICTYPE, SQL, SQLEXCEPTION, SQLSTATE, SQLWARNING, START, STATE, STATEMENT, STATIC, STRUCTURE, SYSTEM_USER, TEMPORARY, TERMINATE, THAN, TIMEZONE_HOUR, TIMEZONE_MINUTE, TRANSACTION, TRANSLATION, TREAT, TRIGGER, UNDER, UNKNOWN, UNNEST, USAGE, USER, VALUE, VARIABLE, WHENEVER, WITHOUT, WORK, WRITE, ZONE
• SQL99 Non Reserved Key Words
• with direct data flow lineage impact:  BETWEEN, CONVERT, EXISTS, NUMBER, OVERLAPS
• with no lineage impact:  ABS, ADA, ASENSITIVE, ASSIGNMENT, ASYMMETRIC, ATOMIC, AVG, BIT_LENGTH, BITVAR, C, CALLED, CARDINALITY, CATALOG_NAME, CHAIN, CHAR_LENGTH, CHARACTER_LENGTH, CHARACTER_SET_CATALOG, CHARACTER_SET_NAME, CHARACTER_SET_SCHEMA, CHECKED, CLASS_ORIGIN, COALESCE, COBOL, COLLATION_CATALOG, COLLATION_NAME, COLLATION_SCHEMA, COLUMN_NAME, COMMAND_FUNCTION, COMMAND_FUNCTION_CODE, COMMITTED, CONDITION_NUMBER, CONNECTION_NAME, CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, CONTAINS, COUNT, CURSOR_NAME, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, DEFINED, DEFINER, DISPATCH, DYNAMIC_FUNCTION, DYNAMIC_FUNCTION_CODE, EXISTING, EXTRACT, FINAL, FORTRAN, G, GENERATED, GRANTED, HIERARCHY, HOLD, IMPLEMENTATION, INFIX, INSENSITIVE, INSTANCE, INSTANTIABLE, INVOKER, K, KEY_MEMBER, KEY_TYPE, LENGTH, LOWER, M, MAX, MIN, MESSAGE_LENGTH, MESSAGE_OCTET_LENGTH, MESSAGE_TEXT, METHOD, MOD, MORE, MUMPS, NAME, NULLABLE, NULLIF, OCTET_LENGTH, OPTIONS, OVERLAY, OVERRIDING, PASCAL, PARAMETER_MODE, PARAMETER_NAME, PARAMETER_ORDINAL_POSITION, PARAMETER_SPECIFIC_CATALOG, PARAMETER_SPECIFIC_NAME, PARAMETER_SPECIFIC_SCHEMA, PLI, POSITION, REPEATABLE, RETURNED_LENGTH, RETURNED_OCTET_LENGTH, RETURNED_SQLSTATE, ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_SCHEMA, ROW_COUNT, SCALE, SCHEMA_NAME, SECURITY, SELF, SENSITIVE, SERIALIZABLE, SERVER_NAME, SIMPLE, SOURCE, SPECIFIC_NAME, SIMILAR, SUBLIST, SUBSTRING, SUM, STYLE, SUBCLASS_ORIGIN, SYMMETRIC, SYSTEM, TABLE_NAME, TRANSACTIONS_COMMITTED, TRANSACTIONS_ROLLED_BACK, TRANSACTION_ACTIVE, TRANSFORM, TRANSFORMS, TRANSLATE, TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME, TRIM, TYPE, UNCOMMITTED, UNNAMED, UPPER, USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_NAME, USER_DEFINED_TYPE_SCHEMA

In addition to the list of SQL99 keywords, the expression analyzer has been enhanced to support common database specific constructs. The limitations are in the list below:

• IBM DB2 extension limitations:
• The CONCAT keyword in DB2 can be used as a function (supported) or as an operator (not supported)
  ‎ SELECT CONCAT(first_name, last_name) FROM customer;
  ‎ SELECT first_name CONCAT last_name FROM customer;
• Oracle extension limitations:
• Support for Oracle Text Literals is limited to the two single quotation marks mechanism, like:
  ‎     'Jackie''s raincoat'
  ‎ but not the Q/q alternative quoting mechanism, like:
  ‎     Q<'Jackie's raincoat>'

The expression analyzer doesn't support user defined complex data types.