[hibernate-issues] [JIRA] (HHH-14153) HQL update query with multiple condition generates temporary tables on SQLServer

[François DAMBRINE (JIRA)]

François DAMBRINE ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5c07a505d3af3b1ccfec791d ) *created* an issue

Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMzFmYWRhMGRkMWI5NDRhOWI5NzgxNTE5NDAwYzAwNjIiLCJwIjoiaiJ9 ) / Bug ( https://hibernate.atlassian.net/browse/HHH-14153?atlOrigin=eyJpIjoiMzFmYWRhMGRkMWI5NDRhOWI5NzgxNTE5NDAwYzAwNjIiLCJwIjoiaiJ9 ) HHH-14153 ( https://hibernate.atlassian.net/browse/HHH-14153?atlOrigin=eyJpIjoiMzFmYWRhMGRkMWI5NDRhOWI5NzgxNTE5NDAwYzAwNjIiLCJwIjoiaiJ9 ) HQL update query with multiple condition generates temporary tables on SQLServer ( https://hibernate.atlassian.net/browse/HHH-14153?atlOrigin=eyJpIjoiMzFmYWRhMGRkMWI5NDRhOWI5NzgxNTE5NDAwYzAwNjIiLCJwIjoiaiJ9 )

Issue Type: Bug Affects Versions: 5.4.10, 5.4.19 Assignee: Unassigned Components: hibernate-core Created: 13/Aug/2020 00:14 AM Environment: Windows Server 2019
SQLServer 2016
hibernate-core 5.4.19 (also tested with 5.4.10)
hibernate-c3p0 5.4.19 (also tested with 5.4.10)
Labels: HQL Priority: Major Reporter: François DAMBRINE ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5c07a505d3af3b1ccfec791d )

When we try to run an HQL UPDATE query that will update a table with a set of two conditions, hibernate split the query in three parts :

* create a temp table
* insert all matching records in this table
* launch update query

This only happen with SQLServer, the main issue is that HHH-3326 ( https://hibernate.atlassian.net/browse/HHH-3326 ) Open is not yet fixed, so, when the SQLServer tempdb has a collation that is not compatible with our current db, the query just fails with a collation conflict.

*Minimal code to reproduce*

           Query q = entityManager.createQuery( "UPDATE abstracttaskentity e SET "
                   + "e.locked = true "
                   + "WHERE e.locked = false AND e.taskId = :id" );
           q.setParameter( "id" , taskToAcquire.getTaskId());
           entityManager.getTransaction().commit();

*Hibernate query log*

Hibernate: 
   create table #abstracttaskentity (
       id varchar(255) not null
   ) 
Hibernate: 
   insert 
   into
       #abstracttaskentity
       select
           abstractta0_.id as id 
       from
           abstracttaskentity abstractta0_ 
       where
           abstractta0_.locked=0 
           and abstractta0_.id=?
Hibernate: 
   update
       abstracttaskentity 
   set
       locked=1,
   where
       (
           id
       ) IN (
           select
               id 
           from
               #abstracttaskentity
       )

( https://hibernate.atlassian.net/browse/HHH-14153#add-comment?atlOrigin=eyJpIjoiMzFmYWRhMGRkMWI5NDRhOWI5NzgxNTE5NDAwYzAwNjIiLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-14153#add-comment?atlOrigin=eyJpIjoiMzFmYWRhMGRkMWI5NDRhOWI5NzgxNTE5NDAwYzAwNjIiLCJwIjoiaiJ9 )

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.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100144- sha1:b6d623e )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200813/8d59dfa6/attachment.html