We have some code that adds {{row_number}} expressions like in the table based insert handler, which should rather use {{dense_rank}} or wrap a query in certain circumstances. See [https://hibernate.atlassian.net/browse/HHH-15517|https://hibernate.atlassian.net/browse/HHH-15517|smart-link] for an example.
Paraphrasing:
If I rewrite the HQL to this (don’t select {{id}}, use {{distinct}} instead of {{group by}}):
{code:sql}insert into TabData (col1, col2, col3)
select distinct col1, col2, col3
from TabDataStaging{code}
Hibernate 6 does the following:
{code:sql}insert into HTE_tab_data(col1, col2, col3, rn_)
(select distinct p2_0.col1,
p2_0.col2,
p2_0.col3,
row_number() over () -- adding this column effectively disables the `distinct`
from tab_data_staging p2_0);{code}
Followed by the {{updates}} on the HTE table you were suspecting earlier:
{code:sql}update HTE_tab_data set id=? where rn_=?
update HTE_tab_data set id=? where rn_=?
...{code}
Followed by
{noformat}insert into tab_data(col1,col2,col3,id)
select p1_0.col1,p1_0.col2,p1_0.col3,p1_0.id
from HTE_tab_data p1_0{noformat}
The final {{insert}} into {{tab_data}} can then fail due to unique constraint violation (I have a composite unique constraint on {{col1, col2, col3}}), because even though I’m using {{distinct}}, the {{row_number()}} clause added by Hibernate effectively disabled it… |
|
