History and Roadmap

History of this Database Engine
Change Log
Roadmap
Supporters

History of this Database Engine

The development of H2 was started in May 2004, but it was first published on December 14th 2005. The author of H2, Thomas Mueller, is also the original developer of Hypersonic SQL. In 2001, he joined PointBase Inc. where he created PointBase Micro. At that point, he had to discontinue Hypersonic SQL, but then the HSQLDB Group was formed to continued to work on the Hypersonic SQL codebase. The name H2 stands for Hypersonic 2; however H2 does not share any code with Hypersonic SQL or HSQLDB. H2 is built from scratch.

Change Log

Version 1.0 (Current)

Version 1.0 / 2007-TODO

  • After calling SHUTDOWN and closing the connection and a superfluous error message appeared in the trace file. Fixed.
  • In many situations, views did not use an index if they could have. Fixed. Also the explain plan for views works now.
  • The table id (important for LOB files) is now included in INFORMATION_SCHEMA.TABLES.
  • When using DISTINCT, ORDER BY a function works now as long as it is in the column list.
  • Support for the data type CHAR. The difference to VARCHAR is: trailing spaces are ignored. This data type is supported for compatibility with other databases and older applications.
  • The aggregate function COUNT(...) now returns a long instead of an int.
  • The 'ordering' of data types was not always correct, for example an operation involving REAL and DOUBLE produced a result of type REAL. Fixed.
  • CSV tool: If the same instance was used for reading and writing, the tool wrote the column names twice. Fixed.
  • Compatibility: Support for the data type notation CHARACTER VARYING
  • Can now ORDER BY -1 (meaning order by first column, descending), and ORDER BY ? (parameterized column number).
  • Databases with invalid linked tables (for example, because the target database is not accessible) can now be opened. Old table links don't work however.
  • There was a small memory leak in the trace module. One object per opened connection was kept in a hash map.
  • Linked tables can now emit UPDATE statements if 'EMIT UPDATES' is specified in the CREATE LINKED TABLE statement. So far, updating a row always deleted the old row and then inserted the new row.
  • In the last release, the H2 Console opened two connection when logging into a database, and only closed one connection when logging out. Fixed.
  • New functions LEAST and GREATEST to get the smallest or largest value from a list.

Version 1.0 / 2007-04-29 (Build 46)

  • Unnamed private in-memory database (jdbc:h2:mem:) were not 'private' as documented. Fixed.
  • Autocomplete in the Console application: now the result frame scrolls to the top when the list is updated.
  • GROUP BY expressions did not work correctly in subqueries. Fixed.
  • New function TABLE to define ad-hoc (temporary) tables in a query. This also solves problems with variable-size IN(...) queries: instead of SELECT * FROM TEST WHERE ID IN(?, ?, ...) you can now write: SELECT * FROM TABLE(ID INT=?) X, TEST WHERE X.ID=TEST.ID In this case, the index is used.
  • New data type ARRAY. Actually it was there before, but is now documented and better tested (however it must still be considered experimental). The java.sql.Array implementation is incomplete, but setObject(1, new Object[]{...}) and getObject(..) can be used. New functions ARRAY_GET and ARRAY_LENGTH.
  • SimpleResultSet now has some basic data type conversion features.
  • When using JDK 1.5 or later, and switching on h2.lobFilesInDirectories, the performance for creating LOBs was bad. This has been fixed, however creating lots of LOBs it is still faster when the setting is switched off.
  • A problem with multiple unnamed dynamic tables (FROM (SELECT...)) has been fixed.
  • Appending 'Z' to a timestamp did not have an effect. Now it is interpreted as +00:00 (GMT).
  • The BACKUP command is better tested and documented. This means hot backup (online backup) is now possible.
  • The old 'Backup' tool is now called 'Script' (as the SQL statement).
  • There are new 'Backup' and 'Restore' tools that work with database files directly.
  • The complete syntax for referential and check constraints is now supported when written as part of the column definition, behind PRIMARY KEY.
  • CASE WHEN ... returned the wrong result when the condition evaluated to NULL.
  • The new function LINK_SCHEMA simplifies linking all tables of a schema.
  • SCRIPT DROP now also drops aliases (Java functions) if they exist.
  • For encrypted databases, the trace option can no longer be enabled manually by creating a file.
  • For linked tables, NULL in the unique key is now supported.
  • For read-only databases, temp files are now created in the default temp directory instead of the database directory.
  • Sending CLOB data was slow in some systems when using the server version. Fixed.
  • CSVWRITE now returns the number of rows written.
  • The data type of NULLIF was NULL if the first expression was a column. Now the data type is set correctly.
  • Indexes (and other related objects) for local temporary tables where not dropped when the session was closed. Fixed.
  • ALTER TABLE did not work for tables with computed columns.
  • SQLException.getCause of the now works for JDK 1.4 and higher.
  • If the index file was deleted, an error was logged in the .trace.db file. This is no longer done.
  • The Portuguese (Europe) translation is available. Thanks a lot to Antonio Casqueiro!
  • The error message for invalid views has been improved (the root cause is included in the message now).
  • IN(SELECT ...) was not working correctly if the subquery returned a NULL value. Fixed.
  • DROP ALL OBJECTS did not drop constants.
  • DROP ALL OBJECTS dropped the role PUBLIC, which was wrong. Fixed.
  • CASE was parsed as a function if the expression was in (). Fixed.
  • When ORDER BY was used together with DISTINCT, it was required to type the column name exactly in the select list and the order list exactly in the same way. This is not required any longer.

Version 1.0 / 2007-03-04 (Build 44)

  • System sequences (automatically created sequences for IDENTITY or AUTO_INCREMENT columns) are now random (UUIDs) to avoid clashes when merging databases using RUNSCRIPT.
  • The precision for linked tables was not correct for some data types, for example VARCHAR. Fixed.
  • Many problems and bugs in the XA support (package javax.sql) have been fixed.
  • Now the server tool (org.h2.tools.Server) terminates with an exit code if a problem occured.
  • The JDBC driver is now loaded if the JdbcDataSource class is loaded.
  • After renaming a user the password becomes invalid. This is now documented.
  • XAResource.recover didn't work. Fixed.
  • XAResource.recover did throw an exception with the code XAER_OUTSIDE if there was no connection. Now the code is XAER_RMERR.
  • SCRIPT did not work correctly with BLOB or CLOB data. Fixed.
  • BACKUP TO 'test.zip' now works with encrypted databases and CLOB and BLOB data.
  • The function CASE WHEN ... didn't convert the returned value to the same data type, resulting in unexpected behavior in many cases. Fixed.
  • Truncating a table is now allowed if the table references another table (but still not allowed if the table is references by another table).
  • ORDER BY picked the wrong column if the same column name (but with a different table name) was used twice in the select list.
  • When a subquery was used in the select list of a query, and GROUP BY was used at the same time, a NullPointerException could occur. Fixed.
  • ORDER BY did not work when DISTINCT was used at the same time in some situations. Fixed.
  • When using IN(...) on a case insensitive column (VARCHAR_IGNORECASE), an incorrect optimization was made and the result was wrong sometimes.

Version 1.0 / 2007-01-30 (Build 41)

  • Experimental online backup feature using the SQL statement BACKUP TO 'fileName'. This creates a backup in the form of a zip file. Unlike the SCRIPT TO command, the data tables are not locked.
  • When using the server mode, temporary files for large LOB values are now deleted when the result set is closed. This also means that LOBs become unavailable after closing the result, however this is according to the specs.
  • It was possible that SUM throws a class cast exception if the parameter was a conditional expression.
  • Benchmark: Added a multi-client test case, BenchB (similar to TPC-B).
  • Compatibility: SCHEMA_NAME.SEQUENCE_NAME.NEXTVAL now works as expected.
  • The Console is now translated to Hungarian thanks to Andras Hideg, and to Indonesian thanks to Joko Yuliantoro
  • XAConnection: A NullPointerException was thrown if addConnectionEventListener was called before opening the connection.
  • In case the result set of a subquery was re-used, an exception was throws if the subquery result did not fit in memory. Now the result is not re-used in this case. Generally, large subqueries should be avoided for performance reasons.
  • The command "drop all objects delete files" did not work on linux if the database name was lower case.
  • When setting the URL to an empty string the DataSource now throws an better exception.
  • Parsing of LIKE .. ESCAPE did not stop at the expected point. Fixed.
  • Can now use UUID columns as generated key values. However, the UUID column must be the primary key.
  • Improved the Javadoc documentation. Now unsupported features are marked with [Not supported], and partially supported features are [Partially supported].
  • The forum subscriptions (the emails sent from the forum) now works.

