Quoted table name in FROM clause and Column in @Formula gets wrongly qualified with generated alias

Description

SUMMARY

An entity attribute mapped with the @Formula annotation that contains a subselect as value, that in turn contains a table name in the FROM clause which is quoted, leads to a wrongly generated SQL. Quoting the table name in the FROM clause may be necessary depending on the underlying DBMS, if the table name is also a reserved keyword. In my case it's a table named transaction (as in the SQL keyword) that needs quoting for the underlying HSQLDB.

STEPS TO REPRODUCE

  1. Map an entity attribute via the @Formula annotation and set the following query as value: (SELECT t.value_a FROM \"table\" t WHERE t.value_b = outer_value)

  2. Trigger a load of such an entity

EXPECTED RESULT

Generation of the following SQL:
select ..., (SELECT t.value_a FROM "table" t WHERE t.value_b = outerTable0_.outer_value) as formula0_ from OuterTable outerTable0_ ...
Note that the quoted table name in the FROM clause in the subselect is not qualified with the generated alias from the outer table, but verbatim.

ACTUAL RESULT

Generation of the following SQL:
select ..., (SELECT t.value_a FROM outerTable0_."table" t WHERE t.value_b = outerTable0_.outer_value) as formula0_ from OuterTable outerTable0_ ...
Note that the quoted table name in the FROM clause in the subselect is qualified with the generated alias from the outer table.

MORE DETAILS

Omitting the quotes for the table name leads to an exception if HSQLDB is used as DBMS: org.hsqldb.HsqlException: unexpected token: SELECT. It seems that HSQLDB can't handle the unquoted use of the SQL keyword transaction in the FROM clause and thus fails to compile the subselect (see e.g. this discussion for a similar example). Properly quoting the table name helps. This has been verified via overwriting the wrong qualification in the generated SQL at runtime.
PostgreSQL on the other hand handles the unquoted use well.

REPRODUCER TEST CASE

org.hibernate.sql.TemplateTest

1 2 3 4 5 6 7 8 9 ... @Test public void testQuotedTableNameNotQualified() { String fragment = "(SELECT t.value_a FROM \"table\" t WHERE t.value_b = outer_value)"; String template = Template.renderWhereStringTemplate( fragment, Template.TEMPLATE, DIALECT, FUNCTION_REGISTRY ); assertEquals("(SELECT t.value_a FROM \"table\" t WHERE t.value_b = " + Template.TEMPLATE + ".outer_value)", template); } ...

The test case fails on 5.3.7.Final and on 5.4.5-SNAPSHOT.

PROPOSED FIX

org.hibernate.sql.Template

1 2 3 4 5 6 7 8 9 10 11 --- hibernate-core/src/main/java/org/hibernate/sql/Template.java (revision ea301a7084fbbde81b749e64908369aa382d0962) +++ hibernate-core/src/main/java/org/hibernate/sql/Template.java (date 1567533972000) @@ -193,7 +193,7 @@ isOpenQuote = false; } - if ( isOpenQuote ) { + if ( isOpenQuote && !inFromClause ) { result.append( placeholder ).append( '.' ); } }

The fix is probably not the best solution as there may be use cases that require qualification in the FROM clause.

Environment

HSQLDB

Status

Assignee

Andrea Boriero

Reporter

Marius Kleber

Fix versions

None

Labels

None

backPortable

Backport?

Suitable for new contributors

None

Requires Release Note

Affirmative

Pull Request

None

backportDecision

None

Components

Affects versions

5.4.5

Priority

Major