Chapter 13. Compatibility With Other DBMS

Use SET DATABASE SQL SYNTAX PGS TRUE or the equivalent URL property sql.syntax_pgs=true to enable the PostgreSQL's non-standard features. References to SERIAL, BIGSERIAL, TEXT and UUID data types, as well as sequence functions, are translated into HyperSQL equivalents.

The case of unquoted identifiers is non-standard in PostgreSQL, which stores these identifiers in lowercase instead of uppercase. Use SET DATABASE SQL LOWER CASE IDENTIFIER or the URL property sql.lowercase_ident=true to change the case of unquoted identifiers (table names and column names) to lowercase in ResultSetMetaData.

Use SET DATABASE TRANSACTION CONTROL MVCC only if your application is multi-user.

Many PostgreSQL functions are supported, including STRING_AGG.

For identity columns, PostgreSQL uses a non-standard linkage with an external identity sequence. In most cases, this can be converted to GENERATED BY DEFAULT AS IDENTITY. In those cases where the identity sequence needs to be shared by multiple tables, you can use a new HyperSQL feature, GENERATED BY DEFAULT AS SEQUENCE <sequence name>, which is the equivalent of the PostgreSQL implementation.

In CREATE TABLE statements, the SERIAL and BIGSERIAL types are translated into INTEGER or BIGINT, with GENERATED BY DEFAULT AS IDENTITY. Usage of DEFAULT NEXTVAL(<sequence name>) is supported so long as the <sequence name> refers to an existing sequence. This usage is translated into GENERATED BY DEFAULT AS SEQUENCE <sequence name>.

In SELECT and other statements, the NEXTVAL(<sequence name>) and LASTVAL() functions are supported and translated into HyperSQL's NEXT VALUE FOR <sequence name> and IDENTITY() expressions.

PostgreSQL uses a non-standard expression, SELECT 'A Test String' to return a single row table. The standard form is VALUES('A Test String'). In PGS syntax mode, this type of SELECT is supported.

HyperSQL supports SQL Standard ARRAY types. PostgreSQL also supports this, but not entirely according to the Standard.

SQL routines are portable, but some syntax elements are different and require changes.

You may need to use SET DATABASE SQL TDC { DELETE | UPDATE } FALSE statements, as PostgreSQL does not enforce the subtle rules of the Standard for foreign key cascading deletes and updates. PostgreSQL allows cascading operations to update a field value multiple times with different values, the Standard disallows this.

With HyperSQL, an UPDATE statement can update UNIQUE and PRIMARY KEY columns of a table without causing an exception due to temporary violation of constraints. These constraints are checked at the end of execution, therefore there is no need for an ORDER BY clause in an UPDATE statement.

MySQL foreign key constraints are not enforced by the MyISAM engine. Be aware of the possibility of data being rejected by HyperSQL due to these constraints.

With HyperSQL INSERT or UPDATE statements either succeed or fail due to constraint violation. MySQL has the non-standard IGNORE override to ignore violations and alter the data, which is not accepted by HyperSQL.

Unlike MySQL, HyperSQL allows you to modify a table with an INSERT, UPDATE or DELETE statement which selects from the same table in a subquery.

Use SET DATABASE SQL SYNTAX MYS TRUE or the equivalent URL property sql.syntax_mys=true to enable support for MySQL features.

The case of unquoted identifiers is non-standard in MySQL, which stores these identifiers in the original case but compares them regardless of case. If you use lower-case unquoted identifiers in MySQL, use SET DATABASE SQL LOWER CASE IDENTIFIER or the URL property sql.lowercase_ident=true to change the case of unquoted identifiers (table names and column names) to lowercase in ResultSetMetaData.

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

Avoid storing invalid values, for example invalid dates such as '0000-00-00' or '2001-00-00' which are rejected by HyperSQL.

Avoid the MySQL feature that trims spaces at the end of CHAR values.

In MySQL, a database is the same as a schema. In HyperSQL, several schemas can exist in the same database and accessed transparently. In addition, a HyperSQL server supports multiple separate databases.