Version 1.0 / 2007-01-17 (Build 40)

  • Setting the collation (SET COLLATOR) was very slow on some systems (up to 24 seconds). Thanks a lot to Martina Nissler for finding this problem!
  • The Console is now translated to Japanese thanks to IKEMOTO, Masahiro (ikeyan (at) arizona (dot) ne (dot) jp)
  • The database engine can now be compiled with JDK 1.3 using ant codeswitch_jdk13. There are still some limitations, and the ant script to build the jar does not work yet.
  • Fixed a problem where data in the log file was not written to the data file (recovery failure) after a crash, if an index was deleted previously.
  • SCRIPT NODATA now writes the row count for each table (this simplifies comparing databases).
  • Selecting a column using the syntax schemaName.tableName.columnName did not work in all cases.
  • Can now parse timestamps with timezone information (Z or +/-hh:mm) and dates before year 1. However dates before year 1 are not formatted correctly (this is a Java problem).
  • When stopping the TCP server from an application and immediately afterwards staring it again using a different TCP password, an exception was thrown sometimes.
  • Now PreparedStatement.setBigDecimal(..) can only be called with an object of type java.math.BigDecimal. Derived classes are not allowed any more. Many thanks to Maciej Wegorkiewicz for finding this problem.
  • It was possible to manipulate values in the byte array after calling PreparedStatement.setBytes, and this could lead to problems if the same byte array was used again. Now the byte array is copied if required.
  • Date, time and timestamp objects were cloned in cases where it was not required. Fixed.

Version 1.0 / 2007-01-02 (Build 36)

  • It was possible to drop the sequence of a temporary tables with DROP ALL OBJECTS, resulting in a null pointer exception afterwards.
  • Prepared statements with non-constant functions such as CURRENT_TIMESTAMP() did not get re-evaluated if the result of the function changed. Fixed.
  • The (relative or absolute) directory where the script files are stored or read can now be changed using the system property h2.scriptDirectory
  • Client trace files now created in the directory 'trace.db' and no longer the application directory. This can be changed using the system property h2.clientTraceDirectory.
  • In some situations the log file got corrupt if the process was terminated while the database was opening.
  • Using ;RECOVER=1 in the database URL threw a syntax exception. Fixed.
  • If a CLOB or BLOB was deleted in a transaction and the database crashed before the transaction was committed or rolled back, the object was lost if it was large. Fixed.
  • Now using ant-build.properties. The jdk is automatically updated when using ant codeswitch_...
  • Cluster: Now the server can detect if a query is read-only, and in this case the result is only read from the first cluster node. However, there is currently no load balancing made to avoid problems with transactions / locking.
  • Many settings are now initialized from system properties and can be changed on the command line without having recompile the database. See Advances / Settings Read from System Properties.
  • H2 is now available in Maven. The groupId is com.h2database, the artifactId h2 and the version 1.0.20061217. To create the maven artifacts yourself, use 'ant mavenUploadLocal' and 'ant mavenBuildCentral'.

Version 1.0 / 2006-12-17 (Build 34)

  • Can be compiled with JDK 1.6. However, only very few of the JDBC 4.0 features are implemented so far.
  • The unit test of OpenJPA works now.
  • Unfortunately, the Hibernate dialect has changed due to a change in the meta data in the last release (INFORMATION_SCHEMA.SEQUENCES).
  • String.toUpperCase and toLowerCase can not be used to process SQL, as they depend on the current locale. Now using toUpperCase(Locale.ENGLISH) or Character.toUpperCase(..)
  • Table aliases are now supported in DELETE and UPDATE. Example: DELETE FROM TEST T0.
  • The RunScript tool can now include other files using a new syntax: @INCLUDE fileName. It was already possible to do that using embedded RUNSCRIPT statements, but not remotely.
  • When the database URL contains ;RECOVER=1 then the index file is now deleted if it was not closed before.
  • The scale of a NUMERIC(1) column is now 0. It used to be 32767.
  • Deleting old temp files now uses a phantom reference queue. Generally, temp files should now be deleted earlier.
  • Opening a large database is now much faster when even when using the default log mode (LOG=1), if the database was closed previously.
  • Very large BLOB and CLOB data can now be used with the server and the cluster mode. The objects will temporarily be buffered on the client side if they are larger than some size (currently 64 KB).
  • PreparedStatement.setObject(x, y, Types.OTHER) does now serialize the object in every case (even for Integer).
  • EXISTS subqueries with parameters were not re-evaluated when the prepared statement was reused. This could lead to incorrect results.
  • Support for indexed parameters in PreparedStatements: update test set name=?2 where id=?1

Version 1.0 / 2006-12-03 (Build 32)

  • The SQL statement COMMENT did not work as expected. Many bugs have been fixed in this area. If you already have comments in the database, it is recommended to backup and restore the database, using the Backup and RunScript tools or the SQL commands SCRIPT and RUNSCRIPT.
  • Mixing certain data types in an operation, for example VARCHAR and TIMESTAMP, now converts both expressions to TIMESTAMP. This was a problem when comparing a string against a date.
  • Improved performance for CREATE INDEX. This method is about 10% faster if the original order is random.
  • Server: If an object was already closed on the server side because it was too 'old' (not used for a long time), even calling close() threw an exception. This is not done any more as it is not a problem.
  • Optimization: The left and right side of a AND and OR conditions are now ordered by the expected cost.
  • There was a bug in the database encryption algorithm. The initialization vector was not correctly calculated, and pattern of repeated encrypted bytes where generated for empty blocks in the file. This has been is fixed. The security of the data itself was not compromised, but this was not the intended behavior. If you have an encrypted database, you will need to decrypt the database using the org.h2.tools.ChangePassword (using the old database engine), and encrypt the database using the new engine. Alternatively, you can use the Backup and RunScript tools or the SQL commands SCRIPT and RUNSCRIPT.
  • New connection time setting CACHE_TYPE=TQ to use the 2Q page replacement algorithm. The overall performance seems to be a bit better when using 2Q. Also, 2Q should be more resistant to table scan.
  • Change behavior: If both sides of a comparison are parameters with unknown data type, then an exception is thrown now. The same happens for UNION SELECT if both columns are parameters.
  • New system function SESSION_ID().
  • Deeply nested views where slow to execute, because the query was parsed many times. Fixed.
  • The service wrapper is now included in the default installation and documented.
  • Java functions returning a result set (such as CSVREAD) now can be used in a SELECT statement like a table. The function is still called twice (first to get the column list at parse time, and then at execute time). The behavior has been changed: Now first call contains the values if set, but the connection URL is different (jdbc:columnlist:connection instead of jdbc:default:connection).

Version 1.0 / 2006-11-20 (Build 31)

  • SCRIPT: New option BLOCKSIZE to split BLOB and CLOB data into separate blocks, to avoid OutOfMemory problems.
  • When using the READ_COMMITTED isolation level, a transaction now waits until there are no write locks when trying to read data. However, it still does not add a read lock.
  • INSERT INTO ... SELECT ... and ALTER TABLE with CLOB and/or BLOB did not work. Fixed.
  • CSV tool: the methods setFieldSeparatorWrite and setRowSeparatorWrite where not accessible. The API has been changed, there is now only one static method, getInstance().
  • ALTER TABLE ADD did throw a strange message if the table contained views. Now the message is better, but it is still not possible to do that if views on this table exist.
  • Direct links to the Javadoc were not working.
  • CURRVAL and NEXTVAL functions: New optional sequence name parameter.
  • ALTER TABLE: If there was a foreign key in another table that references to the change table, the constraint was dropped.
  • The input streams returned from this database did not always read all data when calling read. This was not wrong, but unexpected. Now changed that read behaves like readFully.
  • The default cache size is now 65536 pages instead of 32768. Like this the memory used for caching is about 10 MB. Before, it used to be about 6 MB.
  • Inserting rows into linked tables did not work for HSQLDB when the value was NULL. Now NULL values are inserted if the value for a column was not set in the insert statement.
  • New optimization to reuse subquery results. Can be disabled with SET OPTIMIZE_REUSE_RESULTS 0.
  • Oracle SYNONYM tables are now listed in the H2 Console.
  • CREATE LINKED TABLE didn't work for Oracle SYNONYM tables. Fixed.
  • Dependencies between the JDBC client and the database have been removed. The h2client.jar is now 295 KB. There are still some classes included that should not be there. The theoretical limit is about 253 KB currently.
  • When using the server version, when not closing result sets or using nested DatabaseMetaData result sets, the connection could break. This has been fixed.
  • EXPLAIN... results are now formatted on multiple lines so it is easier to read them.
  • The Spanish translation was completed by Miguel Angel. Thanks a lot! Translations to other languages are always welcome.
  • New system function SCHEMA() to get the current schema.
  • New SQL statement SET SCHEMA to change the current schema of this session.
  • The Recovery tool has been improved. It now creates a SQL script file that can be executed directly.
  • LENGTH now returns the precision for CLOB, BLOB, and BINARY (and is therefore faster).
  • The built-in FTP server can now access a virtual directory stored in a database.

