What Is New in HSQLDB 2.0
HSQLDB 2.0 is a major release packed with significant enhancements to SQL standard support, performance, and tooling. It introduces new data types, improves existing features, and fixes numerous bugs for greater stability. This version solidifies HSQLDB's position as a robust, feature-rich Java database for development and production.
| Category | Key Changes |
|---|---|
| New Features | SQL:2016 support, Identity columns, SQL/JSON features, New built-in functions |
| Improvements | Faster text table processing, Enhanced aggregate functions, Better optimizer performance |
| Bug Fixes | Numerous fixes for SQL standard compliance, data type handling, and transaction management |
| Deprecated | BIT, BIT VARYING data types; Old CLOB, BLOB aliases |
What SQL standard enhancements were introduced?
HSQLDB 2.0 brings the database much closer to full SQL:2016 core conformance. A major addition is support for identity columns using the GENERATED BY DEFAULT AS IDENTITY syntax, which provides a standardized alternative to auto-increment columns. This matters because it aligns your schema with the SQL standard, making it more portable across different database systems.
The update also includes initial support for SQL/JSON features, allowing for more sophisticated JSON data manipulation directly within SQL queries. Several other smaller syntax and behavioral changes have been implemented to improve overall standard compliance.
How did built-in functions and data types change?
This release expands the function library with new options for developers. New functions like TRANSLATE(), TRIM_ARRAY(), and OCTET_LENGTH() provide more string and array manipulation power directly in SQL.
In practice, the handling of existing functions has been refined. Aggregate functions and those dealing with NULL values now behave more predictably according to the SQL standard. Be aware that the old BIT and BIT VARYING types are now deprecated; you should use the standard BIT(n) or VARBIT(n) instead.
What performance improvements can we expect?
The engine for processing CSV and text tables has been completely rewritten for significantly faster import and query performance. This is a big deal if you regularly work with external data files, as operations can be multiple times faster than in previous versions.
Beyond file handling, the query optimizer and aggregate function execution have seen general performance boosts. These optimizations help complex queries and data aggregation operations run more efficiently, improving overall application responsiveness.
Were there any important syntax or behavior changes?
Yes, several behaviors have been adjusted for SQL standard compliance. The syntax for the LOCALTIME and LOCALTIMESTAMP functions now requires parentheses, bringing it in line with the standard specification.
Handling of interval literals and timestamp arithmetic has been made more consistent. These changes ensure that your temporal calculations produce reliable, predictable results across different query scenarios.
FAQ
Should I be concerned about the deprecated BIT and BIT VARYING data types?
Yes, you should plan to migrate. These types are deprecated and will likely be removed in a future version. Replace them with the standard BIT(n) or VARBIT(n) types in your schema definitions.
How do identity columns differ from auto-increment columns?
Identity columns follow the SQL standard syntax (GENERATED BY DEFAULT AS IDENTITY) and behavior, making your schema more portable. While auto-increment still works, using identity columns is the standards-compliant path forward.
Is the text table performance improvement significant for large files?
Absolutely. The text table processing engine was completely rewritten and is now much faster. You'll notice the biggest gains when importing or querying large CSV files, where operations can be several times faster.
Do I need to change my code for the new LOCALTIME() syntax?
Yes, if you use these functions without parentheses. The standard syntax now requires them, so you'll need to update calls from LOCALTIME to LOCALTIME() to avoid errors.
What's the most important reason to upgrade to HSQLDB 2.0?
The significantly improved SQL standard compliance and performance enhancements. You get better portability, faster operations, and access to modern SQL features like identity columns and JSON support.