Query not supported on JOINED strategy

[glassfishrobot]

As per the EJB3 PFD it looks like JOINED strategy is required to be supported for persistence. There is nothink preventing people from doing polymorphic queries (see PFD §4.4.8).

But on b35 if I got a parent class foo.bean.Person, and two child (inheriting from Person) foo.bean.Expert and foo.bean.Customer.

All three classes are entities in a EJB3 joined inheritence scenario (with proper discriminator column and values).

Now, let's ask the PM, all the persons for a userId (not the PK of my class):

final Person person = Person.class.cast(manager.createQuery("select p from Person p where p.userId= :userId") .setParameter("userId",userId) .getSingleResult());

This throws the following exception :

Caused by: Exception [TOPLINK-6136] (Oracle TopLink Essentials - 10g release 4 (10.1.4.0.0) (Build 060104Dev)): oracle.toplink.essentials.exceptions.QueryException Exception Description: Classes mapped with multi table inheritance can not be ReportQuery items. Item: p, Expression: Base foo.bean.Person. Query: ReportQuery(foo.bean.Person) at oracle.toplink.essentials.exceptions.QueryException.polymorphicReportItemWithMultipletableNotSupported(QueryException.java:952) at oracle.toplink.essentials.internal.queryframework.ReportItem.initialize(ReportItem.java:98) at oracle.toplink.essentials.queryframework.ReportQuery.prepare(ReportQuery.java:792) at oracle.toplink.essentials.queryframework.DatabaseQuery.checkPrepare(DatabaseQuery.java:387) at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:588) at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:587) at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:796) at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:850) at oracle.toplink.essentials.internal.ejb.cmp3.base.RepeatableWriteUnitOfWork.internalExecuteQuery(RepeatableWriteUnitOfWork.java:130) at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:925) at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:897) at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:319) at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:425) at foo.service.AssistanceManagerService.findPersonByUserId(AssistanceManagerService.java:65) at foo.service.AssistanceManagerService.createCustomerIntervention(AssistanceManagerService.java:106) at foo.service.AssistanceManagerService.createNormalOfferIntervention(AssistanceManagerService.java:88) 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.SecurityUtil$2.run(SecurityUtil.java:171) at java.security.AccessController.doPrivileged(Native Method) at com.sun.enterprise.security.application.EJBSecurityManager.doAsPrivileged(EJBSecurityManager.java:975) at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:176) at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2748) at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3825) at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:190) ... 61 more

FYI, userId is in the Parent class, but I would like all possible instance (even of subclass) to be returned (in a regular polymorphic way).

"select object(p) from Person p where p.userId= :userId" will also fail with the same exception beeing thown.

A very ugly work around is apparently to use two queries instead :

// First one fetch the PK final Long identifier = Long.class.cast(manager.createQuery("SELECT p.identifier FROM Person AS p WHERE p.userId = :userId") .setParameter("userId",userId) .getSingleResult()); // Second one fetch the object ... final Person person = person.class.cast(manager.find(Person.class,identifier));

This is very blocking as polymorphism is an important feature of the new EJB3 specification.

Environment

Operating System: All Platform: All

Affected Versions

[9.0pe]

[glassfishrobot]

• Issue Imported From: https://github.com/javaee/glassfish/issues/206
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: @yaminikb
• Original Issue Closed By:@glassfishrobot

[glassfishrobot]

@glassfishrobot Commented mvatkina said: Is this a duplicate bug?

[glassfishrobot]

@glassfishrobot Commented tware said: The joined Inheritance feature will be supported by final release.

[glassfishrobot]

@glassfishrobot Commented tware said: Reducing to P3 because of noted workaround and the ability to use single table inheritance.

[glassfishrobot]

@glassfishrobot Commented bjb said: Single table inheritance is no viable alternative as it breaks the database structure and requires nullable column.

[glassfishrobot]

@glassfishrobot Commented isana said: I added me to CC, because I have the same problem with B36.

And SINGLE_TABLE mapping fails in my case, so it cannot be an alternative for me. The followings is a description of SINGLE_TABLE mapping failure. I write it as additional information.