Version 1.0 / 2006-11-03 (Build 30)

  • Two simple full text search implementations (Lucene and native) are now included. This is work in progress, and currently undocumented. See test/org/h2/samples/fullTextSearch.sql. To enable the Lucene implementation, you first need to install Lucene, rename FullTextLucene.java.txt to *.java and call 'ant compile'.
  • Wide b-tree indexes (with large VARCHAR columns for example) could get corrupted. Fixed.
  • If a custom shutdown hook was installed, and the database was called at shutdown, a NullPointException was thrown. Now, the following exception is thrown: Database called at VM shutdown; add ";DB_CLOSE_ON_EXIT=FALSE" to the db URL to disable automatic database closing
  • If SHUTDOWN was called and DB_CLOSE_DELAY was set, the database was not closed. This has been changed so that shutdown always closes the database.
  • Subqueries with order by outside the column list didn't work correctly. Example: SELECT (SELECT NAME FROM TEST ORDER BY ID); This is fixed now.
  • Linked Tables: Only the first column was linked when linking to PostgreSQL, or a subquery. Fixed.
  • Sequences: When the database is not closed normally, the value was not set correctly. Fixed.
  • The optimization for IN(SELECT...) was too aggressive; changes in the inner query where not accounted for.
  • Index names of referential constraints are now prefixed with the constraint name.
  • Blob.getBytes skipped the wrong number of bytes. Fixed.
  • Group by a function didn't work if a column list was specified in the select list. Fixed.
  • Improved search functionality in the HTML documentation.
  • Triggers can now be defined on a list of actions (for example: INSERT, UPDATE) instead of just one action per trigger.
  • On some systems (for example, some Linux VFAT and USB flash disk drivers), RandomAccessFile.setLength does not work correctly. A workaround for this problem has been implemented.
  • DatabaseMetaData.getTableTypes now also returns SYSTEM TABLE and TABLE VIEW. This was done for DbVisualizer. Please tell me if this breaks other applications or tools.
  • Java functions with Blob or Clob parameters are now supported.
  • LOCK_MODE 0 (READ_UNCOMMITTED) did not work when using multiple connections. Fixed, however LOCK_MODE 0 should still not be used when using multiple connections.
  • New SQL statement COMMENT ON ... IS ...
  • Added a 'remarks' column to most system tables. New system table INFORMATION_SCHEMA.TRIGGERS
  • PostgreSQL compatibility: Support for the date format 2006-09-22T13:18:17.061
  • MySQL compatibility: ResultSet.getString("PEOPLE.NAME") is now supported.
  • JDBC 4.0 driver auto discovery: When using JDK 1.6, Class.forName("org.h2.Driver") is no longer required.

Version 1.0 / 2006-10-10 (Build 28)

  • Redundant () in a IN subquery is now supported: where id in ((select id from test))
  • The multi-threaded kernel can not be enabled using SET MULTI_THREADED 1 or jdbc:h2:test;MULTI_THREADED=1. A new tests has been written for this feature, but more tests are required, so this is still experimental.
  • Can now compile everything with JDK 1.6. However, only very few of the JDBC 4.0 features are implemented so far.
  • A small FTP server is now included. Disabled by default. Intended as a simple mechanism to transfer data in ad-hoc networks.
  • GROUP BY an formula or function didn't work if the same expression was used in the select list. Fixed.
  • Reconnect didn't work after renaming a user if rights were granted for this user. Fixed.
  • Opening and closing connections in many threads sometimes failed because opening a session was not correctly synchronized. Fixed.
  • Function aliases may optionally include parameter classes. Example: CREATE ALIAS PARSE_INT2 FOR "java.lang.Integer.parseInt(java.lang.String, int)"
  • Support for UUID
  • Support for DOMAIN (user defined data types).
  • Could not re-connect to a database when ALLOW_LITERALS or COMPRESS_LOB was set. Fixed.

Version 1.0 / 2006-09-24 (Build 27)

  • New LOCK_MODE 3 (READ_COMMITTED). Table level locking, but only when writing (no read locks).
  • Connection.setTransactionIsolation and getTransactionIsolation now set / get the LOCK_MODE of the database.
  • New system function LOCK_MODE()
  • Optimizations for WHERE ... IN(...) and SELECT MIN(..), MAX(..) are now enabled by default, but can be disabled in the application (Constants.OPTIMIZE_MIN_MAX, OPTIMIZE_IN)
  • LOBs are now automatically converted to files. Constants.AUTO_CONVERT_LOB_TO_FILES is now set to true by default, however this can be disabled in the application.
  • Reading from compressed LOBs didn't work in some cases. Fixed.
  • CLOB / BLOB: Copying LOB data directly from one table to another, and altering a table with with LOBs did not work, the BLOB data was deleted when the old table was deleted. Fixed.
  • Wide b-tree indexes (with large VARCHAR columns for example) with a long common prefix (where many rows start with the same text) could get corrupted. Fixed.
  • For compatibility with Derby, the precision in a data type definition can now include K, M or G as in BLOB(10M).
  • CREATE TABLE ... AS SELECT ... is now supported.
  • DROP TABLE: Can now drop more than one column in one step: DROP TABLE A, B
  • CREATE SCHEMA: The authorization part is now optional.
  • Protection against SQL injection: New command SET ALLOW_LITERALS {NONE|ALL|NUMBERS}. With SET ALLOW_LITERALS NONE, SQL injections are not possible because literals in SQL statements are rejected; User input must be set using parameters ('?') in this case.
  • New concept 'Constants': New SQL statements CREATE CONSTANT and DROP CONSTANT. Constants can be used where expressions can be used. New metadata table INFORMATION_SCHEMA.CONSTANTS. Built-in constant function ZERO() to get the integer value 0.
  • New data type OTHER (alternative names OBJECT and JAVA_OBJECT). When using this data type, Java Objects are automatically serialized and deserialized in the JDBC layer. Constants.SERIALIZE_JAVA_OBJECTS is now true by default.
  • [NOT] EXISTS(SELECT ... EXCEPT SELECT ...) did not work in all cases. Fixed.
  • DatabaseMetaData.getProcedures and getProcedureColumns are implemented now. INFORMATION_SCHEMA.FUNCTION_ALIASES was changed, and there is a new table INFORMATION_SCHEMA.FUNCTION_COLUMNS.
  • As a workaround for a problem on (maybe misconfigured) Linux system, now use InetAddress.getByName("127.0.0.1") instead of InetAddress.getLocalHost() to get the loopback address.
  • Functions returning a result set that are used like a table are now first called (to get the column names) with null values (or 0 / false for primitive types) as documented and not with any values even if they are constants.
  • Improved performance for MetaData calls. The table name is now indexed.
  • BatchUpdateException was not helpful, now includes the cause
  • Unknown setting in the database URL (which are most likely typos) are now detected and an exception is thrown. Unknown settings in the connection properties however (for example used by OpenOffice and Hibernate) are ignored.
  • Now the log size is automatically increased to at least 10% of the data file. This solves a problem with 'Too many open files' for very large databases.
  • Backup and Runscript tools now support options (H2 only)

Version 1.0 / 2006-09-10 (Build 26)

  • Updated the performance test so that Firebird can be tested as well.
  • Now an exception is thrown when the an overflow occurs for mathematical operations (sum, multiply and so on) for the data type selected.
  • Correlated subqueries: It is now possible to use columns of the outer query in the select list of the inner query (not only in the condition).
  • The script can now be compressed. Syntax: SCRIPT TO 'file' COMPRESSION {DEFLATE|LZF|ZIP|GZIP}.
  • New setting SET COMPRESS_LOB {NO|LZF|DEFLATE} to automatically compress BLOBs and CLOBs. This is helpful when storing highly redundant textual data (XML, HTML).
  • ROWNUM didn't always work as expected when using subqueries. This is fixed now. However, there is no standard definition for ROWNUM, for situations where you want to limit the number of rows (specially for offset), the standardized LIMIT [OFFSET] should be used
  • Deleting many rows from a table with a self-referencing constraint with 'on delete cascade' did not work. Now referential constraints are checked after the action is performed.
  • The cross references in the SQL grammar docs where broken in the last release.
  • There was a bug in the default settings for the Console, the setting ;hsqldb.default_table_type=cached was added to the H2 database instead of the HSQLDB database.
  • Workaround for an OpenOffice.org problem: DatabaseMetaData calls with schema name pattern now return the objects for the PUBLIC schema if the schema name pattern was an empty string.
  • Until now, unknown connection properties where ignored (for OpenOffice compatibility). This is not a good solution because typos are not detected. This behavior is still the default but it can be disabled by adding ;IGNORE_UNKNOWN_SETTINGS=FALSE to the database URL. However this is not the final solution.
  • Workaround for an OpenOffice problem: OpenOffice Base calls DatabaseMetaData functions with "" as the catalog. As the catalog in H2 is not an empty string, the result should strictly be empty; however to make OpenOffice work, the catalog is not used as a criteria in this case.
  • New SQL statement DROP ALL OBJECTS [DELETE FILES] to drop all tables, sequences and so on. If DELETE FILES is added, the database files are deleted as well when the last connection is closed. The DROP DATABASE statement found in other systems means drop another database, while DROP ALL OBJECTS means remove all data from the current database.
  • When running a script that contained referential or unique constraints, and if the indexes for those constraints are not created by the user, a internal exception could occur. This is fixed. Also, the script command will no longer write the internal indexes used into the file.
  • SET IGNORECASE is now supported for compatibility with HSQLDB, and because using collations (SET COLLATION) is very slow on JDKs (JDK 1.5)
  • ORDER BY an expression didn't work when using GROUP BY at the same time.

Version 1.0 / 2006-08-31 (Build 25)

  • In some situations, wide b-tree indexes (with large VARCHAR columns for example) could get corrupted. Fixed.
  • ORDER BY was broken in the last release when using table aliases. Fixed.

