What's new in H2 Database Engine Portable 2.2.224 Build 2023-09-17

Sep 17, 2023
  • Issue #3883 Performance regression in 2.2.222

New in H2 Database Engine Portable 2.2.222 Build 2023-08-22 (Aug 23, 2023)

  • Fixed race condition in MVStore causing database corruption "File corrupted in chunk ###"
  • Issue #3868: INFORMATION_SCHEMA.SESSIONS.ISOLATION_LEVEL returns isolation level of the current session in all rows
  • RP #3865: Add possibility to enable virtual worker threads in TCP, Web, and PG servers on Java 21+
  • PR #3864: Improve performance of TCP client driver when it is used from virtual threads
  • Issue #2665: Parser shouldn't suggest compatibility syntax elements on parsing error
  • Issue #3089: GREATEST and LEAST aren't fully compliant with the SQL Standard
  • PR #3861: Improve SET EXCLUSIVE 2 and use stable time source for timeout exceptions
  • PR #3859: Fix conversion of PK columns to identity columns
  • Issue #3855: StackOverflowError when using TRACE_LEVEL_SYSTEM_OUT=4
  • PR #3854: Fix issues with newer JVMs
  • PR #3851: Remove lift configuration
  • PR #3847: Fix the NullPointerException caused by accessing 'user' after the session has been closed
  • PR #3846: Make DB_CLOSE_ON_EXIT safer
  • PR #3842: Adds support of quotedNulls in CSV handling

New in H2 Database Engine Portable 2.2.220 Build 2023-07-04 (Jul 4, 2023)

  • PR #3834: Increase database format version
  • PR #3833: Disallow plain webAdminPassword values to force usage of hashes
  • PR #3831: Add Oracle-style NOWAIT, WAIT n, and SKIP LOCKED to FOR UPDATE clause
  • PR #3830: Fix time zone of time/timestamp with time zone AT LOCAL
  • PR #3822 / Issue #2671: Add quantified comparison predicates with array
  • PR #3820: Add partial implementation of JSON simplified accessor
  • PR #3818: Add support of underscores in numeric literals
  • PR #3817: Add ANY_VALUE() aggregate function
  • PR #3814: Fix regression in comparisons with infinities and NaNs
  • Issue #3040: System objectIds are recycled twice when CTE queries are executed.
  • PR #3812: UNIQUE null treatment
  • PR #3811: BTRIM function, octal and binary literals and other changes
  • Issue #352: In comparison text values are converted to INT even when they should be converted to BIGINT
  • PR #3797: MSSQL mode: Discard table hints on plain UPDATE statements
  • PR #3791: Formatted cast of datetimes to/from character strings
  • Issue #3785: CSVRead: Fails to translate empty Numbers, when cells are quoted
  • Issue #3762: Comparison predicates with row values don't create index conditions
  • PR #3761: LAST_DAY function and other changes
  • Issue #3705: Oracle DATE type: milliseconds (second fractions) rounded in H2 but truncated in Oracle (fixed in SYSDATE only)
  • Issue #3642: AssertionError in mvstore.FileStore.serializeAndStore
  • Issue #3675: H2 2.x cannot read PostgreSQL-style sequence generator start with option without WITH keyword
  • Issue #3757: FORMATDATETIME function doesn't handle time with time zone properly
  • PR #3756: Limit the row list allocation based on the row count
  • PR #3753: Add missing NEWSEQUENTIALID function in MSSQLServer mode
  • Issue #3730: FILE_READ from JAR filesystem on classpath results in file of length 0
  • PR #3749: Fix min/max description for sequences
  • PR #3739: Fix count(*) for linked table to Oracle
  • Issue #3731: Division result exceeds numeric precision constraint
  • PR #3718: Add test coverage for JDK 17
  • Issue #3580: TestCrashAPI: NPE in ParserUtil.getTokenType() (called by Parser.readIfDataType1())
  • PR #3709: Update copyright years and fix building of documentation
  • Issue #3701: CLOBs can cause ClassCastExceptions
  • Issue #3698: MySQL mode show columns from table, if modificationMetaId changed between prepared and execute. Then error occurred.
  • PR #3699: Upgrade to the latest OSGi JDBC specification
  • Issue #3659: User-defined variable "sticky" if used in view with join
  • Issue #3693: Wrong result when intersecting unnested arrays
  • PR #3691: GitHub Workflows security hardening
  • PR #3688: Construct FormatTokenEnum.TOKENS during class initialization
  • Issue #3682: MVStoreException at accountForRemovedPage
  • Issue #3664: [2.1.214] NullPointerException in org.h2.command.query.Select.queryDistinct
  • PR #3650: fix version number in the archives html table
  • PR #3649: Basic implementation of materialized view
  • Issue #3646: org.h2.mvstore.MVStoreException: Chunk metadata too long
  • Issue #3645: The BITCOUNT function incorrectly counts BINARY/VARBINARY values of >=16 bytes.
  • Issue #3637: DB content massacred when opening a 2.1.214 DB with current master
  • Issue #3636: "This store is closed" after two-phase commit
  • PR #3639: Add random_uuid() alias to be compatible with postgres
  • Issue #3640: SOUNDEX function should not be case-sensitive
  • Issue #3633: Memory leak in case of in-memory database
  • PR #3629 Better separation between MVStore and FileStore
  • PR #3626: Improve memory estimation of CacheLongKeyLIRS and correct some passed memory sizes
  • Issue #3619: PostgreSQL mode: STRING_AGG with prepared statement parameter not working
  • Issue #3615: H2 Console connecting to Oracle DB will not show the list of tables
  • PR #3613: Fix infinite loop in Tokenizer when special whitespace character is used
  • Issue #3606: Support for GraalVMs native-image
  • Issue #3607: [MySQL] UNIX_TIMESTAMP should return NULL
  • Issue #3604: Improper FROM_1X implementation corrupts some BLOBs during migration
  • Issue #3597: Support array element assignments in UPDATE statements
  • Issue #3599: [2.1.214][MariaDB] DELETE query failure
  • Issue #3600: NPE in MVTable.lock(), version 2.1.214
  • Issue #3601: InvalidPathException when saving lock file
  • Issue #3583: lob cleaner issue
  • Issue #3585: Misuse ValueReal.DECIMAL_PRECISION when optimize typeinfo from DOUBLE to DECFLOAT
  • Issue #3575: Possible syntax mismatch for json_object in MySQL compatibility mode
  • PR #3577: Add support of TINYINT and DECFLOAT to TO_CHAR
  • Issue #3567: No AUTO_INCREMENT in DatabaseMetaData.getTypeInfo()
  • PR #3555: Add missing check for -webExternalNames flag
  • Issue #3543: PostgreSQL mode, update with "from", why "NULL not allowed" error?
  • PR #3542: Fix failed to delete a readonly file on Windows file systems

New in H2 Database Engine Portable 2.1.214 Build 2022-06-13 (Jun 14, 2022)

  • Important bugs and regression fixes discovered after 2.1.210 release:
  • Issue #3538: In Postgres compatibility mode the NUMERIC type w/o scale should not default to 0
  • Issue #3534: Subquery has incorrect empty parameters since 2.1.210 that breaks sameResultAsLast()
  • Issue #3390: "ROW" cannot be set as a non keyword in 2.x
  • Issue #3448: With linked table to postgreSQL, case-sensitive column names not respected in where part
  • Issue #3434: JavaTableFunction is not closing underlying ResultSet when reading column list
  • Issue #3468: Invalid DB format exception (for 1.x DB in 2.x h2) should have a specific SQLException vendorCode
  • Issue #3528: Weird syntax error with HAVING clause in Oracle Mode
  • Issue #3307: Fix SHUTDOWN DEFRAG for encrypted databases
  • Issue #3515: Support for NEXTVAL property in DB2 mode
  • Issue #3444: Conversion 'text' to 'integer' doesn't work anymore
  • Issue #3493: org.h2.tools.DeleteDbFiles won't delete files under certain circumstances
  • Issue #3486: FilePathDisk.newDirectoryStream() may fail on remote drive on Windows due to AccessDeniedException in Path.toRealPath()
  • Issue #3484: LOB issue

New in H2 Database Engine Portable 2.1.212 Build 2022-04-09 (Apr 9, 2022)

  • Add support for standard interval literals with precision
  • Possibility of corruption after SHUTDOWN DEFRAG
  • DROP TABLE/INDEX causes memory leak
  • Increase max length of VAR* types
  • fix bug in readStoreHeader()
  • Add performance tests for SQLite
  • Occasional NPE in concurrent update of LOB
  • Cannot use enum values in JSON without explicit casts
  • Regression: BIT(1) is not accepted in MySQL compatibility mode
  • Allow combination of any geometry types with the same SRID
  • H2 2.1.210: Query with Parameters throws NPE
  • Parser can't parse REFERENCES … NOT NULL
  • OOME with nested derived tables
  • Enhance SCRIPT to support operations on STDOUT
  • FunctionMultiReturn.polar2CartesianArray result set iteration throws ClassCastException
  • Regression: ORDER BY ROWNUM fails with General error: "Unexpected code path"
  • SYSDATE behavior changed in 2.x
  • SYSDATE Considered Identifier when used in inner select
  • Hang on merge statement with data change delta table
  • Remove abandoned Java to C converter and fix some warnings from Sonatype Lift
  • Use secure parser in H2AuthConfigXml to avoid future reports

New in H2 Database Engine Portable 2.1.210 Build 2022-01-17 (Jan 17, 2022)

  • PR #3381: Add IDENTITY() and SCOPE_IDENTITY() to LEGACY mode
  • Issue #3376: Data cannot be read after insert of clob data > MAX_LENGTH_INPLACE_LOB with data change delta table
  • PR #3377: Add -webExternalNames setting and fix WebServer.getConnection()
  • PR #3367: Use faster checks of dimension systems of geometries
  • PR #3369: Added v2 changes in migration docs
  • Issue #3361: MemoryEstimator.estimateMemory() can return negative size
  • PR #3362: Use BufferedReader instead of BufferedInputStream to avoid Illegal seek exception
  • Issue #3353: Wrong rownum() scope for DML with change delta table
  • PR #3352: make Javadoc happier
  • Issue #3344: Changelog could link to github issue
  • Issue #3340: JDBC index type seems wrong
  • Issue #3336: FT_INIT error when mode=MySQL
  • Issue #3334: Regression with CREATE ALIAS - Parameter "#2" is not set
  • Issue #3321: Insert Primary Key after import CSV Data does not work
  • PR #3323: Tokenize SQL before parsing and preserve tokens for recompilation
  • PR #3320: Add Servlet 5-compatible servlet for H2 Console
  • Issue #918: Parser fails recognising set operations in correlated subqueries
  • Issue #2050: PostgreSQL with recursive fail with union in the final query
  • PR #3316: Update copyright years
  • PR #3315: Never put read locks into lockSharedSessions and other minor changes
  • Issue #492: H2 does not correctly parse <parenthesized joined table>
  • Issue #3311: Parser creates wrong join graph in some cases and uses wrong tables for column mapping
  • FORCE_JOIN_ORDER setting is removed
  • Issue #1983: Official build script is not compatible with Java 13
  • PR #3305: Add UNIQUE(VALUE) and remove some non-standard keywords
  • PR #3299: Remove useless StringBuilder.toString() call
  • PR #3298: Delete unused sqlTypes array

New in H2 Database Engine Portable 2.0.206 Build 2022-01-04 (Jan 4, 2022)

  • Issue #3322: Create linked table fails when the table contains a Geometry with a data type specified
  • Issue #3297: Unexpected GROUP BY results with indexed IGNORECASE column

New in H2 Database Engine Portable 1.4.200 Beta (Oct 14, 2019)

  • PR #2168: Add non-standard SNAPSHOT isolation level to MVStore databases
  • Issue #2165: Problem with secondary index on SERIALIZABLE isolation level
  • Issue #2161: Remove undocumented PageStore-only FILE_LOCK=SERIALIZED
  • PR #2155: Reduce code duplication
  • Issue #1894: Confusing error message when database creation is disallowed
  • Issue #2123: Random failures in TestTransactionStore
  • Issue #2153: Different behavior in SET LOCK_TIMEOUT after 1.4.197
  • Issue #2150: Remove MULTI_THREADED setting and use multi-threaded MVStore and single-threaded PageStore backends
  • Issue #216: Support READ UNCOMMITTED isolation level in MVStore mode
  • Issue #678: Support REPEATABLE READ isolation level in MVStore mode
  • Issue #174: Support SERIALIZABLE isolation level in MVStore mode
  • Issue #2144: MVStore: read uncommitted doesn't see committed rows
  • Issue #2142: CURRVAL / CURRENT VALUE FOR should return the value for the current session
  • Issue #2136: ConstraintCheck concurrency regression
  • PR #2137: Don't use SYSTEM_RANGE for SELECT without a FROM
  • PR #2134: Assorted fixes and other changes in DateTimeUtils
  • PR #2133: Optimize COUNT([ALL] constant) and other changes
  • PR #2132: Typo and another bug in MVStore.readStoreHeader()
  • Issue #2130: Group-sorted query returns invalid results with duplicate grouped columns in select list
  • Issue #2120: Add IF EXISTS clause to column name in ALTER TABLE ALTER COLUMN statement
  • Issue #521: Add support for the TIME WITH TIME ZONE data type
  • PR #2127: Fix race condition / performance issue during snapshotting
  • Issue #2124: MVStore build is broken
  • PR #2122: Add support for LMT in time zones and fix large years in datetime values
  • Issue #2067: Incorrect chunk space allocation during chunks movement
  • PR #2066: Not so happy path - "four alternatives" implementation
  • PR #2121: Reduce code duplication for datetime API with custom Calendar instances
  • PR #2119: SQL: statement read consistency
  • Issue #2116: Empty IN() operator should result in error (MSSQL)
  • Issue #2036: CAST from TIME to TIMESTAMP returns incorrect result
  • PR #2114: Assorted changes
  • PR #2113: Add feature F411: Time zone specification
  • PR #2111: CURRENT_CATALOG, SET CATALOG and other changes
  • Issue #2109: IW date formatting does not produce proper output
  • PR #2104: Fix ordinary grouping set with parentheses and empty grouping set in GROUP BY
  • Issue #2103: Add QUOTE_IDENT() function to enquote an identifier in SQL
  • Issue #2075: Add EXECUTE IMMEDIATE implementation
  • PR #2101: Fix infinite loop in Schema.removeChildrenAndResources()
  • Issue #2096: Convert LEFT and RIGHT to keywords and disallow comma before closing parenthesis
  • PR #2098: Fix typos
  • Issue #1305 / PR #2097: Remove unused and outdated website translation infrastructure
  • PR #2093: CURRENT VALUE FOR and other sequence-related changes
  • PR #2092: Allow to simulate usage of multiple catalogs by one connection
  • PR #2091: Oracle mode now uses DECIMAL with NEXTVAL
  • Issue #2088: Division by zero caused by evaluation of global conditions before local conditions
  • Issue #2086: TCP_QUICKACK on server socket
  • Issue #2073: TableLink should not pass queries to DatabaseMetaData.getColumns()
  • Issue #2074: MySQL and MSSQLServer Mode: TRUNCATE TABLE should always RESTART IDENTITY
  • Issue #2063: MySQL mode: "drop foreign key if exists" support
  • PR #2061: Use VirtualTable as a base class for RangeTable
  • PR #2059: Parse IN predicate with multiple subqueries correctly
  • PR #2057: Fix TestCrashAPI failure with Statement.enquoteIdentifier()
  • PR #2056: Happy path: speed up database opening
  • Issue #2051: The website shows outdated information about the storage engine
  • PR #2049: bugfix - mvstore data lost issue when partial write occurs
  • PR #2047: File maintenance
  • PR #2046: Recovery mode
  • Issue #2044: setTransactionIsolation always call commit() even if transaction is auto-commit
  • Issue #2042: Add possibility to specify generated columns for query in web console
  • Issue #2040: INFORMATION_SCHEMA.SETTINGS contains irrelevant settings
  • PR #2038: MVMap: lock reduction on updates
  • PR #2037: Fix SYS_GUID, RAWTOHEX, and HEXTORAW in Oracle mode
  • Issue #2016: ExpressionColumn.mapColumns() performance complexity is quadratic
  • Issue #2028: Sporadic inconsistent state after concurrent UPDATE in 1.4.199
  • PR #2033: Assorted changes
  • Issue #2025: Incorrect query result when (OFFSET + FETCH) > Integer.MAX_VALUE
  • PR #2023: traverseDown() code deduplication
  • PR #2022: Mvmap minor cleanup
  • Issue #2020: Wrong implementation of IN predicate with subquery
  • PR #2003: Change dead chunks determination algorithm
  • Issue #2013: DECIMAL is casted to double in ROUND function
  • PR #2011: ZonedDateTime and (INTERVAL / INTERVAL)
  • Issue #1997: TestRandomSQL failure with ClassCastException
  • Issue #2007: PostgreSQL compatibility mode: support ON CONFLICT DO NOTHING
  • Issue #1927: Do not allow commit() when auto-commit is enabled
  • PR #1998: Reduce TxCounter memory footprint
  • PR #1999: Make RootReference lock re-entrant
  • PR #2001: Test improvements, OOME elimination
  • Issue #1995: Obscure condition in MVPrimaryIndex.extractPKFromRow()
  • Issue #1975: Add client ip address to information_schema
  • PR #1982: Hindi language translation added
  • Issue #1985: Add thread number to TCP server thread names
  • Do not allow empty password for management DB
  • Issue #1978: getGeneratedKeys() can use the same rules as FINAL TABLE
  • PR #1977: Change JSON literals and add support for compound character literals
  • PR #1974: Use proleptic Gregorian calendar for datetime values
  • Issue #1847: Add support for data change delta tables
  • PR #1971: Add maximum cardinality parameter to ARRAY data type
  • PR #1970: Switch from log map rename to "committed" marker log record
  • PR #1969: Add unique predicate Issue #1963: Expression.addFilterConditions() with outer joins
  • PR #1966: Add standard CURRENT_SCHEMA function
  • PR #1964: Add Feature T571: Truth value tests
  • PR #1962: Fix data types of optimized conditions
  • PR #1961: Failure to open DB after improper shutdown
  • Issue #1957: NullPointerException with DISTINCT and ORDER BY CASE
  • PR #1956: Fix row value handling in the null predicate
  • PR #1955: Add standard UNKNOWN literal
  • Issue #1952: Connection.setSchema doesn't work with query cache
  • PR #1951: Assorted changes
  • PR #1950: Fix NULL handling in ARRAY_AGG
  • PR #1949: Extract aggregate and window functions into own pages in documentation
  • PR #1948: Add standard LOG() function with two arguments
  • Issue #1935: Improve file locking on shared filesystems like SMB
  • PR #1946: Reimplement table value constructor on top of Query
  • PR #1945: Fix IN (SELECT UNION with OFFSET/FETCH)
  • Issue #1942: MySQL Mode: convertInsertNullToZero should be turned off by default?
  • Issue #1940: MySQL Mode: Modify column from NOT NULL to NULL syntax
  • Query:
  • Issue #1938: Regression with CREATE OR REPLACE VIEW. Causes "Duplicate column name" exception.
  • PR #1937: Get rid of FunctionCursorResultSet
  • Issue #1932: Incoherence between DbSettings.mvStore and getSettings()
  • PR #1931: Fix wildcard expansion for multiple schemas
  • PR #1930: Move PageStore table engine into own package
  • PR #1929: Initial implementation of type predicate and other changes
  • PR #1926: Assorted improvements for BINARY data type
  • Issue #1925: Support SQL Server binary literal syntax
  • Issue #1918: MySQL: CREATE TABLE with both CHARSET and COMMENT failed
  • Issue #1913: MySQL: auto_increment changing SQL not supported
  • Issue #1585: The translate function on DB2 mode could have parameters order changed
  • PR #1914: Change storage and network format of JSON to byte[]
  • Issue #1911: Foreign key constraint does not prevent table being dropped
  • PR #1909: Add JSON_OBJECTAGG and JSON_ARRAYAGG aggregate functions
  • PR #1908: Cast VARCHAR to JSON properly and require FORMAT JSON in literals
  • PR #1906: Add JSON_OBJECT and JSON_ARRAY functions
  • Issue #1887: Infinite recursion in ConditionAndOr.java
  • Issue #1903: MSSQLServer Mode - Support Update TOP(X)
  • Issue #1900: Support SQLServer stored procedure execution syntax
  • PR #1898: Add IS JSON predicate
  • Issue #1896: MSSQLServer compatibility mode - GETDATE() incorrectly omits time
  • PR #1895: Add standard array concatenation operation
  • Issue #1892: Window aggregate functions return incorrect result without window ordering and with ROWS unit
  • Issue #1890: ArrayIndexOutOfBoundsException in MVSortedTempResult.getKey
  • Issue #308: Mode MySQL and LAST_INSERT_ID with argument
  • Issue #1883: Suspicious code in Session.getLocks()
  • Issue #1878: OPTIMIZE_REUSE_RESULTS causes incorrect result after rollback since 1.4.198
  • PR #1880: Collation names like CHARSET_* recognition
  • Issue #1844: MySQL Compatibility: create table error when primary key has comment
  • Issue #1864: Failing to format NotSerializableException corrupting the database
  • PR #1868: add more checking to TestFileLock
  • Issue #1819: Trace.db file exceed file size limit (64MB)
  • Issue #1861: Use COALESCE in named columns join for some data types
  • PR #1860: Additional fix for deadlock on shutdown (exclusively in PageStore mode)
  • Issue #1855: Wrong qualified asterisked projections in named column join
  • Issue #1854: Wrong asterisked projection and result in named column right outer join
  • Issue #1852: Named column joins doesn't work with the VALUES constructor and derived column lists
  • Issue #1851: Wrong asterisked projection in named column joins
  • PR #1850: Duplicate map identifiers
  • PR #1849: Reimplement MVStore.findOldChunks() with PriorityQueue
  • PR #1848: Reimplement MVStore.findChunksToMove() with PriorityQueue
  • Issue #1843: Named columns join syntax is not supported
  • Issue #1841: Deadlock during concurrent shutdown attempts with 1.4.199
  • Issue #1834: NUMERIC does not preserve its scale for some values
  • PR #1838: Implement conversion from JSON to GEOMETRY
  • PR #1837: Implement conversion from GEOMETRY to JSON
  • PR #1836: Add LSHIFT and RSHIFT function
  • PR #1833: Add BITNOT function
  • PR #1832: JSON validation and normalization
  • PR #1829: MVStore chunks occupancy rate calculation fixes
  • PR #1828: Basis for implementation of SQL/JSON standard
  • PR #1827: Add support for Lucene 8.0.0
  • Issue #1820: Performance problem on commit
  • PR #1817: Assorted minor changes in documentation and other places
  • PR #1812: An IllegalStateException that wraps EOFException is thrown when partial writes happens

