[JIRA] (HHH-15517) "insert from select" With hibernate.default_schema Configured Doesn't Work With Hibernate 6
by Kai Zander (JIRA)
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=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100207- sha1:30c8888 )
2 years, 1 month
[JIRA] (HHH-15517) "insert from select" With hibernate.default_schema Configured Doesn't Work With Hibernate 6
by Kai Zander (JIRA)
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=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100207- sha1:30c8888 )
2 years, 1 month
[JIRA] (HHH-15517) "insert from select" With hibernate.default_schema Configured Doesn't Work With Hibernate 6
by Kai Zander (JIRA)
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=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100207- sha1:30c8888 )
2 years, 1 month