Breaking Changes in BETA5 (en)

[akirakw]

Breaking Changes in BETA5

As of version 1.0.0-BETA5 (hereafter referred to as BETA5), we have replaced the SQL compiler with one that is more compatible with the SQL standard.
As a result, there are some features that are not compatible with versions 1.0.0-BETA4 (BETA4) and earlier.

Please refer to Available SQL features in Tsurugi for the syntax of SQL available in BETA5.

Exact/Approximate Numeric Literals

In BETA4 and earlier versions, numeric literals with a decimal part, such as 1.0, were treated as DOUBLE PRECISION type.
From BETA5 onwards, they are treated as DECIMAL type.

If you wish to treat them as DOUBLE PRECISION, please use exponential notation, such as 1.0e0.

Until BETA4:

• Integers like 1 were treated as BIGINT
• Real numbers like 1.0 were treated as DOUBLE PRECISION.
  • Exponential notation (e.g., 314e-2) did not allowed.

From BETA5 onwards:

• Exact numeric literals
  • All numeric literals without an exponential part, such as 1 and 1.0, are treated as exact numeric literals.
  • Exact numeric literals are primary treated as the DECIMAL type, with their scale value corresponding to the number of decimal places.
  • However, integers within the range of BIGINT are treated as BIGINT.
• Approximate numeric literals
  • Numerical literals with a trailing exponential part (e.g., 3.14e0, 314e-2) are treated as approximate numeric literals.
  • Approximate numeric literals are always treated as DOUBLE PRECISION type.

For more details, please refer the "Exact numeric literals" and "Approximate numeric literals" sections of Available SQL features in Tsurugi.

Escape Sequences in String Literals

Escape sequences within string literals (e.g., \n), which were available in BETA4 and earlier versions, are no longer supported from BETA5 onwards.

Until BETA4:

• Escape sequences started with \, treating the following character as a special character.
• Handling of special characters:
  • n, r, t -> Treated as LF, CR, HT respectively.
  • Others -> Treated the character itself (continuing the literal even if ' follows).

From BETA5 onwards:

• Escape sequences do not exist.
• In string literals, writing the quote character (') twice is treated as a single quote character (e.g., 'I''m').
• All characters except for the single quote (') can appear in the literal.

The above specification in BETA5 is still under review, and there is a possibility that escape sequences will be available in the future.
In that case, we plan to introduce a new unconventional syntax for literals, such as c'I\'m' (indicating C-style character string literals), which can be used alongside the conventional ones.

The above is detailed in the "Character string literals" section of Available SQL features in Tsurugi.

Addition of Reserved Words

From BETA5 onwards, we have added reserved words that cannot be used as identifier (table or column name).
Due to our adherence to the SQL standard, the variety of reserved words has increased compared to BETA4 or earlier.

Below is a list of reserved words newly added in BETA5:

• A
  • ABS, ABSOLUTE, ACTION, ADD, ADMIN, AFTER, ALIAS, ALTER, ALWAYS, ANY, ARE, ARRAY, ASSERTION, ASYMMETRIC, AT, AUTHORIZATION, AVG
• B
  • BEFORE, BEGIN, BETWEEN, BIT, BIT_AND, BIT_LENGTH, BIT_OR, BITVAR, BLOB, BOOL_AND, BOOL_OR, BOOLEAN, BOTH
• C
  • CALL, CARDINALITY, CASCADE, CASCADED, CASE, CHAR_LENGTH, CHARACTER, CHARACTER_LENGTH, CHECK, CLASS, CLOB, CLOSE, COALESCE, COLLATE, COLUMN, COMMIT, CONNECT, CONSTRAINT, CONSTRAINTS, CONVERT, CORRESPONDING, COUNT, CUBE, CURRENT, CURRENT_DATE, CURRENT_PATH, CURRENT_ROLE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CYCLE
• D
  • DAY, DEALLOCATE, DEC, DECLARE, DEREF, DESCRIBE, DETERMINISTIC, DISCONNECT, DYNAMIC
• E
  • EACH, ELSE, END, END-EXEC, ESCAPE, EVERY, EXCEPT, EXEC, EXECUTE, EXTERNAL, EXTRACT
• F
  • FETCH, FOR, FOREIGN, FUNCTION
• G
  • GENERATED, GET, GLOBAL, GRANT, GROUPING
• H
  • HOUR
• I
  • IDENTITY, IN, INCLUDE, INCREMENT, INDICATOR, INOUT, INTEGER, INTERSECT, INTERVAL
• L
  • LANGUAGE, LARGE, LATERAL, LEADING, LENGTH, LIMIT, LOCAL, LOCALTIME, LOCALTIMESTAMP, LOWER
• M
  • MATCH, MAX, MAXVALUE, MIN, MINUTE, MINVALUE, MOD, MODIFIES, MODULE, MONTH
• N
  • NATIONAL, NCHAR, NCLOB, NEW, NEXT, NO, NONE, NULLIF, NULLS, NUMERIC
• O
  • OCTET_LENGTH, OF, OLD, ONLY, OPEN, OUT, OVERLAPS, OVERLAY, OWNED
• P
  • PARAMETER, PLACING, POSITION, PREPARE, PROCEDURE
• R
  • RECURSIVE, REF, REFERENCES, REFERENCING, RESULT, RETURN, RETURNS, REVOKE, ROLE, ROLLBACK, ROLLUP, ROW, ROWS
• S
  • SAVEPOINT, SCOPE, SEARCH, SECOND, SESSION_USER, SIMILAR, SMALLINT, SOME, SPECIFIC, SQL, SQLEXCEPTION, SQLSTATE, SQLWARNING, START, STATIC, SUBLIST, SUBSTRING, SUM, SYMMETRIC, SYSTEM_USER
• T
  • TEMPORARY, THEN, TIMEZONE_HOUR, TIMEZONE_MINUTE, TINYINT, TO, TRAILING, TRANSLATE, TRANSLATION, TREAT, TRIGGER, TRIM, UNKNOWN
• U
  • UNNEST, UPPER, USER, USING
• V
  • VARBIT, VARYING, VIEW
• W
  • WHEN, WHENEVER, WITHOUT
• Y
  • YEAR

The following is a list of reserved words removed since BETA5:

• ASC
• DESC
• KEY
• STRING

A complete list of reserved words in BETA5 is available in the "Reserved words in Tsurugi" section of Available SQL features in Tsurugi.

Note that some tables or columns defined in BETA4 or earlier may no longer be available due to the additional reserved words.
In such cases, the delimited identifier (e.g., "TABLE") introduced in BETA5, allows continued use of reserved words as table or column names.

The above is detailed in the "Delimited identifiers" section of Available SQL features in Tsurugi.

Notation of Host Parameters

The host parameter feature allows the declaration of parameters in SQL (e.g., INSERT INTO t VALUES(:key, :value)), which can be substituted with the values at execution time.
Client programs such as Tsubakuro and Iceaxe can specify host parameter values.

In BETA4 or earlier, host parameters could be referenced without a leading colon (:).
From BETA5 onwards, this behavior is revoked, and host parameters must always be prefixed with a colon.
This is to prevent confusion with other symbols, such as columns, and to avoid accidentally referring to the host parameters.