-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 
  Previous topic | Next topic 
Author Message
sagimann
 Post subject: query generates invalid SQL?
PostPosted: Tue Apr 29, 2008 3:50 pm 
Senior
Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164
Hi,
I'm trying to create a delete query, but hibernate generates invalid SQL for it:

Hibernate version:
3.2.6

Name and version of the database:
MySQL 5.0

Entities:
Code:
@Entity
@Inheritance(strategy=InheritanceType.JOINED)
@DiscriminatorColumn(name="classname", discriminatorType=DiscriminatorType.STRING, length=255)
public abstract class JoinedBaseClass implements Serializable {
   
    @Id
    @GeneratedValue
    public Long getId() { return this.id; }
    public void setId(Long id) { this.id = id; }
    private Long id;
   
}

@Entity
@DiscriminatorValue(value="joinedsubclass1")
public class JoinedSubClass1 extends JoinedBaseClass {
   
    @Basic
    public String getSubProp1() { return bp1; }
    public void setSubProp1(String bp) { this.bp1 = bp; }
    private String bp1;
}


Code in EJB
em.createQuery("delete from JoinedSubClass1 c where c.id = 3")
.executeUpdate();

The generated SQL (from MySQL trace file):
Code:
create temporary table if not exists HT_JoinedSubClass1 (id bigint not null)
insert into HT_JoinedSubClass1 select joinedsubc0_.id as id from JoinedSubClass1 joinedsubc0_ inner join JoinedBaseClass joinedsubc0_1_ on joinedsubc0_.id=joinedsubc0_1_.id where id=3

Note the final 'where' clause. Shouldn't it be where joinedsubc0_.id=3 instead of where id=3 ???? When I manually tried to replace the where clause (just to see if the query gives correct output), it works... but Hibernate insists on generating an invalid query :-( I'm not sure what I need to do in order to instruct Hibernate to generate the correct syntax.
I'd appreciate any ideas...

Full stack trace of any exception that occurs:
SQL Error: 1052, SQLState: 23000
Column 'id' in where clause is ambiguous
EJB5018: An exception was thrown during an ejb invocation on [TestBean]
javax.ejb.EJBException
at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:3869)
at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3769)
at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3571)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1354)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1316)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:210)
at com.sun.ejb.containers.EJBObjectInvocationHandlerDelegate.invoke(EJBObjectInvocationHandlerDelegate.java:117)
at $Proxy87.removeSubClass1Bulk(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie._invoke(ReflectiveTie.java:154)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatchToServant(CorbaServerRequestDispatcherImpl.java:687)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:227)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1846)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1706)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:1088)
at com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:223)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:806)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.dispatch(CorbaMessageMediatorImpl.java:563)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.doWork(CorbaMessageMediatorImpl.java:2567)
at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:555)
Caused by: javax.persistence.EntityExistsException: org.hibernate.exception.ConstraintViolationException: could not insert/select ids for bulk delete
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:612)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:58)
at pu1.TestBean.removeSubClass1Bulk(TestBean.java:55)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1067)
at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:176)
at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2895)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3986)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:203)
... 17 more
Caused by: org.hibernate.exception.ConstraintViolationException: could not insert/select ids for bulk delete
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:102)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:49)
... 27 more
Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Column 'id' in where clause is ambiguous
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1011)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:93)
... 32 more
Top
 Profile  
 
hardy.ferentschik
 Post subject:
PostPosted: Thu May 01, 2008 7:44 am 
Hibernate Team
Hibernate Team

Joined: Thu Apr 05, 2007 5:52 am
Posts: 1689
Location: Sweden
Hi,

are you not mixing inherit strategies here? @Inheritance(strategy=InheritanceType.JOINED) implies that you want to use the strategy table per subclass, meaning you would end up with a table JoinedBaseClass and JoinedSubClass1.

At the same time you are specifying @DiscriminatorColumn and @DiscriminatorValue which would be used in a table per class hierarchy approach (JPA type InheritanceType.SINGLE_TABLE) where one column specifies which type of class a single row in the database represents.

