[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%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMDZhMDQ0MzZm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiMDZhMD... ) HHH-15517 ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiMDZhMD... ) "insert from select" With hibernate.default_schema Configured Doesn't Work With Hibernate 6 ( https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiMDZhMD... ) Issue Type: Bug Affects Versions: 6.1.3 Assignee: Unassigned Components: hibernate-core Created: 16/Sep/2022 00:21 AM Environment: Hibernate 6.1.3 Eclipse Temurin 17.0.4.1 Windows/Linux Oracle Database Priority: Major Reporter: 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: 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 Hibernate 5 rendered this HQL into a single DML statement as expected: 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 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. 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/b0f2faedca2992bb820bb6628... ) and here ( https://github.com/hibernate/hibernate-orm/blob/b0f2faedca2992bb820bb6628... ) , 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 )