In MySQL, older, non-standard, forms of database object name case-sensitivity make is difficult to port applications. The modern form, which encloses case-sensitive names in double quotes, follows the SQL standard and is supported by HyperSQL. Use of the backtick character for case-sensitive names, only allowed by MySQL, is also supported and is translated to double quotes.

Almost all MySQL functions are supported, including GROUP_CONCAT.

For fine control over type conversion, check the settings for SET DATABASE SQL CONVERT TRUNCATE FALSE

Avoid using concatenation of possibly NULL values in your select statements. If you have to, change the setting with the SET DATABASE SQL CONCAT NULLS FALSE

If your application relies on MySQL behaviour for ordering of nulls in SELECT statements with ORDER BY, use both SET DATABASE SQL NULLS FIRST FALSE and SET DATABASE SQL NULLS ORDER FALSE to change the defaults.

In CREATE TABLE, MySQL syntax for KEYS, INDEX, COMMENT and some other features is supported.

MySQL supports most SQL Standard types (except INTERVAL types), as well as non-standard types, which are also supported by HyperSQL. Supported types include SMALLINT, INT, BIGINT, DOUBLE, FLOAT, DECIMAL, NUMERIC, VARCHAR, CHAR, BINARY, VARBINARY, BLOB, DATE, TIMESTAMP (all Standard SQL). Non Standard types such as AUTO_INCREMENT, TINYINT, DATETIME, TEXT, TINYLOB, MEDIUMLOB are also supported. UNSIGNED types are converted to signed. These type definitions are translated into HyperSQL equivalents.

In MYS syntax compatibility mode, HyperSQL translates MySQL's ENUM data type to VARCHAR with a check constraint on the enum values.

In MYS syntax compatibility mode, HyperSQL supports MySQL's non-standard version of INTERVAL symbols such as DAY_HOUR and DAY_SECOND in DATEADD and DATESUB functions. The SQL Standard form is DAY TO HOUR or DAY TO SECOND.

MySQL uses a non-standard expression, SELECT 'A Test String' to return a single row table. The standard form is VALUES('A Test String'). In MYS syntax mode, this type of SELECT is supported.

Indexes defined inside CREATE TABLE statements are accepted and created. The index names must be unique within the schema.

HyperSQL supports ON UPDATE CURRENT_TIMESTAMP for column definitions in CREATE TABLE statements.

HyperSQL supports and translates INSERT IGNORE, REPLACE and ON DUPLICATE KEY UPDATE variations of INSERT into predictable and error-free operations. These MySQL variations do not throw an exception if any of the inserted rows would violate a PRIMARY KEY or UNIQUE constraint, and take a different action instead.

When INSERT IGNORE is used, if any of the inserted rows would violate a PRIMARY KEY or UNIQUE constraint, that row is not inserted. With multi-row inserts, the rest of the rows are then inserted only if there is no other violation such as long strings or type mismatch, otherwise the appropriate error is returned.

When REPLACE or ON DUPLICATE KEY UPDATE is used, the rows that need replacing or updating are updated with the given values. This works exactly like an UPDATE statement for those rows. Referential constraints and other integrity checks are enforced and update triggers are activated. The row count returned is simply the total number of rows inserted and updated.

With all the above statements, unique indexes are not considered the same as unique constraints for the alternative action and an exception is thrown if there is violation of a unique index. It is generally better to create a unique constraint instead of a unique index.

MySQL user-defined function and procedure syntax is very similar to SQL Standard syntax supported by HSQLDB. A few changes may still be required.

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

HyperSQL supports Java language functions and stored procedures with the SQL Standard syntax, which is similar to the way Derby supports these features.

Use SET DATABASE SQL SYNTAX ORA TRUE or the equivalent URL property sql.syntax_ora=true to enable support for some non-standard syntax of Oracle.

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

Fine control over MVCC deadlock avoidance is provided by the SET DATABASE TRANSACTION ROLLBACK ON CONFLICT FALSE and the corresponding hsqldb.tx_conflict_rollback connection property.

If your application relies on Oracle behaviour for nulls in multi-column UNIQUE constraints, use SET DATABASE SQL UNIQUE NULLS FALSE to change the default.

