New Features in HSQLDB 1.9 and 2.0

(10 Dec 2009)

HyperSQL version 2.0 is currently in Release Candidate phase. 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.

NEW CORE

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

SCALABILITY

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

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

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.

SQL STORED PROCEDURES AND FUNCTIONS

HyperSQL supports stored procedures and functions written entirely in SQL.

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. Support for DOMAIN objects with constraints and DISTINCT types.

NEW EXPRESSION TYPES

Complete rewrite of scanner and parser classes. Supports all SQL standard identifier and character string literals (Unicode strings and escapes, etc.).

 

Supports extended CASE WHEN conditions such as CASE X WHEN IN (,,), BETWEEN A AND B, 21, 56, IS NULL THEN ..

UNIQUE(SELECT ..) predicate.

(A,B) OVERLAPS(X,Y) predicated.

Supports Z BETWEEN [SYMMETRIC | ASYMMETRIC] (X, Y) predicate.

Multi-column (A,B,C) IN ((,,), (,,), ) both with literals and queries

Supports (A, B, C) {= | > | < | <> |…}(W,X,Y,Z) predicates.

Supports (A,B,C) IS [NOT] DISTINCT FROM (W,X,Y,Z) predicates

Supports (A,B,C) MATCHES [UNIQUE] [SIMPLE]|[PARTIAL]|[FULL] (SELECT …) predicates.

Supports (A,B,C) {= | < | > | <= | >=} {ANY | ALL} SELECT … predicates.

 

Full Standard syntax and semantics (arithmetic and other operations) of expressions involving INTERVAL types. Supports type casts to INTERVAL types modifiers (e.g. <expression> DAY). Supports WITH TIME ZONE data types, including zone modifiers (i.e. <expression> AT LOCAL | AT TIME ZONE …).

Supports standard SQL grammar, including IS [NOT] {NULL | TRUE | FALSE | UNKNOWN}.

Supports expressions in all LIKE arguments.

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.

INSERT INTO … VALUES (expr,expr, ..), (exr,expr, …), …

INSERT INTO … VALUES expr

INSERT INTO .. DEFAULT VALUES

INSERT INTO … VALUES (expr, DEFAULT, ..)

UPDATE … SET A = DEFAULT, ..

UPDATE … SET (A, B, ..) = (expr, DEFAULT,…), C = expr, (D,E) = (SELECT …)

MERGE command with full Standard compliant syntax is supported

TRUNCATE TABLE is fully supported

NEW DATA QUERY LANGUAGE FEATURES

SELECT

SELECT has been extensively enhanced, supporting all Standard join types.

The scope of column labels in SELECT queries is now treated according to the SQL Standard. Labels are visible in the ORDER BY expression but not in GROUP BY

Supports TABLE X to introduce the equivalent of SELECT * FROM TABLE X in set operations.

Supports VALUES (,,,), (,,,) as table constructor in joins.

Supports column name list after correlation name SELECT .. FROM A AS B (X,Y,Z) JOIN C…

FULL OUTER JOIN

UNION JOIN

NATURAL JOIN

[LEFT | RIGHT | FULL {OUTER}] JOIN … USING (A, B, ..)

SELECT * with the above and also with RIGHT JOIN, now returns the correct column sequence as per SQL Standard

JOIN conditions can now contain any valid boolean expressions.

Support for UNION {ALL|DISTINCT}, INTERSECT {ALL|DISTINCT} and EXCEPT {ALL|DISTINCT}

Support for all the above with CORRESPONDING[(<column list>)}

Support for <joined table>, e.g. SELECT … FROM (table1 OUTER JOIN table2) JOIN table3

Support for NULLS FIRST, NULLS LAST in ORDER BY

Full support for inclusion of set functions (e.g. COUNT, AVG) in subquery conditions contained within a HAVING clause.

Supports FETCH <row count> ROWS ONLY as SQL Standard alternative to LIMIT at the end of query expression

DATA DEFINITION LANGUAGE ENHANCEMENTS

Supports full syntax of SQL TRIGGER definition for row level triggers using SQL procedure statements (as opposed to Java classes)