Version 0.9 / 2006-08-28

  • DATEDIFF on seconds, minutes, hours did return different results in certain timezones (half-hour timezones) in certain situations. Fixed.
  • LOB files where not deleted when the table was truncated or dropped. This is now done.
  • When large strings or byte arrays where inserted into a LOB (CLOB or BLOB), or if the data was stored using PreparedStatement.setBytes or setString, the data was stored in-place (no separate files where created). This is now implemented (that means distinct files are created in every case), however disabled by default. It is possible to enable this option with Constants.AUTO_CONVERT_LOB_TO_FILES = true
  • New setting MAX_LENGTH_INPLACE_LOB
  • When reading from a table with many small CLOB columns, in some situations an ArrayIndexOutOfBoundsException was thrown. Fixed.
  • Optimization for MIN and MAX (but currently disabled by default until after release 1.0): Queries such as SELECT MIN(ID), MAX(ID)+1, COUNT(*) FROM TEST now use an index if one is available. To enable manually, set Constants.OPTIMIZE_MIN_MAX = true in your application.
  • Subqueries: Constant subqueries are now only evaluated once (like this was before).
  • Linked tables: Improved compatibility with other databases and improved error messages.
  • Linked tables: The table name is no longer quoted when accessing the foreign database. This allows to use schema names, and possibly subqueries as table names (when used in queries).
  • Outer join: There where some incompatibilities with PostgreSQL and MySQL with more complex outer joins. Fixed.

Version 0.9 / 2006-08-23 (Build 23)

  • Bugfix for LIKE: If collation was set (SET COLLATION ...), it was ignored when using LIKE. Fixed.
  • Optimization for IN(value list) and IN(subquery). The optimization is disabled by default, but can be switched on by setting Constants.OPTIMIZE_IN = true (no compilation required). Currently, the IN(value,...) is converted to BETWEEN min AND max, that means it is not converted to an inner join.
  • Arithmetic overflows in can now be detected for sql types TINYINT, SMALLINT, INTEGER, BIGINT. Operations: addition, subtraction, multiplication, negation. By default, this detection is switched off but can be switched on by setting Constants.OVERFLOW_EXCEPTIONS = true (no compilation required).
  • Referential integrity: fixed a stack overflow problem when a deleted record caused (directly or indirectly) deleting other rows in the same table via cascade delete.
  • Database opening: sometimes opening a database was very slow because indexes were re-created. even when the index was consistent. This lead to long database opening times for some databases. Fixed.
  • Local temporary tables where not included in the meta data. Fixed.
  • Very large transactions are now supported. The undo log of large transactions is buffered to disk. The maximum size of undo log records to be kept in-memory can be changed with SET MAX_MEMORY_UNDO. Currently, this feature is disabled by default (MAX_MEMORY_UNDO is set to Integer.MAX_VALUE by default) because it needs more testing. It will be enabled after release 1.0. The current implementation has a limitation: changes to tables without a primary key can not be buffered to disk
  • Improvements in the autocomplete feature. Thanks a lot to James Devenish for his very valuable feedback and testing!
  • Bugfix for an outer join problem (too many rows where returned for a combined inner join / outer join).
  • Date and time constants outside the valid range (February 31 and so on) are no longer accepted.

Version 0.9 / 2006-08-14 (Build 21)

  • SET LOG 0 didn't work (except if the log level was set to some other value before). Fixed.
  • Outer join optimization. An outer join now evaluates expressions like (ID=1) in the where clause early. However expressions like ID IS NULL or NOT ID IS NOT NULL in the where clause can not be optimized.
  • Autocomplete is now improved. Schemas and quoted identifiers are not yet supported. There are some browser incompatibilities, for example Enter doesn't work in Opera (but clicking on the item does), clicking on the item doesn't work in Internet Explorer (but Enter works). However everything should work in Firefox. Please report any incompatibilities.
  • Source code to support H2 in Resin is included (see src/tools, package com.caucho.jdbc). For the complete patch, see http://forum.caucho.com/node/61
  • Space is better re-used after deleting many records.
  • Umlauts and Chinese characters are now supported in identifier names (table name, column names and so on).
  • Fixed a problem when comparing BIGINT values with constants.
  • NULL handling was wrong for: true IN (true, null). Fixed.
  • It was not possible to cancel a select statement with a (temporary) view. Fixed.

Version 0.9 / 2006-07-29 (Build 18)

  • ParameterMetaData is now implemented (mainly to support getParameterCount).
  • Improved performance for Statement.getGeneratedKeys().
  • SCRIPT: The system generated indexes are now not included in the script file because they will be created automatically. Also, the drop statements for generated sequences are not included in the script any longer.
  • Bugfix: IN(NULL) didn't return NULL in every case. Fixed.
  • Bugfix: DATEDIFF didn't work correctly for hour, minute and second if one of the dates was before 1970. Fixed.
  • Shutdown TCP Server: The Server tool didn't throw / print a meaningful exception if the server was not running or if the password was wrong. Fixed.
  • Experimental auto-complete functionality in the H2 Console. Does not yet work for all cases. Press [Ctrl]+[Space] to activate, and [Esc] to deactivate it.
  • SELECT EXCEPT (or MINUS) did not work for some cases. Fixed.
  • DATEDIFF now returns a BIGINT and not an INT
  • 1.0/3.0 is now 0.33333... and not 0.3 as before. The scale of a DECIMAL division is adjusted automatically (up to current scale + 25).
  • 'SELECT * FROM TEST' can now be written as 'FROM TEST SELECT *' to enable improved autocomplete (column names can be suggested in the SELECT part of the query because the tables are known if the FROM part comes first). SELECT is now a keyword.
  • H2 Console: First version of an autocomplete feature.
  • DATEADD didn't work for milliseconds. Fixed.
  • New parameter schemaName in Trigger.init.
  • New method DatabaseEventListener.init to pass the database URL.
  • Opening a database that was not closed previously is now faster (specially if using a database URL of the form jdbc:h2:test;LOG=2) Applying the redo-log is buffered and writing to the file is ordered.
  • Could not connect to a database that was closing at the same time.
  • C-style block comments /* */ are not parsed correctly when they contain * or /

Version 0.9 / 2006-07-14 (Build 16)

  • The regression tests are no longer included in the jar file. This reduces the size by about 200 KB.
  • Fixed some bugs in the CSV tool. This tool should now work for most cases, but is still not fully tested.
  • The cache size is now measured in blocks and no longer in rows. Manually setting the cache size is no longer necessary in most cases.
  • Objects of unknown type are no longer serialized to a byte array (and deserialized when calling getObject on a byte array data type) by default. This behavior can be changed with Constants.SERIALIZE_JAVA_OBJECTS = true
  • New column IS_GENERATED in the metadata tables SEQUENCES and INDEXES
  • Optimization: deterministic subqueries are evaluated only once.
  • An exception was thrown if a scalar subquery returned no rows. Now the NULL value is used in this case.
  • IF EXISTS / IF NOT EXISTS implemented for the remaining CREATE / DROP statements.
  • ResultSetMetaData.isNullable is now implemented.
  • LIKE ... ESCAPE: The escape character may now also be an expression.
  • Compatibility: TRIM(whitespace FROM string)
  • Compatibility: SUBSTRING(string FROM start FOR length)
  • CREATE VIEW now supports a column list: CREATE VIEW TEST_V(A, B) AS ...
  • Compatibility: 'T', 'Y', 'YES', 'F', 'N', 'NO' (case insensitive) can now also be converted to boolean. This is allowed now: WHERE BOOLEAN_FIELD='T'=(ID>1)
  • Optimization: data conversion of constants was not optimized. This is done now.
  • Compatibility: Implemented a shortcut version to declare single column referential integrity: CREATE TABLE TEST(ID INT PRIMARY KEY, PARENT INT REFERENCES TEST)
  • Issue #126: It is possible to create multiple primary keys for the same table.
  • Issue #125: Foreign key constraints of local temporary tables are not dropped when the table is dropped.
  • Issue #124: Adding a column didn't work when the table contains a referential integrity check.
  • Issue #123: The connection to the server is lost if an abnormal exception occurs. Example SQL statement: select 1=(1,2)
  • The H2 Console didn't parse statements containing '-' or '/' correctly. Fixed. Now uses the same facility to split a script into SQL statements for the RunScript tool, for the RUNSCRIPT command and for the H2 Console.
  • DatabaseMetaData.getTypeInfo: BIGINT was returning AUTO_INCREMENT=TRUE, which is wrong. Fixed.

Version 0.9 / 2006-07-01 (Build 14)

  • After dropping constraints and altering a table sometimes the database could not be opened. Fixed.
  • Outer joins did not always use an index even if this was possible. Fixed.
  • Issue #122: Using OFFSET in big result sets (disk buffered result sets) did not work. Fixed.
  • Support DatabaseMetaData.getSuperTables (currently returns an empty result set in every case).
  • Database names are no longer case sensitive for the Windows operating system, because there the files names are not case sensitive.
  • If an index is created for a constraint, this index now belong to the constraint and is removed when removing the constraint.
  • Issue #121: Using a quoted table or alias name in front of a column name (SELECT "TEST".ID FROM TEST) didn't work.
  • Issue #120: Some ALTER TABLE statements didn't work when the table was in another than the main schema. Fixed.
  • Issue #119: If a table with autoincrement column is created in another schema, it was not possible to connect to the database again. Now opening a database first sorts the script by object type.
  • Issue #118: ALTER TABLE RENAME COLUMN doesn't work correctly. Workaround: don't use it.
  • Cache: implemented a String cache and improved the Value cache. Now uses a weak reference to avoid OutOfMemory due to caching values.
  • Server: changed the public API a bit to allow an application to deal easier with start problems. Now instead of Server.startTcpServer(args) use Server.createTcpServer(args).start();
  • Issue #117: Server.start...Server sometimes returned before the server was started. Solved.
  • Issue #116: Server: reduces memory usage. Reduced number of cached objects per connection.
  • Improved trace messages, and trace now starts earlier (when opening the database).
  • Simplified translation of the Web Console (a tool to convert the translation files to UTF-8).
  • Newsfeed sample application (used to create the newsfeed and newsletter).
  • New functions: MEMORY_FREE() and MEMORY_USED().

