[hibernate-issues] [JIRA] (HHH-15517) "insert from select" With hibernate.default_schema Configured Doesn't Work With Hibernate 6

Kai Zander ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZmIyZWIzZTM3... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiZmIyZW... ) HHH-15517 ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiZmIyZW... ) "insert from select" With hibernate.default_schema Configured Doesn't Work With Hibernate 6 ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiZmIyZW... ) 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 _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. 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 )