Re: [h2] Delete with sub-select and no alias not working as expected?

[Noel Grandin]

Looks like the USER_DETAILS.ID column is the problem child, it's not being resolved the way Hibernate wants.

I think you should log a bug with Hibernate, because they should always be using an alias, to work around exactly this kind of problem

Your choice of short-term workarounds are
(a) bypass Hibernate
(b) rename one of the ID columns
(c) use a VIEW to make it look like one of the ID columns has been renamed.

On 2013-06-04 10:19, Steve Potter wrote:

Hi,
We are using H2 extensively for unit testing our Hibernate based application.  We are experiencing a problem on a delete with a sub-select.  The following SQL does not delete any rows in our test (two rows should be deleted):

delete from PERSON where TYPE='P' and  not (exists (select user1_.ID from USER_DETAILS user1_ where user1_.PERSON_ID=ID))

If an alias for PERSON is introduced, the two rows are successfully deleted:

delete from PERSON p where p.TYPE='P' and  not (exists (select user1_.ID from USER_DETAILS user1_ where user1_.PERSON_ID=p.ID))

Unfortunately the first version is being generated by Hibernate so it is not easy to add the alias.  Any suggestions?

We are using H2 version 1.3.172.

Regards,
    Steve P

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Steve Potter]

Noel,
Many thanks for the rapid response.  I will look at raising a Hibernate issue.

Steve P.