New in H2 Database Engine Portable 1.4.199 Beta (Mar 14, 2019)

  • PR #1807: Reduce code duplication and remove h2.mixedGeometries
  • PR #1806: Improve SELECT FOR UPDATE documentation
  • PR #1804: Lift limit of 10 characters on enum value (1.4.198 regression)
  • PR #1803: Do not rely on index sorting in SELECT FOR UPDATE
  • Issue #1800: Remove experimental status from window functions
  • PR #1799: Fire triggers after row locking and remove some leftovers
  • PR #1798: Reuse some string builders, remove StatementBuilder and other minor changes
  • Issue #1795: 1.4.198 regression with batch updates and transactions
  • PR #1794: Ask password in Shell in secure way and improve database creation information in tutorial
  • PR #1791: Move commands to commands.html and other changes
  • Issue #1774: H2 Browser configuration is unclear and fails on KUbuntu
  • PR #1790: Do not convert standard TRIM function to non-standard functions
  • Issue #1787: Non-standard MERGE throws LOCK_TIMEOUT_1 on violation of some constraints
  • PR #1784: improve database not found error
  • Issue #1740: Enhancement Request: h2 server: do not swallow exceptions
  • Issue #1616: Metadata and scripts should be persisted with unconditionally quoted identifiers
  • PR #1779: Improve isSimpleIdentifier() and enquoteIdentifier()
  • PR #1776: Improve DATABASE_TO_LOWER handling
  • Issue #1771: NPE in Comparison.createIndexConditions
  • PR #1772: Fix newlines in test scripts
  • Issue #1762: NullPointerException in Parser. Introduced in 1.4.198
  • PR #1768: Add more context-sensitive keywords
  • Issue #1758: sequence restart issue with 1.4.198
  • Issue #1759: SELECT … FOR UPDATE returns old data in 1.4.198
  • PR #1756: Fix DISTINCT ON in presence of ORDER BY
  • PR #1754: Fix window functions in JOIN with ON condition
  • Issue #1751: making it easier to open console and create local databases
  • Issue #1750: JOIN t ON t.col IN (SELECT ...) throws AssertionError

