I encountered the following issue (issues really) migrating our application from Hibernate 5 to 6.
Situation:
* We have two tables with identical columns, {{OWNER.TAB_DATA}} and {{OWNER.TAB_DATA_STAGING}}. These are in the schema {{OWNER}}.
* We connect to the DB as {{CLIENT}} and have configured {{hibernate.default_schema=OWNER}}. DML privileges to the tables in the {{OWNER}} schema have been granted to the {{CLIENT}} user.
* {{TAB_DATA_STAGING}} is populated by a batch job in chunks over the course of multiple transactions. Then, within a new transaction, we move the data over to {{TAB_DATA}} with the following HQL:
{code:sql}insert into TabData(id, col1, col2, col3)
select max(id), col1, col2, col3
from TabDataStaging
group by col1, col2, col3; -- to eliminate possible duplicates in the staging table{code}
Hibernate 5 rendered this HQL into a single DML statement as expected:
{code:sql}insert into OWNER.tab_data (id, col1, col2, col3)
select max(tabdatasta0_.id),
tabdatasta0_.col1,
tabdatasta0_.col2,
tabdatasta0_.col3
from OWNER.tab_data_staging
group by tabdatasta0_.col1,
tabdatasta0_.col2,
tabdatasta0_.col3{code}
Hibernate 6 however does the following steps:
# Attempts to create a temporary table {{HTE_OWNER.tab_data}}. This fails without logging any errors, because {{HTE_OWNER}} is not a known schema. Also, even if the schema _was_ correct, it would still fail because {{CLIENT}} is not allowed to create tables in the {{OWNER}} schema.
# Issue our insert statement against the temporary table instead of {{OWNER.tab_data}}. This now fails because the temporary table doesn’t exist ({{oracle.jdbc.OracleDatabaseException: ORA-00942: table or view does not exist}}). Testing this scenario against H2 where everything happens in the same schema and the temporary table could be created, reveals the next step that would have happened:
# Copy all rows from the temporary table into the actual target table, {{OWNER.tab_data}}.
*Question/Issue*
* Why is an {{insert into ... select from}} no longer performed directly? Why the need to copy into a temporary table first and then into the actual target table?
* When resolving the names of the temporary tables, Hibernate 6 doesn’t consider that a {{hibernate.default_schema}} may have been configured. I found the offending code [here|https://github.com/hibernate/hibernate-orm/blob/b0f2faedca2992bb820bb6628f469b5fbf4bbc9f/hibernate-core/src/main/java/org/hibernate/dialect/Dialect.java#L1948] and [here|https://github.com/hibernate/hibernate-orm/blob/b0f2faedca2992bb820bb6628f469b5fbf4bbc9f/hibernate-core/src/main/java/org/hibernate/dialect/Dialect.java#L1962], the {{basename}} and {{name}} variables can already be prefixed with the custom schema, which isn’t handled there. The {{HT_}} and {{HTE_}} prefixes are just blindly prepended. |
|