Version 0.9 / 2006-06-16

  • Implemented distributing lob files into directories, and only keep up to 255 files in one directory. However this is disabled by default; it will be enabled the next time the file format changes (maybe not before 1.1). It can be enabled by the application by setting Constants.LOB_FILES_IN_DIRECTORIES = true;
  • If a connection is closed while there is still an operation running, this operation is stopped (like when calling Statement.cancel).
  • Issue #115: If three or more threads / connections are used, sometimes lock timeout exceptions can occur when they should not. Solved.
  • Calling Server.start...Server now doesn't return until the server socket is ready to accept connections.
  • Issue #112: Two threads could not open the same database at the same time. Solved. This was only a problem when the database was opened in a non-standard was, without using DriverManager.getConnection.
  • Implemented DROP TRIGGER.
  • Issue #113: Drop is now restricted: can only drop sequences / functions / tables if nothing depends on them.
  • Issue #114: Support large index data size. Before, there was a limit of around 1000 bytes per row.
  • Blob.getLength() and Clob.getLength() are now fast operations and don't read the whole object any longer.
  • Issue #111: The catalog name in the DatabaseMetaData calls and Connection.getCatalog was lowercase; some applications may not work because they expect it to be uppercase.
  • Issue #110: PreparedStatement.setCharacterStream(int parameterIndex, Reader reader, int length) and ResultSet.updateCharacterStream(...) didn't work correctly for 'length' larger than 0 and using Unicode characters higher than 127 (or 0). The number of UTF-8 bytes where counted instead of the number of characters.
  • The catalog name is now uppercase, to conform the JDBC standard for DatabaseMetaData.storesUpperCaseIdentifiers().
  • Creating or opening a small encrypted database is now a lot faster.
  • New functions FORMATDATETIME and PARSEDATETIME.
  • New XML encoding functions (XMLATTR, XMLNODE, XMLCOMMENT, XMLCDATA, XMLSTARTDOC, XMLTEXT).
  • Performance: improved opening of a large databases (about 3 times faster now for 500 MB databases).
  • Documented ALTER TABLE DROP COLUMN. The functionality was there already, but the documentation not.

Version 0.9 / 2006-06-02 (Build 10)

  • Removed the GCJ h2-server.exe from download. It was not stable on Windows.
  • Issue #109: ALTER TABLE ADD COLUMN can make the database unusable if the original table contained a IDENTITY column.
  • New option to disable automatic closing of a database when the virtual machine exits. Database URL: jdbc:h2:test;db_close_on_exit=false
  • New event: DatabaseEventListener.closingDatabase() is called before closing the database.
  • Connection.getCatalog() now returns the database name (CALL DATABASE()). This name is also used in all system tables and DatabaseMetaData calls where applicable.
  • The function DATABASE() now return the short name of the database (without path), and 'Unnamed' for anonymous in-memory databases.
  • Issue #108: There is a concurrency problem when multi threads access the same database at the same time, and one is closing the connection and the other is executing CHECKPOINT at the exact the same time. Fixed.
  • Statements containing LIKE are now re-compiled when executed. Depending on the data, an index on the column is used or not.
  • Issue# 107: When executing scripts that contained inserts with many columns, an OutOfMemory error could occur. The problem was usage of shared Strings (String.substring). Now each String value is copied if required, releasing memory.
  • Issue #106: SET commands where not persisted if they where the first DDL commands for a connection. Fixed.
  • Issue #105: RUNSCRIPT (the command) didn't commit after each command if autocommit was on, therefore large scripts run out of memory. Now it automatically commits.
  • Automatic starting of a web browser for Mac OS X should work now.
  • Starting the server is not a bit more intuitive. Just setting the -baseDir option for example will still start all servers. By default, -tcp, -web, -browser and -odbc are started.
  • Issue #104: A HAVING condition on a column that was not in the GROUP BY list didn't work correctly in all cases. Fixed.
  • ORDER BY now uses an index if possible. Queries with LIMIT with ORDER BY are faster when the index can be used.
  • New option '-ifExists' for the TCP and ODBC server to disallow creating new databases remotely.
  • Shutdown of a TCP Server: Can now specify a password (tcpPassword). Passwords used at startup and shutdown must match to shutdown. Uses a management database now for each server / port. New option tcpShutdownForce (default is false) to kill the server without waiting for other connections to close.
  • Issue #103: Shutdown of a TCP Server from command line didn't always work.

Version 0.9 / 2006-05-14

  • New functions: CSVREAD and CSVWRITE to access CSV (comma separated values) files.
  • Locking: the synchronization was too restrictive, locking out the connection holding a lock when another connection tried to lock the same object. Fixed.
  • Outer Join: currently, the table order of outer joins is kept, the tables are evaluated left to right (with the exception of right outer join tables). This is a temporary solution only to solve problems with join conditions.
  • Bugfix for SCRIPT: the rights where created before the objects. Fixed.
  • Implemented function LOCK_TIMEOUT().
  • Compatibility with DBPool: Support 'holdability' in the Connection methods (however the value it is currently ignored).
  • Connection.setTypeMap does not throw an exception any more if the type map is empty (null or 0 size).
  • Issue #102: INSTR('123456','34') should return 2.
  • Issue #101: A big result set with order by on a column or value that is not in the result list didn't work. Fixed.
  • Referential integrity: cascade didn't work for more than one level when it was self-referencing. Fixed.
  • New parameter charsetName in RunScript.execute. New option CHARSET in the RUNSCRIPT command.
  • A backslash in the database URL (database name) didn't work, fixed. But a backslash in the settings part of the URL is used as an escape for ';', that means database URLs of the form jdbc:h2:test;SETTING=a\;b\;c are possible (but hopefully this is never required).
  • Added an interface SimpleRowSource so that an application / tool can create a dynamic result set that produces rows on demand (for streaming result sets).
  • Foreign key constraints with different data types on the references / referencing column did not work. Fixed.
  • Fixed a problems that lead to a database (index file) corruption when killing the process. Added a test case.
  • If a user has SELECT privileges for a view, he can now retrieve the data even if he does not have privileges for the underlying table(s).
  • New system table INFORMATION_SCHEMA.CONSTRAINT that returns information about constraints (mainly for check and unique constraints; for referential constraints see CROSS_REFERENCES).
  • Alter table alter column: the data type of a column could not be changed, and columns could not be dropped if there was a constraint on the table. Now the data type can always be changed. Alter table drop column: now the column can be dropped if it is not part of a constraint.
  • Views can now be 'invalid', for example if the table does not exist. New syntax CREATE FORCE VIEW. New column STATUS (invalid / valid) in INFORMATION_SCHEMA.VIEWS table. New SQL statement ALTER VIEW viewName RECOMPILE.
  • Issue #100: Altering a table with a self-referencing constraint didn't work. Fixed.
  • Support for IDENTITY(start, increment) as in CREATE TABLE TEST(ID BIGINT NOT NULL IDENTITY(10, 5));
  • Recovery did not always work correctly when the log file was switched. Added tests for this use case.
  • Bugfix for CREATE ROLE IF NOT EXISTS X.
  • Removed support for LZMA. It was slow, and code coverage could not run. If somebody needs this algorithm, it is better to add an open compression API.
  • Starting a server took 1 second before, now it takes only about 100 ms.
  • Server mode: If a parameter was not set in a prepared statement when using the server mode, the connection to the server broke. Fixed.
  • Referential integrity: If there was a unique index with fewer columns than the foreign key on the child table, it did not work. Fixed.
  • Javadoc / Doclet: ResultSet.getBytes was documented to return byte instead of byte[]. Fixed.
  • New setting RECOVER in the database URL (jdbc:h2:test;RECOVER=1) to open corrupted databases (wrong checksum, corrupted index file or summary).
  • Bugfix: In server mode, big scrollable result sets didn't work. Fixed.
  • MERGE: instead of delete-insert, now use update-[insert]. This solves problems with foreign key constraints.
  • Bugfix for LIKE: A null pointer exception was thrown for WHERE NAME LIKE CAST(? AS VARCHAR).
  • Join optimization: expressions are now evaluated as early as possible, avoiding unnecessary lookups.
  • ResultSetMetaData.isAutoIncrement is implemented.
  • SCRIPT: STRINGENCODE instead of STRINGDECODE was used. The implementation of STRINGDECODE was not correct for Unicode characters > 127. Fixed.
  • Documentation: [[NOT] NULL] instead of [NOT [NULL]]
  • Because of the REAL support, databases are not compatible with the old version. Backup (with the old version), replace 'STRINGENCODE' in the script with 'STRINGDECODE', and restore is required to upgrade to the new version.
  • Support for REAL data type (Java 'float'; so far the DOUBLE type was used internally).

