CODEWITHSUNDEEP
oracle-databasehibernatehqloracle11g
chrismarx
chrismarx

Reputation: 12555

hibernate's size function produces sql that fails in oracle 11g

When hibernate parses out this hql:

UPDATE VERSIONED Person SET groupsCount = followingGroup.size WHERE id = :id

it generates this sql:

UPDATE Person
  SET version  =version+1,
  groupsCount=
  (SELECT COUNT(followingg1_.followerId)
     FROM GroupFollower followingg1_
    WHERE Person.id=followingg1_.followerId
  )-1
  WHERE id IN
  (SELECT groupfollo2_.followerId
     FROM GroupFollower groupfollo2_
    WHERE groupfollo2_.followingId=?
  )

That query fails at the "-1" after the subquery for the count of the followingids. If i take out the "-1" it runs just fine, or if i move the "-1" to "SELECT COUNT(followingg1_.followerId) - 1" it also works.

I don't see how to work around this, short of using db specific sql, is this a bug with 11g?

Upvotes: 1

Views: 531

Answers (2)

chrismarx
chrismarx

Reputation: 12555

Ok, i found the problem, I was wrong, that statement deals with a "Person" object, which is more complicated that I first realized, although it was at that line that the test was failing, it was actually hql in the class it was testing that produced the sql that was the problem. 

UPDATE VERSIONED Person SET groupsCount = followingGroup.size - 1 WHERE id IN (SELECT etc...)

So that answers the first part, the second part of the answer is that its a known bug apparently in oracle 10g that you can't do arithmetic operations after the subquery in an update statement (duh!) so changing the above to

UPDATE VERSIONED Person SET groupsCount = - 1 + followingGroup.size WHERE id IN (SELECT etc...)

works just fine. phew-

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

The following is working on the 11.2.0.2 instance behind apex.oracle.com

create table person (id number, version number, groupscount number);

create table groupfollower (followerid number, followingid number);

insert into person values (1,1,0);
insert into person values (2,1,0);
insert into groupfollower values (1,2);


UPDATE Person
  SET version  =version+1,
  groupsCount=
  (SELECT COUNT(followingg1_.followerId)
     FROM GroupFollower followingg1_
    WHERE Person.id=followingg1_.followerId
  )-1
  WHERE id IN
  (SELECT groupfollo2_.followerId
     FROM GroupFollower groupfollo2_
    WHERE groupfollo2_.followingId=2
  )

Suggest you check that SQL in your version. It may be a Hibernate issue.

Upvotes: 1

Related Questions