New in H2 Database Engine Portable 1.4.198 Beta (Feb 25, 2019)

  • Issue #1739: Table and view names not case sensitive when using DATABASE_TO_UPPER=FALSE
  • Issue #848: H2 PostgreSQL Compatibility Mode: lowercase metadata
  • Issue #485: Problem is in invalid case for schema's IGNORECASE=true;DATABASE_TO_UPPER=false
  • Issue #1742, PR #1743: Assorted small changes
  • PR #1738: Reduce memory allocation in getSQL() methods
  • PR #1737: more javadoc updates
  • Issue #1735: Creating views with DATABASE_TO_UPPER=FALSE fails
  • Issue #1732: source.html does not work
  • Issue #1730: Show error in H2 Console if specified driver is not compatible with URL
  • Issue #1590: Error on executing "DELETE FROM table1 WHERE ID = ?; DELETE FROM table2 WHERE ID = ?;"
  • Issue #1727: Support ISODOW as identifier for the extract function additional to ISO_DAY_OF_WEEK
  • PR #1580, #1726: Disable remote database creation by default
  • PR #1725: Add partial implementation of standard LISTAGG aggregate function
  • PR #1722: Fix window definition lookup in some queries
  • PR #1721: Fix derived column list in complex queries
  • Issue #1718: Window function and values clause don't work well together
  • Issue #1592: Index out of bounds exception in Page.getKey()
  • PR #1716: Improve documentation of some DML commands
  • Issue #1715: Postgres mode: Domain "regproc" already exists
  • PR #1714: Assorted changes
  • PR #1713: Remove DataType.defaultDisplaySize and fix display size in TypeInfo
  • PR #1711: Add QUALIFY clause to SELECT command
  • Issue #1708: CREATE TABLE AS doesn't support column lists without data types
  • PR #1707: Fix sort order and ENUM data type in external results
  • PR #1706: Add hypothetical set functions
  • PR #1705: Fix GROUP_CONCAT with variable separator
  • PR #1704: Fix return type of PERCENTILE_CONT and MEDIAN
  • PR #1701: Add PERCENTILE_CONT and PERCENTILE_DISC inverse distribution functions
  • Issues #1297, #1697: Failure on concurrent session closure
  • Issue #1297: removeOldTempIndexes on PageStore causes NullPointerException
  • Issue #1354: TestCrashAPI: another NPE
  • PR #1695: Reduce memory for TestMVTempResult to 64m
  • Issue #1691: Append mode causes OOME in MVPlainTempResult
  • PR #1692: Use MVTempResult unconditionally
  • Issue #1689: Use separate constants for data types in Data, ValueDataType, and Transfer
  • PR #1687: MVMap minor cleanup
  • PR #1686: Fix a regression with ENUM data type
  • PR #1685: Fix CHAR in PostgreSQL mode and refactor some code
  • Issue #1681: IN () doesn't work with row values when data types are not exactly the same
  • Issue #1320: OOME / GC overhead in IndexCursor.nextCursor()
  • PR #1680: Assorted fixes for ALTER TABLE ALTER COLUMN
  • PR #1679: Use TestScript for testSimple
  • Issue #1677: Unable to use VALUES keyword in WHERE clause
  • Issue #1672: Deadlock on MVStore close in TestOutOfMemory
  • Issue #1665: TestCrashAPI: NPE with ENUM in MINUS operator
  • Issue #1602: Combine type, precision, scale, display size and extTypeInfo into one object
  • PR #1671: Assorted changes
  • Issue #1668: MySQL compatibility DATE() function should return NULL on error
  • Issue #1604: TestCrashAPI: PreparedStatement.getGeneratedKeys() is already closed
  • PR #1667: Detect NULL values and overflow in window frame bounds
  • PR #1664: Allow any expressions in window frames
  • Issue #1576: H2 Console should not display precision and scale for data types that don't have them
  • PR #1662: Fix Alter Table Drop Column In View when table name is wrapped by Double Quotes
  • PR #1660: Optimize window aggregates with AND UNBOUNDED FOLLOWING and no exclusions
  • PR #1658: Assorted small changes
  • PR #1657: Failure to stop background thread
  • PR #1656: Optimize window aggregates with ORDER BY + UNBOUNDED PRECEDING + no exclusions
  • Issue #1654: OOM in TestMemoryUsage, in big mode
  • Issue #1651: TIMESTAMP values near DST may be changed in MVStore database due to UTC-based PageStore format in some temporary storages
  • PR #1650: Fix race in MVStore.close()
  • Issue #1212: TestDiskFull: The file is locked
  • PR #1648: Add functions ARRAY_CAT(), ARRAY_APPEND() and ARRAY_SLICE()
  • PR #1646: In preparation to a release
  • PR #1643: more javadoc update
  • PR #1642: update javadoc
  • PR #1641: Update copyright years
  • PR #1640: Suggest ANY(?) instead of variable IN() again
  • PR #1638: Add support for Java 11 to test suite
  • PR #1637: Remove explicit unboxing
  • PR #1635: Optimize UUID to VARCHAR conversion and use correct time check in Engine.openSession()
  • Issue #1632: TestMVTableEngine failure
  • PR #1631: Prepare to release: javadoc cleanup
  • PR #1630: fix duplicate words typos in comments and javadoc
  • PR #1627: Use lock to protect append buffer
  • Issue #1618: GROUP BY does not work with two identical columns in selected expressions
  • Issue #1619: Two-phase commit regression in MASTER
  • PR #1626: fix doc
  • PR #1625: Prepare to release: javadoc cleanup, fix maven build, fix javadoc build
  • Issue #1620: UUIDs are unexpectedly sorted as signed
  • PR #1614: Use bulk .addAll() operation
  • PR #1613: Add explicit table query
  • Issue #1608: ARRAY and row value expression should not be the same
  • Issue #1606: Quantified comparison predicate doesn't work correctly on primary key column
  • Issue #1057: Very slow execution with subquery and connection parameter LAZY_QUERY_EXECUTION=1
  • Issue #1072: Very slow execution with join and connection parameter LAZY_QUERY_EXECUTION=1
  • PR #1601: Return BIGINT from ROWNUM(), ROW_NUMBER() and rank functions
  • PR #1599: cleanup StringUtils.cache
  • PR #1598: Minor changes in parser and documentation
  • PR #1597: Remove SysProperties.CHECK preconditions around simple assertions
  • PR #1596: Improve SQL Standard compliance in LOB precision parsing
  • Issue #1594: DBSettings.optimizeIsNull and dead code in IndexCursor.getMax()
  • PR #1591: Use multi-catch java 7 language construction to simplify code
  • Issue #1582: h2 not using best index for >=
  • PR #1588: Add support for java.time.Period
  • Issue #446: FILE_READ from classpath not working because of 0 byte file length
  • PR #1579: fix unintentional append mode disruption
  • Issue #1573: DELETE FROM w/ ROWNUM and subquery
  • Issue #187: SHUTDOWN DEFRAG corrupts splitted file database
  • PR #1571: Optimizing ConditionAndOr queries
  • Issue #1565: SOME / ANY conflict
  • PR #1564: Refactor Expression implementations
  • Issue #1561: Incorrect documentation and strange fallback value of SysProperties.FILE_ENCODING
  • Issue #1566: MVStore implements Closeable/AutoCloseable
  • Issue #1550: OutOfMemoryError during "shutdown defrag"
  • Issue #1440: OOM when executing "shutdown compact" in server mode
  • Issue #1561: Incorrect documentation and strange fallback value of SysProperties.FILE_ENCODING
  • PR #1557: increase lock timeout to TestConcurrentUpdate due to Travis failures
  • Issue #1554: REGEXP_REPLACE - accept 'g' flag in PostgreSQL compatibility mode
  • Issue #950: Comparison between databases in README.md and in features.html
  • Issue #1549: [RFE] Implement locking modes (select for update)
  • PR #1548: Add AsynchronousFileChannel-based experimental FilePathAsync
  • PR #1547: Speedup unused chunks collection
  • PR #1546: Tiny optimization: use `System.arraycopy` when possible
  • PR #1545: Export datetime value functions to SQL using standard syntax
  • Issue #1371: NPE in CacheLRU
  • Issue #1534: Typo in message
  • Issue #1527: Parser performance: Excessive use of regular expressions to validate column names
  • PR #1543: MVStore assorted re-factorings
  • PR #1538: Add support for newer Lucene versions without recompilation
  • Issue #1536: CURRENT_TIMESTAMP result doesn't change under Transactions
  • Issue #239: Consider supporting Lucene 5 indexes
  • PR #1520: Fixes bug in PutIfAbsentDecisionMaker
  • Issue #1518: ENUM and VIEW with filtering on enum column
  • Issue #1516: Array element reference array[index] should be 1-based
  • Issue #1512: TestMVTableEngine.testLowRetentionTime(): NPE in VersionedValue.Type
  • PR #1513: Assorted minor changes
  • PR #1510: Add optional EXCEPT clause to wildcards
  • PR #1509: Use domain term everywhere
  • Issue #1507: Add INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE qualification for domains
  • Issue #1499: TestScript::envelope.sql failure in “big” mode
  • Issue #1498: NPE in SimpleResultSet.getColumnCount()
  • Issue #1495: MERGE statement doesn't affect any rows when Oracle UPDATE .. WHERE .. DELETE .. WHERE is used
  • Issue #1493: MERGE statement fails when it updates more than one row
  • Issue #1492: Unnecessary restriction on MERGE USING statement when ON clause doesn't reference any target table columns
  • Issue #1491: Unnecessary restriction on MERGE USING statement when ON predicate doesn't match inserted row
  • Issue #1490: NullPointerException when running invalid MERGE statement
  • Issue #1489: MERGE USING documentation has misleading railroad diagram
  • Issue #1488: Improve documentation of window and some other functions
  • Issue #1485: Default window frame in presence of ORDER BY is RANGE .., not ROWS
  • PR #1484: New tests, reimplemented EXCLUDE clause, and assorted changes
  • Issue #1338: MSSQLServer compatibility enhancements
  • PR #1480: Update Maven build instruction and fix some problems
  • PR #1478: Upgrade maven-surefire-plugin
  • PR #1476: Add TransactionStore to MVStore jar
  • Issue #1475: Dropping column used by a view produces misleading error message
  • Issue #1473: TestScript needs better detection of sorted result
  • PR #1471: issue 1350: TestCrashAPI: PageStore.freeListPagesPerList
  • PR #1470: Fix window functions in queries with HAVING
  • PR #1469: Forbid incorrect nesting of aggregates and window functions
  • Issue #1437: Generated as Identity has a different behaviour.
  • PR #1467: Fix subtraction of timestamps
  • PR #1464: Assorted minor changes in window processing code
  • PR #1463: Fix some window aggregates and reduce amount of collecting implementations
  • PR #1462: Separate aggregate and window code in some places
  • PR #1461: Add WINDOW clause support
  • Issue #1427: Scalability problem in MVSpatialIndex
  • PR #1459: Improve window clause correctness checks
  • PR #1457: Add NTILE(), LEAD() and LAG() window functions
  • PR #1456: Add experimental implementation of remaining types of window frames
  • PR #1454: Add FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE()
  • PR #1453, Issue #1161: Add ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(), and CUME_DIST() window functions
  • PR #1452: Reset aggregates before reuse
  • PR #1451: Add experimental support for aggregates with OVER (ORDER BY *)
  • PR #1450: Evaluate window aggregates only once for each partition
  • PR #1449: Move more code from Aggregate and JavaAggregate to AbstractAggregate
  • PR #1448: Add experimental implementation of grouped window queries
  • PR #1447: Refactor OVER() processing code and fix some issues
  • PR #1446: fix : The French messages are bad generated (not contain DB message)
  • PR #1445: Use PostGIS-compatible format for SRID-only constraint in GEOMETRY
  • PR #1444: Add experimental unoptimized support for OVER ([PARTITION BY ...]) in aggregates
  • PR #1442: Bugfix - Release MVStore lock and file resources rightly even if errors when compacting database
  • PR #1441: Add GEOMETRY type subtypes with type and SRID constraints
  • PR #1434: Add support for ENUM in CAST and other changes
  • PR #1431: Fix some inconsistencies in documentation and improve mvn build
  • PR #1428: Add support for M and ZM dimensions to GEOMETRY data type
  • Issue #1405: Introduce LocalResult factory
  • PR #1422: Add ENVELOPE aggregate function
  • Issue #1421: Remove old-style outer join
  • PR #1419: Assorted minor changes
  • PR #1414: DEFRAG and COMPACT mixup
  • PR #1413: improvements to MVStore garbage collection
  • PR #1412: Added org.h2.store.fs package to exported osgi bundles
  • PR #1409: Map all remaining error codes to custom exception classes
  • Issue #1407: Add a MODE() aggregate function
  • PR #1402: Duplicate conditions in column check constraint
  • PR #1399: Add more subclasses of SQLException and use it for some error codes
  • PR #1397: Add DATEADD return type detection
  • Issue #1393: Add INFORMATION_SCHEMA.COLUMNS.IS_VISIBLE
  • PR #1392: Some refactoring and assorted minor optimizations
  • PR #1388: Extract UnaryOperation from Operation and other changes
  • PR #1386: DISK_SPACE_USED() for MVStore and other minor changes
  • PR #1385: split up the rather large convertTo method
  • PR #1384: Throw exception if unknown mode is specified in database URL
  • Issue #1365, PR #1382: Parse more date-time literals for compatibility with other databases
  • PR #1381: Minor fixes for INTERVAL data type
  • PR #1380: Improve documentation of intervals
  • Issue #1189: "Merge into using" parameters aren't found
  • Issue #1377: org.h2.api.Interval and TIME leftovers
  • PR #1376: TestMultiThreadedKernel is back
  • PR #1373: INTERVAL data type
  • Issue #1369: In MSSQL Server Mode generated UUID fields need NEWID() function
  • Issue #756: FunctionsMySql is not in the main jar
  • PR #1368: Parse BINARY VARYING, BINARY LARGE OBJECT, and CHARACTER LARGE OBJECT
  • PR #1367: Assorted changes with SELECT output limitation clauses
  • Issue #1363: Why H2 requires random own packages in OSGi bundle description?
  • Issue #1192: Add an Automatic-Module-Name
  • Issue #1361, PR #1362: Add limited support for MONEY and SMALLMONEY in compatibility modes
  • Issue #1327: mvn build misses some resources
  • PR #1359: Add system property to return OffsetDateTime from ResultSet.getObject()
  • PR #1357: Simplify execution flow in some places
  • PR #1356: Fix NPE in Query.initExpression()
  • PR #1355: Assorted changes in MetaTable
  • Issue #1352: TestCrashAPI: Prepared.getObjectId() was called before
  • PR #1349: Changes is conversion and comparison methods of Value
  • Issue #1346: Exception when using IN condition for enums
  • PR #1345: Replace some init methods with constructors
  • PR #1344: Streamline last chunk verification on startup
  • PR #1341: Optimize MVSecondaryIndex.convertToKey()
  • PR #1340: NoSuchElementException instead of returning null
  • PR #1339: Add support of TIMESTAMP WITH TIME ZONE to addition and subtraction operators
  • PR #1337: Streamline Value comparison
  • PR #1336: Minor refactorings
  • Issue #1332: Constraint name not set correctly
  • Rename fields to reflect actual type
  • Issue #1331: Regression in Database.updateMeta()
  • Issue #1323: Slow update after altering table in 1.4.197
  • PR #1326: Add support of PERCENT in FETCH and TOP clauses
  • PR #1325: Optimize WITH TIES in some queries and specify data types for KEY_COLUMN_USAGE
  • PR #1321: Do not add rows before OFFSET to result if possible
  • PR #1319: Treat NEXTVAL as an auto-generated key
  • PR #1318: Mode append fo MVPlainTempResult
  • PR #1314: Add ALTER VIEW RENAME command
  • PR #1313, issue #1315: Bugfix - using default locale encoding issue in conversion between varchar and varbinary value, and checking javac output text issue in SourceCompiler
  • PR #1312: Add Java 9+ support to NIO_CLEANER_HACK
  • PR #1311: Fix minor issues with ResultSet.getObject(..., Class) and WITH TIES
  • Issue #1298: TestKillRestartMulti: A map named undoLog.2 already exists
  • Issue #1307: Invalid value "null" for parameter "calendar" [90008-193]
  • PR #1306: Add initial implementation of WITH TIES clause
  • PR #1304: Update changelog and fix building of documentation
  • PR #1302: Use OpenJDK instead of OracleJDK 10 in Travis builds due to Travis problem
  • Issue #1032: Error when executing "SELECT DISTINCT ON"
  • Issue #1295: ConditionInSelect violates requirements of LocalResult
  • PR #1296: Assorted minor changes
  • PR #1293: Move HELP and SHOW tests into own files
  • PR #1291: Fix update count for REPLACE and move some SQL tests into separate files
  • PR #1290: Do not load the whole LOBs into memory for comparison operation
  • Issue #408: DISTINCT does not properly work with ORDER BY on function like LOWER
  • PR #1286: Fix MVTempResult implementations for results with invisible columns
  • Issue #1284: Nanoseconds of timestamps from old H2 versions are not read properly
  • PR #1283: Clean up interaction between LocalResult and ResultExternal
  • Issue #1265: OOME is not handled properly in TraceObject.logAndConvert()
  • Issue #1061: Regression: Braces after WITH clause not allowed anymore
  • PR #1277: Assorted changes in Parser
  • PR #1276: Improve support of ARRAY and SQLXML in JDBC layer
  • PR #1275: Do not quote other lower case characters
  • PR #1274: Use token type in Parser instead of string comparisons
  • PR #1272: Reduce code duplication in Parser
  • PR #1271: Minor memory leak
  • PR #1270: drop TableView isPersistent field
  • PR #1269: Eliminate commit of empty batch in some tests
  • Issue #1266: Add INFORMATION_SCHEMA.COLUMNS.DATETIME_PRECISION
  • Issue #1261: How to discover stored enum types through INFORMATION_SCHEMA
  • Issue #1258: Failing to remove index when using schema.table
  • PR #1256: misc tiny refactorings
  • PR #1255: Minor changes in MERGE USING, DATE_TRUNC, and EXTRACT
  • Issue #1214: Internal compiler believes that "3 warnings" is an error
  • PR #1252: Assorted minor changes
  • PR #1251: Fix SQL representation of CAST for types with fractional seconds precision
  • PR #1250: Batch append mode for MVMap
  • PR #1248: StringIndexOutOfBoundsException due to undoLog map
  • PR #1246: Detect disabled tests
  • PR #1242: Add implementation of SQLXML interface
  • PR #1241: Various tweaks in attempting to fix TestDiskFull test
  • PR #1240: Optimise ValueLobDB comparison methods
  • PR #1239: Don't try to find tools.jar on Java 9+
  • PR #1238: remove unfinished android API
  • PR #1237: remove JaQu
  • PR #1236: remove STORE_LOCAL_TIME code
  • PR #1235: Do not use deprecated Class.newInstance()
  • PR #1234: Fix NPE in Parser.parseMergeUsing()
  • PR #1233: Simplify old lob ValueLob class
  • Issue 1227: lob growth in pagestore mode
  • PR #1230: clean up some javadoc and some throws clauses
  • PR #1229: Create UndoLog only when necessary and remove outdated code
  • PR #1228: Remove some PageStore+MVCC leftovers
  • PR #1226: Fix inconsistencies in checks for transaction isolation level
  • PR #1224: Enable Java 10 testing on Travis
  • PR #1223: Fix issues with testing on latest Java versions
  • PR #1222: Leftovers handling
  • Issue #1220: JDK-9 build fails due to usage of java.xml.bind in external authentication
  • PR #1218: Test utilities only once during TestAll
  • PR #1217: Postpone session.endStatement() until after commit
  • PR #1213: KillRestart fix
  • PR #1211: Assorted minor changes
  • Issue #1204: Always use MVCC with MVStore and never use it with PageStore
  • PR #1206: Forbid reconnects in non-regular modes in TestScript
  • PR #1205: Misc test fixes
  • Issue 1198: Enable MULTI_THREADED by default for MVStore mode
  • Issue #1195: Calling setBytes to set VARCHAR field fails
  • PR #1197: Fix or suppress errors in tests
  • PR #1194: TestKillRestartMulti: A map named undoLog-1 already exists
  • PR #1193: enable TestRandomSQL on non-memory databases
  • PR #1191: External authentication with datasource issue
  • PR #1188: Undo log split to reduce contention
  • PR #1186: TransactionMap::sizeAsLong() optimized - temp map eliminated
  • PR #1185: Improve naming of the object id field in Prepared
  • Issue #1196: Feature request for MS SQL Server Compatibility Mode
  • Issue #1177: Resource leak in Recover tool
  • PR #1183: Improve concurrency of connection pool with wait-free implement
  • Issue #1073: H2 v1.4.197 fails to open an existing database with the error [Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"]
  • PR #1179: Drop TransactionMap.readLogId
  • PR #1181: Improve CURRENT_TIMESTAMP and add LOCALTIME and LOCALTIMESTAMP
  • PR #1176: Magic value replacement with constant
  • PR #1171: Introduce last committed value into a VersionedValue
  • PR #1175: tighten test conditions - do not ignore any exceptions
  • PR #1174: Remove mapid
  • PR #1173: protect first background exception encountered and relate it to clients
  • PR #1172: Yet another attempt to tighten that testing loop
  • PR #1170: Add support of CONTINUE | RESTART IDENTITY to TRUNCATE TABLE
  • Issue #1168: ARRAY_CONTAINS() returning incorrect results when inside subquery with Long elements.
  • PR #1167: MVStore: Undo log synchronization removal
  • PR #1166: Add SRID support to EWKT format
  • PR #1165: Optimize isTargetRowFound() and buildColumnListFromOnCondition() in MergeUsing
  • PR #1164: More fixes for parsing of MERGE USING and other changes in Parser
  • PR #1154: Support for external authentication
  • PR #1162: Reduce allocation of temporary strings
  • PR #1158: make fields final
  • Issue #1129: TestCrashAPI / TestFuzzOptimizations throw OOME on Travis in PageStore mode
  • PR #1156: Add support for SQL:2003 WITH [NO] DATA to CREATE TABLE AS
  • PR #1149: fix deadlock between OnExitDatabaseCloser.DATABASES and Engine.DATABASES
  • PR #1152: skip intermediate DbException object when creating SQLException
  • PR #1144: Add missing schema name with recursive view
  • Issue #1091: get rid of the "New" class
  • PR #1147: Assorted minor optimizations
  • PR #1145: Reduce code duplication
  • PR #1142: Misc small fixes
  • PR #1141: Assorted optimizations and fixes
  • PR #1138, #1139: Fix a memory leak caused by DatabaseCloser objects
  • PR #1137: Step toward making transaction commit atomic
  • PR #1136: Assorted minor optimizations
  • PR #1134: Detect possible overflow in integer division and optimize some code
  • PR #1133: Implement Comparable<Value> in CompareMode and optimize ValueHashMap.keys()
  • PR #1132: Reduce allocation of ExpressionVisitor instances
  • PR #1130: Improve TestScript and TestCrashAPI
  • PR #1128: Fix ON DUPLICATE KEY UPDATE with ENUM
  • PR #1127: Update JdbcDatabaseMetaData.getSQLKeywords() and perform some minor optimizations
  • PR #1126: Fix an issue with code coverage and building of documentation
  • PR #1123: Fix TCP version check
  • PR #1122: Assorted changes
  • PR #1121: Add some protection to ValueHashMap against hashes with the same less significant bits
  • Issue #1097: H2 10x slower than HSQLDB and 6x than Apache Derby for specific query with GROUP BY and DISTINCT subquery
  • Issue #1093: Use temporary files for ResultSet buffer tables in MVStore
  • PR #1117: Fix sorting with distinct in ResultTempTable
  • Issue #1095: Add support for INSERT IGNORE INTO <table> (<columns>) SELECT in MySQL Mode
  • PR #1114: Minor cleanup and formatting fixes
  • PR #1112: Improve test scripts
  • PR #1111: Use a better fix for issue with SRID
  • Issue #1107: Restore support of DATETIME2 with specified fractional seconds precision
  • Issue #1106: Get rid of SwitchSource
  • PR #1105: Assorted minor changes
  • Issue #1102: CREATE SYNONYM rejects valid definition
  • PR #1103: Remove redundant synchronization
  • Issue #1048: 1.4.197 regression. org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "SYS"
  • PR #1101: Move some tests in better place and add an additional test for 2PC
  • PR #1100: Fix Insert.prepareUpdateCondition() for PageStore
  • PR #1098: Fix some issues with NULLS FIRST / LAST
  • Issue #1089: Parser does not quote words INTERSECTS, DUAL, TOP
  • Issue #230: Renaming a column does not update foreign key constraint
  • Issue #1091 Get rid if the New class
  • PR #1087: improve performance of planning large queries
  • PR #1085: Add tests for simple one-column index sorting
  • PR #1084: re-enable some pagestore testing
  • PR #1083: Assorted changes
  • Issue #394: Recover tool places COLLATION and BINARY_COLLATION after temporary tables
  • PR #1081: Session.getTransactionId should return a more distinguishing value
  • Improve the script-based unit testing to check the error code of the exception thrown.
  • Issue #1041: Support OR syntax while creating trigger
  • Issue #1023: MVCC and existing page store file
  • Issue #1003: Decrypting database with incorrect password renders the database corrupt
  • Issue #873: No error when `=` in equal condition when column is not of array type
  • Issue #1069: Failed to add DATETIME(3) column since 1.4.197
  • Issue #456: H2 table privileges referring to old schema after schema rename
  • Issue #1062: Concurrent update in table "SYS" caused by Analyze.analyzeTable()
  • Yet another fix to Page memory accounting
  • Replace MVStore.ASSERT variable with assertions
  • Issue #1063: Leftover comments about enhanced for loops
  • PR #1059: Assorted minor changes
  • PR #1058: Txcommit atomic
  • Issue #1038: ora_hash function implementation off by one
  • PR #1054: Introduce overflow bit in tx state
  • Issue #1047: Support DISTINCT in custom aggregate functions
  • PR #1051: Atomic change of transaction state
  • PR #1046: Split off Transaction TransactionMap VersionedValue
  • PR #1045: TransactionStore move into separate org.h2.mvstore.tx package
  • PR #1044: Encapsulate TransactionStore.store field in preparation to a move
  • PR #1040: generate less garbage for String substring+trim
  • PR #1035: Minor free space accounting changes
  • Issue #1034: MERGE USING should not require the same column count in tables
  • PR #1033: Fix issues with BUILTIN_ALIAS_OVERRIDE=1
  • PR #1031: Drop schema rights together with schema
  • PR #1029: No need to remove orphaned LOBs when the db is read-only
  • Issue #1027: Add support for fully qualified names in MySQL compatibility mode
  • Issue #178: INSERT ON DUPLICATE KEY UPDATE returns wrong generated key
  • PR #1025: Remove BitField and replace its usages with BitSet
  • Issue #1019: Console incorrectly sorts BigDecimal columns alphanumerically
  • PR #1021: Update JdbcDatabaseMetaData to JDBC 4.1 (Java 7)
  • Issue #992: 1.4.197 client cannot use DatabaseMetaData with 1.4.196 and older server
  • Issue #1016: ResultSet.getObject() should return enum value, not ordinal
  • Issue #1012: NPE when querying INFORMATION_SCHEMA.COLUMNS on a view that references an ENUM column
  • Issue #1010: MERGE USING table not found with qualified table
  • PR #1009: Fix ARRAY_AGG with ORDER BY and refactor aggregates
  • Issue #1006: "Empty enums are not allowed" in 1.4.197 (mode=MYSQL)
  • PR #1007: Copy also SRID in ValueGeometry.getGeometry()
  • PR #1004: Preserve type names in more places especially for UUID
  • Issue #1000: Regression in INFORMATION_SCHEMA.CONSTRAINTS.CONSTRAINT_TYPE content
  • Issue #997: Can not delete from tables with enums
  • Issue #994: Too much column in result set for GENERATED_KEYS on table with DEFAULT
  • PR #993: Fix some compiler warnings and improve assert*() methods
  • PR #991: Generate shorter queries in JdbcDatabaseMetaData.getTables() and remove some dead code
  • PR #989: Fix more issues with range table and improve its documentation

