-->
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 » Hibernate & Java Persistence » Hibernate Users

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
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
toonstge
 Post subject: invalid outer join generation on 'formula' with 3.1?
PostPosted: Wed Dec 28, 2005 8:37 am 
Newbie

Joined: Wed Dec 28, 2005 8:13 am
Posts: 2
Location: Recife, Brazil
Hi all,

I'm working on a project with a poor data-model, which was inherited. We cannot modify everything at once, so with Hibernate 3.0 we've successfully mapped tables to POJO's and things worked fine.

I'm looking at upgrading to 3.1 in the future to benefit from a bug hitting us on queries and ran a couple of initial tests with 3.1 with the same mapping files. Maybe we're incorrectly using the 'formula' here in ways that it was not designed to work, so please comment.

Database: Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0
Host OS: Windows XP

In one mapping, we have the following set declaration using a formula:

<set name="billingMethods" table="media_type_supports_billing" inverse="true" >
<key column="media_type_id"/>
<many-to-many class="BillingMethod" formula="(SELECT bm.name FROM billing_methods bm WHERE bm.REFERENCE_NAME = billing_method_name )"/>
</set>

Our tables are:

create table MEDIA_TYPE_SUPPORTS_BILLING
(
MEDIA_TYPE_ID NUMBER(19) not null,
BILLING_METHOD_NAME VARCHAR2(64) not null
)

create table BILLING_METHODS
(
REFERENCE_NAME VARCHAR2(64),
NAME VARCHAR2(64) not null,
ENABLED_FOR_CARRIER NUMBER(1),
ENABLED_FOR_CP NUMBER(1)
)

create table MEDIA_TYPES
(
ID NUMBER(19) not null,
MIME VARCHAR2(50) not null,
EXTENSIONS VARCHAR2(50) not null,
DESCRIPTOR_TYPE NUMBER(19)
)

On Hibernate 3.0.5, this was generating the following SQL:

select
billingmet0_.media_type_id as media1_1_,
(SELECT bm.name FROM billing_methods bm WHERE bm.REFERENCE_NAME = billingmet0_.billing_method_name ) as formula0_1_,
billingmet1_.name as name0_,
billingmet1_.reference_name as reference2_22_0_,
billingmet1_.ENABLED_FOR_CARRIER as ENABLED3_22_0_,
billingmet1_.ENABLED_FOR_CP as ENABLED4_22_0_ from media_type_supports_billing billingmet0_,
BILLING_METHODS billingmet1_
where
(SELECT bm.name FROM billing_methods bm WHERE bm.REFERENCE_NAME = billingmet0_.billing_method_name )
= billingmet1_.name
and billingmet0_.media_type_id = ?


On Hibernate 3.1, compiled with a CVS extract taken yesterday December 27 2005, this generated the following SQL:

select
billingmet0_.media_type_id as media1_1_,
(SELECT bm.name FROM billing_methods bm WHERE bm.REFERENCE_NAME = billingmet0_.billing_method_name ) as formula0_1_,
billingmet1_.name as name22_0_,
billingmet1_.reference_name as reference2_22_0_,
billingmet1_.ENABLED_FOR_CARRIER as ENABLED3_22_0_,
billingmet1_.ENABLED_FOR_CP as ENABLED4_22_0_
from
media_type_supports_billing billingmet0_,
BILLING_METHODS billingmet1_
where
(SELECT bm.name FROM billing_methods bm WHERE bm.REFERENCE_NAME = billingmet0_.billing_method_name )
= billingmet1_.name(+) and billingmet0_.media_type_id = ?


( Caused by: java.sql.SQLException: ORA-00936: missing expression )