If your application relies on Oracle behaviour for ordering of nulls in SELECT statements with ORDER BY, without NULLS FIRST or NULLS LAST, use both SET DATABASE SQL NULLS FIRST FALSE and SET DATABASE SQL NULLS ORDER FALSE to change the defaults.

If you use the non-standard concatenation of possibly NULL values in your select statements, you may need to change the setting for SET DATABASE SQL CONCAT NULLS FALSE.

You may want to use SET DATABASE COLLATION SQL_TEXT NO PAD to take into account differences in trailing spaces in string comparisons.

Many Oracle functions are supported, including no-arg functions such as SYSDATE and SYSTIMESTAMP and more complex ones such as TO_DATE and TO_CHAR.

Non-standard data type definitions such as NUMBER, VARCHAR2, NVARCHAR2, BINARY_DOUBLE, BINARY_FLOAT, LONG, RAW are translated into the closest SQL Standard equivalent in ORA mode.

Non-standard column DEFAULT definitions in CREATE TABLE, such as the use of DUAL with a SEQUENCE function are supported and translated in ORA syntax mode.

The DATE type is interpreted as TIMESTAMP(0) in ORA syntax mode.

The DUAL table and the expressions, ROWNUM, CURRVAL, NEXTVAL are supported in ORA syntax mode.

HyperSQL natively supports operations involving datetime and interval values. These features are based on the SQL Standard.

Many subtle automatic type conversions, syntax refinements and other common features are supported.

SQL routines in PL/SQL are generally portable, but some changes are required.

More advanced compatibility is offered by HyperXtremeSQL, which is a product based on HyperSQL. It supports more function compatibility, the PL/HXSQL language with a similar syntax to PL/SQL, extensive support for additional aggregate functions, window analytic functions with OVER(PARTITION ... ORDER ... ROWS | RANGE ...) and WITHIN GROUP (ORDER BY).

Use SET DATABASE SQL SYNTAX DB2 TRUE or the equivalent URL property sql.syntax_db2=true to enable support for some non-standard syntax of DB2.

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

HyperSQL supports almost the entire syntax of DB2 together with many of the functions. Even local temporary tables using the SESSION pseudo schema are supported.

The DB2 binary type definition FOR BIT DATA, as well as empty definition of column default values are supported in DB2 syntax mode.

Many DB2 functions are supported.

The DUAL table and the expressions, ROWNUM, CURRVAL, NEXTVAL are supported in DB2 syntax mode.

SQL routines are highly portable with minimal change.

More advanced compatibility is offered by HyperXtremeSQL, which is a product based on HyperSQL. It has extensive support for additional aggregate functions, window analytic functions with OVER(PARTITION ... ORDER BY ... ROWS | RANGE ...) and WITHIN GROUP (ORDER BY ...).

Use SET DATABASE SQL SYNTAX MSS TRUE or the equivalent URL property sql.syntax_mss=true to enable support for the CONVERT(<type definition>, <expression) function with switched order of arguments

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

If you use the non-standard concatenation of possibly NULL values in your select statements, you may need to change the setting for SET DATABASE SQL CONCAT NULLS FALSE.

HyperSQL supports + for string concatenation.

SQLServer uses a non-standard expression, SELECT 'A Test String' to return a single row table. The standard form is VALUES('A Test String'). In MSS syntax mode, this type of SELECT is supported.

SQLServer's non-standard data types, MONEY, UNIQUEIDENTIFIER, DATETIME2, DATETIMEOFFSET, IMAGE, TEXT, NTEXT, are translated to their SQL Standard equivalents.

HyperSQL 2.7 supports several datetime functions in MSS compatibility mode. These include DATEPART, DATENAME, EOMONTH and compatible DATEADD and DATEDIFF behaviour.

SQL routines need quite a lot of changes.

More advanced compatibility is offered by HyperXtremeSQL, which is a product based on HyperSQL. It has extensive support for additional aggregate functions, window analytic functions with OVER(PARTITION ... ORDER BY ... ROWS | RANGE ...) and WITHIN GROUP (ORDER BY ...).