New in H2 Database Engine Portable 1.4.197 Beta (Mar 19, 2018)

  • PR #984: Minor refactorings in Parser
  • Issue #933: MVStore background writer endless loop
  • PR #981: Reorganize date-time functions
  • PR #980: Add Parser.toString() method for improved debugging experience
  • PR #979: Remove support of TCP protocol versions 6 and 7
  • PR #977: Add database versions to javadoc of TCP protocol versions and update dictionary.txt
  • PR #976: Add and use incrementDateValue() and decrementDateValue()
  • Issue #974: Inline PRIMARY KEY definition loses its name
  • PR #972: Add META-INF/versions to all non-Android jars that use Bits
  • PR #971: Update ASM from 6.1-beta to 6.1
  • PR #970: Added support for ENUM in prepared statement where clause
  • PR #968: Assorted changes
  • PR #967: Adds ARRAY_AGG function
  • PR #966: Do not include help and images in client jar
  • PR #965: Do not include mvstore.DataUtils in client jar and other changes
  • PR #964: Fix TestFunctions.testToCharFromDateTime()
  • PR #963 / Issue #962: Improve documentation of compatibility modes and fix ssl URL description
  • Issue #219: H2 mode MySQL- ON UPDATE CURRENT_TIMESTAMP not supported
  • PR #958: More fixes for PgServer
  • PR #957: Update database size information and links in README.md
  • PR #956: Move tests added in 821117f1db120a265647a063dca13ab5bee98efc to a proper place
  • PR #955: Support getObject(?, Class) in generated keys
  • PR #954: Avoid incorrect reads in iterators of TransactionMap
  • PR #952: Optimize arguments for MVMap.init()
  • PR #949: Fix table borders in PDF and other changes
  • PR #948: Fix some grammar descriptions and ALTER TABLE DROP COLUMN parsing
  • PR #947: Fix building of documentation and use modern names of Java versions
  • PR #943: Assorted changes in documentation and a fix for current-time.sql
  • PR #942: Fix page numbers in TOC in PDF and move System Tables into own HTML / section in PDF
  • PR #941: Use >> syntax in median.sql and move out more tests from testScript.sql
  • PR #940: add Support for MySQL: DROP INDEX index_name ON tbl_name
  • PR #939: Short syntax for SQL tests
  • Issue #935: The "date_trunc" function is not recognized for 'day'
  • PR #936: Fix font size, line length, TOC, and many broken links in PDF
  • PR #931: Assorted changes in documentation
  • PR #930: Use Math.log10() and remove Mode.getOracle()
  • PR #929: Remove Mode.supportOffsetFetch
  • PR #928: Show information about office configuration instead of fallback PDF generation mode
  • PR #926: Describe datetime fields in documentation
  • PR #925: Fix time overflow in DATEADD
  • Issue #416: Add support for DROP SCHEMA x { RESTRICT | CASCADE }
  • PR #922: Parse and treat fractional seconds precision as described in SQL standard
  • Issue #919: Add support for mixing adding constraints and columns in multi-add ALTER TABLE statement
  • PR #916: Implement TABLE_CONSTRAINTS and REFERENTIAL_CONSTRAINTS from the SQL standard
  • PR #915: Implement INFORMATION_SCHEMA.KEY_COLUMN_USAGE from SQL standard
  • PR #914: don't allow null values in ConcurrentArrayList
  • PR #913: Assorted changes in tests and documentation
  • Issue #755: Missing FLOAT(precision)?
  • PR #911: Add support for MySQL-style ALTER TABLE ADD ... FIRST
  • Issue #409: Support derived column list syntax on table alias
  • PR #908: remove dead code
  • PR #907: Nest joins only if required and fix some issues with complex joins
  • PR #906: Fix obscure error on non-standard SELECT * FROM A LEFT JOIN B NATURAL JOIN C
  • PR #805: Move some JOIN tests from testScript.sql to own file
  • PR #804: Remove unused parameters from readJoin() and readTableFilter()
  • Issue #322: CSVREAD WHERE clause containing ORs duplicates number of rows
  • PR #902: Remove DbSettings.nestedJoins
  • PR #900: Convert duplicate anonymous classes in TableFilter to nested for reuse
  • PR #899: Fix ON DUPLICATE KEY UPDATE for inserts with multiple rows
  • PR #898: Parse TIME WITHOUT TIME ZONE and fix TIMESTAMP as column name
  • PR #897: Update JTS to version 1.15.0 from LocationTech
  • PR #896: Assorted changes in help.csv
  • PR #895: Parse more variants of timestamps with time zones
  • PR #893: TIMESTAMP WITHOUT TIME ZONE, TIMEZONE_HOUR, and TIMEZONE_MINUTE
  • PR #892: Assorted minor changes in Parser
  • PR #891: Update documentation of date-time types and clean up related code a bit
  • PR #890: Implement conversions for TIMESTAMP WITH TIME ZONE
  • PR #888: Fix two-phase commit in MVStore
  • Issue #884: Wrong test Resources path in pom.xml
  • PR #886: Fix building of documentation
  • PR #883: Add support for TIMESTAMP WITH TIME ZONE to FORMATDATETIME
  • PR #881: Reimplement dateValueFromDate() and nanosFromDate() without a Calendar
  • PR #880: Assorted date-time related changes
  • PR #879: Reimplement TO_DATE without a Calendar and fix a lot of bugs an incompatibilities
  • PR #878: Fix IYYY in TO_CHAR and reimplement TRUNCATE without a Calendar
  • PR #877: Reimplement TO_CHAR without a Calendar and fix 12 AM / 12 PM in it
  • PR #876: Test out of memory
  • PR #875: Improve date-time related parts of documentation
  • PR #872: Assorted date-time related changes
  • PR #871: Fix OOME in Transfer.readValue() with large CLOB V2
  • PR #867: TestOutOfMemory stability
  • Issue #834: Add support for the SQL standard FILTER clause on aggregate functions
  • PR #864: Minor changes in DateUtils and Function
  • PR #863: Polish: use isEmpty() to check whether the collection is empty or not.
  • PR #862: Convert constraint type into enum
  • PR #861: Avoid resource leak
  • PR #860: IndexCursor inList
  • PR #858 / Issue #690 and others: Return all generated rows and columns from getGeneratedKeys()
  • Make the JDBC client independent of the database engine
  • PR #857: Do not write each SQL error multiple times in TestScript
  • PR #856: Fix TestDateTimeUtils.testDayOfWeek() and example with ANY(?
  • PR #855: Reimplement DATEADD without a Calendar and fix some incompatibilities
  • PR #854: Improve test stability
  • PR #851: Reimplement DATEDIFF without a Calendar
  • Issue #502: SQL "= ANY (?)" supported?
  • PR #849: Encode date and time in fast and proper way in PgServerThread
  • PR #847: Reimplement remaining parts of EXTRACT, ISO_YEAR, etc without a Calendar
  • PR #846: Read known fields directly in DateTimeUtils.getDatePart()
  • Issue #832: Extract EPOCH from a timestamp
  • PR #844: Add simple implementations of isWrapperFor() and unwrap() to JdbcDataSource
  • PR #843: Add MEDIAN to help.csv and fix building of documentation
  • PR #841: Support indexes with nulls last for MEDIAN aggregate
  • PR #840: Add MEDIAN aggregate
  • PR #839: TestTools should not leave testing thread in interrupted state
  • PR #838: (tests) Excessive calls to Runtime.getRuntime().gc() cause OOM for no reason
  • Don't use substring when doing StringBuffer#append
  • PR #837: Use StringUtils.replaceAll() in Function.replace()
  • PR #836: Allow to read invalid February 29 dates with LocalDate as March 1
  • PR #835: Inline getTimeTry() into DateTimeUtils.getMillis()
  • PR #827: Use dateValueFromDate() and nanosFromDate() in parseTimestamp()
  • Issue #115: to_char fails with pattern FM0D099
  • PR #825: Merge code for parsing and formatting timestamp values
  • Enums for ConstraintActionType, UnionType, and OpType
  • PR 824: Add partial support for INSERT IGNORE in MySQL mode
  • PR #823: Use ValueByte.getInt() and ValueShort.getInt() in convertTo()
  • PR #820: Fix some compiler warnings
  • PR #818: Fixes for remaining issues with boolean parameters
  • Use enum for file lock method
  • PR #817: Parse also 1 as true and 0 as false in Utils.parseBoolean()
  • PR #815: Fix count of completed statements
  • PR #814: Method.isVarArgs() is available on all supported platforms
  • Issue #812: TIME values should be in range 0:00:00.000000000 23:59:59.999999999?
  • PR #811: Issues with Boolean.parseBoolean()
  • PR #809: Use type constants from LocalDateTimeUtils directly
  • PR #808: Use HmacSHA256 provided by JRE
  • PR #807: Use SHA-256 provided by JRE / Android and use rotateLeft / Right in Fog
  • PR #806: Implement setBytes() and setString() with offset and len
  • PR #805: Improve support of TIMESTAMP WITH TIME ZONE
  • PR #803: Use new ArrayList(Collection) and assertThrows()
  • PR #802: Use Arrays.copyOf() and Arrays.copyOfRange()
  • PR #801: Fix NULL support in PgServer for primitive types too
  • PR #800: More fixes in date-time types for ODBC drivers
  • PR #798: Add partial support of DATE, TIME, and TIMESTAMP data types to PgServer
  • PR #799: Use result of ArrayList.remove()
  • PR #797: Add ceilingKey() and floorKey() to TransactionMap (version 2)
  • PR #796: Add MDY to DateStyle in PgServerThread
  • PR #794: Sort files in generated jars
  • PR #793: Change return type of Value.getBoolean() to boolean (unwrapped)
  • PR #792: Inherit classpath from parent process
  • PR #791: Switch to JaCoCo code coverage
  • PR #788: Update lists of keywords
  • PR #789: Map DATE in Oracle mode to ValueTimestamp
  • PR #787: Assorted changes
  • PR #785: Optimize NULL handling in MVSecondaryIndex.add()
  • PR #783: Add Bits implementation for Java 9 and later versions
  • PR #784: Hardcoded port numbers should not be used in unit tests
  • PR #780: Close JavaFileManager after use.
  • PR #782: Leftover shared lock after release
  • PR #781: Locks left behind after commit
  • PR #778: Reduce code duplication
  • PR #775: Fix building of documentation and zip
  • PR #774: Assorted changes
  • PR #773: Better checks for arguments of partial LOB reading methods
  • PR #772: getBinaryStream() and getCharacterStream() with pos and length
  • Issue #754: Make NUMERIC type read as NUMERIC
  • PR #768: Add DataUtils.parseChecksummedMap()
  • PR #769: Do not copy result of DataUtils.parseMap() to a new maps
  • PR #766: Minor clean up of DateTimeUtils
  • PR #764: Make use of try-with-resources statement
  • Issue #406: Return from ResultSet.getObject not in line with JDBC specification
  • Issue #710: Misleading exception message when INSERT has no value for self referential 'AS' column
  • PR #763: Add DataUtils.getMapName()
  • PR #762: Add row deletion confirmation to web console
  • PR #760: Assorted minor optimizations
  • PR #759: Improve the look of error messages in web console
  • PR #758: Allocate less amount of garbage
  • PR #757: Fix handling of UUID in Datatype.readValue()
  • PR #753: Optimize StringUtils.trim() and remove StringUtils.equals()
  • PR #752: Use predefined charsets instead of names where possible
  • PR #750: Use AtomicIntegerArray and StandardCharsets
  • PR #749: Fix some build checks in sources
  • Issue #740: TestWeb hangups if webSSL=true specified in configuration
  • Issue #736: Copyright years in sources
  • Issue #744: TestFile failure on Java 9 and Java 10
  • PR #741: More cleanups in LocalDateTimeUtils and other minor changes
  • PR #743: Change REGEXP_REPLACE mode for MariaDB and PostgreSQL
  • Issue#646 NPE in CREATE VIEW WITH RECURSIVE & NON_RECURSIVE CTE
  • PR #738: Copy javadoc to *BackwardsCompat to fix building of documentation
  • PR #735: Add support of java.time.Instant V2
  • PR #733: Remove JPA/ORM configuration txt files as they're already integrated
  • PR #732: Fix ==
  • PR #730: Implement enquoteIdentifier() and isSimpleIdentifier() from JDBC 4.3
  • PR #729: Grammar documentation change
  • PR #727: Integer/Long.compare(x, y) can be used to compare primitive values
  • PR #726: Fixes in tests
  • Issue #725: FilePathMem.tryLock() fails since Java 9
  • PR #723: Clean up LocalDateTimeUtils
  • PR #724: Use StringBuilder instead of StringBuffer
  • PR #720: DROP TABLE RESTRICT shouldn't drop foreign keys in other tables
  • PR #722: Assorted minor changes
  • Issue #638: Oracle mode: incompatible regexp back-reference syntax
  • Make ALL a reserved keyword
  • Issue #311: Avoid calling list.toArray(new X[list.size()]) for performance
  • PR #715: Better getObject error message
  • PR #714: SecureRandom is already synchronized
  • PR #712: Return properly encoded UUID from SimpleResultSet.getBytes()
  • PR #711: TestFunctions less english dependent
  • Issue #644: Year changing from negative -509 to a positive 510.
  • PR #706: SIGNAL function
  • PR #704: added Javascript support for Triggers' source
  • Issue #694: Oracle syntax for adding NOT NULL constraint not supported.
  • Issue #699: When using an index for sorting, the index is ignored when also using NULLS LAST/FIRST
  • Issue #697: FilePathDisk.newInputStream fails for contextual class loading
  • Issue #695: jdbc:postgresql protocol connection issue in H2 Console Application in case of redshift driver in classpath
  • Fix 'file not closed' when using FILE_READ
  • Fix bug in LinkSchema tool when object exists with same name in different schemas
  • Issue #675: Fix date operations on Locales with non-Gregorian calendars
  • Fix removal of LOB when rolling back transaction on a table containing more than one LOB column.
  • Issue #654: List ENUM type values in INFORMATION_SCHEMA.COLUMNS
  • Issue #650: Simple nested SELECT causes error for table with TIMESTAMP WITH TIMEZONE column
  • Issue #654: List ENUM type values in INFORMATION_SCHEMA.COLUMNS
  • Issue #668: Fail of an update command on large table with ENUM column
  • Issue #662: column called CONSTRAINT is not properly escaped when storing to metadata
  • Issue #660: Outdated java version mentioned on http://h2database.com/html/build.html#providing_patches
  • Issue #643: H2 doesn't use index when I use IN and EQUAL in one query
  • Reset transaction start timestamp on ROLLBACK
  • Issue #632: CREATE OR REPLACE VIEW creates incorrect columns names
  • Issue #630: Integer overflow in CacheLRU can cause unrestricted cache growth
  • Issue #497: Fix TO_DATE in cases of 'inline' text. E.g. the "T" and "Z" in to_date('2017-04-21T00:00:00Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
  • Fix bug in MySQL/ORACLE-syntax silently corrupting the modified column in cases of setting the 'NULL'- or 'NOT NULL'-constraint. E.g. alter table T modify C NULL;
  • Issue #570: MySQL compatibility for ALTER TABLE .. DROP INDEX
  • Issue #537: Include the COLUMN name in message "Numeric value out of range"
  • Issue #600: ROW_NUMBER() behaviour change in H2 1.4.195
  • Fix a bunch of race conditions found by vmlens.com, thank you to vmlens for giving us a license.
  • PR #597: Support more types in getObject
  • Issue #591: Generated SQL from WITH-CTEs does not include a table identifier
  • PR #593: Make it possible to create a cluster without using temporary files.
  • PR #592: "Connection is broken: "unexpected status 16777216" [90067-192]" message when using older h2 releases as client
  • Issue #585: MySQL mode DELETE statements compatibility
  • PR #586: remove extra tx preparation
  • PR #568: Implement MetaData.getColumns() for synonyms.
  • Issue #581: org.h2.tools.RunScript assumes -script parameter is part of protocol
  • Fix a deadlock in the TransactionStore
  • PR #579: Disallow BLOB type in PostgreSQL mode
  • Issue #576: Common Table Expression (CTE): WITH supports INSERT, UPDATE, MERGE, DELETE, CREATE TABLE ...
  • Issue #493: Query with distinct/limit/offset subquery returns unexpected rows
  • Issue #575: Support for full text search in multithreaded mode
  • Issue #569: ClassCastException when filtering on ENUM value in WHERE clause
  • Issue #539: Allow override of builtin functions/aliases
  • Issue #535: Allow explicit paths on Windows without drive letter
  • Issue #549: Removed UNION ALL requirements for CTE
  • Issue #548: Table synonym support
  • Issue #531: Rollback and delayed meta save.
  • Issue #515: "Unique index or primary key violation" in TestMvccMultiThreaded
  • Issue #458: TIMESTAMPDIFF() test failing. Handling of timestamp literals.
  • PR #546: Fixes the missing file tree.js in the web console
  • Issue #543: Prepare statement with regexp will not refresh parameter after metadata change
  • PR #536: Support TIMESTAMP_WITH_TIMEZONE 2014 JDBC type
  • Fix bug in parsing ANALYZE TABLE xxx SAMPLE_SIZE yyy
  • Add padding for CHAR(N) values in PostgreSQL mode
  • Issue #89: Add DB2 timestamp format compatibility

New in H2 Database Engine Portable 1.4.196 (Mar 19, 2018)

  • Issue#479 Allow non-recursive CTEs (WITH statements), patch from stumc
  • Fix startup issue when using "CHECK" as a column name.
  • Issue #423: ANALYZE performed multiple times on one table during execution of the same statement.
  • Issue #426: Support ANALYZE TABLE statement
  • Issue #438: Fix slow logging via SLF4J (TRACE_LEVEL_FILE=4).
  • Issue #472: Support CREATE SEQUENCE ... ORDER as a NOOP for Oracle compatibility
  • Issue #479: Allow non-recursive Common Table Expressions (CTE)
  • On Mac OS X, with IPv6 and no network connection, the Console tool was not working as expected.

New in H2 Database Engine Portable 1.4.195 Beta (Apr 24, 2017)

  • Lazy query execution support.
  • Added API for handling custom data types (System property "h2.customDataTypesHandler", API org.h2.api.CustomDataTypesHandler).
  • Added support for invisible columns.
  • Added an ENUM data type, with syntax similar to that of MySQL and Oracle.
  • MVStore: for object data types, the cache size memory estimation was sometimes far off in a read-only scenario. This could result in inefficient cache usage.