I am not sure if this is the cause for your problem, but it is definitely confusing.

Hope it helps.

--Hardy
Top
 Profile  
 
sagimann
 Post subject: tried that
PostPosted: Fri May 02, 2008 7:57 am 
Senior
Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164
Hi, I tried without a discr. column but it did not make any difference - same exception, same query. Is this a bug??


p.s. see my replies in the following thread regarding the JOINED strategy and usage of a discr. column.
http://forum.hibernate.org/viewtopic.php?t=986327
Top
 Profile  
 
hardy.ferentschik
 Post subject:
PostPosted: Fri May 02, 2008 8:29 am 
Hibernate Team
Hibernate Team

Joined: Thu Apr 05, 2007 5:52 am
Posts: 1689
Location: Sweden
Hi,

Have you tried to use subProp1 in the query?

Code:
em.createQuery("delete from JoinedSubClass1 c where c.subProp1 = 'foo'")
.executeUpdate();


I know that won't fix your problem, but I would be interested to see what happens then. Or you could try to rename the 'id' property to 'myId' or something like this.

--Hardy
Top
 Profile  
 
sagimann
 Post subject: optimistic update :-)
PostPosted: Sun May 04, 2008 2:39 am 
Senior
Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164
yes, I have tried, but it doesn't cover my use cases, as I need to get the entities by id.

anyway, I posted a bug report:
http://opensource.atlassian.com/projects/hibernate/browse/EJB-355
and it seems to be a known issue, which has some sort of patch:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3150
But it doesn't yet say which future release will contain it. I will try the patch and update this post with my findings.
Top
 Profile  
 
andydale
 Post subject:
PostPosted: Mon May 05, 2008 4:31 am 
Senior
Senior

Joined: Tue Jul 25, 2006 9:05 am
Posts: 163
Location: Stuttgart/Karlsruhe, Germany
Hi,

If it is not an issue to delete the base class as well you could just issue the following command:

Code:
mEntityManager.createQuery("delete from JoinedBaseClass c where c.id = 3").executeUpdate();


This generates the following:

Code:
    insert
    into
        HT_JoinedBaseClass
        select
            joinedbase0_.id as id
        from
            JoinedBaseClass joinedbase0_
        where
            id=3

    delete
    from
        JoinedSubClass1
    where
        (
            id
        ) IN (
            select
                id
            from
                HT_JoinedBaseClass
        )

    delete
    from
        JoinedBaseClass
    where
        (
            id
        ) IN (
            select
                id
            from
                HT_JoinedBaseClass
        )


Cheers,

Andy

_________________
Rules are only there to be broken

Top
 Profile  
 
sagimann
 Post subject: the problem is the subclass
PostPosted: Mon May 05, 2008 5:06 am 
Senior
Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164
Andy,
Yes - deleting using the baseclass is ok. The problem is "delete from subclass..."
Top
 Profile  
 
andydale
 Post subject:
PostPosted: Mon May 05, 2008 5:33 am 
Senior
Senior

Joined: Tue Jul 25, 2006 9:05 am
Posts: 163
Location: Stuttgart/Karlsruhe, Germany
Hi,

If you happen to know the ID of the the sub class you could do it like so:

Code:
mEntityManager.remove(mEntityManager.getReference(JoinedSubClass1.class, 163L));

or
Code:
mEntityManager.remove(mEntityManager.find(JoinedSubClass1.class, 163L));


but you will have to make sure that the object exists or you will get the following exception:

for getReference:

Quote:
Exception in thread "main" java.lang.NoSuchMethodError: javax.persistence.EntityNotFoundException: method <init>(Ljava/lang/String;Ljava/lang/Throwable;)V not found


for find:

Quote:
Exception in thread "main" java.lang.IllegalArgumentException: attempt to create delete event with null entity



Cheers,

Andy

_________________
Rules are only there to be broken

Top
 Profile  
 
sagimann
 Post subject: em.remove works fine
PostPosted: Mon May 05, 2008 5:55 am 
Senior
Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164
Andy,
There was never any issue with em.remove() - only with bulk delete. em.remove works fine in any circumstance.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.