Version 0.9 / 2006-04-20

  • Performance improvement for AND, OR, IFNULL, CASEWHEN, CASE, COALESCE and ARRAY_GET: only the necessary parameters / operators are evaluated.
  • Bugfix for CASEWHEN: data type of return value was not evaluated, and this was a problem when using prepared statements like this: WHERE CASEWHEN(ID < 10, A, B)=?
  • New function DATABASE_PATH to retrieve the path and file name of a database.
  • Made the code more modular. New ant target: jarClient to compile the JDBC driver and the classes used to remotely connect to a H2 server.
  • JdbcDataSourceFactory constructor is now public.
  • SET THROTTLE allows to throttle down the resource usage of a connection. After each 50ms, the session sleeps for the specified amount of time.
  • ALTER TABLE ALTER COLUMN for a table that was referenced by another table didn't work. Fixed.
  • If index log is disabled (the default), index changes are flushed automatically each second if the index was not changed. Like this index rebuilding is only required (after a unexpected program termination, like power off) for indexes that where recently changed.
  • Bugfix: ids of large objects (LOBs) are now correctly reserved when opening the database. This improves the performance when using many LOBs.
  • Replaced log_index=1 with log=2. There are 3 log options now: 0 (disabled), 1 (default), and 2 (log index changes as well).
  • Got rid of the summary (.sum.db) file. The data is now stored in the log file instead.
  • If the application stopped without closing all connections (for example calling System.exit), some committed transactions may have not been written to disk. Fixed.
  • Bugfix if index changes where logged (default is off): not all changes where logged, in some situations recovery would not work fast (but no data loss as the indexes can be rebuilt; just slower database opening). Fixed.
  • DatabaseMetaData.getTypeInfo: compatibility with HSQLDB improved.
  • Parser: quoted keywords where not allowed as table or column names. Fixed.
  • An exception was thrown in FileStoreInputStream when reading 0 bytes. But this is allowed according to the specs.
  • Performance of LIMIT was slow when the result set was very big and the number of rows was small. Fixed
  • The RunScript tool (org.h2.tools.RunScript) now uses the same algorithm to parse scripts.
  • Bugfix: it was allowed to use jdbc:h2:test;database_event_listener=Test, but only for the first connection. Now, the class name must be quoted in all cases: jdbc:h2:test;database_event_listener='Test'
  • Implemented SET LOG 0 to disable logging for improved performance (about twice as fast).
  • Implemented TRUNCATE TABLE.
  • SCRIPT: New option 'DROP' to drop tables and views before creating them.
  • Chinese translation updates.
  • It was not possible to create views on system tables. Fixed.
  • If a database can't be opened because there is a bug in the startup information (which is basically the SQL script of CREATE statements), then the database can now be opened by specifying a database event listener.
  • Parser / MySQL compatibility: in the last release, for MySQL compatibility, this syntax was supported: CREATE TABLE TEST(ID INT, INDEX(ID), KEY(ID)); But that means INDEX and KEY can't be used as column names. Changed the parser so that this syntax is only supported in MySQL mode.
  • Optimizer: now chose a plan with index even if there are no or only a few rows in a table. To avoid doing a table scan when no rows are in the table at prepare time, and many rows at execution time. Using a row count offset of 1000.
  • MERGE: new SQL command to insert or update a row. Sometimes this is called UPSERT for update or insert. The grammar is a lot simpler than what Oracle supports.
  • SCRIPT: now the CREATE INDEX statements are after the INSERT statements, to improve performance.
  • Console: Bugfix for newline in a view definition.
  • Bugfix for CONCAT with more than 2 parameters.