New in H2 Database Engine Portable 1.4.194 Beta (Mar 11, 2017)

  • Issue #453: MVStore setCacheSize() should also limit the cacheChunkRef.
  • Issue #448: Newly added TO_DATE and TO_TIMESTAMP functions have wrong datatype.
  • The "nioMemLZF" filesystem now supports an extra option "nioMemLZF:12:" to tweak the size of the compress later cache.
  • Various multi-threading fixes and optimisations to the "nioMemLZF" filesystem.
  • [API CHANGE] #439: the JDBC type of TIMESTAMP WITH TIME ZONE changed from Types.OTHER (1111) to Types.TIMESTAMP_WITH_TIMEZONE (2014)
  • #430: Subquery not cached if number of rows exceeds MAX_MEMORY_ROWS.
  • #411: "TIMEZONE" should be "TIME ZONE" in type "TIMESTAMP WITH TIMEZONE".
  • PR #418, Implement Connection#createArrayOf and PreparedStatement#setArray.
  • PR #427, Add MySQL compatibility functions UNIX_TIMESTAMP, FROM_UNIXTIME and DATE.
  • #429: Tables not found : Fix some Turkish locale bugs around uppercasing.
  • Fixed bug in metadata locking, obscure combination of DDL and SELECT SEQUENCE.NEXTVAL required.
  • Added index hints: SELECT * FROM TEST USE INDEX (idx1, idx2).
  • Add a test case to ensure that spatial index is used with and order by command by Fortin N.
  • Fix multi-threaded mode update exception "NullPointerException", test case by Anatolii K.
  • Fix multi-threaded mode insert exception "Unique index or primary key violation", test case by Anatolii K.
  • Implement ILIKE operator for case-insensitive matching.
  • Optimise LIKE queries for the common cases of '%Foo' and '%Foo%'.
  • Issue #387: H2 MSSQL Compatibility Mode - Support uniqueidentifier.
  • Issue #401: NPE in "SELECT DISTINCT * ORDER BY".
  • Added BITGET function.
  • Fixed bug in FilePathRetryOnInterrupt that caused infinite loop.
  • PR #389, Handle LocalTime with nanosecond resolution, patch by katzyn.
  • PR #382, Recover for "page store" H2 breaks LOBs consistency, patch by vitalus.
  • PR #393, Run tests on Travis, patch by marschall.
  • Fix bug in REGEX_REPLACE, not parsing the mode parameter.
  • ResultSet.getObject(..., Class) threw a ClassNotFoundException if the JTS suite was not in the classpath.
  • File systems: the "cache:" file system, and the compressed in-memory file systems memLZF and nioMemLZF did not correctly support concurrent reading and writing.
  • TIMESTAMP WITH TIMEZONE: serialization for the PageStore was broken.

New in H2 Database Engine Portable 1.4.193 Beta (Nov 1, 2016)

  • WARNING: THE MERGE BELOW WILL AFFECT ANY 'TIMESTAMP WITH TIMEZONE' INDEXES. You will need to drop and recreate any such indexes.
  • PR #364: fix compare TIMESTAMP WITH TIMEZONE
  • Fix bug in picking the right index for INSERT..ON DUPLICATE KEY UPDATE when there are both UNIQUE and PRIMARY KEY constraints.
  • Issue #380: Error Analyzer doesn't show source code
  • Remove the "TIMESTAMP UTC" datatype, an experiment that was never finished.
  • PR #363: Added support to define last IDENTIFIER on a Trigger.
  • PR #366: Tests for timestamps
  • PR #361: Improve TimestampWithTimeZone javadoc
  • PR #360: Change getters in TimestampWithTimeZone to int
  • PR #359: Added missing source encoding. Assuming UTF-8.
  • PR #353: Add support for converting JAVA_OBJECT to UUID
  • PR #358: Add support for getObject(int|String, Class)
  • PR #357: Server: use xdg-open to open the WebConsole in the user's preferred browser on Linux
  • PR #356: Support for BEFORE and AFTER clauses when using multiple columns in ALTER TABLE ADD
  • PR #351: Respect format codes from Bind message when sending results
  • ignore summary line when compiling stored procedure
  • PR #348: pg: send RowDescription in response to Describe (statement variant), patch by kostya-sh
  • PR #337: Update russian translation, patch by avp1983
  • PR #329: Update to servlet API version 3.1.0 from 3.0.1, patch by Mat Booth
  • PR #331: ChangeFileEncryption progress logging ignores -quiet flag, patch by Stefan Bodewig
  • PR #325: Make Row an interface
  • PR #323: Regular expression functions (REGEXP_REPLACE, REGEXP_LIKE) enhancement, patch by Akkuzin
  • Use System.nanoTime for measuring query statistics
  • Issue #324: Deadlock when sending BLOBs over TCP
  • Fix for creating and accessing views in MULTITHREADED mode, test-case courtesy of Daniel Rosenbaum
  • Issue #266: Spatial index not updating, fixed by merging PR #267
  • PR #302: add support for "with"-subqueries into "join" & "sub-query" statements
  • Issue #299: Nested derived tables did not always work as expected.
  • Use interfaces to replace the java version templating, idea from Lukas Eder.
  • Issue #295: JdbcResultSet.getObject(int, Class) returns null instead of throwing.
  • Mac OS X: Console tool process did not stop on exit.
  • MVStoreTool: add "repair" feature.
  • Garbage collection of unused chunks should be faster still.
  • MVStore / transaction store: opening a store in read-only mode does no longer loop.
  • MVStore: disabled the file system cache by default, because it limits concurrency when using larger databases and many threads. To re-enable, use the file name prefix "cache:".
  • MVStore: add feature to set the cache concurrency.
  • File system nioMemFS: support concurrent reads.
  • File systems: the compressed in-memory file systems now compress better.
  • LIRS cache: improved hit rate because now added entries get hot if they were in the non-resident part of the cache before.

New in H2 Database Engine Portable 1.4.192 Beta (Nov 1, 2016)

  • Garbage collection of unused chunks should now be faster.
  • Prevent people using unsupported combination of auto-increment columns and clustering mode.
  • Support for DB2 time format, patch by Niklas Mehner
  • Added support for Connection.setClientInfo() in compatibility modes for DB2, Postgresql, Oracle and MySQL.
  • Issue #249: Clarify license declaration in Maven POM xml
  • Fix NullPointerException in querying spatial data through a sub-select.
  • Fix bug where a lock on the SYS table was not released when closing a session that contained a temp table with an LOB column.
  • Issue #255: ConcurrentModificationException with multiple threads in embedded mode and temporary LOBs
  • Issue #235: Anonymous SSL connections fail in many situations
  • Fix race condition in FILE_LOCK=SOCKET, which could result in the watchdog thread not running
  • Experimental support for datatype TIMESTAMP WITH TIMEZONE
  • Add support for ALTER TABLE ... RENAME CONSTRAINT .. TO ...
  • Add support for PostgreSQL ALTER TABLE ... RENAME COLUMN .. TO ...
  • Add support for ALTER SCHEMA [ IF EXISTS ]
  • Add support for ALTER TABLE [ IF EXISTS ]
  • Add support for ALTER VIEW [ IF EXISTS ]
  • Add support for ALTER INDEX [ IF EXISTS ]
  • Add support for ALTER SEQUENCE [ IF EXISTS ]
  • Improve performance of cleaning up temp tables - patch from Eric Faulhaber.
  • Fix bug where table locks were not dropped when the connection closed
  • Fix extra CPU usage caused by query planner enhancement in 1.4.191
  • improve performance of queries that use LIKE 'foo%' - 10x in the case of one of my queries
  • The function IFNULL did not always return the result in the right data type.
  • Issue #231: Possible infinite loop when initializing the ObjectDataType class when concurrently writing into MVStore.

New in H2 Database Engine Portable 1.4.191 Beta (Jan 21, 2016)

  • Issue #229: DATEDIFF does not work for 'WEEK'.
  • Issue #156: Add support for getGeneratedKeys() when executing commands via PreparedStatement#executeBatch.
  • Issue #195: The new Maven uses a .cmd file instead of a .bat file.
  • Issue #212: EXPLAIN PLAN for UPDATE statement did not display LIMIT expression.
  • upport OFFSET without LIMIT in SELECT.
  • Improve error message for METHOD_NOT_FOUND_1/90087.
  • CLOB and BLOB objects of removed rows were sometimes kept in the database file.
  • Server mode: executing "shutdown" left a thread on the server.
  • The condition "in(select...)" did not work correctly in some cases if the subquery had an "order by".
  • Issue #184: The Platform-independent zip had Windows line endings in Linux scripts.
  • Issue #186: The "script" command did not include sequences of temporary tables.
  • Issue #115: to_char fails with pattern FM0D099.

New in H2 Database Engine Portable 1.4.190 Beta (Jan 21, 2016)

  • Pull request #183: optimizer hints (so far without special SQL syntax).
  • Issue #180: In MVCC mode, executing UPDATE and SELECT ... FOR UPDATE simultaneously silently can drop rows.
  • PageStore storage: the cooperative file locking mechanism did not always work as expected (with very slow computers).
  • Temporary CLOB and BLOB objects are now removed while the database is open (and not just when closing the database).
  • MVStore CLOB and BLOB larger than about 25 MB: An exception could be thrown when using the MVStore storage.
  • Add FILE_WRITE function. Patch provided by Nicolas Fortin (Lab-STICC - CNRS UMR 6285 and Ecole Centrale de Nantes)

New in H2 Database Engine Portable 1.4.189 Beta (Oct 9, 2015)

  • Fix bug in XA management when doing rollback after prepare. Patch by Stephane Lacoin.
  • MVStore CLOB and BLOB: An exception with the message "Block not found" could be thrown when using the MVStore storage, when copying LOB objects (for example due to "alter table" on a table with a LOB object), and then re-opening the database.
  • Fix for issue #171: Broken QueryStatisticsData duration data when trace level smaller than TraceSystem.INFO
  • Pull request #170: Added SET QUERY_STATISTICS_MAX_ENTRIES
  • Pull request #165: Fix compatibility postgresql function string_agg
  • Pull request #163: improved performance when not using the default timezone.
  • Local temporary tables with many rows did not work correctly due to automatic analyze.
  • Server mode: concurrently using the same connection could throw an exception "Connection is broken: unexpected status".
  • Performance improvement for metadata queries that join against the COLUMNS metadata table.
  • An ArrayIndexOutOfBoundsException was thrown in some cases when opening an old version 1.3 database, or an 1.4 database with both "mv_store=false" and the system property "h2.storeLocalTime" set to false. It mainly showed up with an index on a time, date, or timestamp column. The system property "h2.storeLocalTime" is no longer supported (MVStore databases always store local time, and PageStore now databases never do).

New in H2 Database Engine Portable 1.4.187 Beta (Apr 11, 2015)

  • MVStore: concurrent changes to the same row could result in the exception "The transaction log might be corrupt for key ...". This could only be reproduced with 3 or more threads.
  • Results with CLOB or BLOB data are no longer reused.
  • References to BLOB and CLOB objects now have a timeout. The configuration setting is LOB_TIMEOUT (default 5 minutes). This should avoid growing the database file if there are many queries that return BLOB or CLOB objects, and the database is not closed for a longer time.
  • MVStore: when committing a session that removed LOB values, changes were flushed unnecessarily.
  • Issue 610: possible integer overflow in WriteBuffer.grow().
  • Issue 609: the spatial index did not support NULL (ClassCastException).
  • MVStore: in some cases, CLOB/BLOB data blocks were removed incorrectly when opening a database.
  • MVStore: updates that affected many rows were were slow in some cases if there was a secondary index.
  • Using "runscript" with autocommit disabled could result in a lock timeout on the internal table "SYS".
  • Issue 603: there was a memory leak when using H2 in a web application. Apache Tomcat logged an error message: "The web application ... created a ThreadLocal with key of type [org.h2.util.DateTimeUtils$1]".
  • When using the MVStore, running a SQL script generate by the Recover tool from a PageStore file failed with a strange error message (NullPointerException), now a clear error message is shown.
  • Issue 605: with version 1.4.186, opening a database could result in an endless loop in LobStorageMap.init.
  • Queries that use the same table alias multiple times now work. Before, the select expression list was expanded incorrectly. Example: "select * from a as x, b as x".
  • The MySQL compatibility feature "insert ... on duplicate key update" did not work with a non-default schema.
  • Issue 599: the condition "in(x, y)" could not be used in the select list when using "group by".
  • The LIRS cache could grow larger than the allocated memory.
  • A new file system implementation that re-opens the file if it was closed due to the application calling Thread.interrupt(). File name prefix "retry:". Please note it is strongly recommended to avoid calling Thread.interrupt; this is a problem for various libraries, including Apache Lucene.
  • MVStore: use RandomAccessFile file system if the file name starts with "file:".
  • Allow DATEADD to take a long value for count when manipulating milliseconds.
  • When using MV_STORE=TRUE and the SET CACHE_SIZE setting, the cache size was incorrectly set, so that it was effectively 1024 times smaller than it should be.
  • Concurrent CREATE TABLE... IF NOT EXISTS in the presence of MULTI_THREAD=TRUE could throw an exception.
  • Fix bug in MVStore when creating lots of temporary tables, where we could run out of transaction IDs.
  • Add support for PostgreSQL STRING_AGG function. Patch by Fred Aquiles.
  • Fix bug in "jdbc:h2:nioMemFS" isRoot() function. Also, the page size was increased to 64 KB.

New in H2 Database Engine Portable 1.4.186 Beta (Mar 2, 2015)

  • The Servlet API 3.0.1 is now used, instead of 2.4.
  • MVStore: old chunks no longer removed in append-only mode.
  • MVStore: the cache for page references could grow far too big, resulting in out of memory in some cases.
  • MVStore: orphaned lob objects were not correctly removed in some cases, making the database grow unnecessarily.
  • MVStore: the maximum cache size was artificially limited to 2 GB (due to an integer overflow).
  • MVStore / TransactionStore: concurrent updates could result in a "Too many open transactions" exception.
  • StringUtils.toUpperEnglish now has a small cache. This should speed up reading from a ResultSet when using the column name.
  • MVStore: up to 65535 open transactions are now supported. Previously, the limit was at most 65535 transactions between the oldest open and the newest open transaction (which was quite a strange limit).
  • The default limit for in-place LOB objects was changed from 128 to 256 bytes. This is because each read creates a reference to a LOB, and maintaining the references is a big overhead. With the higher limit, less references are needed.
  • Tables without columns didn't work. (The use case for such tables is testing.)
  • The LIRS cache now resizes the table automatically in all cases and no longer needs the averageMemory configuration.
  • Creating a linked table from an MVStore database to a non-MVStore database created a second (non-MVStore) database file.
  • In version 1.4.184, a bug was introduced that broke queries that have both joins and wildcards, for example: select * from dual join(select x from dual) on 1=1
  • Issue 598: parser fails on timestamp "24:00:00.1234" - prevent the creation of out-of-range time values.
  • Allow declaring triggers as source code (like functions).
  • Make the planner use indexes for sorting when doing a GROUP BY where all of the GROUP BY columns are not mentioned in the select.
  • PostgreSQL compatibility: generate_series (as an alias for system_range).
  • Fix missing "column" type in right-hand parameter in ConditionIn.

New in H2 Database Engine Portable 1.4.185 Beta (Jan 16, 2015)

  • In version 1.4.184, "group by" ignored the table name, and could pick a select column by mistake. Example: select 0 as x from system_range(1, 2) d group by d.x;
  • New connection setting "REUSE_SPACE" (default: true). If disabled, all changes are appended to the database file, and existing content is never overwritten. This allows to rollback to a previous state of the database by truncating the database file.
  • Issue 587: MVStore: concurrent compaction and store operations could result in an IllegalStateException.
  • Issue 594: Profiler.copyInThread does not work properly.
  • Script tool: Now, SCRIPT ... TO is always used (for higher speed and lower disk space usage).
  • Script tool: Fix parsing of BLOCKSIZE parameter, original patch by Ken Jorissen.
  • Fix bug in PageStore#commit method - when the ignoreBigLog flag was set, the logic that cleared the flag could never be reached, resulting in performance degradation. Reported by Alexander Nesterov.
  • Issue 552: Implement BIT_AND and BIT_OR aggregate functions.

New in H2 Database Engine Portable 1.4.184 Beta (Dec 19, 2014)

  • In version 1.3.183, indexes were not used if the table contains columns with a default value generated by a sequence. This includes tables with identity and auto-increment columns. This bug was introduced by supporting "rownum" in views and derived tables.
  • MVStore: imported BLOB and CLOB data sometimes disappeared. This was caused by a bug in the ObjectDataType comparison.
  • Reading from a StreamStore now throws an IOException if the underlying data doesn't exist.
  • MVStore: if there is an exception while saving, the store is now in all cases immediately closed.
  • MVStore: the dump tool could go into an endless loop for some files.
  • MVStore: recovery for a database with many CLOB or BLOB entries is now much faster.
  • Group by with a quoted select column name alias didn't work. Example: select 1 "a" from dual group by "a"
  • Auto-server mode: the host name is now stored in the .lock.db file.

