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
- 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)
- 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
...
@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
--- 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. |
