HSQLDialect relies on "MODULE"-Prefix for local temporary table creation but MODULE-Prefix has been dropped in HSQLDB Version 2.5.1

Description

Since HSQLDB 2.0 the HSQLDialect uses local temporary tables for as multi table bulk id strategy.
This strategy is relevant e. g. when bulk deletes for entities spanned to multiple tables are performed (like entities with joined table inheritance).

This strategy in the HSQLDialect uses a table prefix "MODULE" for the temporary table to be created. This prefix is valid as a pseudo schema for local temporary tables in HSQLDB up to Version 2.5.0.

Since Version 2.5.1 the MODULE-Prefix is no longer "supported" even though the HSQLDB documentation still states in some places that MODULE can be used for that purpose.
(See HSQLDB Source -> https://sourceforge.net/p/hsqldb/svn/HEAD/tree/base/tags/2.5.1/src/org/hsqldb/ParserDDL.java#l978 vs. https://sourceforge.net/p/hsqldb/svn/HEAD/tree/base/tags/2.5.0/src/org/hsqldb/ParserDDL.java#l978)

I currently don't know if this was an intented change in HSQLDB but nevertheless it breaks compatibility with the HSQLDialect leading to exception when trying to fill the temporary table (actually an exception already was raised before when the temporary table should be created but since this exception is swallowed on purpose the next statements using this table fail. see https://github.com/hibernate/hibernate-orm/blob/93b8095ef616cadd23d7a65f1579fd66a33ed0b2/hibernate-core/src/main/java/org/hibernate/hql/spi/id/local/Helper.java#L44)

One probably safe solution to adress this problem could be to change the used "MODULE"-Prefix to "SESSION" which is also a valid prefix for local temporary tables (see http://hsqldb.org/doc/guide/sqlgeneral-chapt.html#sgc_temp_tables).

Environment

None

Activity

Show:
Marc Weyland
November 19, 2020, 2:11 PM

I’ve spoken to Fred Toussi which is project lead of hsqldb who told me the following:

The proper prefix for local temp tables is "SESSION" and has been supported in several versions. The prefix "MODULE" is reserved for a future implementation of SQL modules according to the Procedural Stored Modules specification (a concept similar to PL/SQL packages). In earlier versions, this prefix was accepted incorrectly for non-module session tables.

Even though the MODULE keyword might be restored in an upcoming version "SESSION" should rather be used as the correct prefix.

Assignee

Unassigned

Reporter

Marc Weyland

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Critical
Configure