New in H2 Database Engine Portable 1.4.183 Beta (Dec 15, 2014)

  • MVStore: the default auto-commit buffer size is now about twice as big. This should reduce the database file size after inserting a lot of data.
  • The built-in functions "power" and "radians" now always return a double.
  • Using "row_number" or "rownum" in views or derived tables had unexpected results if the outer query contained constraints for the given view. Example: select b.nr, b.id from (select row_number() over() as nr, a.id as id from (select id from test order by name) as a) as b where b.id = 1
  • MVStore: the Recover tool can now deal with more types of corruption in the file.
  • MVStore: the TransactionStore now first needs to be initialized before it can be used.
  • Views and derived tables with equality and range conditions on the same columns did not work properly. example: select x from (select x from (select 1 as x) where x > 0 and x < 2) where x = 1
  • The database URL setting PAGE_SIZE setting is now also used for the MVStore.
  • MVStore: the default page split size for persistent stores is now 4096 (it was 16 KB so far). This should reduce the database file size for most situations (in some cases, less than half the size of the previous version).
  • With query literals disabled, auto-analyze of a table with CLOB or BLOB did not work.
  • MVStore: use a mark and sweep GC algorithm instead of reference counting, to ensure used chunks are never overwrite, even if the reference counting algorithm does not work properly.
  • In the multi-threaded mode, updating the column selectivity ("analyze") in the background sometimes did not work.
  • In the multi-threaded mode, database metadata operations did sometimes not work if the schema was changed at the same time (for example, if tables were dropped).
  • Some CLOB and BLOB values could no longer be read when the original row was removed (even when using the MVCC mode).
  • The MVStoreTool could throw an IllegalArgumentException.
  • Improved performance for some date / time / timestamp conversion operations. Thanks to Sergey Evdokimov for reporting the problem.
  • H2 Console: the built-in web server did not work properly if an unknown file was requested.
  • MVStore: the jar file is renamed to "h2-mvstore-*.jar" and is deployed to Maven separately.
  • MVStore: support for concurrent reads and writes is now enabled by default.
  • Server mode: the transfer buffer size has been changed from 16 KB to 64 KB, after it was found that this improves performance on Linux quite a lot.
  • H2 Console and server mode: SSL is now disabled and TLS is used to protect against the Poodle SSLv3 vulnerability. The system property to disable secure anonymous connections is now "h2.enableAnonymousTLS". The default certificate is still self-signed, so you need to manually install another one if you want to avoid man in the middle attacks.
  • MVStore: the R-tree did not correctly measure the memory usage.
  • MVStore: compacting a store with an R-tree did not always work.
  • Issue 581: When running in LOCK_MODE=0, JdbcDatabaseMetaData#supportsTransactionIsolationLevel(TRANSACTION_READ_UNCOMMITTED) should return false
  • Fix bug which could generate deadlocks when multiple connections accessed the same table.
  • Some places in the code were not respecting the value set in the "SET MAX_MEMORY_ROWS x" command
  • Fix bug which could generate a NegativeArraySizeException when performing large (>40M) row union operations
  • Fix "USE schema" command for MySQL compatibility, patch by mfulton
  • Parse and ignore the ROW_FORMAT=DYNAMIC MySQL syntax, patch by mfulton

New in H2 Database Engine Portable 1.4.182 Beta (Oct 17, 2014)

  • MVStore: improved error messages and logging; improved behavior if there is an error when serializing objects.
  • OSGi: the MVStore packages are now exported.
  • With the MVStore option, when using multiple threads that concurrently create indexes or tables, it was relatively easy to get a lock timeout on the "SYS" table.
  • When using the multi-threaded option, the exception "Unexpected code path" could be thrown, specially if the option "analyze_auto" was set to a low value.
  • In the server mode, when reading from a CLOB or BLOB, if the connection was closed, a NullPointerException could be thrown instead of an exception saying the connection is closed.
  • DatabaseMetaData.getProcedures and getProcedureColumns could throw an exception if a user defined class is not available.
  • Issue 584: the error message for a wrong sequence definition was wrong.
  • CSV tool: the rowSeparator option is no longer supported, as the same can be achieved with the lineSeparator.
  • Descending indexes on MVStore tables did not work properly.
  • Issue 579: Conditions on the "_rowid_" pseudo-column didn't use an index when using the MVStore.
  • Fixed documentation that "offset" and "fetch" are also keywords since version 1.4.x.
  • The Long.MIN_VALUE could not be parsed for auto-increment (identity) columns.
  • Issue 573: Add implementation for Methods "isWrapperFor()" and "unwrap()" in other JDBC classes.
  • Issue 572: MySQL compatibility for "order by" in update statements.
  • The change in JDBC escape processing in version 1.4.181 affects both the parser (which is running on the server) and the JDBC API (which is running on the client). If you (or a tool you use) use the syntax "{t 'time}", or "{ts 'timestamp'}", or "{d 'data'}", then both the client and the server need to be upgraded to version 1.4.181 or later.

New in H2 Database Engine Portable 1.4.181 Beta (Aug 7, 2014)

  • Improved MySQL compatibility by supporting "use schema". Thanks a lot to Karl Pietrzak for the patch!
  • Writing to the trace file is now faster, specially with the debug level.
  • The database option "defrag_always=true" did not work with the MVStore.
  • The JDBC escape syntax {ts 'value'} did not interpret the value as a timestamp. The same for {d 'value'} (for date) and {t 'value'} (for time). Thanks to Lukas Eder for reporting the issue.
  • File system abstraction: support replacing existing files using move (currently not for Windows).
  • The statement "shutdown defrag" now compresses the database (with the MVStore). This command can greatly reduce the file size, and is relatively fast, but is not incremental.
  • The MVStore now automatically compacts the store in the background if there is no read or write activity, which should (after some time; sometimes about one minute) reduce the file size. This is still work in progress, feedback is welcome!
  • Change default value of PAGE_SIZE from 2048 to 4096 to more closely match most file systems block size (PageStore only; the MVStore already used 4096).
  • Auto-scale MAX_MEMORY_ROWS and CACHE_SIZE settings by the amount of available RAM. Gives a better out of box experience for people with more powerful machines.
  • Handle tabs like 4 spaces in web console, patch by Martin Grajcar.
  • Issue 573: Add implementation for Methods "isWrapperFor()" and "unwrap()" in JdbcConnection.java, patch by BigMichi1.

New in H2 Database Engine Portable 1.4.180 Beta (Aug 7, 2014)

  • MVStore: the store is now auto-compacted automatically up to some point, to avoid very large file sizes. This area is still work in progress.
  • Sequences of temporary tables (auto-increment or identity columns) were persisted unnecessarily in the database file, and were not removed when re-opening the database.
  • MVStore: an IndexOutOfBoundsException could sometimes occur MVMap.openVersion when concurrently accessing the store.
  • The LIRS cache now re-sizes the internal hash map if needed.
  • Optionally persist session history in the H2 console. (patch from Martin Grajcar)
  • Add client-info property to get the number of servers currently in the cluster and which servers that are available. (patch from Nikolaj Fogh)
  • Fix bug in changing encrypted DB password that kept the file handle open when the wrong password was supplied. (test case from Jens Hohmuth).
  • Issue 567: H2 hangs for a long time then (sometimes) recovers. Introduce a queue when doing table locking to prevent session starvation.

New in H2 Database Engine Portable 1.4.179 Beta (Jun 23, 2014)

  • The license was changed to MPL 2.0 (from 1.0) and EPL 1.0.
  • Issue 565: MVStore: concurrently adding LOB objects (with MULTI_THREADED option) resulted in a NullPointerException.
  • MVStore: reduced dependencies to other H2 classes.
  • There was a way to prevent a database from being re-opened, by creating a column constraint that references a table with a higher id. This is now detected, and creating the table is prohibited. In future versions of H2, most likely creating references to other tables will no longer be supported because of such problems.
  • MVStore: descending indexes with "nulls first" did not work as expected (null was ordered last).
  • Large result sets now always create temporary tables instead of temporary files.
  • When using the PageStore, opening a database failed in some cases with a NullPointerException if temporary tables were used (explicitly, or implicitly when using large result sets).
  • If a database file in the PageStore file format exists, this file and this mode is now used, even if the database URL does not contain "MV_STORE=FALSE". If a MVStore file exists, it is used.
  • Databases created with version 1.3.175 and earlier that contained foreign keys in combination with multi-column indexes could not be opened in some cases. This was due to a bugfix in version 1.3.176: Referential integrity constraints sometimes used the wrong index.
  • MVStore: the ObjectDataType comparison method was incorrect if one key was Serializable and the other was of a common class.
  • Recursive queries with many result rows (more than the setting "max_memory_rows") did not work correctly.
  • The license has changed to MPL 2.0 + EPL 1.0.
  • MVStore: temporary tables from result sets could survive re-opening a database, which could result in a ClassCastException.
  • Issue 566: MVStore: unique indexes that were created later on did not work correctly if there were over 5000 rows in the table.
  • MVStore: creating secondary indexes on large tables results in missing rows in the index.
  • Metadata: the password of linked tables is now only visible for admin users.
  • For Windows, database URLs of the form "jdbc:h2:/test" where considered relative and did not work unless the system property "h2.implicitRelativePath" was used.
  • Windows: using a base directory of "C:/" and similar did not work as expected.
  • Follow JDBC specification on Procedures MetaData, use P0 as return type of procedure.
  • Issue 531: IDENTITY ignored for added column.
  • FileSystem: improve exception throwing compatibility with JDK
  • Spatial Index: adjust costs so we do not use the spatial index if the query does not contain an intersects operator.
  • Fix multi-threaded deadlock when using a View that includes a TableFunction.
  • Fix bug in dividing very-small BigDecimal numbers.

