Chronological Change List This file is the accumulated list of changes in the course of development of hsqldb 1.8.0, including point releases, in reverse chronological order. Fred Toussi (fredt@users.sourceforge.net) Maintainer, HSQLDB Project 2007.09.06 1.8.0.8.1 Fixed regression that impacted performance of system table selects and JDBC DatabaseMetaData calls that use system tables. 2007.08.28 1.8.0.8 Fixed issue with some text table sources. Fixed issue caused after adding columns to tables used in certain view definitions Added sych call before closing .script files. Minor bug fixes New implementation of HsqlTimer to allow proper database shutdown while used within an application server or similar. Improvements in TEXT TABLE source file handling. If source file is unavailable at startup, the source specification is retained but its usage is turned off. An SQL warning is added to the first session in the event. Added SET TABLE SOURCE {ON | OFF} enables/disables the usage of the source file. Some relaxation of use of LIMIT in subqueries Improvements to SqlTool and DatabaseManager tools by Blaine Simpson. 2006.09.27 1.8.0.7 Fixed regression in 1.8.0.6 that prevented reuse of empty spaces in the .data file. Fixed regression that prevented setting the textdb.allow_full_path property 2006.09.22 1.8.0.6 Fixed issue where missing dbname.0 in server properties files was not ignored Fixed issue where a crash during logging of an update query may result in incomplete logging and recovery Fixed issue where a PK defined after another constraint would result in wrong constraint name Fixed issue with incorrect evaluation of null values in CASE WHEN in certain cases Fixed issue where ResultSet column class names were misreported for functions returning primitive types 2006.07.27 1.8.0.5 (3 fixed issues affecting durability) Fixed serious reported issue in some JVM's zip libraries by adding a workaround. Fixed serious issue when cache_scale and cache_size_scale are small but very large rows are inserted in cached tables. Fixed bug where an automatic rollback of an UPDATE statement was not logged. Fixed bug with nested JDBC escapes. Fixed bug with logging of schema definition. Fixed bug where DROP TABLE was allowed even when a view used the table. Fixed bug where DISTINCT together with some GROUP BY lists were not allowed. Fixed bug where Server.shutdown() threw spurious NPE. Improved .data file persistence classes with better errror logging. Improved client/server retrieval speed of large result sets. Column declarations of TIME(0) are now accepted. Named parameter in the form of :name are allowed in prepared statements. 2006.04.16 1.8.0.4 General improvements to all utilities, including SQLTool, DatabaseManager, etc. General improvements to build.xml. Improvement to fail-safe persistence by saving the .properties file first as a new file, before renaming. Fixed bugs with text tables that use quoted source files and have a heading row. Fixed bug which prevented dropping a column before an indexed column. Fixed bug where DISTINCT was not allowed with SQL functions (BT 1400344) Fixed bug where PreparedStatement calls were allowed with some unbound variable (Bug Tracker 1399324) but commented out to allow compatibility with earlier versions (see org.hsqldb.jdbc.jdbcPreparedStatement). VARCHAR_IGNORECASE size specification is now enforeced. Fixed bugs where ORDER BY did not work in some cases (BT 1383218, BT 1344895). Fixed bug where IFNULL was used with a function (BT 1315834). Fixed bug where creation of a new table was not allowed if the old table with the same name had been renamed. Fixed bug where SYSTEM_INDEXINFO could not be read. Fixed bug to allow running with JDK 1.1 when compiled for this target. Fixed bug with COALESCE, et containing an aggregate function (BT 1242448). Fixed bug with ResultSet.getTimestamp(int, Clandar). Fixed bug with access non-admin access to tables was allowed when using PreparedStatement objects. Added org.hsqldb.Library function, getDatabaseFullProductVersion to return the full version string, including the 4th digit (e.g 1.8.0.4). 2006.01.08 Fixed bug in WebServer Added support for remote startup of databases in Server and WebServer 2005.12.22 Fixed bug where ON DELETE SET DEFAULT was accepted without a matching DEFAULT value Fixed bug where column renaming was allowed in tables accessed in views Fixed bug where TRUE or FALSE is used as search condition as WHERE .. AND FALSE Fixed bug where dropping TEMPORARY tables was not logged Fixed bug where negative values for IDENTITY columns were treated as positive 2005.11.02 1.8.0.3 Bug fixes Fixed bug with tables formed with SELECT .. INTO that did not allow creation of indexes Fixed bug with scripting of some VIEW definitions with quoted column names Fixed bug in generation of some system tables with multi schema databases Fixed bug with access to network result set data when UNION with different column types is used Fixed bug with scripting of some TIMESTAMP values 2005.08.18 1.8.0.2 Bug fixes and enhancements: Fixed bugs with GLOBAL TEMPORARY tables with PRESERVE ROWS or DELETE ROWS options. Fixed bug with DESC ignored in ORDER BY when a correlation name was used to specify the column. Fixed ResultSet.getTimestamp() and PreparedStatement.setTimestam() bug with null timestamp parameters. Enhancement to allow string contatenation in LIKE predicates. Fixed two JDK 1.1.x incompatibilities. Fixed issue with some SET TABLE SOURCE command. Fixed issue with DDL not being logged immediatedly with WRITE_DELAY set to 0. Fixed issue with the combination of CASE WHEN (or COALESCE, etc.) with aggregate functions when the table contains no rows and the aggregate function returns null. Fixed issue with ALTER SCHEMA RENAME TO New features Support for RIGHT OUTER JOIN and CROSS JOIN in select statements ALTER TABLE ALTER COLUMN SET [NOT] NULL Added DAY_OF_WEEK as an alternative for EXTRACT(XXX FROM ) 2005.07.11 1.8.0.1 Bug fixes and enhancements: Correlated subqueries with UNION (and other set operators) are allowed. LEFT OUTER JOIN join statement can contain NOT expressions. Set functions, SOME() and EVERY() implementation corrected. Quoted empty strings in TEXT table sources are no-longer interpreted as null. CHECKPOINT DEFRAG issued while there are uncommitted transactions works correctly. build.xml updated to fix issue with JDK 1.3 build 2005.07.01 1.8.0.0 First release. 2005.06.16 1.8.0.RC12 Introduced new connection property shutdown, which, if true on the first connection, will cause the engine to perform a SHUTDOWN on the database when the last connection is closed. Enhancements to TEXT table implementation to maintain the commit status of rows during recovery from an abrupt shutdown. Uncommitted rows will not appear in TEXT tables. 2005.05.17 1.8.0.RC10 SCHEMAS Support for SQL schemas. Each database can contain multiple schemas. The following commands have been introduced: CREATE SCHMEA AUTHORIZATION DBA DROP SCHEMA {CASCADE | RESTRICT} ALTER SCHEMA RENAME TO SET SCHEMA Initially, the default user schema will be created with the name PUBLIC. This schema can be renamed or dropped. When the last user schema has been dropped, an empty default schema with the name PUBLIC is created. System tables all belong to INFORMATION_SCHEMA. To access system tables, either SET SCHEMA INFORMATION_SCHEMA should be used once or they should be referred to by fully specified names, e.g. INFORMATION_SCHEMA.SYSTEM_TABLES Similarly all database objects apart from columns can be referenced with fully qualified schema names. The CREATE SCHEMA command can be followed by other CREATE and GRANT commands without an intervening semicolon. All such commands are executed in the context of the newly created schema. A semicolon terminates an extended CREATE SCHEMA command. ROLES Support for SQL standard roles. CREATE ROLE GRANT ... TO REVOKE ... FROM GRANT TO DROP ROLE The GRANT and REVOKE commands are similar to those used for granting permissions on different objects to USER objects. A role can then be granted to or revoked from different users, simplifying permission management. GLOBAL TEMPORARY TABLES The implementation of temporary tables has changed to conform to SQL standards. The definition of a GLOBAL TEMPORARY table persists with the database. When a session (JDBC Connection) is started, an empty instance of the table is created. Temporary tables can be created with (the default) ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS added to the end of table definition. With ON COMMIT PRESERVE ROWS, the table contents are not cleared when the session commits. In both cases, the contents are cleared when the session is closed. SCHEMA MANIPULATION COMMANDS Several schema manipulation commands have been enhanced. Tables, views and sequences can be dropped with the CASCADE option. This silently drops all tables and views that reference the given database object. DROP TABLE [IF EXISTS] [CASCADE]; DROP VIEW [IF EXISTS] [CASCADE]; DROP SEQUENCE [IF EXISTS] [CASCADE]; ALTER TABLE
DROP COLUMN now silently drops any primary key or unique constraint declared on the column (excluding multi-column constraints). ALTER TABLE
ADD [COLUMN] now accepts primary key and identity attributes. COLUMN MANIPULATION Support for converting type, nullability and identity attributes of a column ALTER TABLE
ALTER COLUMN has the same syntax as normal column definition. The new column definition replaces the old one, so it is possible to add/remove a DEFAULT expression, a NOT NULL constraint, or an IDENTITY definition. No change to the primary key is allowed with this command. - The column must be already be a PK column to accept an IDENTITY definition. - If the column is already an IDENTITY column and there is no IDENTITY definition, the existing IDENTITY attribute is removed. - The default expression will be that of the new definition, meaning an existing default can be dropped by ommission, or a new default added. - The NOT NULL attribute will be that of the new definition, similar to above. - Depending on the conversion type, the table may have to be empty for the command to work. It always works when the conversion is possible in general and the individual existing values can all be converted. A different syntax can be used to change the next value for an IDENTITY column: ALTER TABLE
ALTER [COLUMN] RESTART WITH ALL and ANY expressions Full support for ALL(SELECT ....) and ANY(SELECT ....) with comparison o perators: =, >, <, <>, >=, <= Example: SELECT ... WHERE >= ALL(SELECT ...) LIMIT and OFFSET New alternative syntax for LIMIT at the end of the query: LIMIT L [OFFSET O] It is now possible to use LIMIT combined with ORDER BY in subqueries and SELECT statements in brackets that are terms of UNION or other set operations. An ORDER BY or LIMIT clause applies to the complete result of the UNION and other set operations or alternatively to one of its components depending on how parentheses are used. In the first example the scope is the second SELECT, while in the second query, the scope is the result of the UNION. SELECT ... FROM ... UNION (SELECT ... FROM ... ORDER BY .. LIMIT) SELECT ... FROM ... UNION SELECT ... FROM ... ORDER BY .. LIMIT Support for ORDER BY, LIMIT and OFFSET in CREATE VIEW statements COLLATIONS Added support for collations. Each database can have its own collation. The SQL command below sets the collation from the set of collations in the source for org.hsqldb.Collation: SET DATABASE COLLATION Once this command has been issued, the database can be opened in any JVM and will retain its collation. The property sql.compare_in_locale=true is no longer supported. If the line exists in a .properties file, it will switch the database to the collation for the current default. ENHANCEMENTS Support for the res: connection protocol (database files in a jar) has been extended to allow CACHED tables. Support for correct casting of TIME into TIMESTAMP, using CURRENT_DATE Symmetrical handling of setTimestamp() and getTimestamp() with Calendar parameters. BUG FIX Fixed bug where two indexes where switched, causing wrong results in some queries in the following circumstances: CREATE TABLE is executed. ALTER TABLE ADD FORIEGN KEY is used to create an FK on a different table that was already present when the first command was issued. CREATE INDEX is used to add an index. Data is added to the table. Database is shutdown. Database is restarted. At this point the indexes are switched and queries that use either of the indexes will not return the correct set of rows. BUG FIXES Fixed reported bug with NOT LIKE and null values Fixed bug with OR conditions in OUTER JOIN Fixed bug with duplicated closing of prepared statements Fixed bug with scalar subqueries. If the result of a scalar subquery is empty (no rows returned), the value is NULL. Fixed various parsing anomalies where SQL commands were accepted when quoted, double-quoted or prefixed with an identifier, or identifiers were accepted in single quotes. Example of a command that is no-longer tolerated: Malformed query: MY. "SELECT" ID FROM 'CUSTOMER' IF.WHERE ID=0; Actual query: SELECT ID FROM CUSTOMER WHERE ID=0; Fixed bug that prevented adding a primary key with no name to a table. Fixed bug that resulted in an error when the type of a column was changed to the same type as before. E.g. ALTER TABLE
ALTER COLUMN INTEGER BUG FIX Fixed bug in ROLLBACK. If several updates were made to a row of a table that has no primary key, then ROLLBACK was issued, there would remain multiple versions of the row. E.g. CREATE TABLE T (I INT); INSERT INTO T VALUES (0); SET AUTOCOMMIT FALSE; UPDATE T SET I=1 WHERE I = 0; UPDATE T SET I=2 WHERE I = 1; UPDATE T SET I=3 WHERE I = 2; UPDATE T SET I=4 WHERE I = 3; ROLLBACK After the rollback the table would contain more than one row. 2005.03.02 1.8.0.RC9 Fixed unreported bug in recovery when first column contains null. Fixed unreported issue with possible PK and FK name duplication Fixed reported bug in recovery when there is no primary key. Fixed reported issue with deleting self referencing row (with foreign key). Fixed reported issue with illegal user names Fixed jdk 1.3 compilation issues Applied submitted patch to Server Applied submitted patch for setting the default table type when CREATE TABLE is used. The connection property, hsqldb.default_table_type=cached will set the default to CACHED tables, or the SET PROPERTY command can be used. Values, "cached" and "memory" are allowed. The database property sql.enforce_strict_size=true has now a wider effect Previously CHAR /VARCHAR lengths could be checked and padding performed only when inserting / updating rows. Added support for CHAR(n), VARCHAR(n), NUMERIC(p,s) and DECIMAL(p,s) including SQL standard cast and convert semantics. Explicit CAST(c AS VARCHAR(2)) will always truncate the string. Explicit CAST(n AS NUMERIC(p,s)) will always perform the conversion or throw if n is out of bounds. All other implicit and explicit conversions to CHAR(n) and VARCHAR(n) are subject to SQL standard rules. Bob has improved support for text tables. Newline support in quoted fields is now complete. It is now possible to save and restore the first line header of a CSV file when ignore_first=true is specified. When a text table is created with a new source (CSV) file, and ignore_first=true has been specified the following command can be used to set a user defined string as the first line: SET TABLE SOURCE HEADER . When embedded in OOo, several defaults and properties are set automatically: CREATE TABLE ... defaults to CREATE CACHED TABLE ... hsqldb.cache_scale=13 hsqldb.cache_size_scale=8 hsqldb.log_size=10 SET WRITE DELAY 2 sql.enforce_strict_size=true hsqldb.first_identity=1 2005.02.11 1.8.0.RC8 Fixed minor bugs Added public shutdown() method to Server. A new application log has been introduced as an optional feature. The property/value pair "hsqldb.applog=1" can be used in the first connection string to log some important messages. The default is "hsqldb.applog=0", meaning no logging. A file with the ending ".app.log" is generated alongside the rest of the database files for this purpose. In the current version only the classes used for file persistence (different in OpenOffice.org), plus any error encountered while processing the .log file after an abnormal end is logged. Bob Preston has updated the text table support to accept new line characters inside quoted strings. It is still not possible to create / modify a row containing a new line inside the engine. This will be a simple upgrade and will be included in the next RC. Note that the JDBC driver and the engine for 1.8.0 cannot be mixed with those of earlier versions in client/server setup. Check your classpaths and use the same version of the engine for both client and server. 2005.01.28 1.8.0.RC7 Fixed minor bugs 2005.01.24 1.8.0.RC5 Issues with SET CHECKPOINT DEFRAG fixed New property for larger data file limits is introduced. Once set, the limit will go up to 8GB. The property can be set with the following SQL command only when the database has no tables (new database). SET PROPERTY "hsqldb.cache_file_scale" 8 To apply the change to an existing database, SHUTDOWN SCRIPT should be performed first, then the property=value line below should be added to the .properties file before reopening the database: hsqldb.cache_file_scale=8 It is now possible to add or drop a primary key. An existing primary key that is to be removed should not be referenced in a FOREIGN KEY constraint. If a table has an IDENTITY column, removing a primary key will remove the identity attribute of the column but leave the actual data. When adding a primary key, a NOT NULL constraint is automatically added to the column definitions. The table data for the columns of a newly declared primary key should not contain null values. ALTER TABLE ADD CONSTRAINT PRIMARY KEY(collist); ALTER TABLE DROP CONSTRAINT ; ALTER TABLE DROP PRIMARY KEY; // alternative syntax 2005.01.20 1.8.0.RC4 More minor bug fixes ResultSetMetaData reports identical precision/scale in embedded and client/server modes 2005.01.16 1.8.0.RC3 Regression bug fixes. New property allows a CHECKPOINT DEFRAG to be performed automatically whenever CHECKPOINT is performed internally or via a user command. SET CHECKPOINT DEFRAG n The parameter n is the megabytes of abandoned space in the .data file. When a CHECKPOINT is performed either as a result of the .log file reaching the limit set by "SET LOGSIZE m", or by the user issuing a CHECKPOINT command, the amount of space abandoned during the session is checked and if it is larger than n, a CHECKPOINT es. New property allows a CHECKPOINT DEFRAG to be performed automatically whenever CHECKPOINT is performed internally or via a user command. SET CHECKPOINT DEFRAG n The parameter n is the megabytes of abandoned space in the .data file. When a CHECKPOINT is performed either as a result of the .log file reaching the limit set by "SET LOGSIZE m", or by the user issuing a CHECKPOINT command, the amount of space abandoned during the session is checked and if it is larger than n, a CHECKPOINT DEFRAG is performed instead of a checkpoint. 2005.01.14 1.8.0.RC2 Regression bug fixes. 2005.01.10 1.8.0.RC1 Allows embedding into OpenOffice.org Rewrite of log and cache handling classes, including: New deleted block manager with more effective deleted block reuse. Faster log processing after an abnormal termination. Better checks when maximum data file size is reached. Better recovery when maximum data file size is reached. Changes to row access to use iterators. More code clarity. Various refactorings and new package for improved modularity. Enhancements to DatabaseManagerSwing In all other respects, 1.8.0.RC1 is identical to 1.7.3.1