CREATE TRIGGER <name> {BEFORE | AFTER} 
{INSERT | DELETE | UPDATE [OF (<column name>, ..)] 
ON <table name>
[ REFERENCING OLD [ROW] [AS] <old transition variable name> |
NEW [ROW] [AS] <new transition variable name>]
[FOR EACH ROW]
[WHEN (<search condition>)]
<sql procedure statement>

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

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

Supports CREATE TABLE .. (<column list>) AS (<query expression>) WITH [NO] DATA

CREATE TABLE can have mixed column and constraint creation elements. A column definition can include a PRIMARY KEY, UNIQUE, FOREIGN KEY or CHECK constraint,

Similarly, ALTER TABLE … ADD COLUMN can feature constraint definitions.

ALTER TABLE … ALTER COLUMN … SET DATA TYPE … supported.

Supports CASCADE with ALTER TABLE .. DROP COLUMN and ALTER TABLE … DROP CONSTRAINT.

Named NOT NULL constraints now supported in column definition CONSTRAINT C NOT NULL. All NOT NULL constraints are treated as CHECK (C IS NOT NULL) type constraints and listed as such in system tables.

Supports VIEW definitions including tables and sequences in other schemas.

Supports updatable views, including WITH {LOCAL | CASCADED} CHECK OPTION

SQL FUNCTIONS

A wide 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

JDBC FEATURES

Supports getGeneratedKeys() calls in Statement and PreparedStatement.

Supports POSIX functions TIMESTAMPDIFF and TIMESTAMPADD.

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

SQL STANDARD FEATURE LIST

SQL-92 direct SQL is supported fully to Intermediate Level, while most extra features of Advanced Level are also supported. The only features of Advanced Level not supported are deferrable constraint enforcement, ASSERTION, and CHECK constraints that contain subqueries.

All SQL:2003 - SQL:2008 Standard CORE features are supported.

HSQLDB 1.9.0 also supports the following set of SQL:1999, SQL:2003 and SQL:2008 NON-CORE features. (some features not yet added to this list)

B031 Direct SQL
C071-01 Read-only scrollable cursors (via JDBC ResultSet)
F032 CASCADE drop behaviour
F033 ALTER TABLE statement: DROP COLUMN clause
F034 Extended REVOKE statement
F034-1 REVOKE statement performed by other than the owner of a schema object
F034-2 REVOKE statement: GRANT OPTION FOR clause
F034-3 REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION
F052 Interval and datetime arithmetic
F053 OVERLAPS predicate
F171 Multiple schemas per user
F191 Referential delete actions
F200 TRUNCATE TABLE statement
F202 TRUNCATE TABLE: identity column restart option
F222 INSERT statement: DEFAULT VALUES clause
F231 Privilege tables
F231-01 TABLE_PRIVILEGES view
F231-02 COLUMN PRIVILEGES view
F231-03 USAGE PRIVILEGES view
F251 Domain Support
F262 Extended CASE expression
F263 Comma-separated predicates in simple CASE expression
F271 Compound character literals
F291 UNIQUE predicate
F301 CORRESPONDING in query expressions
F302 INTERSECT table operator
F302-1 INTERSET DISTINCT table operator
F302-2 INTERSECT ALL table operator
F304 EXCEPT ALL
F312 MERGE statement
F321 User authorization
F381 Extended schema manipulation
F381-01 ALTER TABLE statement: ALTER COLUMN clause
F381-02 ALTER TABLE statement: ADD CONSTRAINT clause
F381-03 ALTER TABLE statement: DROP CONSTRAINT clause
F382 Alter column data type
F391 Long identifiers
F392 Unicode escapes in identifiers
F393 Unicode escape in literals
F401 Extended joined table
F401-01 NATURAL JOIN
F401-02 FULL OUTER JOIN
F401-04 CROSS JOIN
F402 Named column joins for LOBs, arrays, and multisets
F411 Time zone specification
F421 National character
F431 Read-only scrollable cursor
F441 Extended set function support
F442 Mixed column references in set functions
F451 Character set definition
F461 Named character sets
F491 Constraint management
F502 Enhanced documentation tables
F502-01 SQL_SIZING_PROFILES view
F502-02 SQL_IMPLEMENTATION_INFO view
F502-01 SQL_PACKAGES view
F511 BIT data type
F531 Temporary tables
F555 Enhanced seconds precision
F561 Full value expressions
F571 Truth value tests
F591 Derived table
F641 Row and table constructors
F651 Catalog name qualifiers
F661 Simple Tables
F672 Retrospective check constraints
F690 Collation support (limited)
F701 Referential update actions
F711 ALTER domain
F731 INSERT column privileges
F751 View CHECK enhancements
F762 CURRENT_CATALOG
F763 CURRENT_SCHEMA
F781 Self-referencing operations
F791 Insensitive cursors
F801 Full set function
F831 Full cursor update
F850 Top-level <order by clause> in <query expression>
F851 <order by clause> in subqueries
F852 Top-level <order by clause> in views
F855 Nested <order by clause> in <query expression>
F856 Nested <fetch first clause> in <query expression>
F857 Top-level <fetch first clause> in <query expression>
F858 <fetch first clause> in subqueries
F859 Top-level <fetch first clause> in views
F860 <fetch first row count> in <fetch first clause>
F861 Top-level <result offset clause> in <query expression>
F862 <result offset clause> in subqueries
F863 Nested <result offset clause> in <query expression>
F864 Top-level <result offset clause> in views
F865 <offset row count> in <result offset clause>

T011 Timestamp in Information Schema
T021 BINARY and VARBINARY data types
T022 Advanced BINARY and VARBINARY data type support
T023 Compound binary literals
T024 Spaces in binary literals
T031 BOOLEAN data type
T041 Basic LOB data type support
T041-01 BLOB data type
T041-02 CLOB data type
T041-03 POSITION, LENGTH etc functions for LOB data types
T021-04 Concatenation of LOB data types
T071 BIGINT data type
T151 DISTINCT predicate
T152 DISTINCT predicate with negation
T172 AS subquery clause in table definition

T174 Identity columns
T176 Sequence generator support
T177 Sequence generator support: simple restart option
T178 Identity columns: simple restart option
T191 Referential action RESTRICT
T201 Comparable data types for referential constraints
T211 Basic trigger capability
T211-01 Triggers activated on UPDATE, INSERT, or DELETE of one base table. 
T211-02 BEFORE triggers
T211-03 AFTER triggers
T211-04 FOR EACH ROW triggers
T211-05 Ability to specify a search condition that shall be True before the trigger is invoked.
T211-06 Support for run-time rules for the interaction of triggers and constraints.
T211-07 TRIGGER privilege 
T211-08 Multiple triggers for the same event are executed in the order in which they were created in the catalog.
T271 Savepoints
T281 SELECT privilege with column granularity
T301 Functional dependencies
T312 OVERLAY function
T331 Basic roles
T332 Extended roles (partial)
T351 Bracketed SQL comments
T441 ABS and MOD functions
T461 Symmetric BETWEEN predicate
T501 Enhanced EXISTS predicate
T551 Optional key words for default syntax
T591 UNIQUE constraints of possibly null columns
T621 Enhanced numeric functions
T641 Multiple column assignment