Chronological Change List This file is the accumulated list of changes in the course of development of hsqldb 1.7.2 in reverse chronological order. A list of changes by subject area is in changelog_1_7_2.txt. Fred Toussi (fredt@users.sourceforge.net) 2004.12.24 UPDATED RELEASE 1.7.2.11 Fixed NPE error when functions in subqueries. Added support for STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, EVERY and SOME set functions. Fixed potential issues with logging DELETE statements for tables with multi-key primary keys. 2004.12.05 UPDATED RELEASE 1.7.2.10 Fixed minor type conversion bugs. Fixed JDK 1.1 compile issue Fixed minor bug relating to server thread startup 2004.11.25 UPDATED RELEASE 1.7.2.8 Fixed bug LIMIT and TOP with UNION Fixed bug running Database Manager as applet Enhancement to allow a table with a label used in DELETE and UPDATE Enhancement to reduce memory use when restoring long log with too many connections Enhancement to persist values set by SET LOGSIZE Minor internal enhancements and refactoring 2004.10.28 UPDATED RELEASE 1.7.2.7 Fixed new bug with TEXT TABLES without primary key 2004.10.25 UPDATED RELEASE 1.7.2.6 Fixed new bug with DATETIME literal default values 2004.10.22 UPDATED RELEASE 1.7.2.5 Fixed bugs OUTER support. Fixed bug that resulted in slowdown when JDBC metadata calls are called many times. Fixed bug related to Statement.setMaxRows(int). The maximum row count will no longer apply to result sets returned and used internally. Fixed bug to allow ResultSetMetaData methods to be used inside stored procedures. Fixed bugs related to some OUTER queries. Performance improvements to some operations on CACHED tables. Improved compatibility with older database files, allowing old style DEFAULT clauses to compile. Improvements to startup after a machine crash. The .log file is now read only until the first malformed line. Corrections to build scripts and #idfef blocks to allow jars to be compiled to run under JDK 1.1. 2004.08.26 UPDATED RELEASE 1.7.2.4 Fixed bug in data file enlargement. Fixed bug where temp tables were removed after another connection closed. 2004.08.12 UPDATED RELEASE 1.7.2.3 Fixed bug preventing the use of hsqldb.files_read_only property Minor enhancements allowing use of SELECT ALL and COUNT(ALL colname) etc. Corrections to the documentation 2004.07.25 UPDATED RELEASE 1.7.2.2 Fixed bug that prevented ALTER TABLE ADD FOREIGH KEY, ADD UNIQUE and ADD CHECK (unnamed constraints) from executing. Fixed excessive logging of SEQUENCE values. 2004.07.20 UPDATED RELEASE 1.7.2.1 Fixed bug where a foreign key with ON DELETE CASCADE action on a table masked other foreign keys on the table and resulted in completion of the cascading action when referential integrity was violated. 2004.07.15 RC_6e INITIAL RELEASE 1.7.2 Enhancements to ALTER TABLE ADD COLUMN and DROP COLUMN. Fixed bug with closed HTTP connections. Fixed bug with large .data files switching to random access from nio. 2004.06.28 RC_6d The implemntation of UNION, EXCEPT and INTERSECT set operations has been rewritten to comply with SQL standards. When multiple set operations are present in a query, they are now evaluated from left to right, with INTERSECT taking precedence over the rest. It is now possible to use parentheses around multiple (or single) select statements with set operations to change the order of evalation.E.g.: ((SELECT * FROM A EXCEPT SELECT * FROM B) INTERSECT SELECT * FROM D UNION SELECT * FROM E) Timestamp, date and time values in TEXT TABLE source files are treated more leniently, allowing spaces before values. 2004.06.19 RC_6c Fixed bug in default value definition. Fixed bug in getURL() for internal connections Fixed bug in evaluation of BOOLEAN values Fixed bug in Statement.executeBatch() Improvements in CHECKPIONT DEFRAG Improvements in nio .data file handling Added a procedural language to SqlTool. Made error handling and control more robust. Implemented several new features. Updated documentation 2004.05.31 RC_6b Fixed CHECKPOINT DEFRAG bug with empty tables Fixed ORDER BY with HAVING Fixed SELECT .. INTO table issue with outer joins etc. Fixed issue with NOT used in WHERE clause of OUTER select Fixed issue with DAYOFWEEK() etc. library calls with NULL parameter Fixes for TRIGGER thread termination Fixes for minor bugs Updated documentation 2004.05.18 RC_6a Fix for two bugs, some doc updates, etc. 2004.05.17 RC_6 Final Release Candidate This is the final release candidate for 1.7.2. More tests are required to verify the improvements. Please perform tests and report any issues. Testers are needed for build scripts with different JVM's using both ANT and batch builds. Documentation has been updated and is available in HTML and PDF formats. More bug fixes to engine and JDBC classes. Bug fixes and minor enhancements to Database Manager and Transfer tools. Database Manager Swing table view is sortable. Fixed bug with different values in one row with multiple CURRENT_TIMESTAMP or NOW() calls. Introduction of CURRENT_USER, can be used as column default value as well. CURRENT_TIMESTAMP, NOW, TODAY etc. should not be quoted when used as column default values. Improvements to PreparedStatement set stream methods. Improvements to Clob and Blob implementations. Much of the UNIX init script redesigned. Now supports TLS and WebServer. 2004.05.01 Small improvements in different areas CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP and their aliases can now be used in views and prepared statements. Some bug fixes and updates to utility programs. Improvements to cascading update operations, allowing full capability with FK's referencing the same table. Improved parametric INSERT .... SELECT. CHECKPOINT DEFRAG now works with nio mode .data files. DATEDIFF function has been added. 2004.04.01 RC_5 Some bug fixes. 2004.03.22 RC_4 Included hsqldb.jar has been compiled with JDK 1.4 and will not work with JRE 1.3.x or earlier. Please recompile with JDK 1.3 for use with this or earlier JDK's. Improvements to ALTER TABLE when VIEWS reference the table Enhancements to Server Bug fixes for: BIGINT values in IN predicate value lists. Creation of self ref foreign keys. Corrupted log file with SET AUTOCOMMIT SET LOGSIZE Bug fixes for LIKE with default local and ALTER TABLE with CHECK 2004.03.13 RC_3 Various bug fixes. 2004.02.22 RC_2 Release Candidate 2 for HSQLDB version 1.7.2 includes almost all of the features of the release version. It is possible that by the time of the release, a small number of minor features will be added or changed. One area that is likely to change is the SQL_STATE values for errors returned by JDBC. Changes since RC_1 Constraint names must be unique within each database. Refactoring groups of classes into new packages. This change is internal and does not affect the use of hsqldb. New SqlTool and SqlFile utilities by Blaine Simpson. See hsqlSqlTool.html for details. Support for new system tables for constraints, views and sequences. Support for increments in GENERATED BY DEFAULT AS IDENTITY(START WITH , INCREMENT BY >) Support for table aliases in UPDATE BOOLEAN is now the default SQL type for boolean values. BIT is treated as an alias for BOOLEAN in table definition and queries. Library function CURTIME() changed to return CURRENT_TIME instead of CURRENT_TIMESTAMP Added logging for sequence generation Bug Fixes since RC_1 Values for BOOLEAN expressions containing AND, OR, LIKE and used in columns returned by queries are now correct. Fixed issue with single value selects in UPDATE and SELECT statements. Fixed issue with Turkish Locale. Fixed issue with compiled statements. Added mutable object cloning and serialization on PrepredStatement parameter entry points for in-process connections. Isolates the stored objects (DATETIME, BINARY and OTHER) from subsequent changes made to the copy used for setting the parameter. To do same for Result. More extensive error message when internal asserts fail. Various other bug fixes. 2003.12.28 RC_1 Release Candidate 1 for HSQLDB version 1.7.2 includes most of the features of the release version. It is likely that by the time of the release, a small number of features will be added to the engine. One area that is likely to change is the SQL_STATE values for errors returned by JDBC. Changes since ALPHA_T Added referential integrity checks when a foreign key is added to a populated table. support for SQL style SEQUENCE objects. support for DROP INDEX IF EXISTS Changes to SET PROPERTY command with better error checking. Added support for LIMIT ? ? and TOP ? (prepared statement) Fixed reported bugs including order by with UNION. Added support for aggregate functions in CASEWHEN, IFNULL, etc. Added support for aggregate functions as arguments to non-aggregate functions. Views and subqueries can appear in a view definition. 2003.10.26 HSQLDB 1.7.2 ALPHA_T Improved index use for multiple key joins. 2003.10.24 Fixed some reported and unreported bugs. View definitions can now have a column name list: CREATE VIEW [(, ...)] AS SELECT .... 2003.10.21 HSQLDB 1.7.2 ALPHA_S Fixed name resolution issues in some IN (SELECT ...) and EXISTS (SELECT ...) queries. Optimisation of these queries when not correlated. Speed optimisation of joins with views and subqueries. Fixed LIKE parameter bug. Support for CHECK constraints that apply to the modified/inserted row only. This can be added via ALTER TABLE ADD CHECK() or in table definition. Fixed the DATETIME normalisation issues. Fixed ResultSet.isAfterLast() issue. Reverted column name case in ResultSet methods to JDBC compliance. 2003.10.12 HSQLDB 1.7.2 ALPHA_R This release features full support for prepared statements with parameters, including those in IN lists. Query processing has been extensively enhanced in this release and better standards compliance has been achieved. Major implications of the recent changes are as follows: - Columns in correlated subqueries are resolved independently first. If there is an unresolved column, then the surrounding context is used to resolve it. This is opposite the resolution order previously enforced in the engine. - Some ambiguities and errors in ORDER BY clauses are now caught. - UNION and other set queries accept only one ORDER BY clause at the end. In this clause, only column indexes are allowed as sort specification e.g.: SELECT .. UNION SELECT .. ORDER BY 1 DESC, 5, 3 - Outer join conditions can now include most comparison operators, as well as OR logical operators , e.g LEFT OUTER JOIN atable ON a=b AND c>d OR a=2 ... Illegal forward table references are no longer allowed in join conditions. There are many other small improvements and fixes, including: - IN value lists can now contain column values or expressions. See TestSelfQueries.txt for an example. - LIKE has been optimised when possible. - COALESCE() can now take several arguments 2003.09.24 HSQLDB 1.7.2 ALPHA_Q The problem with PreparedStatement parameters in IN lists still remains to be fixed. This release includes some bug fixes and improvements. In the /doc directory there is a new how-to document on using HSQLDB in OpenOffice.org. HSQLDB 1.7.2 ALPHA_P Apart from the bugs and limitations of PreparedStatement, all the bugs reported since the release of ALPHA_O have been fixed in this alpha version. Some minor enhancements have been made to increase compatibility with OpenOffice.org applications. In addition, it is now possible to execute triggers in the main execution thread. This allows uses for triggers that were not possible before. HSQLDB 1.7.2 ALPHA_O Due to the large number of small bug fixes since the release of ALPHA_N, a new ALPHA_0 version is now released. Please see the bug tracker for a list of some of the bugs that have been fixed. This version does not yet support prepared statements with IN(?,?) parameters. Please report any bugs or suggestions through the normal channels. Bugs with test cases should be posted to the bug tracker on the HSQLDB Sourceforge site. A new JDBC connection property "toupper_column_name" can be used when the application program submits lowercase / mixedcase column names to Result.getXXX(String column) methods when the names are in fact UPPERCASE as they were not quoted when the table was created. The default value is false. See related documentation in hsqldbAdvancedGuide.html on how to set this property value. (this property was subsequently dropped) The rest of the information for ALPHA_N below applies to this version too. HSQLDB 1.7.2 ALPHA_N ALPHA_N is the ultimate ALPHA version for the 1.7.2 release cycle. Known deficiencies and reported bugs will be addressed for the first Release Candidate to be issued in the next few weeks. Please report any bugs or suggestions through the normal channels. Bugs with test cases should be posted to the bug tracker on the HSQLDB Sourceforge site. The included documentation has been updated. The JavaDoc for the jdbcXXX classes covers the JDBC interface. You can make the JavaDoc via the build scripts. The jar has been compiled with JDK 1.4. To run under earlier JDK's recompile using JDK 1.3.x. Changes since 1.7.2 are listed here in the reverse chronological order. 2003.09.02 ADDED JDBC SUPPORT FOR SAVPOINTS 2003.08.22 FURTHER SPEED IMPROVEMENTS IN ALL LOGGED MODES INSERT and UPDATE ops are faster by 5-20% in MEMORY tables, less in CACHED tables. ADDED SUPPORT FOR A RANGE OF SQL STYLE FUNCTIONS: CASE .. WHEN ... THEN .. ELSE ... END CASE WHEN .. THEN .. ELSE ... END NULLIF(.. , ..) SUBSTRING(.. FROM .. FOR ..) POSITION(.. IN ..) TRIM( {LEADING | TRAILING .. } FROM ..) EXTRACT({DAY | TIME |..} FROM ..) COALESCE(.. , ..) 2003.08.17 FURTHER IMPROVEMENTS IN PREPARED STATEMENT EXECUTION SPEED SUPPORT FOR JDBC BATCH EXECUTION SUPPORT FOR MULTIPLE ROWS WITH NULL FIELDS UNDER UNIQUE CONSTRAINTS IMPROVEMENTS TO TestSelf IMPROVEMENTS TO ON UPDATE CASCADE / SET DEFAULT / SET NULL 2003.08.11 REWRITE OF LEFT OUTER JOIN SUPPORT - Complete rewrite of join processing abolishes the requirement for an index on any joined columns. All expressions with AND are supported in the join condition (JOIN atable ON ....) IMPROVEMENTS TO ON DELETE CASCADE / SET DEFAULT / SET NULL - Self referencing foreign keys and multiple foreign keys now support ON DELETE CASCADE. 2003.08.02 NIO ACCESS FOR .data FILES New nio access layer for .data files speeds up most CACHED TABLE related operations very significantly. 90% speedups in TestCacheSize tests have been observed. The program must be compiled with JDK 1.4 and run in a 1.4 JRE to use the new access mode. IMPROVEMENTS TO UPDATE AND INSERT Certain types of UPDATES and INSERTS that previously failed due to blanket application of UNIQUE constraints now work. Examples include UPDATE ... SET col = col + 1 where col is an identity column or INSERT a self referencing row under FOREIGN key constraints. 2003.07.30 IMPORTANT FIX IN SELECT QUERY PROCESSING Problems with OUTER and INNER joins returning incorrect results have been reported over the last couple of years. A new fix is intended to ensure correct results in all cases. - When two tables are joined, rows resulting from joining null values in the joined columns are no longer returned. - Use of OUTER requires the existence of an index on a joined OUTER column - There are still limitations on the conditions used in OUTER joins. AGGREGATES - enhancements to aggregate functions: aggregates of different numeric types are now supported SUM returns a BIGINT for TINYINT, SMALLINT and INTEGER columns. It returns a DECIMAL for BIGINT columns (scale 0) and DECIMAL columns scale equals to that of the column). AVG returns the same type as the column or the expression in its argument. - aggregates with GROUP BY do not return any rows if table is empty OLDER ENHANCEMENTS NOT PREVIOUSLY REPORTED - IDENTITY columns can now be of BIGINT type: CREATE TABLE ( BIGINT IDENTITY, ...) - With contributed patch, TEXT TABLES encoding of the source file can now be specified. UTF-8 and other encodings can be used. - Two new options for databases: files_readonly and files_in_jar were added based on submitted patches. FILE READ-ONLY If the property files_readonly=true is set in the database .properties file, no attempt is made to write the changes to data to file. Default, memory tables can be read/write but TEXT and CACHED tables are treated as read-only. FILES IN JAR This option allows database files to be distributed in the application jar. We have changed the original contribution so that a special URL is used for this mode in the form of: jdbc:hsqldb:res: The URL type 'res' determines that the path that follows is a path into the JAR. The database can be readonly or files_readonly, depending on the value set in .properties file. 2003.07.09 SCRIPT FORMAT - change to command previously named SET LOGTYPE (discussed below for earlier alphas) new form is: SET SCRIPTFORMAT {TEXT | BINARY | COMPRESSED } The new binary and compressed formats are not compatible with previous ones, so you should change any old 1.7.2 ALPHA_? database to text mode with SET LOGTYPE 0 before opening with the new version. 'OTHER' DATA TYPE - change to handling of OTHER columns. It is no longer required that the classes for objects stored in OTHER columns to be available on the path of an HSQLDB engine running as a server. Classes must be available on the JDBC client's path. OBJECT POOLING - the new Object pool has been incorporated. This reduces memory usage to varying degrees depending on the contents of database tables and speeds up the database in most cases. Currently the size of the pool is hard-coded but it will be user adjustable in the release version. CONNECTION PROPERTY - a new property, ifexists={true|false} can be specified for connections. It has an effect only on connections to in-process databases. The default is false and corresponds to current behaviour. If set true, the connection is opened only if the database files have already been created -- otherwise no new database is created and the connection attempt will fail. Example: jdbc:hsqldb:hsql:mydb;ifexists=true This property is intended to reduce problems resulting from wrong URL's which get translated to unintended new database files. It is recommended to use this property for troubleshooting. 2003.07.04 PREPARED STATEMENTS - support for real PreparedStatements - major speedup. Current limitations include lack of support for parameters in IN() predicates of queries. TRANSACTIONS VIA WEB SERVER - uniform support for transactions via HSQL and HTTP (WebServer and Servlet) protocols MULTIPLE IN-MEMORY AND SERVERS DATABASES - support for multiple memory-only databases within the same JVM - support for simultaneous multiple servers, multiple internal connections and multiple databases within the same JVM NEW CONVENTIONS FOR URL'S AND .properties FILES Each HSQLDB server or webserver can now serve up to 10 different databases. The server.properties and webserver.properties method for defining the databases has changed. The following properties should be used: server.database.0 path_of_the_first_database server.dbname.0 alias_for_the_first_database Up to 10 databases can be defined but they must start from 0 The same applies to command line arguments for Server and WebServer. The urls for connecting to servers should have the alias of the database at the end. For example, to connect to the HSQL protocol server on the localhost use: jdbc:hsqldb:hsql://localhost/alias_for_the_database where alias_for_the_database is the same string as defined in server.properties as server.dbnam.n The default for server.dbname.0 is "" (empty string) so that old URL types continue to work. Multiple memory-only database are supported by the use of: jdbc:hsqldb:mem:alias_for_the_first_database jdbc:hsqldb:mem:alias_for_the_second_database Examples: jdbc:hsqldb:mem:db1 jdbc:hsqldb:mem:mydb The conneciton type, 'file', can be used for file database connections. example below: jdbc:hsqldb:hsql:file:mydb;ifexists=true The URL for connecting to a Servlet HTTP server must have a forward-slash at the end. Servlet serves only one database. jdbc:hsqldb:hsql://localhost:8080/servlet/HsqlServlet/ 2003.03.10 DATABASE METADATA -system table support and java.sql.DatabaseMetadate results have been overhauled by Campbell. STRICT FOREIGN KEYS -strict treatment of foreign key index requirements is now enforced. A foreign key declaration _requires_ a unique constraint or index to exist on the columns of the referenced table. This applies both to old and new databases. Duplicate foreign keys (with exactly the same column sets) are now disallowed. TEXT TABLES -further improvements to TEXT table support. Smaller cache sizes are now the default and the default properties can be specified in the *.properties file. HSQLDB 1.7.2 ALPHA_M 2003.01.23 -fixed reported bugs in SHUTDOWN COMPACT -fixed reported bugs in GRANT statements on system tables -fixed bug that prevented UPDATE in some circumstances some enhancements that appeared in previous versions but not reported: -enhanced handling of DATE objects - comparability and normalisation -support for CLOB methods in ResultSet -fixed bug in afterLast() followed by previous() in ResultSet HSQLDB 1.7.2 ALPHA_L 2003.01.16 various patches and fixes -fixes new issues reported with ALPHA_K -fixes old issues related to uncommitted transactions in abnormal shutdown -fixes old issues with SAVEPOINT names -enhanced TEXT table handling and reporting of errors in CSV (source) files HSQLDB 1.7.2 ALPHA_K 2003.01.10 various patches and fixes -OUTER with multiple column joins is now supported while OR is disallowed in the join expression. It is now possible to specify a range or equality condition on the OUTER columns returned. -submitted patch for exclusion of NULL values from results of range queries has been applied. e.g. WHERE columnvalue < 3 will exclude from the result all rows with NULL in columnvalue. -a number of small enhancements and bug fixes. further enhancements to logging. -The *.script file now contains only the DDL and data that is written at CHECKPOINT or SHUTDOWN. The statements logged while the engine is running are stored in a *.log file. -The format of the *.script file can now be one of TEXT, BINARY or COMPRESSED. The SET LOGTYPE {0|1|3} will reset the format. A checkpoint is performed at this point if the format is different from the existing one. The COMPRESSED format has the side benefit of hiding the DDL and the admin password. -The behaviour of SET WRITE_DELAY has changed with the introduction of the sync() method to force the log to be written out completely to disk at given intervals. SET WRITE_DELAY {TRUE | FALSE} is interpreted as synch every 60 seconds or 1 second. SET WRITE_DELAY where n is an integer is interpreted as synch every n seconds. The current default is 60 seconds which seems to provide the right balance. The performance impact of SET WRITE_DELAY 1 is probably about 15% over that of SET WRITE_DELAY 300. -The recovery from crash has been modified so that any line in the log that is not properly written (and causes an error) ends the redo process. A message is reported to the user, instead of stopping engine operation. HSQLDB 1.7.2 ALPHA_J 2002.12.24 CVS hsqldb-dev module tag: HSQLDB_1_7_2_ALPHA_J -More work on text tables. See hsqlTextTables.html. -Some refactoring of Table.java and Parser.java HSQLDB 1.7.2 ALPHA_I 2002.12.16 CVS hsqldb-dev module tag: HSQLDB_1_7_2_ALPHA_I More work on core classes to consolidate previous changes -correction of reported bug with text tables and primary keys -reducing data file writes esp. with big rows -preventing Cache related error in ON DELETE CASCADE HSQLDB 1.7.2 ALPHA_H 2002.12.11 CVS hsqldb-dev module tag: HSQLDB_1_7_2_ALPHA_H Reduction in JDK / JRE dependencies (see readmebuild.txt) Extensive refactoring of some of the larger classes. More changes to core classes Node, Row, Index, Cache, Log ... Some minor bug fixes and enhancements. Enhancements to text table support, including binary columns. Bug fixes to User.java and UserManager.java. Support for ON UPDATE / DELETE SET NULL / SET DEFAULT SSL support for Server mode operation. 2002.10.30 HSQLDB 1.7.2 ALPHA_G CVS hsqldb-dev module tag: HEAD data: 2002.10.30 New features include: Tony Lai's enhancements to aggratates and support for HAVING. Tony's new UnifiedTable class (provisional name) which he kindly wrote at my request and provides sorted arrays with minimal storage requirements. My refactoring work on DatabaseRowInput/Output subclasses and Cache.java. This is still ongoing and has several features aimed at a more robust database engine, including: - reduction in object creation; - possibility of resizing the cache while the engine is running; - possibility of exporting and backing up the DB files while the engine is running. I have implemented the new feature, CHECKPOINT DEFRAG to defragment a *.data file without shutting down the engine, in two different versions, taking advantage of the above improvements. I have implemented binary logging of MEMORY table inserts in the *.script file, resulting in much smaller startup and shutdown times with large memory tdly wrote at my request and provides sorted arrays with minimal storage requirements. My refactoring work on DatabaseRowInput/Output subclasses and Cache.java. This is still ongoing and has several features aimed at a more robust database engine, including: - reduction in object creation; - possibility of resizing the cache while the engine is running; - possibility of exporting and backing up the DB files while the engine is running. I have implemented the new feature, CHECKPOINT DEFRAG to defragment a *.data file without shutting down the engine, in two different versions, taking advantage of the above improvements. I have implemented binary logging of MEMORY table inserts in the *.script file, resulting in much smaller startup and shutdown times with large memory tables. Use SET LOGTYPE {0|1} to set the log type to text (0) or binary (1). This performs a checkpoint if the type needs changing. Also further performance optimisations resulting in faster CACHED table operations have been made. Sebastian has implemented ON UPDATE CASCADE support for foreign keys.