New in H2 Database Engine Portable 1.4.178 Beta (May 2, 2014)

  • Issue 559: Make dependency on org.osgi.service.jdbc optional.
  • Improve error message when the user specifies an unsupported combination of database settings.
  • MVStore: in the multi-threaded mode, NullPointerException and other exceptions could occur.
  • MVStore: some database file could not be compacted due to a bug in the bookkeeping of the fill rate. Also, database file were compacted quite slowly. This has been improved; but more changes in this area are expected.
  • MVStore: support for volatile maps (that don't store changes).
  • MVStore mode: in-memory databases now also use the MVStore.
  • In server mode, appending ";autocommit=false" to the database URL was working, but the return value of Connection.getAutoCommit() was wrong.
  • Issue 561: OSGi: the import package declaration of org.h2 excluded version 1.4.
  • Issue 558: with the MVStore, a NullPointerException could occur when using LOBs at session commit (LobStorageMap.removeLob).
  • Remove the "h2.MAX_MEMORY_ROWS_DISTINCT" system property to reduce confusion. We already have the MAX_MEMORY_ROWS setting which does a very similar thing, and is better documented.
  • Issue 554: Web Console in an IFrame was not fully supported.

New in H2 Database Engine Portable 1.4.177 Beta (Apr 18, 2014)

  • By default, the MV_STORE option is enabled, so it is using the new MVStore storage. The MVCC setting is by default set to the same values as the MV_STORE setting, so it is also enabled by default. For testing, both settings can be disabled by appending ";MV_STORE=FALSE" and/or ";MVCC=FALSE" to the database URL.
  • The file locking method 'serialized' is no longer supported. This mode might return in a future version, however this is not clear right now. A new implementation and new tests would be needed.
  • Enable the new storage format for dates (system property "h2.storeLocalTime"). For the MVStore mode, this is always enabled, but with version 1.4 this is even enabled in the PageStore mode.
  • Implicit relative paths are disabled (system property "h2.implicitRelativePath"), so that the database URL jdbc:h2:test now needs to be written as jdbc:h2:./test.
  • "select ... fetch first 1 row only" is supported with the regular mode. This was disabled so far because "fetch" is now a keyword. See also Mode.supportOffsetFetch.
  • Byte arrays are now sorted in unsigned mode (x'99' is larger than x'09'). (System property "h2.sortBinaryUnsigned", Mode.binaryUnsigned, setting "binary_collation").
  • Csv.getInstance will be removed in future versions of 1.4. Use the public constructor instead.
  • Remove support for the limited old-style outer join syntax using "(+)". Use "outer join" instead. System property "h2.oldStyleOuterJoin".
  • Support the data type "DATETIME2" as an alias for "DATETIME", for MS SQL Server compatibility.
  • Add Oracle-compatible TRANSLATE function, patch by Eric Chatellier.

New in H2 Database Engine Portable 1.3.176 (Apr 18, 2014)

  • The file locking method 'serialized' is no longer documented, as it will not be available in version 1.4.
  • The static method Csv.getInstance() was removed. Use the public constructor instead.
  • The default user name for the Script, RunScript, Shell, and CreateCluster tools are no longer "sa" but an empty string.
  • The stack trace of the exception "The object is already closed" is no longer logged by default.
  • If a value of a result set was itself a result set, the result could only be read once.
  • Column constraints are also visible in views (patch from Nicolas Fortin for H2GIS).
  • Granting a additional right to a role that already had a right for that table was not working.
  • Spatial index: a few bugs have been fixed (using spatial constraints in views, transferring geometry objects over TCP/IP, the returned geometry object is copied when needed).
  • Issue 551: the datatype documentation was incorrect (found by Bernd Eckenfels).
  • Issue 368: ON DUPLICATE KEY UPDATE did not work for multi-row inserts. Test case from Angus Macdonald.
  • OSGi: the package javax.tools is now imported (as an optional).
  • H2 Console: auto-complete is now disabled by default, but there is a hot-key (Ctrl+Space).
  • H2 Console: auto-complete did not work with multi-line statements.
  • CLOB and BLOB data was not immediately removed after a rollback.
  • There is a new Aggregate API that supports the internal H2 data types (GEOMETRY for example). Thanks a lot to Nicolas Fortin for the patch!
  • Referential integrity constraints sometimes used the wrong index, such that updating a row in the referenced table incorrectly failed with a constraint violation.
  • The Polish translation was completed and corrected by Wojtek Jurczyk. Thanks a lot!
  • Issue 545: Unnecessary duplicate code was removed.
  • The profiler tool can now process files with full thread dumps.
  • MVStore: the file format was changed slightly.
  • MVStore mode: the CLOB and BLOB storage was re-implemented and is now much faster than with the PageStore (which is still the default storage).
  • MVStore mode: creating indexes is now much faster (in many cases faster than with the default PageStore).
  • Various bugs in the MVStore storage and have been fixed, including a bug in the R-tree implementation. The database could get corrupt if there were transient IO exceptions while storing.
  • The method org.h2.expression.Function.getCost could throw a NullPointException.
  • Storing LOBs in separate files (outside of the main database file) is no longer supported for new databases.
  • Lucene 2 is no longer supported.
  • Fix bug in calculating default MIN and MAX values for SEQUENCE.
  • Fix bug in performing IN queries with multiple values when IGNORECASE=TRUE
  • Add entry-point to org.h2.tools.Shell so it can be called from inside an application. patch by Thomas Gillet.
  • Fix bug that prevented the PgServer from being stopped and started multiple times.
  • Support some more DDL syntax for MySQL, patch from Peter Jentsch.
  • Issue 548: TO_CHAR does not format MM and DD correctly when the month or day of the month is 1 digit, patch from "the.tucc"
  • Fix bug in varargs support in ALIAS's, patch from Nicolas Fortin

New in H2 Database Engine Portable 1.3.175 (Jan 30, 2014)

  • EXPLAIN was incorrect for queries with "ANY" or "SOME" conditions.
  • CallableStatement with "out" parameters: running the same statement twice could result in an exception ("parameter not set").
  • Union queries: duplicate rows could be returned if the sub-queries contained "order by".
  • The GEOMETRY data type now works for user defined functions that return a result set.
  • PostgreSQL compatibility: the PgServer was not working properly when the setting database_to_upper was set to false.
  • JdbcDataSource: the methods setUrl and getUrl where added as aliases for setURL and getURL. This should solve problems with the HikariCP (Hikari connection pool).
  • Issue 537: H2 Console: when loading the schema, incorrect JDBC calls where issued, which caused the exception "Column PRECISION not found".
  • Improved Geometry processing (issue 535).
  • The collation can now be set in the database URL, even if there are data tables, if the collection is the same as the current collation.
  • Improved Oracle compatibility for CASE WHEN and DECODE.
  • The statement "drop all objects" did not work if a table depends on a view via a constraint.
  • Subqueries or views with "order by" an alias expression could not be executed due to a regression introduced in version 1.3.174.
  • Issue 73: MySQL compatibility: support REPLACE
  • The spatial index now works in MVCC mode when using the MVStore storage.
  • MVStore: concurrency problems have been fixed. The API has been simplified.
  • Improve error message when dropping an index that belongs to a constraint, specify constraint in error message.
  • Issue 518: java.sql.Connection.commit() freezes after LOB modification with EXCLUSIVE connection
  • Issue 517: Create or replace view statement has no effect on the others already existing JDBC connection
  • Support 123L syntax as in Java; example: SELECT (2000000000L*2).
  • Issue 520: Add support for sequence max value, min value and cycle
  • Issue 521: ScriptReader should implement Closeable
  • Issue 524: RunScript.execute does not close its Statement, patch from Gaul.
  • Add support for DB2 "WITH UR" clause
  • Added support for ON DUPLICATE KEY UPDATE like MySQL with the values() function to update with the value that was to be inserted.
  • Issue 522: Treat empty strings like NULL in Oracle compatibility mode
  • Issue 527: Oracle compatibility mode: incorrect scale behavior
  • Slightly reduce the memory cost of View metadata.
  • Extend support of "GRANT ALTER ANY SCHEMA TO " to allow grantee ability to manipulate tables
  • Issue 532: Javadoc for ErrorCode.ROLES_AND_RIGHT_CANNOT_BE_MIXED looks wrong
  • Issue 528: Add Oracle-compatible TO_CHAR function, patch by Daniel Gredler.
  • Issue 534: CREATE ALIAS NOCACHE
  • Fix an issue with storing Unicode surrogate pairs in CLOB columns.
  • H2 console: add SHIFT+ENTER "run selected" functionality
  • Fix bug in DB2 syntax "select * from test with ur"
  • Fix bug in DROP ALL OBJECTS when dealing with inter-schema dependencies.

New in H2 Database Engine Portable 1.3.174 (Oct 21, 2013)

  • LIRS cache: bugfix for caches that only contain non-resident entries.
  • For in-memory databases, queries with a "group by" column that is also a hash index threw a RuntimeException.
  • Improved error message for some syntax errors.
  • File system abstraction: if used directly, some file systems did not work correctly with spliced byte buffers (the database engine doesn't use those).
  • To use the MVStore storage engine (which is still work in progress), append ";mv_store=true" to the database URL. Using the MVTableEngine when creating the table is no longer recommended.
  • To compile user defined functions, the javax.tools.JavaCompiler is now used if available, and no temporary files are created. This should solve problems when multiple H2 database concurrently compile the same user defined functions. To disable, system the system property "h2.javaSystemCompiler" to false.
  • Concurrently creating function aliases in different databases could result in the exception "javac: file not found".
  • The function "regexp_replace" threw the wrong kind of exception if the replacement string was invalid.
  • A checkpoint is now done every MAX_LOG_SIZE / 2 instead of every MAX_LOG_SIZE, so that the transaction log doesn't grow as large.
  • MVStore table engine: new setting "retention_time" to configure the time to retain old data. The default is 45 seconds.
  • The method TableEngine.createTable() now returns a Table object.
  • For read-only databases, for the trace level "debug", the trace info is written to the temp directory.
  • Closing the file lock will now wait until the background thread is stopped.
  • In version 1.3.172, a performance regression was introduced when fixing the issue 389 (when there is a multi-column primary key, H2 does not seem to always pick the right index). This was related to boosting an index that matches the "order by" column list (the wrong index was used in some cases).
  • Improved spatial index and data type.
  • Issue 467: OSGi Class Loader (ability to create reference to class in other ClassLoader, for example in another OSGi bundle).
  • Queries "group by" that contain a subquery with an aggregate function returned the wrong result in some cases.
  • Fix bug in unique and non-unique hash indexes which manifested as incorrect results when the search key was a different cardinal type from the table index key. e.g. where the one was INT and the other was LONG
  • Bug: Changes to the database structure did not result in the Session query cache being invalidated.
  • New feature from Davide Cavestro - allow using custom Java object serialization engines on a per-DB basis.
  • When running the Recover tool on very large (>6G) databases, some statistics were reported with negative numbers.
  • Add a CONTAINS_UNCOMMITTED column to the SESSIONS metadata table, to allow detecting when rogue sessions are creating large transactions.
  • Some small fixes to the GEOMETRY support, patches by Nicolas Fortin.
  • The BNF tool and the autocomplete feature are exported in OSGi, which makes it possible for smart editors to do autocomplete. Patch from Nicolas Fortin.
  • Fix DROP ALL OBJECTS and DROP SCHEMA in the presence of tables with computed column dependencies.
  • Session-temporary LOB's could sometimes accumulate, increasing the size of the DB file until shutdown. Now they are cleared out at every commit.
  • There was a bug where a hash index with more than one column would be silently converted to a regular index. It will now throw an exception.
  • Query Statistics: new feature which stores the newest 100 SQL queries executed and their performance data. Useful for tracking down badly performing queries.
  • Fix an LOB deadlock between reading and updating LOB columns.
  • Support the JDBC DatabaseMetaData#getClientInfoProperties() method, in the sense of returning an empty result, in order to make WebSphere happy.
  • Make Server#openBrowser respect the $BROWSER environment variable. Add "chromium" to the list of browsers tried. Patch from Felix Kaiser.
  • Fix to org.h2.util.ScriptReader when handling unclosed block comments.
  • Make org.h2.util.ScriptReader throw a better exception when handling broken scripts which generate extremely large statements.
  • Fix bug with ALLOW_LITERALS=NONE, where the periodic analyze table on insert would throw an exception. A similar problem was fixed in the Console tool.
  • Issue 510: Make org.h2.bnf public for consumption by external projects, patch by Nicolas Fortin
  • Issue 509: Important fix on ValueGeometry, patch by Nicolas Fortin (with some tweaking) Make ValueGeometry#getDimensionCount more reliable. Add unit test to check for illegal ValueGeometry comparison Add unit test for conversion of Geometry object into Object Add optional export to MANIFEST.MF for JTS Geometry classes Validate that geometry values can be represented in WKB.
  • Issue 506: RFE: Include Thread.getName() in case of a deadlock
  • Adding support for "GRANT ALTER ANY SCHEMA TO ", patch by John Yates

New in H2 Database Engine Portable 1.3.173 (Jul 29, 2013)

  • Support empty statements that just contains a comment.
  • Server mode: if there was an error while reading from a LOB, the session was closed in some cases.
  • Issue 463: Driver name and version are now the same in OsgiDataSourceFactory and JdbcDatabaseMetaData.
  • JaQu: The data type VARCHAR is now (again) used for Strings (no longer TEXT, except when explicitly set).
  • For in-memory databases, creating an index on a CLOB or BLOB column is no longer supported. This is to simplify the MVTableEngine.
  • New column "information_schema.tables.row_count_estimate".
  • Issue 468: trunc(timestamp) could return the wrong value (+12 hours), and trunc(number) throw a NullPointerException.
  • The expression trunc(number) threw a NullPointerException.
  • Fixed a deadlock when updating LOB's concurrently. See TestLob.testDeadlock2().
  • Fixed a deadlock related to very large temporary result sets.
  • Add "-list" command line option to Shell tool so that result-list-mode can be triggered when reading from a file.
  • Issue 474: H2 MySQL Compatibility code fails to ignore "COMMENT" in CREATE TABLE, patch from Aaron Azeckoski.
  • Issue 476: Broken link in jaqu.html
  • Fix potential UTF8 encoding issue in org.h2.store.FileStore, reported by Juerg Spiess.
  • Improve error message when check constraint is broken, test case from Gili (cowwoc).
  • Improve error message when we have a unique constraint violation, displays the offending key in the error message.
  • Issue 478: Support for "SHOW TRANSACTION ISOLATION LEVEL", patch from Andrew Franklin.
  • Issue 475: PgServer: add support for CancelRequest, patch from Andrew Franklin.
  • Issue 473: PgServer missing -key option, patch from Andrew Franklin.
  • Issue 471: CREATE VIEW does not check user rights, patch from Andrew Franklin.
  • Issue 477: PgServer binary transmission of query params is unimplemented, patch from Andrew Franklin.
  • Issue 479: Support for SUBSTRING without a FROM condition, patch from Andrew Franklin.
  • Issue 472: PgServer does not work with any recent Postgres JDBC driver, patch from Andrew Franklin.
  • Add syntax for passing additional parameters into custom TableEngine implementations.
  • Issue 480: Bugfix post issue 475, 477, patch from Andrew Franklin.
  • Issue 481: Further extensions to PgServer to support better support PG JDBC, patch from Andrew Franklin.
  • Add support for spatial datatype GEOMETRY.
  • Add support for in-memory spatial index.
  • change the PageStore#changeCount field from an int to a long, to cope with databases with very high transaction rates.
  • Fix a NullPointerException when attempting to add foreign key reference to a view.
  • Add sufficient ClientInfo support to our javax.sql.Connection implementation to make WebSphere happy.
  • Issue 482: class LobStorageBackend$LobInputStream does not override the method InputStream.available().
  • Fix corruption resulting from a mix of the "WRITE_DELAY=0" option and "SELECT DISTINCT" queries that don't fit in memory.
  • Fix the combination of updating a table which contains an LOB, and reading from the LOB at the same time. Previously it would throw an exception, now it works.
  • Issue 484: In the H2 Console tool, all schemas starting with "INFO" where hidden. Now they are hidden only if the database is not H2. Patch from "mgcodeact"/"cumer d"
  • MySQL compatibility, support the "AUTO_INCREMENT=3" part of the CREATE TABLE statement.
  • Issue 486: MySQL compatibility, support the "DEFAULT CHARSET" part of the CREATE TABLE statement.
  • Issue 487: support the MySQL "SET foreign_key_checks = 0" command
  • Issue 490: support MySQL "USING BTREE" index declaration
  • Issue 485: Database get corrupted when column is renamed for which check constraint was defined inside create table statement.
  • Issue 499: support MySQL "UNIQUE KEY (ID) USING BTREE" constraint syntax
  • Issue 501: "CREATE TABLE .. WITH" not serialized, patch from nico.devel
  • Avoid problems with runtime-compiled ALIAS methods when people have set the JAVA_TOOL_OPTIONS environment variable.

New in H2 Database Engine Portable 1.3.172 (May 30, 2013)

  • Referential integrity: when adding a referential integrity constraint failed, and if creating the constraint automatically created an index, this index was not removed.
  • The auto-analyze feature now only reads 1000 rows per table instead of 10000.
  • The optimization for IN(...) queries combined with OR could result in a strange exception of the type "column x must be included in the group by list".
  • Issue 454: Use Charset for type-safety.
  • Queries with both LIMIT and OFFSET could throw an IllegalArgumentException.
  • MVStore: multiple issues were fixed: 460, 461, 462, 464, 466.
  • MVStore: larger stores (multiple GB) are now much faster.
  • When using local temporary tables and not dropping them manually before closing the session, and then killing the process could result in a database that couldn't be opened (except when using the recover tool).
  • Support TRUNC(timestamp) for improved Oracle compatibility.
  • Add support for CREATE TABLE TEST (ID BIGSERIAL) for PostgreSQL compatibility. Patch from Jesse Long.
  • Add new collation command SET BINARY_COLLATION UNSIGNED, helps with people testing BINARY columns in MySQL mode.
  • Issue 453: ABBA race conditions in TABLE LINK connection sharing.
  • Issue 449: Postgres Serial data type should not automatically be marked as primary key
  • Issue 406: Support "select h2version()"
  • Issue 389: When there is a multi-column primary key, H2 does not seem to always pick the right index
  • Issue 305: Implement SELECT ... FOR FETCH ONLY
  • Issue 274: Sybase/MSSQLServer compatibility - Add GETDATE and CHARINDEX system functions
  • Issue 274: Sybase/MSSQLServer compatibility - swap parameters of CONVERT function.
  • Issue 274: Sybase/MSSQLServer compatibility - support index clause e.g. "select * from test (index table1_index)"
  • Fix bug in Optimizing SELECT * FROM A WHERE X=1 OR X=2 OR X=3 into SELECT * FROM A WHERE X IN (1,2,3)
  • Issue 442: Groovy patch for SourceCompiler (function ALIAS)
  • Issue 459: Improve LOB documentation

New in H2 Database Engine Portable 1.3.170 (Nov 30, 2012)

  • Issue 407: The TriggerAdapter didn't work with CLOB and BLOB columns.
  • PostgreSQL compatibility: support for data types BIGSERIAL and SERIAL as an alias for AUTO_INCREMENT.
  • Issue 417: H2 Console: the web session timeout didn't work, resulting in a memory leak. This was only a problem if the H2 Console was run for a long time and many sessions were opened.
  • Issue 412: Running the Server tool with just the option "-browser" will now log a warning.
  • Issue 411: CloseWatcher registration was not concurrency-safe.
  • MySQL compatibility: support for CONCAT_WS. Thanks a lot to litailang for the patch!
  • PostgreSQL compatibility: support for EXTRACT(WEEK FROM dateColumn). Thanks to Prashant Bhat for the patch!
  • Fix for a bug where we would sometimes use the wrong unique constraint to validate foreign key constraints.
  • Support BOM at the beginning of files for the RUNSCRIPT command
  • Fix in calling SET @X = IDENTITY() where it would return NULL incorrectly
  • Fix ABBA deadlock between adding a constraint and the H2-Log-Writer thread.
  • Optimize IN(...) queries where the values are constant and of the same type.
  • Restore tool: the parameter "quiet" was not used and is now removed.
  • Fix ConcurrentModificationException when creating tables and executing SHOW TABLES in parallel. Reported by Viktor Voytovych.
  • Serialization is now pluggable using the system property "h2.javaObjectSerializer". Thanks to Sergi Vladykin for the patch!

New in H2 Database Engine Portable 1.3.169 (Sep 10, 2012)

  • The default jar file is now compiled for Java 6.
  • The new jar file will probably not end up in the central Maven repository in the next few weeks because Sonatype has disabled automatic synchronization from SourceForge (which they call 'legacy sync' now). It will probably take some time until this is sorted out. The H2 jar files are deployed to http://h2database.com/m2-repo/com/h2database/h2/maven-metadata.xml and http://hsql.sourceforge.net/m2-repo/com/h2database/h2/maven-metadata.xml as usual.
  • A part of the documentation and the H2 Console has been changed to support the Apple retina display.
  • The CreateCluster tool could not be used if the source database contained a CLOB or BLOB. The root cause was that the TCP server did not synchronize on the session, which caused a problem when using the exclusive mode.
  • Statement.getQueryTimeout(): only the first call to this method will query the database. If the query timeout was changed in another way than calling setQueryTimeout, this method will always return the last value. This was changed because Hibernate calls getQueryTimeout() a lot.
  • Issue 416: PreparedStatement.setNString throws AbstractMethodError. All implemented JDBC 4 methods that don't break compatibility with Java 5 are now included in the default jar file.
  • Issue 414: for some functions, the parameters were evaluated twice (for example "char(nextval(..))" ran "nextval(..)" twice).
  • The ResultSetMetaData methods getSchemaName and getTableName could return null instead of "" (an empty string) as specified in the JDBC API.
  • Added compatibility for "SET NAMES" query in MySQL compatibility mode.

New in H2 Database Engine Portable 1.3.168 (Jul 14, 2012)

  • The message "Transaction log could not be truncated" was sometimes written to the .trace.db file even if there was no problem truncating the transaction log.
  • New system property "h2.serializeJavaObject" (default: true) that allows to disable serializing Java objects, so that the objects compareTo and toString methods can be used.
  • Dylan has translated the H2 Console tool to Korean. Thanks a lot!
  • Executing the statement CREATE INDEX IF ALREADY EXISTS if the index already exists no longer fails for a read only database.
  • MVCC: concurrently updating a row could result in the row to appear deleted in the second connection, if there are multiple unique indexes (or a primary key and at least one unique index). Thanks a lot to Teruo for the patch!
  • Fulltext search: in-memory Lucene indexes are now supported.
  • Fulltext search: UUID primary keys are now supported.
  • Apache Tomcat 7.x will now longer log a warning when unloading the web application, if using a connection pool.
  • H2 Console: support the Midori browser (for Debian / Raspberry Pi)
  • When opening a remote session, don't open a temporary file if the trace level is set to zero
  • Use HMAC for authenticating remote LOB id's, removing the need for maintaining a cache, and removing the limit on the number of LOBs per result set.
  • H2 Console: HTML and XML documents can now be edited in an updatable result set. There is (limited) support for editing multi-line documents.

New in H2 Database Engine Portable 1.3.167 (Jul 14, 2012)

  • H2 Console: when editing a row, an empty varchar column was replaced with a single space.
  • Lukas Eder has updated the jOOQ documentation.
  • Some nested joins could not be executed, for example: select * from (select * from (select * from a) a right join b b) c;
  • MS SQL Server compatibility: ISNULL is now an alias for IFNULL.
  • Server mode: the number of CLOB / BLOB values that were cached on the server is now the maximum of: 5 times the SERVER_RESULT_SET_FETCH_SIZE (which is 100 by default), and SysProperties.SERVER_CACHED_OBJECTS.
  • In the trace file, the query execution time was incorrect in some cases, specially for the statement SET TRACE_LEVEL_FILE 2.
  • The feature LOG_SIZE_LIMIT that was introduced in version 1.3.165 did not always work correctly (specially with regards to multithreading) and has been removed. The message "Transaction log could not be truncated" is still written to the .trace.db file if required.
  • Then reading from a resource using the prefix "classpath:", the ContextClassLoader is now used if the resource can't be read otherwise.
  • DatabaseEventListener now calls setProgress whenever a statement starts and ends.
  • DatabaseEventListener now calls setProgress periodically while a statement is running.
  • The table INFORMATION_SCHEMA.FUNCTION_ALIASES now includes a column TYPE_NAME.
  • Issue 378: when using views, the wrong values were bound to a parameter in some cases.
  • TRUNC was added as an alias for TRUNCATE.
  • Small optimisation for accessing result values by column name.
  • Fix for bug in Statement.getMoreResults(int)
  • The SCRIPT statements now supports filtering by schema and table.

New in H2 Database Engine Portable 1.3.166 (Apr 9, 2012)

  • Indexes on column that are larger than half the page size (wide indexes) could sometimes get corrupt, resulting in an ArrayIndexOutOfBoundsException in PageBtree.getRow or "Row not found" in PageBtreeLeaf. Also, such indexes used too much disk space.
  • Server mode: when retrieving more than 64 rows each containing a CLOB or BLOB, the error message "The object is already closed" was thrown.
  • ConvertTraceFile: the time in the trace file is now parsed as a long.
  • Invalid connection settings are now detected.
  • Issue 387: WHERE condition getting pushed into sub-query with LIMIT.

New in H2 Database Engine Portable 1.3.161 (Oct 29, 2011)

  • Issue 351: MySQL mode: can not create a table with the column "KEY", and can not open databases where such a table already exists.
  • TCP server: when using the trace option ("-trace"), the trace output contained unnecessary stack traces when stopping the server.
  • Issue 354: when using the multi-threaded kernel option, and multiple threads concurrently prepared SQL statements that use the same view or the same index of a view, then in some cases an infinite loop could occur.
  • Issue 350: when using instead of triggers, executeUpdate for delete operations always returned 0.
  • Some timestamps with timezone were not converted correctly. For example, in the PST timezone, the timestamp 2011-10-26 08:00:00Z was converted to 2011-10-25 25:00:00 instead of 2011-10-26 01:00:00. Depending on the database operation, this caused subsequent error.
  • Sequences with cache size smaller than 0 did not work correctly.
  • Issue 356: for Blob objects, InputStream.skip() returned 0, causing EOFException in Blob.getBytes(.., ..).
  • Updatable result sets: if the value is not set when inserting a new row, the default value is now used (the same behavior as MySQL, PostgreSQL, and Apache Derby) instead of NULL.
  • Conditions of the form IN(SELECT ...) where slow and increased the database size if the subquery result size was larger then the configured MAX_MEMORY_ROWS.
  • Issue 347: the RunScript and Shell tools now ignore empty statements.
  • Issue 246: improved error message for data conversion problems.
  • Issue 344: the build now supports a custom Maven repository location.
  • Issue 334: Java functions that return CLOB or BLOB objects could not be used as tables.
  • SimpleResultSet now supports getColumnTypeName and getColumnClassName.
  • SimpleResultSet now has minimal BLOB and CLOB support.
  • Improved performance for large databases (many GB), and databases with a small page size.
  • The Polish translation has been improved by Jarosław Kokociński.
  • When using the built-in connection pool, after calling the "shutdown" SQL statement, a warning was written to the .trace.db file about an unclosed connection.
  • Improved compatibility for "fetch first / next row(s)". Thanks a lot to litailang for the patch!
  • Improved compatibility with the Java 7 FileSystem abstraction.

New in H2 Database Engine Portable 1.3.159 (Aug 16, 2011)

  • Creating a temporary table with the option 'transactional' will now also create the indexes in transactional mode, if the indexes are included in the 'create table' statement as follows: "create local temporary table temp(id int primary key, name varchar, constraint x index(name)) transactional".
  • The database file size grows now 35%, but at most 256 MB at a time.
  • Improved error message on network configuration problems.
  • The build now support an offline build using ./build.sh offline. This will list the required dependencies if jar files are missing.
  • The BLOB / CLOB data was dropped a little bit before the table was dropped. This could cause "lob not found" errors when the process was killed while a table was dropped.
  • "group_concat(distinct ...)" did not work correctly in a view or subquery (the 'distinct' was lost). Example: select * from (select group_concat(distinct 1) from system_range(1, 3));
  • Database URLs can now be re-mapped to another URL using the system property "h2.urlMap", which points to a properties file with database URL mappings.
  • When using InputStream.skip, trying to read past the end of a BLOB failed with the exception "IO Exception: Missing lob entry: ..." [90028-...].
  • The in-memory file system "memFS:" now has limited support for directories.
  • To test recovery, append ;RECOVER_TEST=64 to the database URL. This will simulate an application crash after each 64 writes to the database file. A log file named databaseName.h2.db.log is created that lists the operations. The recovery is tested using an in-memory file system, that means it may require a larger heap setting.
  • Converting a hex string to a byte array is now faster.
  • The SQL statement "shutdown defrag" could corrupt the database if the process was killed while the shutdown was in progress. The same problem could occur when the database setting "defrag_always" was used.

New in H2 Database Engine Portable 1.3.158 (Jul 18, 2011)

  • Fulltext search (native): after re-opening the connection once the fulltext index was created, a transaction rollback did not roll back the modifications in the index.
  • Support for ROW_NUMBER() OVER() as an alias for ROWNUM() for compatibility with PostgreSQL and Apache Derby.
  • Issue 325: a deadlock in the fulltext search could occur if the connection that was used while initializing the fulltext search was later used to query the database. This was actually not only a problem for fulltext search, but also for other triggers that used the initializing connection later on.
  • DATABASE_TO_UPPER: when set to false, all identifier names (table names, column names) are case sensitive (except aggregate, built-in functions, data types, and keywords). This is for improved compatibility with MySQL and PostgreSQL.
  • When upgrading from an older 1.3.x version to version 1.3.157, when using BLOB or CLOB data, the database could only be opened once.
  • SUM on a boolean expression will now count the number of 'true' rows.
  • Simplified test cases using assertThrows(...).
  • A Java function that returns a result set that was generated using the provided connection could cause an internal error in some cases. Now such result sets are copied early on.
  • Data type RESULT_SET: information_schema.function_aliases returned type 0; now type -10 is returned (the same as Oracle: OracleTypes.CURSOR = -10).
  • Data type RESULT_SET: tables with columns of type RESULT_SET could be created, but no data could be persisted in this column.
  • H2 Console: improved system tray icon for Mac OS X (transparent background).
  • String.toUpperCase() was used a few places, which is problematic when using the Turkish locale. The method has been replaced with toUpperCase(Locale.ENGLISH) to solve such problems.
  • Shell tool: the built-in command "distinct" has been removed (use the SQL statements "show tables" / "show columns from tableName" instead). The result set formatting has been improved.
  • MERGE: if a unique key was violated (but not the primary key or the key columns of the merge itself), the wrong exception was thrown.
  • IBM DB2 and Apache Derby compatibility: support the pseudo-table SYSIBM.SYSDUMMY1.
  • ROWNUM() did not work in combination with IN(..). The following query did not work as expected: select * from (select rownum r from test) where r in (1, 2).
  • H2 Console autocomplete: the autocomplete feature didn't support quoted names.
  • It is now longer allowed to create an index on a CLOB or BLOB column (except for in-memory databases or indexes), because recovery doesn't work on such columns. Fulltext indexes on such column are still supported of course.
  • The PUBLIC schema could be renamed, which could result in a database that can't be opened normally. Now it can no longer be renamed. The schema INFORMATION_SCHEMA can also no longer be renamed.
  • Server mode: the problem with server and client using different timezones with different daylight saving rules has been solved (this only applies when both the client and the server use this or a newer version).
  • There is a new system property "h2.storeLocalTime" to avoid problems opening a database file in a timezone that has different daylight saving rules (see also below).
  • Date, time, and timestamp data type processing has been re-implemented. Time now supports nanoseconds resolution and now now supports a wider range (negative and large values), similar to PostgreSQL.
  • SQL statements with a non-breaking space were considered invalid.

New in H2 Database Engine Portable 1.3.157 (Jun 27, 2011)

  • The syntax for CREATE TABLE ... AS SELECT ... has been changed: options such as NOT PERSISTENT are now before the query (AS SELECT), because otherwise they might be interpreted as a part of the query.
  • With version 1.3.156, the DB2 mode could not be used with CLOB and BLOB data.
  • When upgrading from an older 1.3.x version to version 1.3.156, CLOB and BLOB data was lost in many cases.
  • The optimization for COUNT(..) on columns that are not nullable was also used for COUNT(DISTINCT ..), which is incorrect.
  • PgServer: non-admin users could not open a database.
  • Non-admin users could not open a database using a mode (MODE=xxx) if the database was already open, even if the mode matched.
  • The SQL state of the following exceptions has been changed: From 90009, 90010, 90011 to 22007: "Cannot parse DATE/TIME/TIMESTAMP constant ...".
  • There is a problem when opening a database file in a timezone that has different daylight saving rules: the time part of dates where the daylight saving doesn't match will differ. This is not a problem within regions that use the same rules (such as, within USA, or within Europe), even if the timezone itself is different. As a workaround, export the database to a SQL script using the old timezone, and create a new database in the new timezone. The same problem occurs when accessing a database over TCP/IP if the client and server timezones use different rules, if an old server or client is used.

New in H2 Database Engine Portable 1.3.156 (Jun 18, 2011)

  • Issue 323: NullPointerException when using IN(...) with a function table.
  • Clob.toString() and Blob.toString() now include the trace identifier.
  • The SQL parser silently ignored characters such as '^' or '\'. Now a syntax error is thrown.
  • ROUND(..) now also works with just one parameter.
  • Access to system tables is now more restrictive for non-admin users (the tables can be still listed, but some of the data is not included).
  • BLOB: InputStream.skip is now more efficient in embedded mode.
  • The default expression of a column may no longer reference the table, because a database with such a table couldn't be opened normally.
  • Referenced objects in check constraints could be dropped, which resulted in a database that can't be opened normally.
  • Improved Oracle compatibility for NVL2. Thanks again to litailang for the patch.
  • CSVREAD now supports the option 'preserveWhitespace'.
  • Recursive queries with many rows could throw an IndexOutOfBoundsException.
  • The auto-server mode can't be combined with an in-memory database. This invalid combination wasn't detected so far. Now trying to open a database in this way fails.
  • Improved performance for reading and writing date and time values.
  • Java functions: array component types are now preserved, so that a ResultSet.getObject() will return Integer[] if the Java functions returns Integer[]. Thanks to Noel Grandin for the patch. Component types are transferred over the network, but not persisted in the database file as of now.
  • Recover tool: the script generated by the recover tool didn't work if fulltext search was used, because the triggers were created before the primary keys.
  • Issue 321: the database does not expect XA rollback without XA prepare (an exception was unnecessarily written into the .trace.db file).
  • Read-only databases: some queries (such as distinct queries) tried to create temporary tables, which failed for read only databases.
  • This database no longer uses finalize() except for temporary files. The system property "h2.runFinalize" is no longer supported. Unclosed connections are still detected, but the opening stack trace is now collected only if connections are not closed repeatedly (starting with the second unclosed connection). To detect unclosed connections, a PhantomReference is used.
  • Issue 322: Left joins didn't work when used as subquery in the form clause.
  • A prepared statement if type CALL that returned a result set could throw a NullPointerException if executed multiple times.
  • The following built-in functions were marked as deterministic and therefore not re-executed when using in a prepared statement: LOCK_TIMEOUT, LINK_SCHEMA, CANCEL_SESSION, FILE_READ.
  • Count on a column that can not be null is now optimized to COUNT(*). Thanks a lot to Ross Judson for providing a patch and test case!
  • The following prepared statements are now cached as well: insert, update, delete, call, merge, and transactional commands.
  • The built-in connection pool is now a bit faster.
  • The scale was not set correctly in some cases when using CREATE TABLE AS SELECT if there was no explicit column definition.
  • When trying to connect to a server using TCP/IP failed, it will retry at most until the timeout. Before, it was the timeout multiplied with the retry count

New in H2 Database Engine Portable 1.3.155 (Jun 18, 2011)

  • When altering a table with triggers, new triggers are created and initialized with a table name that ends with tableName_COPY_x_y. The plan is that this will not be required in future versions.
  • If some of the file system operations failed, System.gc() was called in a loop. Now it is called once at most.
  • Triggers (including the fulltext search) could throw the exception 'Unexpected code path' in Session.log.
  • CURRENT_TIMESTAMP() and so on now return the same value within a transaction.
  • The statement CALL no longer converts an ARRAY return value to a list of values. Now an ARRAY is returned.
  • VALUES is now supported as a standalone command and as a table source: SELECT * FROM (VALUES(1, 'Hello'), (2, 'World')) AS V;
  • TIMESTAMPADD is now an alias for DATEADD. Most SQL_TSI_ constants are now supported for for TIMESTAMPADD and TIMESTAMPDIFF (all except SQL_TSI_QUARTER and SQL_TSI_FRAC_SECOND).
  • Issue 313: NullPointerException in select query with a subquery or view.
  • Native fulltext search: the characters '', and '\' are now also whitespace characters. Also, the list of whitespace characters can be changed using FullText.setWhitespaceChars(conn, ...)
  • When reading from the classpath (for example read_file('classpath:logo.png')), now the content class loader is used if the resource can't be found otherwise.
  • The statement "script drop" create a script that couldn't be executed if a table contained a reference to a user defined function.
  • Opening a database with both multi-threaded mode and lock mode 0 (disable locking) was possible (but only once), even thought it does not work properly. Now it is no longer allowed.
  • LOB objects are now deleted (and the empty space is reused) when the transaction is committed. So far, the space wasn't re-used until the connection was closed.
  • SCRIPT DROP did not use the cascade option for table, which would cause problems if a view was dropped before a table it depends on. Now the cascade option is used.
  • The query plan of queries with subqueries that contain nested joins was wrong. This could result in syntax exceptions for correct queries. Example: select 1 from (select 2 from ((test t1 inner join test t2 on t1.id=t2.id) inner join test t3 on t3.id=t1.id)) x;
  • DatabaseEventListener.init was called with a null url when using the server mode.
  • Support for the function LN (an alias for LOG).
  • Support for the function CEIL (an alias for CEILING).
  • Issue 315: Access to LOBs could cause a Java level deadlock.
  • Support for the ICU4J collator.
  • Improved Oracle compatibility: support for NVL2. Thanks to litailang for the patch!
  • Improved PostgreSQL compatibility: support for RANDOM() in addition to RAND().
  • There was a classloader memory leak problem because a class contained a static references to an exception (including stack trace).
  • Split file system: truncating a file now deletes the parts in reverse order, so that the file list is consistent if the process is interrupted while truncating.
  • The H2 JDBC client can now be used in an unsigned Applet. The problem was that System.getProperty throws a SecurityException, which is now ignored.
  • The condition "in(select ...)" did not work correctly if the subquery could not be converted to a "distinct" query, as in: "select * from dual where x in (select x from dual group by x order by max(x))". Now the subquery needs to be made distinct manually if this optimization is needed.
  • In the last release, the error code for "Wrong user name or password" was still 08004 instead of 28000, which resulted in the wrong error message.
  • In some cases, creating a new table or altering an existing table threw the exception: Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX".
  • The Shell tool no longer supports the built-in command "show", because it is a legal SQL statement for H2 and MySQL. However, the "describe" command is still supported, and now lists all tables if called without parameter.
  • The sorted insert mode (as in "insert into ... direct sorted select") did not work for not-persisted tables in a persisted database. It threw a ClassCastException.
  • Issue 308: Statement.getGeneratedKeys() now returns an empty result set if no key was generated.
  • The h2small.jar (created with build jarSmall) included the Android API. This has been removed, shrinking the jar file by 21 KB.
  • When creating a table, the precision must now be at least as large as the scale.
  • Support for Java 1.3 and Java 1.4 has been removed.
  • Improved error message for syntax error: the list of expected tokens was sometimes not set correctly.
  • Database file growth can now be limited using the database setting PAGE_STORE_MAX_GROWTH. Slower growth may slow down operation, but speed up closing database.
  • A read only database with writable linked tables could lead to a DATABASE_IS_READ_ONLY (90097) error. This is fixed now.

New in H2 Database Engine Portable 1.3.154 (Apr 5, 2011)

  • The SQL state of the following exceptions has been changed: From 90005 to 22001: "Value too long for column ...: ...". From 90039 to 22003: "The value is too large for the precision ..." is merged with "Numeric value out of range". From 90110 to 22003: "... out of range" is merged with "Numeric value out of range". From 90021 to 22018: "Data conversion error converting ...". From 90006 to 23502: "NULL not allowed for column ...". From 23003 to 23503: "Referential integrity constraint violation: ...". From 23001 to 23505: "Unique index or primary key violation: ...". From 23002 to 23506: "Referential integrity constraint violation: ...". From 90056 to 23507: "No default value is set for column ...". From 23000 to 23513: "Check constraint violation: ...". From 08004 to 28000: "Wrong user name or password". From 90051 to 57014: "Statement was canceled or the session timed out".
  • The H2 Console now detects unsupported command line options. (In the future, it will be combined with the Server tool.)
  • MySQL compatibility: SHOW COLUMN FOR schemaName.columnName.
  • DatabaseMetaData.getCrossReference now also lists the name of the primary index (PK_NAME).
  • If the database contained many CLOB or BLOB values, then closing a connection was very slow. Reason: was that when closing a connection, the temporary CLOB and BLOB values for this connection were removed, but there was no index on this column. For existing databases with many CLOB and BLOB values, creating the index may take a few seconds. The index is added to existing databases automatically.
  • If a CLOB or BLOB was copied into the same table using INSERT INTO X ... SELECT ... FROM X, and then the original row was deleted, then the copied value was also deleted. This could also result in an ArrayIndexOutOfBoundsException on checkpoint or when closing the database.
  • Improved performance (up to 10%) due to changed synchronization in the page store.
  • Reading a resource from the classpath didn't work if there was a leading slash (classpath:/org/...). Now resources can be loaded with or without leading slash.
  • Support INSERT INTO TEST SET ID = 1, NAME = 'World' (MySQL compatibility).
  • Issue 304: The condition [NOT] IN (SELECT ...) could throw the exception "Unexpected code path" if the subquery contained ORDER BY.
  • ALTER TABLE ALTER ADD / REMOVE /ALTER COLUMN dropped some dependent objects (access rights, triggers) of views that depend on the modified table.
  • CREATE OR REPLACE VIEW dropped some dependent objects (access rights, triggers) if the view already existed before.
  • New aggregate function HISTOGRAM to calculate the histogram. The plan is to use this function is only used internally when running ANALYZE to generate the histogram that is then used by the query optimizer.
  • New system function TRUNCATE_VALUE to truncate a value to the required precision. This is the same as for CAST, but without changing the data type, and support for truncating decimal values. (The main use case for this function is to build a histogram of the truncated values). Now BINARY, BLOB, and CLOB can be truncated / cast efficiently as well.
  • The precision of an ARRAY value is now the sum of the precision of all elements, and result set values have the precision Integer.MAX_VALUE.
  • CAST(x AS VARCHAR_IGNORECASE(y) / CHAR(y)) returned a VARCHAR if the new precision was lower than the old precision.
  • SHUTDOWN DEFRAG didn't defragment the database completely if there was a unique hash index for a memory table.
  • There were some smaller changes in the encrypting file system (work in progress).
  • Some time and timezone functions didn't support years before 1.

New in H2 Database Engine Portable 1.3.153 (Mar 15, 2011)

  • Issue 294: The Maven source bundle now contains a manifest file that allows Eclipse to automatically attach the source code (if Maven is used).
  • A query with an explicit LIMIT 0 will now return no rows (so far it meant no limit), which is compatible with PostgreSQL and MySQL. A negative limit value, (as well as LIMIT NULL) mean no limit (so far a negative limit meant a limit of one row). There were similar problems with OFFSET.
  • A new encrypting file system has been implemented. The idea is to allow encrypting all files, not just database files. Also implemented is a simple file system shell tool. At some point, this might replace the Backup, ChangeFileEncryption, DeleteDbFiles, and Restore tools.
  • Lob in database: storing lob objects was not correctly synchronized. This was specially a problem when using Connection.createBlob() / createClob().
  • Updated the license page (thanks a lot to Abe to help me with this).
  • Support for database paths with '\' on non-Windows systems.
  • The NIO cleaner hack wasn't working as expected, because the clear method was called instead of the clean method. This has been fixed. Please note this hack is not enabled by default, to try it you need to set the system property "h2.nioCleanerHack" to true.
  • Improved MS SQL Server and MySQL compatibility: support DELETE with TOP or LIMIT.
  • Support for COSH, SINH, and TANH functions.
  • Support for the % operator (modulo) thanks to Noel Grandin.
  • Issue 288: Some right outer join queries failed to produce the correct result or threw exceptions such as "column x must be in the group by list".
  • Some right outer joins with invalid column referenced (typos) threw a NullPointerException instead of "column not found" exception.
  • New database setting EARLY_FILTER to allow table implementations to apply filter conditions early on.
  • Cluster: in a two node cluster, if cluster node stopped, and autocommit was enabled, then changes on the remaining cluster node didn't get committed automatically.
  • Issue 291: Allow org.h2.value.Value as FUNCTION ALIAS params and return value.
  • Issue 265: Linked tables: auto-reconnect if the backside connection is lost (workaround for the MySQL problem that disconnects after 8 hours of inactivity).
  • Linked tables: the index conditions was sometimes not used when querying the remote database.
  • Issue 294: OSGi: the versions were missing in manifest package exports.
  • The option -baseDir didn't work with symbolic links.
  • Support for ALTER TABLE ADD COLUMN IF NOT EXISTS.
  • Database-level connection settings could only be set in the database URL, but not using the Properties parameter of DriverManager.getConnection(String url, Properties info).