Bug 977520 - HHH-8318 "delete" with "member of" query fails
HHH-8318 "delete" with "member of" query fails
Status: CLOSED CURRENTRELEASE
Product: JBoss Enterprise Application Platform 6
Classification: JBoss
Component: Hibernate (Show other bugs)
6.1.0
Unspecified Unspecified
medium Severity medium
: ER1
: EAP 6.2.0
Assigned To: Brett Meyer
:
Depends On:
Blocks: 977522 eap62-upgrade-hibernate425 1065062
  Show dependency treegraph
 
Reported: 2013-06-24 15:08 EDT by csams
Modified: 2015-09-01 00:00 EDT (History)
9 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
An bug in the conversion of HQL `delete` queries that contained a `member of` clause would result in incorrect SQL being generated. As a result, the incorrect SQL delete query would fail. This issue was caused by the `member of` subquery using an incorrect alias. This issue has been fixed in this release of JBoss EAP 6 by making `member of` subqueries use the correct alias when used as part of a HQL `delete` query, and as a result, will now produce correct SQL.
Story Points: ---
Clone Of:
Environment:
JBoss EAP 6.1, Hibernate Core 4.2.0
Last Closed: 2013-12-15 11:19:26 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description csams 2013-06-24 15:08:28 EDT
Description of problem:
The fix for HHH-5209 exposed (possibly caused, but not sure) a new issue with a "delete" query using a "member of".
The commit in question: https://github.com/hibernate/hibernate-orm/commit/95bc0194ec37738572c6b8202f1312f82622d747
Query, generated SQL before the commit, and generated SQL after the commit: https://gist.github.com/brmeyer/8434aee0de36e0639d0f
The failure is caused by the "Attrset.id=attrvalues5_.Attrset_id" in the last "where". It should be using the "attrset3_" alias.

Version-Release number of selected component (if applicable):
Hibernate core 4.2.0, but issue was introduced in 4.1.8

How reproducible:
Always

Steps to Reproduce:
EntityManager em = getOrCreateEntityManager();
    	em.getTransaction().begin();
    	em.createQuery(
                "delete Attrvalue aval where aval.id in ( "
                    + "select val2.id from Employee e, Employeegroup eg, Attrset aset, Attrvalue val2 "
                    + "where eg.id = e.employeegroup.id " + "and aset.id = e.attrset.id "
                    + "and val2.id member of aset.attrvalues)").executeUpdate();
    	em.getTransaction().commit();

Actual results:
delete
from
Attrvalue
where
id in (
	select
		attrvalue4_.id
	from
	    Employee employee1_ cross
	join
        Employeegroup employeegr2_ cross
	join
        Attrset attrset3_ cross
	join
        Attrvalue attrvalue4_
	where
	    employeegr2_.id=employee1_.employeegroup_id
	    and attrset3_.id=employee1_.attrset_id
	    and (
	        attrvalue4_.id in (
	            select
	                attrvalues5_.attrvalues_id
	            from
                    ATTRSET_X_ATTRVALUE attrvalues5_
	            where
                    Attrset.id=attrvalues5_.Attrset_id
            )
        )
    )

Expected results:
delete 
from
Attrvalue 
where
id in (
    select
        attrvalue4_.id 
    from
        Employee employee1_ cross 
    join
        Employeegroup employeegr2_ cross 
    join
        Attrset attrset3_ cross 
    join
        Attrvalue attrvalue4_ 
    where
        employeegr2_.id=employee1_.employeegroup_id 
        and attrset3_.id=employee1_.attrset_id 
        and (
            attrvalue4_.id in (
                select
                    attrvalue6_.id 
                from
                    ATTRSET_X_ATTRVALUE attrvalues5_,
                    Attrvalue attrvalue6_ 
                where
                    attrset3_.id=attrvalues5_.Attrset_id 
                    and attrvalues5_.attrvalues_id=attrvalue6_.id
            )
        )
    )

Additional info:
Comment 3 Rostislav Svoboda 2013-07-29 01:16:24 EDT
There was one-off patch for EAP 6.1.0 [1] to fix this issue. It must be included into EAP 6.1.1 to have all one-offs covered 

Reseting jboss-eap-6.1.1 flag to '?' to evaluate it again.

[1] https://bugzilla.redhat.com/show_bug.cgi?id=977522#c9
Comment 9 Zbyněk Roubalík 2013-09-23 07:27:11 EDT
EAP 6.2.0.ER1

Note You need to log in before you can comment on or make changes to this bug.