org.hibernate.PropertyAccessException: Exception occurred inside setter of MediaType.billingMethods
at org.hibernate.property.BasicPropertyAccessor$BasicSetter.set(BasicPropertyAccessor.java:65)
at org.hibernate.tuple.AbstractEntityTuplizer.setPropertyValues(AbstractEntityTuplizer.java:330)
at org.hibernate.tuple.PojoEntityTuplizer.setPropertyValues(PojoEntityTuplizer.java:188)
at org.hibernate.persister.entity.AbstractEntityPersister.setPropertyValues(AbstractEntityPersister.java:3232)
at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:126)
at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:842)
at org.hibernate.loader.Loader.doQuery(Loader.java:717)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:93)
at org.hibernate.loader.entity.EntityLoader.loadByUniqueKey(EntityLoader.java:85)
at org.hibernate.persister.entity.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:1522)
at org.hibernate.type.EntityType.loadByUniqueKey(EntityType.java:365)
at org.hibernate.type.EntityType.resolve(EntityType.java:306)
at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:113)
at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:842)
at org.hibernate.loader.Loader.doQuery(Loader.java:717)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1919)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:71)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:52
0)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeColl
ectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1631)
at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:4
54)
at org.hibernate.engine.StatefulPersistenceContext.initializeNonLazyCollections(StatefulPersistenceContext.java:
827)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:229)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1919)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:71)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:52
0)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeColl
ectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1631)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344)
at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:86)
at org.hibernate.collection.AbstractPersistentCollection.readSize(AbstractPersistentCollection.java:109)
at org.hibernate.collection.PersistentSet.size(PersistentSet.java:114)
at java.util.ArrayList.addAll(ArrayList.java:413)
....
at weblogic.servlet.internal.ServletStubImpl.createServlet(ServletStubImpl.java:731)
...
at weblogic.t3.srvr.ServletInitRunner.run(ServletInitRunner.java:49)
at java.lang.Thread.run(Thread.java:479)
Caused by: java.lang.reflect.InvocationTargetException: org.hibernate.exception.SQLGrammarException: could not initializ
e a collection: [MediaType.billingMethods#11]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1926)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:71)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:52
0)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeColl
ectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1631)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344)
at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:86)
at org.hibernate.collection.PersistentSet.iterator(PersistentSet.java:138)
at MediaType.setBillingMethods(MediaType.java:375)
at java.lang.reflect.Method.invoke(Native Method)
at org.hibernate.property.BasicPropertyAccessor$BasicSetter.set(BasicPropertyAccessor.java:42)
at org.hibernate.tuple.AbstractEntityTuplizer.setPropertyValues(AbstractEntityTuplizer.java:330)
at org.hibernate.tuple.PojoEntityTuplizer.setPropertyValues(PojoEntityTuplizer.java:188)
at org.hibernate.persister.entity.AbstractEntityPersister.setPropertyValues(AbstractEntityPersister.java:3232)
at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:126)
at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:842)
at org.hibernate.loader.Loader.doQuery(Loader.java:717)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:93)
at org.hibernate.loader.entity.EntityLoader.loadByUniqueKey(EntityLoader.java:85)
at org.hibernate.persister.entity.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:1522)
at org.hibernate.type.EntityType.loadByUniqueKey(EntityType.java:365)
at org.hibernate.type.EntityType.resolve(EntityType.java:306)
at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:113)
at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:842)
at org.hibernate.loader.Loader.doQuery(Loader.java:717)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1919)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:71)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:52
0)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeColl
ectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1631)
at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:4
54)
at org.hibernate.engine.StatefulPersistenceContext.initializeNonLazyCollections(StatefulPersistenceContext.java:
827)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:229)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1919)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:71)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:52
0)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeColl
ectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1631)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344)
at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:86)
at org.hibernate.collection.AbstractPersistentCollection.readSize(AbstractPersistentCollection.java:109)
at org.hibernate.collection.PersistentSet.size(PersistentSet.java:114)
at java.util.ArrayList.addAll(ArrayList.java:413)
...
at javax.servlet.GenericServlet.init(GenericServlet.java:258)
...
at weblogic.t3.srvr.ServletInitRunner.run(ServletInitRunner.java:49)
at java.lang.Thread.run(Thread.java:479)
Caused by: java.sql.SQLException: ORA-00936: missing expression
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:798)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1038)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:838)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1131)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3284)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3328)
at weblogic.jdbc.jts.Statement.executeQuery(Statement.java:208)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1919)
... 63 more
Caused by: org.hibernate.exception.SQLGrammarException: could not initialize a collection: [MediaType.billingMethods#11]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1926)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:71)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:52
0)


My hibernate.cfg.xml was set to use OracleDialect, so I switched to Oracle9Dialect instead and kept the Hibernate 3.1 build:

select
billingmet0_.media_type_id as media1_1_,
(SELECT bm.name FROM billing_methods bm WHERE bm.REFERENCE_NAME = billingmet0_.billing_method_name ) as formula0_1_,
billingmet1_.name as name22_0_,
billingmet1_.reference_name as reference2_22_0_,
billingmet1_.ENABLED_FOR_CARRIER as ENABLED3_22_0_,
billingmet1_.ENABLED_FOR_CP as ENABLED4_22_0_
from
media_type_supports_billing billingmet0_ left outer join BILLING_METHODS billingmet1_ on
( SELECT bm.name
FROM billing_methods bm
WHERE bm.REFERENCE_NAME = billingmet0_.billing_method_name )
= billingmet1_.name
where
billingmet0_.media_type_id = ?

<Dec 28, 2005 10:06:33 AM BRST> <Error> <Unknown> <org.hibernate.util.JDBCExceptionReporter: ORA-01799: a column may not
be outer-joined to a subquery
>


Are we using formula incompatible with how it was designed, or is this a potential Hibernate3.1 bug? Note again, this did work in Hibernate 3.0.

Is there an alternative way for these mappings in Hibernate 3.1 without reorganizing the tables?
Top
 Profile  
 
toonstge
 Post subject:
PostPosted: Wed Dec 28, 2005 9:43 am 
Newbie

Joined: Wed Dec 28, 2005 8:13 am
Posts: 2
Location: Recife, Brazil
I've solved this issue for me based on a suggestion of a colleague by setting the value of "BILLING_METHOD_NAME" in the link table to the value of the primary key of the BILLING_METHODS table. That should be the data-model used anyway.

Using a script like:

"UPDATE MEDIA_TYPE_SUPPORTS_BILLING mtsb SET mtsb. BILLING_METHOD_NAME = ( SELECT bm.NAME WHERE bm.REFERENCE_NAME = mtsb.BILLING_METHOD_NAME );"

This was easy. Luckily, in my case, other classes were using the primary key of the billing methods correctly.

<many-to-many class="BillingMethod" column="BILLING_METHOD_NAME"/>
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
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.