Version 0.9 / 2006-04-09

  • Bugfix for VARCHAR_IGNORECASE.
  • Open database: Improved the performance for opening a database if it was not closed correctly before (due to abnormal program termination or power failure).
  • Compact database: Added a sample application and documented how to do this.
  • Bugfix: the SCRIPT command created insert statements for views. Removed.
  • Implemented a way to shutdown a TCP server. From command line, run: java org.h2.tools.Server -tcpShutdown tcp://localhost:9092
  • LOG_INDEX: new option in the database URL to log changes to the index file. This is allows fast recovery from system crash for big databases. Sample URL: jdbc:h2:test;LOG_INDEX=1
  • Bugfix: using a low MAX_MEMORY_ROWS didn't work for GROUP BY queries with more groups. Fixed.
  • Improved the trigger API (provide a Connection to the Java function) and added a sample application.
  • Support setting parameters in the statement: INSERT INTO TEST VALUES(?, ?) {1: 5000, 2: 'This is a test'}. However, this feature is not documented yet as it's not clear if this is the final syntax. Currently, used in the trace feature to create sql script files instead of Java class files (in many cases, the classes just get too big and can't be compiled).
  • Support multiline statement in RUNSCRIPT.
  • Improved parser performance. Re-parsing of statements is avoided now if the database schema didn't change. This should improve the performance for Hibernate, when many CREATE / DROP statements are executed.
  • Performance improvement for LIMIT when using simple queries without ORDER BY.
  • Improve compression ratio and performance for LZF.
  • Improve performance for Connection.getReadOnly() (Hibernate calls it a lot).
  • Server mode: Increased the socket buffer size to 64 KB. This should help performance for medium size and bigger result sets. May thanks to James Devenish again!
  • Support for IPv6 addresses in JDBC URLs. RFC 2732 format is '[a:b:c:d:e:f:g:h]' or '[a:b:c:d:e:f:g:h]:port'. May thanks to James Devenish
  • Bugfix for Linked Tables: The table definition was not stored correctly (with ''driver'' instead of 'driver' and so on). Fixed. This was found by 'junheng'
  • Chinese localization of the H2 Console.
  • Bugfix: The connection was automatically closed sometimes when using a function with a connection parameter.
  • Bugfix in the Console: Auto-refresh of the object list for DROP / ALTER / CREATE statements, updatable result sets.
  • Improved support for MySQL syntax: ` is the same as ", except that the identifiers are converter to upper case. Added support for special cases of MySQL CREATE TABLE syntax.
  • Java Functions: functions can now return ResultSet. The SQL statement CALL GET_RESULT_SET(123) then returns the result set created by the function. Implemented a simple result set / result set meta data class that can be used to create result sets in an application from scratch.
  • Web Server: the command line options where ignored. Fixed. Renamed the options 'httpAllowOthers', 'httpPort', 'httpSSL' to 'web...'.
  • Oracle compatibility: CASE...END [CASE] (the last word is new). Oracle compatibility: Date addition/subtraction: SYSDATE+1, (SYSDATE-1)-SYSDATE and so on. Limited support for old Oracle outer join syntax (single column outer join, (+) must be on the far right). This works: CREATE TABLE Customers(CustomerID int); CREATE TABLE Orders(CustomerID int); INSERT INTO Customers VALUES(1), (2), (3); INSERT INTO Orders VALUES(1), (3); SELECT * FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; SELECT * FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID(+);

Version 0.9 / 2006-04-23

  • Improved Hibernate Dialect for SQuirreL DB Copy.
  • Support for UPDATE TEST SET (column [,...])=(select | expr [,...]'). For Compiere compatibility.
  • Bugfixes for SCRIPT: write sequences before tables; write CLOB and BLOB data.
  • Added PolePosition benchmark results.
  • Bugfix in the server implementation: free up the memory. The new implementation does not rely on finalizers any more. Removed finalizers, this improves the performance.
  • Implemented CROSS JOIN and NATURAL JOIN.
  • New keywords for join syntax: CROSS, NATURAL, FULL. However full outer join is not supported yet.
  • Improved documentation of the tools (Server port and so on).
  • The schema name of constraints is now automatically set to the table.
  • Compatibility for Derby style column level constraints.
  • New command ANALYZE to update the selectivity statistics. New command ALTER TABLE ALTER COLUMN SELECTIVITY to manually set the selectivity of a column.
  • Allow a java.sql.Connection parameter in the Java function (as in HSQLDB).
  • Optimizer improvements.
  • Implemented SET DB_CLOSE_DELAY numberOfSeconds to be able to delay, or disable (-1) closing the database. Default is still 0 (close database when closing the last connection).
  • After finding that HSQLDB was faster in the PolePosition test, and unsuccessful tries to improve the performance of H2 even more, the reason is finally found: H2 always closed the database when closing the last connection, but HSQLDB leaves the database open. This also explains OutOfMemory problems when testing multiple databases with PolePosition. But leaving the database open is a useful feature for some applications.
  • New system table CROSS_REFERENCES. Implemented DatabaseMetaData.getImportedKeys, getExportedKeys, getCrossReferences.
  • Performance improvements in the binary tree and other places.
  • A new aggregate function SELECTIVITY is implemented. It calculates the selectivity of a column by counting the distinct rows, but only up to 10000 values are kept in memory. SELECT SELECTIVITY(ID), SELECTIVITY(NAME) FROM TEST LIMIT 1 SAMPLE_SIZE 10000 scans only 10000 rows.
  • A new option SAMPLE_SIZE is added to the LIMIT clause to specify the number of rows to scan for a aggregated query.
  • Bugfixes for temporary tables. Implemented ON COMMIT DROP / DELETE ROWS, but not documented because it is only here for compatibility, and I would advise against using it because it is not available in most databases.
  • Improved parser performance and memory usage.

Version 0.9 / 2005-12-13

  • First public release.

Roadmap

Highest Priority

  • Improve test code coverage
  • More fuzz tests
  • Test very large databases and LOBs (up to 256 GB)
  • Test multi-threaded in-memory db access

In Version 1.1

  • Change Constants.DEFAULT_MAX_MEMORY_UNDO to 10000 (and change the docs). Test.
  • Enable and document optimizations, LOB files in directories
  • Special methods for DataPage.writeByte / writeShort and so on
  • Index organized tables CREATE TABLE...(...) ORGANIZATION INDEX (store in data file) (probably file format changes are required for rowId)
  • Change the default for NULL || 'x' to NULL
  • Change the default isolation level to read committed

Priority 1

  • RECOVER=1 should automatically recover, =2 should run the recovery tool if required
  • More tests with MULTI_THREADED=1
  • Improve performance for create table (if this is possible)
  • Test with Spatial DB in a box / JTS (http://docs.codehaus.org/display/GEOS/SpatialDBBox)
  • Document how to use H2 with PHP (generic database API)
  • Optimization: result set caching (like MySQL)
  • MVCC (Multi Version Concurrency Control)
  • Server side cursors
  • Row level locking
  • Read-only databases inside a jar (splitting large files to speed up random access)
  • System table: open sessions and locks of a database
  • Function in management db: open connections and databases of a (TCP) server
  • Fix right outer joins
  • Full outer joins
  • Long running queries / errors / trace system table
  • Migrate database tool (also from other database engines)
  • Shutdown compact
  • Optimization of distinct with index: select distinct name from test
  • Forum: email notification doesn't work? test or disable or document
  • Document server mode, embedded mode, web app mode, dual mode (server+embedded)
  • Stop the server: close all open databases first
  • SET variable { TO | = } { value | 'value' | DEFAULT }
  • Running totals: select @running:=if(@previous=t.ID,@running,0)+t.NUM as TOTAL, @previous:=t.ID
  • Support SET REFERENTIAL_INTEGRITY {TRUE|FALSE}
  • Better support large transactions, large updates / deletes: use less memory
  • Better support large transactions, large updates / deletes: allow tables without primary key
  • Support Oracle RPAD and LPAD(string, n[, pad]) (truncate the end if longer)
  • Allow editing NULL values in the Console

Priority 2

  • Support OSGi: http://oscar-osgi.sourceforge.net, http://incubator.apache.org/felix/index.html
  • System table / function: cache usage
  • Connection pool manager
  • Set the database in an 'exclusive' mode (restrict to one user at a time)
  • Add a migration guide (list differences between databases)
  • Optimization: automatic index creation suggestion using the trace file?
  • Compression performance: don't allocate buffers, compress / expand in to out buffer
  • Start / stop server with database URL
  • Rebuild index functionality (other than delete the index file)
  • Don't use deleteOnExit (bug 4513817: File.deleteOnExit consumes memory)
  • Console: add accesskey to most important commands (A, AREA, BUTTON, INPUT, LABEL, LEGEND, TEXTAREA)
  • Feature: a setting to delete the the log or not (for backup)
  • Test with Sun ASPE1_4; JEE Sun AS PE1.4
  • Test performance again with SQL Server, Oracle, DB2
  • Test with dbmonster (http://dbmonster.kernelpanic.pl/)
  • Test with dbcopy (http://dbcopyplugin.sourceforge.net)
  • Find a tool to view a text file >100 MB, with find, page up and down (like less)
  • Implement, test, document XAConnection and so on
  • Web site: meta keywords, description, get rid of frame set
  • Pluggable data type (for compression, validation, conversion, encryption)
  • CHECK: find out what makes CHECK=TRUE slow, move to CHECK2
  • Improve recovery: improve code for log recovery problems (less try/catch)
  • Log linear hash index changes, fast open / close
  • Index usage for (ID, NAME)=(1, 'Hi'); document
  • Suggestion: include jetty as Servlet Container (like LAMP)
  • Trace shipping to server
  • Performance / server mode: use UDP optionally?
  • Version check: docs / web console (using javascript), and maybe in the library (using TCP/IP)
  • Aggregates: support MEDIAN
  • Web server classloader: override findResource / getResourceFrom
  • Cost for embedded temporary view is calculated wrong, if result is constant
  • Comparison: pluggable sort order: natural sort
  • Count index range query (count(*) where id between 10 and 20)
  • Eclipse plugin
  • iReport to support H2
  • Implement missing JDBC API (CallableStatement,...)
  • Compression of the cache
  • Run H2 Console inside servlet (pass-through servlet of fix the JSP / app)
  • Groovy Stored Procedures (http://groovy.codehaus.org/Groovy+SQL)
  • Include SMPT (mail) server (at least client) (alert on cluster failure, low disk space,...)
  • Make the jar more modular
  • Drop with restrict (currently cascade is the default)
  • JSON parser and functions
  • Option for Java functions: constant/isDeterministic to allow early evaluation when all parameters are constant
  • Automatic collection of statistics (auto ANALYZE)
  • Procedural language
  • Server: client ping from time to time (to avoid timeout - is timeout a problem?)
  • Copy database: Tool with config GUI and batch mode, extensible (example: compare)
  • Document, implement tool for long running transactions using user defined compensation statements
  • Support SET TABLE DUAL READONLY
  • Don't write stack traces for common exceptions like duplicate key to the log by default
  • Setting for MAX_QUERY_TIME (default no limit?)
  • GCJ: what is the state now?
  • Convert large byte[]/Strings to streams in the JDBC API (asap).
  • Use Janino to convert Java to C++
  • Reduce disk space usage (Derby uses less disk space?)
  • Events for: Database Startup, Connections, Login attempts, Disconnections, Prepare (after parsing), Web Server (see http://docs.openlinksw.com/virtuoso/fn_dbev_startup.html)
  • Optimization: Log compression
  • Compatibility: in MySQL, HSQLDB, /0.0 is NULL; in PostgreSQL, Derby: Division by zero
  • Functional tables should accept parameters from other tables (see FunctionMultiReturn) SELECT * FROM TEST T, P2C(T.A, T.R)
  • Custom class loader to reload functions on demand
  • Test http://mysql-je.sourceforge.net/
  • Close all files when closing the database (including LOB files that are open on the client side)
  • Test Connection Pool http://jakarta.apache.org/commons/dbcp
  • Implement Statement.cancel for server connections
  • Profiler option or profiling tool to find long running and often repeated queries (using DatabaseEventListener API)
  • Function to read/write a file from/to LOB
  • Allow custom settings (@PATH for RUNSCRIPT for example)
  • Performance test: read the data (getString) and use column names to get the data
  • EXE file: maybe use http://jsmooth.sourceforge.net
  • System Tray: http://jroller.com/page/stritti?entry=system_tray_implementations_for_java
  • SELECT ... FOR READ WAIT [maxMillisToWait]
  • Automatically delete the index file if opening it fails
  • Performance: Automatically build in-memory indexes if the whole table is in memory
  • H2 Console: The webclient could support more features like phpMyAdmin.
  • The HELP information schema can be directly exposed in the Console
  • Maybe use the 0x1234 notation for binary fields, see MS SQL Server
  • KEY_COLUMN_USAGE (http://dev.mysql.com/doc/refman/5.0/en/information-schema.html, http://www.xcdsql.org/Misc/INFORMATION_SCHEMA%20With%20Rolenames.gif)
  • Support Oracle CONNECT BY in some way: http://www.adp-gmbh.ch/ora/sql/connect_by.html, http://philip.greenspun.com/sql/trees.html
  • SQL 2003 (http://www.wiscorp.com/sql_2003_standard.zip)
  • http://www.jpackage.org
  • Version column (number/sequence and timestamp based)
  • Optimize getGeneratedKey: send last identity after each execute (server).
  • Clustering: recovery needs to becomes fully automatic.
  • Date: default date is '1970-01-01' (is it 1900-01-01 in the standard / other databases?)
  • Test and document UPDATE TEST SET (ID, NAME) = (SELECT ID*10, NAME || '!' FROM TEST T WHERE T.ID=TEST.ID);
  • Support home directory as ~ in database URL (jdbc:h2:file:~/.dir/db)
  • Better space re-use in the files after deleting data (shrink the files)
  • Max memory rows / max undo log size: use block count / row size not row count
  • Index summary is only written if log=2; maybe write it also when log=1 and everything is fine (and no in doubt transactions)
  • Support 123L syntax as in Java; example: SELECT (2000000000*2)
  • Implement point-in-time recovery
  • Memory database: add a feature to keep named database open until 'shutdown'
  • Use the directory of the first script as the default directory for any scripts run inside that script
  • Include the version name in the jar file name
  • Optimize IN(...), IN(select), ID=? OR ID=?: create temp table and use join
  • LIKE: improved version for larger texts (currently using naive search)
  • Auto-reconnect on lost connection to server (even if the server was re-started) except if autocommit was off and there was pending transaction
  • The Script tool should work with other databases as well
  • Deferred integrity checking (DEFERRABLE INITIALLY DEFERRED)
  • Automatically convert to the next 'higher' data type whenever there is an overflow.
  • Throw an exception when the application calls getInt on a Long (optional)
  • Default date format for input and output (local date constants)
  • Cache collation keys for performance
  • Convert OR condition to UNION or IN if possible
  • ValueInt.convertToString and so on (remove Value.convertTo)
  • Support custom Collators
  • Document ROWNUM usage for reports: SELECT ROWNUM, * FROM (subquery)
  • Clustering: Reads should be randomly distributed or to a designated database on RAM
  • Clustering: When a database is back alive, automatically synchronize with the master
  • Standalone tool to get relevant system properties and add it to the trace output.
  • Support mixed clustering mode (one embedded, the other server mode)
  • Support 'call proc($1=value)' (PostgreSQL, Oracle)
  • HSQLDB compatibility: "INSERT INTO TEST(name) VALUES(?); SELECT IDENTITY()"
  • Shutdown lock (shutdown can only start if there are no logins pending, and logins are delayed until shutdown ends)
  • Automatically delete the index file if opening it fails
  • DbAdapters http://incubator.apache.org/cayenne/
  • JAMon (proxy jdbc driver)
  • Console: Allow setting Null value; Alternative display format two column (for copy and paste as well)
  • Console: Improve editing data (Tab, Shift-Tab, Enter, Up, Down, Shift+Del?)
  • Console: Autocomplete Ctrl+Space inserts template
  • Google Code http://code.google.com/p/h2database/issues/list#
  • Simplify translation ('Donate a translation')
  • Option to encrypt .trace.db file
  • Write Behind Cache on SATA leads to data corruption See also http://sr5tech.com/write_back_cache_experiments.htm and http://www.jasonbrome.com/blog/archives/2004/04/03/writecache_enabled.html
  • Functions with unknown return or parameter data types: serialize / deserialize
  • Test if idle TCP connections are closed, and how to disable that
  • Try using a factory for Row, Value[] (faster?), http://javolution.org/, alternative ObjectArray / IntArray
  • Auto-Update feature for database, .jar file
  • ResultSet SimpleResultSet.readFromURL(String url): id varchar, state varchar, released timestamp
  • RANK() and DENSE_RANK(), Partition using OVER()
  • ROW_NUMBER (not the same as ROWNUM)
  • Partial indexing (see PostgreSQL)
  • BUILD should fail if ant test fails
  • http://rubyforge.org/projects/hypersonic/
  • DbVisualizer profile for H2
  • Add comparator (x === y) : (x = y or (x is null and y is null))
  • Try to create trace file even for read only databases
  • Add a sample application that runs the H2 unit test and writes the result to a file (so it can be included in the user app)
  • Count on a column that can not be null would be optimized to COUNT(*)
  • Table order: ALTER TABLE TEST ORDER BY NAME DESC (MySQL compatibility)
  • Backup tool should work with other databases as well
  • Console: -ifExists doesn't work for the console. Add a flag to disable other dbs
  • Maybe use Fowler Noll Vo hash function
  • Improved full text search (supports LOBs, reader / tokenizer / filter).
  • Performance: Update in-place
  • Check if 'FSUTIL behavior set disablelastaccess 1' improves the performance (fsutil behavior query disablelastaccess)
  • Remove finally() (almost) everywhere
  • Java static code analysis: http://pmd.sourceforge.net/
  • Java static code analysis: http://www.eclipse.org/tptp/
  • Java static code analysis: http://checkstyle.sourceforge.net/
  • Compatibility for CREATE SCHEMA AUTHORIZATION
  • Implement Clob / Blob truncate and the remaining functionality
  • Maybe close LOBs after closing connection
  • Tree join functionality
  • Support alter table add column if table has views defined
  • Add multiple columns at the same time with ALTER TABLE .. ADD .. ADD ..
  • Support trigger on the tables information_schema.tables and ...columns
  • Add H2 to Gem (Ruby install system)
  • API for functions / user tables
  • Order conditions inside AND / OR to optimize the performance
  • Support linked JCR tables
  • Make sure H2 is supported by Execute Query: http://executequery.org/
  • Read InputStream when executing, as late as possible (maybe only embedded mode). Problem with re-execute.
  • Full text search: min word length; store word positions
  • FTP Server: Implement a client to send / receive files to server (dir, get, put)
  • FTP Server: Implement SFTP / FTPS
  • Add an option to the SCRIPT command to generate only portable / standard SQL
  • Test Dezign for Databases (http://www.datanamic.com)
  • Fast library for parsing / formatting: http://javolution.org/
  • Updatable Views (simple cases first)
  • Improve create index performance
  • Support ARRAY data type
  • Implement more JDBC 4.0 features
  • H2 Console: implement a servlet to allow simple web app integration
  • Option to globally disable / enable referential integrity checks
  • Support ISO 8601 timestamp / date / time with timezone
  • Support TRANSFORM / PIVOT as in MS Access
  • ALTER SEQUENCE ... RENAME TO ... (http://www.postgresql.org/docs/8.2/static/sql-altersequence.html)
  • SELECT * FROM (VALUES (...), (...), ....) AS alias(f1, ...)
  • Support updatable views with join on primary keys (to extend a table)
  • File_Read / File_Store funktionen: FILE_STORE('test.sql', ?), FILE_READ('test.sql')
  • Public interface for functions (not public static)
  • Index usage for IN(...), IN ARRAY(?), and IN ARRAY RANGES(?, ?); support ARRAY in JDBC API (variable size)
  • Change LOB mechanism (less files, keep index of lob files, point to files and row, delete unused files earlier)
  • Autocomplete: if I type the name of a table that does not exist (should say: syntax not supported)
  • Autocomplete: schema support: "Other Grammar","Table Expression","{[schemaName.]tableName | (select)} [[AS] newTableAlias]
  • Functions: options readonly, deterministic
  • Document FTP server, including -ftpTask option to execute / kill remote processes
  • Add jdbcx to the javadocs
  • Shrink the data file without closing the database (if the end of the file is empty)
  • Delay reading the row if data is not required
  • Eliminate undo log records if stored on disk (just one pointer per block, not per record)
  • User defined aggregate functions
  • System property for base directory (h2.baseDir) in embedded mode
  • Feature matrix like here: http://www.inetsoftware.de/products/jdbc/mssql/features/default.asp.
  • Updatable result set on table without primary key or unique index
  • Use LinkedList instead of ArrayList where applicable
  • Optimization: (A=B AND B=C) > (A=B AND B=C AND A=C)
  • Support % operator (modulo)
  • Large subqueries: close them when the main query is closed, not earlier (so result can be reused)
  • Support 1+'2'=3, '1'+'2'='12' (MS SQL Server compatibility)
  • Support nested transactions
  • Add a benchmark for big databases, and one for many users
  • Compression in the result set (repeating values in the same column)
  • Improve command line consistency (+/- options, or true false options)
  • Allow to use the catalog name in statements: [[catalog.]schema.]object
  • Support curtimestamp (like curtime, curdate)
  • Support ANALYZE {TABLE|INDEX} tableName COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
  • Support Sequoia (Continuent.org)
  • Dynamic length numbers / special methods for DataPage.writeByte / writeShort / Ronni Nielsen
  • Pluggable tracing system, ThreadPool, (AvalonDB / deebee / Paul Hammant)
  • Recursive Queries (see details)
  • Use index on boolean flag (see details)
  • Add build for embedded database only
  • Release locks (shared or exclusive) on demand
  • Support catalog names
  • Add object id to metadata tables
  • Support OUTER UNION
  • Support Parameterized Views (similar to CSVREAD, but using just SQL for the definition)
  • Implement a command line SQL utility similar to HenPlus: http://henplus.sourceforge.net
  • A way (JDBC driver) to map an URL (jdbc:h2map:c1) to a connection object
  • Build script for the embedded functionality only (h2embedded.jar)
  • Option for SCRIPT to only process one or a set of tables, and append to a file
  • Linked schema using CSV files: one schema for a directory of files; support indexes for CSV files
  • Support using a unique index for IS NULL (including linked tables)
  • Support linked tables to the current database
  • Support dynamic linked schema (automatically adding/updating/removing tables)
  • Compatibility with Derby: VALUES(1), (2); SELECT * FROM (VALUES (1), (2)) AS myTable(c1)
  • Compatibility: # is the start of a single line comment (MySQL) but date quote (Access). Mode specific
  • Run benchmarks with JDK 1.5, JDK 1.6, java -server
  • Optimizations: Faster hash function for strings, byte arrays, big decimal
  • Improve trace feature: add replay functionality
  • DatabaseEventListener: callback for all operations (including expected time, RUNSCRIPT) and cancel functionality
  • H2 Console / large result sets: use 'streaming' instead of building the page in-memory
  • Benchmark: add a graph to show how databases scale (performance/database size)
  • Implement a SQLData interface to map your data over to a custom object
  • Extend H2 Console to run tools (show command line as well)
  • Make DDL (Data Definition) operations transactional
  • Sequence: add features [NO] MINVALUE, MAXVALUE, CACHE, CYCLE
  • Allow execution time prepare for SELECT * FROM CSVREAD(?, 'columnNameString')
  • Support multiple directories (on different hard drives) for the same database

Not Planned

  • HSQLDB (did) support this: select id i from test where i>0 (other databases don't)
  • String.intern (so that Strings can be compared with ==) will not be used because some VMs have problems when used extensively

Supporters

Many thanks for those who helped by finding and reporting bugs, gave valuable feedback, spread the word and have translated this project. Also many thanks to the donors who contributed via PayPal:
  • Florent Ramiere, France
  • Pete Haidinyak, USA
  • Jun Iyama, Japan
  • Antonio Casqueiro, Portugal