HSQLDB - 100% Java Database

New Features in HyperSQL 2.3

This document lists the enhancements and new features since version 1.8.0.
An extensive range of new SQL and JDBC capabilities, increased scalability, and
better query optimisation have been achieved by a rewrite of most of the
internal components and the addition of some major new ones.

All areas of functionality have been thoroughly tested with an extensive
internal test suite and by users with their applications. HyperSQL 2.0 went
into alpha release in April 2009 and 9 alpha, beta and RC releases were made
before the final release in June 2010. All code was reviewed twice during the
RC phase and many bugs were found and fixed. Each RC version was downloaded up
to 20000 times and over 200 users submitted bug reports which were promptly
fixed. During the first half of 2010, several users successfully deployed the
RC versions in heavily loaded production environments.

An extensive Guide was written over two years to cover all features of the
engine in detail.

Since the first release, many new features and enhancements have been
introduced. Regular code reviews, user feedback, and bug fixes resulted in the
latest 2.3.2 release.

NEW CORE

Fully multithreaded core supports 2PL (two-phased locking) and MVCC
(multiversion concurrency control), plus a hybrid 2PL+MVCC transaction control
mode. Transactions can be SERIALIZABLE or READ COMMITTED, using strict 2PL
concurrency control. Version 2.0 also adds the MVCC modes, SNAPSHOT ISOLATION
and READ CONSISTENCY, which are comparable to REPEATABLE READS and READ
COMMITTED isolation levels, but with higher concurrency. Many enhancements are
introduced to allow maximum multi-threaded concurrency in different isolation
modes.

SCALABILITY

Massive high performance LOB store for BLOBs and CLOBs up to multi-gigabyte
size, with total storage capacity of 64 terabytes.

Increased default storage space of 64GB for ordinary data, with fast startup
and shutdown. Storage space can be extended to 8TB.

Large result sets, views and subqueries can now be stored on disk (on the
server side) while being generated and accessed. The threshold to store a
result on disk, as well as the actual fetch size in client-server
configurations can be specified per connection.

Internal and external commands for backing up databases to TAR and GZIP
archives.

QUERY OPTIMISATION

All query conditions, whether in a JOIN or WHERE clause, are now allocated
to an index if possible. IN queries are now optimised to use an index if
possible. Conditions with OR are optimised if indexes can be used. MAX(), MIN()
and ORDER BY with or without LIMIT and OFFSET expressions can use indexes. All
indexes can be used in reverse order for these operations. Indexes on multiple
columns are used efficiently. All subquery and view access is optimised.

SQL STORED PROCEDURES AND FUNCTIONS

HyperSQL supports schema-based stored procedures and functions written
entirely in SQL. Procedural SQL language includes WHILE loops, IF, CASE WHEN,
and exception handling statements. Recursive functions are supported. SQL
procedures can return multiple result sets and return values. SQL functions can
return single values, arrays, or tables that can be used in SQL queries. User
defined aggregate functions written in SQL are supported.

JAVA STORED PROCEDURES AND FUNCTIONS

HyperSQL supports schema-based stored procedures and functions written
entirely in JAVA. Polymorphism is supported. User defined aggregate functions
are supported. Java procedures can return multiple result sets and return
values. Java functions can can return single values, arrays or even tables that
can be used in SQL queries.

NEW DATA TYPES

Support for BIT, BIT VARYING, CLOB, BLOB, INTERVAL according to the SQL
Standards. TIME can now have a fractional second part. TIME WITH TIME ZONE and
TIMESTAMP WITH TIME ZONE are supported. The full range of combinations of
datetime and interval types is supported. Support for DOMAIN objects with
constraints and DISTINCT types.

ARRAY TYPES

Arrays of most types can be used in table definitions, expressions, function
parameters and return types.

COLLATIONS

Multiple collations can be used for different tables or columns. A collation
can be specified for ORDER BY.

NEW DATA MANIPULATION LANGUAGE FEATURES

Both INSERT and UPDATE command have been enhanced to support multi-row
inserts, omission of parentheses, DEFAULT keyword, mix of subquery and row
expressions. The powerful MERGE command is fully supported.

Supports full set of SEQUENCE generator options ([NO] MAXVALUE, [NO]
MINVALUE, [NO] CYCLE, etc.) and data types including SMALLINT, INT, BIGINT,
DECIMAL, NUMERIC. These are supported in IDENTITY sequences and in normal
sequences, including all relevant ALTER COLUMN and ALTER SEQUENCE commands.

Supports GENERATED {BY DEFAULT | ALWAYS} AS IDENTITY in a different column
than the PRIMARY KEY column.

A user supplied value or a value returned from a SELECT statement can always
be inserted into an identity column. If GENERATED ALWAYS has been specified,
then OVERRIDING SYSTEM VALUE must be included in the INSERT statement.

Supports GENERATED ALWAYS AS <expression> for derivative columns that
are based on other column values.

Extended CREATE SCHEMA …. statements can include cross references between
FOREIGN KEY constraints in different tables.

SQL FUNCTIONS

Full set of SQL Standard functions, including correct type handling and
application to all supported types (e.g. BINARY, BLOB, CLOB), is now
supported.

SUBSTRING for character (CHAR, VARCHAR, CLOB) and binary (BINARY, VARBINARY,
BLOB) types.
UPPER, LOWER for all character types
TRIM for all character types
OVERLAY for all character types
POSITION
CHAR_LENGTH, CHARACTER_LENGTH, OCTET_LENGTH
EXTRACT
ABS for all number types

OTHER SQL FEATURES

Supports column level SELECT, INSERT and UPDATE access rights, with GRANT
and REVOKE on individual columns of tables, including WITH GRANT OPTION. GRANT
SELECT(A, D) ON X TO U

Supports SQL STATE with messages defined by the standard

COMPATIBILITY FEATURES

Supports several syntax and operation compatibility flags to ease testing
and porting applications written against a different database engine. These
include PostgreSQL, MySQL, Oracle, MS SQL Server and DB2

JDBC FEATURES

Supports getGeneratedKeys() calls in Statement and PreparedStatement.

Supports CallableStatement with multiple result sets and IN and OUT
parameters

Supports POSIX functions TIMESTAMPDIFF and TIMESTAMPADD.

Supports Java 1.6 and JDBC4. All applicable new methods are supported.