Auto schema generation failes, because Toplink generates a bad CREATE with duplicate column definition. The exception message is as follows (in fact, there's no subclass defining property named 'ID' again): JDO76609: Got SQLException executing statement "CREATE TABLE FOO.BAR (ID NUMBER (19) NOT NULL, DTYPE VARCHAR2(255) NULL, JOURNALDATE DATE NOT NULL, BALANCE NUMBER(24,8) NULL, SERIALNUMBER NUMBER(10) NOT NULL, OBJECTVERSION NUMBER(38) NOT NULL, episode_ID NUMBER(19) NULL, ID NUMBER(19) NOT NULL, factor_ID NUMBER (19) NOT NULL, BOOKVALUEPERCENTREMAINING NUMBER(24,8) NOT NULL, PRIMARY KEY (ID, ID))": java.sql.SQLException: ORA-00957: Duplicate column name.

And, when I create table manually, then em.persist() fails with the following exception (schema name in the INSERT query is also strange...). [#|2006-02-25T19:40:03.937+0900|SEVERE|sun-appserver- pe9.0|blanche.kntv.cdms.control.EntityManagerWrapper|_ThreadID=17;_ThreadName=p: thread-pool-1; w: 8;_RequestID=4364cc7b-76ce-4699-a4c5-4b632bf3d8f3;|Datastore Exception : Exception [TOPLINK-4002] (Oracle TopLink Essentials - 10g release 4 (10.1.4.0.0) (Build 060124Dev)): oracle.toplink.essentials.exceptions.DatabaseException Internal Exception: java.sql.SQLException: ORA-00926: No VALUES keyword Error Code: 926 Call:INSERT INTO FOO.FOO.BAR (ID) VALUES (1) Query:InsertObjectQuery(blanche.kntv.cdms.model.Bar[id=1; episodeId=1; serialNumber=0; journalDate=Tue Feb 28 00:00:00 JST 2006; balance=null; factor=1; bookValuePercentRemaining=0.5; objectVersion=1])|#]

[glassfishrobot]

@glassfishrobot Commented bjb said: Dear all,

Any updates on this one ?

As I understand that build of Glassfish are assumed not to cover the complete scope of Java EE 5 features, is there anywhere a document that indicates what part of the spec are supported and what parts are not supported with a given build ? There must be come kind of completion tracking done by architect or manager, isn't it ?

This would realy help people not to fall into traps and use features that are still unsuported by Glassfish.

Rgs, JB

[glassfishrobot]

@glassfishrobot Commented pkrogh said: This is now fixed and checked in to CVS.

[glassfishrobot]

@glassfishrobot Commented bjb said: Unfortunatly this is not fixed.

The following simple query still not work on b42 with : final Person person = Person.class.cast(manager.createQuery("SELECT p FROM Person AS p WHERE p.userId = :userId") .setParameter("userId",userId) .getSingleResult());

And will generate : Tried with user id 0123131231 : java.lang.NullPointerException at oracle.toplink.essentials.internal.expressions.ObjectExpression.additionalExpressionCriteriaMap(ObjectExpression.java:99) at oracle.toplink.essentials.expressions.ExpressionBuilder.normalize(ExpressionBuilder.java:282) at oracle.toplink.essentials.internal.expressions.DataExpression.normalize(DataExpression.java:340) at oracle.toplink.essentials.internal.expressions.QueryKeyExpression.normalize(QueryKeyExpression.java:438) at oracle.toplink.essentials.internal.expressions.QueryKeyExpression.normalize(QueryKeyExpression.java:425) at oracle.toplink.essentials.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:169) at oracle.toplink.essentials.internal.expressions.RelationExpression.normalize(RelationExpression.java:360) at oracle.toplink.essentials.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1173) at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.buildReportQuerySelectStatement(ExpressionQueryMechanism.java:469) at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.buildReportQuerySelectStatement(ExpressionQueryMechanism.java:405) at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.prepareReportQuerySelectAllRows(ExpressionQueryMechanism.java:1279) at oracle.toplink.essentials.queryframework.ReportQuery.prepareSelectAllRows(ReportQuery.java:959) at oracle.toplink.essentials.queryframework.ReadAllQuery.prepare(ReadAllQuery.java:398) at oracle.toplink.essentials.queryframework.ReportQuery.prepare(ReportQuery.java:882) at oracle.toplink.essentials.queryframework.DatabaseQuery.checkPrepare(DatabaseQuery.java:387) at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:469) at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:587) at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:677) at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:731) at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2194) at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937) at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909) at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:342) at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:452) <...>

So I still have to workaound with this ugly code :

final Long identifier = Long.class.cast(manager.createQuery("SELECT p.identifier FROM Person AS p WHERE p.userId = :userId") .setParameter("userId",userId) .getSingleResult());

final Person person = Person.class.cast(manager.find(Person.class,identifier));

Regards, JB

[glassfishrobot]

@glassfishrobot Commented mb124283 said: The new exception looks like you are running now into the issue described in issue 477 (NullPointerException while querying Inheritance.JOINED): https://glassfish.dev.java.net/issues/show_bug.cgi?id=477

Tom added a note to issue 477 that this will be fixed with his next drop.

[glassfishrobot]

@glassfishrobot Commented bjb said: Tnx ! I added dependency on 477, so that it will be in sync.

[glassfishrobot]

@glassfishrobot Commented tware said: Duplicate bug fixed

• • • This issue has been marked as a duplicate of 477 ***

[glassfishrobot]

@glassfishrobot Commented Issue-Links: depends on GLASSFISH-477

[glassfishrobot]

@glassfishrobot Commented Was assigned to tware

[glassfishrobot]

@glassfishrobot Commented This issue was imported from java.net JIRA GLASSFISH-206

[glassfishrobot]

@glassfishrobot Commented Reported by bjb

[glassfishrobot]

@glassfishrobot Commented Marked as duplicate on Tuesday, April 4th 2006, 12:31:26 am