Kai Zander ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZmEyMjM1ZGQx... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiZmEyMj... ) HHH-15517 ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiZmEyMj... ) "insert from select" With hibernate.default_schema Configured Doesn't Work With Hibernate 6 ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiZmEyMj... ) Change By: Kai Zander ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) 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/b0f2faedca2992bb820b...] and [here|https://github.com/hibernate/hibernate-orm/blob/b0f2faedca2992bb820b...], 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. ( https://hibernate.atlassian.net/browse/HHH-15517#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15517#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100207- sha1:30c8888 )