Kai Zander (
https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%...
) *commented* on HHH-15517 (
https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiYmE3Zj...
)
Re: "insert from select" With hibernate.default_schema Configured Doesn't
Work With Hibernate 6 (
https://hibernate.atlassian.net/browse/HHH-15517?atlOrigin=eyJpIjoiYmE3Zj...
)
Thanks for the reply! Here are the entity classes:
@Getter
@Setter
@MappedSuperclass
@EqualsAndHashCode(onlyExplicitlyIncluded = true )
public abstract sealed class BaseTabData permits TabData, TabDataStaging {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator =
"tab_data_seq" )
@SequenceGenerator(name = "tab_data_seq" , sequenceName =
"tab_data_seq" , allocationSize = 500)
private Long id;
@EqualsAndHashCode.Include
private String col1;
@EqualsAndHashCode.Include
private String col2;
@EqualsAndHashCode.Include
private String col3;
}
@Entity
@NamedQuery(name = "TabData.copyFromStaging" ,
query = """
insert into TabData (
id,
col1,
col2,
col3
)
select max(id),
col1,
col2,
col3
from TabDataStaging
group by col1,
col2,
col3""")
public final class TabData extends BaseTabData {}
@Entity
public final class TabDataStaging extends BaseTabData {}
I have an integration test running against H2 that does the following:
* Insert 500 rows into tab_data_staging
* Clear tab_data
* Copy all rows from tab_data_staging into tab_data (using the TabData.copyFromStaging
named query).
Here’s the log output running with Hibernate 5:
2022-09-16 09:18:48.063Z INFO [Pool-1-worker-1] o.s.t.c.transaction.TransactionContext
: Began transaction (1) for test context
2022-09-16 09:18:48.071Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: call next value for tab_data_seq
2022-09-16 09:18:48.072Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: call next value for tab_data_seq
2022-09-16 09:18:48.081Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: insert into tab_data_staging (col2, col1, col3, id) values (?, ?, ?, ?)
... 498 identical insert statements
2022-09-16 09:18:48.096Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: insert into tab_data_staging (col2, col1, col3, id) values (?, ?, ?, ?)
2022-09-16 09:18:48.102Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: delete from tab_data
2022-09-16 09:18:48.103Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: insert into tab_data ( id, col1, col2, col3 ) select max(tabdatasta0_.id) as col_0_0_,
tabdatasta0_.col1 as col_1_0_, tabdatasta0_.col2 as col_2_0_, tabdatasta0_.col3 as
col_3_0_ from tab_data_staging tabdatasta0_ group by tabdatasta0_.col1 , tabdatasta0_.col2
, tabdatasta0_.col3
2022-09-16 09:18:48.110Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: select tabdatasta0_.id as id1_20_, tabdatasta0_.col2 as product_2_20_, tabdatasta0_.col1
as product_3_20_, tabdatasta0_.col3 as col34_20_ from tab_data tabdatasta0_
2022-09-16 09:18:48.120Z INFO [Pool-1-worker-1] o.s.t.c.transaction.TransactionContext
: Rolled back transaction for test
Here’s the log output running with Hibernate 6 (notice there are no update statements
against the HTE table):
2022-09-14T17:50:35.275Z INFO [Pool-1-worker-1] o.s.t.c.transaction.TransactionContext
: Began transaction (1) for test context
2022-09-14T17:50:35.282Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: select next value for tab_data_seq
2022-09-14T17:50:35.282Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: select next value for tab_data_seq
2022-09-14T17:50:35.287Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: insert into tab_data_staging (col2, col1, col3, id) values (?, ?, ?, ?)
... 498 identical insert statements
2022-09-14T17:50:35.301Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: insert into tab_data_staging (col2, col1, col3, id) values (?, ?, ?, ?)
2022-09-14T17:50:35.305Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: delete from tab_data
2022-09-14T17:50:35.305Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: create local temporary table HTE_tab_data(id bigint, col2 varchar(255), col1
varchar(255), col3 varchar(255), rn_ integer not null, primary key (rn_))
2022-09-14T17:50:35.307Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: insert into HTE_tab_data(id,col1,col2,col3,rn_) (select
max(p2_0.id),p2_0.col1,p2_0.col2,p2_0.col3,row_number() over() from tab_data_staging p2_0
group by p2_0.col1,p2_0.col2,p2_0.col3)
2022-09-14T17:50:35.313Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: insert into tab_data(id,col1,col2,col3) select p1_0.id,p1_0.col1,p1_0.col2,p1_0.col3
from HTE_tab_data p1_0
2022-09-14T17:50:35.316Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: delete from HTE_tab_data
2022-09-14T17:50:35.319Z DEBUG [Pool-1-worker-1] org.hibernate.SQL
: select p1_0.id,p1_0.col2,p1_0.col1,p1_0.col3 from tab_data p1_0
2022-09-14T17:50:35.327Z INFO [Pool-1-worker-1] o.s.t.c.transaction.TransactionContext
: Rolled back transaction for test
I have hibernate.jdbc.batch_size=1000 configured, if that matters.
(
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:7afd384 )