RE: Open JPA generates incorrect Union All for Date and Boolean Datatype
Well thanks for your opinion .. Id is the primary key which is a String not Long or int (Obviously with millions of records , i wouldnt want to have primary key within boundary of long or int ) and all tables of my application has the same kind of id column ...Isnt it better and more clear than having long for one , int for another and string for rest ? So far I havent come across any such application .. What is the case when we would want such a scenerio ? But I still donot get the idea that Entity should not be part of inheritance hierarchy ! Infact with 3.0 specification , entity should behave like any other object model and support all kind of OOPS fundamentals ! Such cases include many-to-many linking tables and subordinate tables with a foreign key reference back to their parent table. With my limited knowledge of associations .. I didnt get the issue clearly here .. What will be the issue with mapping table/ref column since all primary keys are of same kind .. ? What could go wrong ghastly ..? Could you elaborte here ? -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4426171.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
Hi Ravi,
Could you summarize the situation in a reply? I understand that the default
value for SubclassFetchMode is incorrectly documented (or implemented,
depending on your perspective). But, is the kodo wrapper affecting how
OpenJPA generates the SQL for this situation? That's the part I'm confused
on. Thanks.
Kevink
On Fri, Dec 11, 2009 at 1:15 AM, Ravi Palacherla ravi.palache...@oracle.com
wrote:
Hi Himadri,
Good to know that it works.
I think kodo uses UNIONs with subclassFetchMode as join.
So, somehow the kodo configuration (which internally again uses openJPA) is
being picked up and hence you are seeing UNIONs in the sql statements.
I will see if there is a way to use only openJPA without kodo coming into
the picture at all on weblogic.
Regards,
Ravi.
-Original Message-
From: Himadri [mailto:hima_...@yahoo.co.in]
Sent: Thursday, December 10, 2009 11:11 PM
To: users@openjpa.apache.org
Subject: Re: Open JPA generates incorrect Union All for Date and Boolean
Datatype
Hi Ravi ,
Thanks ! It worked with none. I need to run all the test cases to see this
is not breaking others.
Yes , the default behaviour is JOIN not parallel , but User Guide mentions
parallel as default. We dont want JOIN. I have put it as none for now.
All Entities extends EntityBase which has common attribute like id and
version. Do you think this is not a good way to implement ?
Ravi P Palacherla wrote:
Hi Himadri,
Here are answers for some of your questions:
Why is it trying to get union all even for em.find(... ) ?
In your application all the entities ( E.g: AddOn.java, Typed.java) are
all subclasses of EntityBase.
If you use either of the following property:
property name=openjpa.jdbc.SubclassFetchMode value=parallel/
or
property name=openjpa.jdbc.SubclassFetchMode value=none/
then you will not see the error.
If you use parallel, then seperate select statements are used to eager
fetch data for subclasses
and if you use none then I guess it will no try to fetch the
subclasses.
so, you can use any of the above properties to fix your issue.
The third type of SubclassFetchMode is join and even though the
documentation claims that parallel is default mode
http://openjpa.apache.org/builds/1.1.0/apache-openjpa-1.1.0/docs/manual/manual.html#openjpa.jdbc.SubclassFetchMode
The default looks like join mode.
So if using any of the above properties is acceptable to you then please
use it.
If you must use JOIN then please let me know and I will spend more time
to find the cause of the issue.
Regards,
Ravi.
Himadri wrote:
So sorry .. attached the wrong one .. Please see the correct one here.If
I run the junit , I get this exception javax.ejb.EJBException: EJB
Exception: ; nested exception is:
openjpa-1.1.0-r422266:657916 nonfatal general error
org.apache.openjpa.persistence.PersistenceException: [BEA][Oracle JDBC
Driver][Oracle]ORA-01790: expression must have same datatype as
corresponding expression
{prepstmnt 2625 SELECT 0, t0.ID, t0.version, t0.master_Ref, t0.NAME_F,
'', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID
= ? UNION ALL SELECT 1, t0.ID, t0.version, t0.addOn_Ref, t0.NAME_F, '',
0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM Master_T t0 WHERE t0.ID =
?
UNION ALL SELECT 2, t0.ID, t0.version, t0.NAME_F, t0.primaryMaster_Ref,
t0.secondaryMaster_Ref, 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM
Slave_T t0 WHERE t0.ID = ? UNION ALL SELECT 3, t0.ID, t0.version,
t0.addOn_Ref, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0
FROM SubAddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 4, t0.ID,
t0.version,
t0.NAME_F, t0.slave_Ref, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0
FROM SubSlave_T t0 WHERE t0.ID = ? UNION ALL SELECT 5, t0.ID,
t0.version,
'', '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F,
t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE
t0.ID
= ? [params=(String) 34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A]} [code=1790, state=HY000]
at
weblogic.ejb.container.internal.RemoteBusinessIntfProxy.unwrapRemoteException(RemoteBusinessIntfProxy.java:105)
at
weblogic.ejb.container.internal.RemoteBusinessIntfProxy.invoke(RemoteBusinessIntfProxy.java:87)
at $Proxy0.testType(Unknown Source)
at com.tieto.magic.poc.test.TestSample.testP(TestSample.java:54)
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:597
RE: Open JPA generates incorrect Union All for Date and Boolean Datatype
- Quoting Message from Himadri - All Entities extends EntityBase which has common attribute like id and version. Do you think this is not a good way to implement ? This could be a terrible, ghastly way to implement entities. It forces every table to have the same kind of id column. I assume you defined id as a long (or int or corresponding wrapper class) mapping to an autogenerated sequence surrogate key. Leaving aside the debate whether such a surrogate key is ever a good idea, there certainly are cases where it's a horridly bad idea. Such cases include many-to-many linking tables and subordinate tables with a foreign key reference back to their parent table. Unless I'm mapping to tables that inherit from other tables, I prefer not to have entities inherit from parent entity classes. There just isn't enough of an is-a relationship in the application object model to justify the practice. Entity classes are supposed to form an object model, not a data model. Creating inheritance hierarchies to reflect a purported database design is backwards thinking. It can even lead to a change in the data model to match the mapping model, an egregious mistake. I've seen this, where the data-interaction classes assumed a 'Long' id key field and the middleware team forced the data modelers to add such a column where the db design didn't call for it. I recommend to keep entity classes POJO, to abandon falsely-economical inheritance notions, and to design objects to support the object model, not the data model. -- Lew Bloch
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
going into details of the fix, Please attach a replicable testcase. That way I can see if the same fix applies for your scenario or not. It would be great if you are able to replicate the issue with openJPA configuration itself; we can work together on resolving the issue in openJPA itself. Also there were some workarounds of setting SubclassFetchMode=none;which I would not completely recommend with out understanding your complete environment. Regards, Ravi. himadri wrote: Hi, This is strange. The problem comes when I add Child Associations to the entity. If I remove Child Asscoiations , all work fine. I have attached my Sample here. Please see JPG for Entity Associations. SampleEJB.ear is the deployable ear on WL10.3. Let me know if you find out anything .. for now I have replace all em.find with em.createQuery(Select ...) , But will like to know the real reason behind it ! Lemme know if I can provide more simplified sample Thanks, Himadri From: Kevin Sutter [via OpenJPA] ml-node+4103283-1191879...@n2.nabble.com To: himadri hima_...@yahoo.co.in Sent: Thu, 3 December, 2009 5:39:17 AM Subject: Re: Open JPA generates incorrect Union All for Date and Boolean Datatype This sounds very strange. From your description, I have no idea why Date and Boolean attributes are having problems with Oracle. And, why a Union All is being generated for a simple find operation baffles me as well. Unless there is some inheritance or eagerly fetched relationships involved. Still, I thought OpenJPA used Joins over Unions. Can you simplify the problem to a simple testcase that could be provided? Thanks, Kevin On Tue, Dec 1, 2009 at 7:15 AM, himadri [hidden email] wrote: Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com. View message @ http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4103283.html To unsubscribe from Open JPA generates incorrect Union All for Date and Boolean Datatype, click here. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4148425.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
Hi Himadri, I am able to replicate the issue even after adding providerorg.apache.openjpa.persistence.PersistenceProviderImpl/provider I will look into it further and will give you an update if I find anything useful. Regards, Ravi. Himadri wrote: Hi, This is strange. The problem comes when I add Child Associations to the entity. If I remove Child Asscoiations , all work fine. I have attached my Sample here. Please see JPG for Entity Associations. SampleEJB.ear is the deployable ear on WL10.3. Let me know if you find out anything .. for now I have replace all em.find with em.createQuery(Select ...) , But will like to know the real reason behind it ! Lemme know if I can provide more simplified sample Thanks, Himadri From: Kevin Sutter [via OpenJPA] ml-node+4103283-1191879...@n2.nabble.com To: himadri hima_...@yahoo.co.in Sent: Thu, 3 December, 2009 5:39:17 AM Subject: Re: Open JPA generates incorrect Union All for Date and Boolean Datatype This sounds very strange. From your description, I have no idea why Date and Boolean attributes are having problems with Oracle. And, why a Union All is being generated for a simple find operation baffles me as well. Unless there is some inheritance or eagerly fetched relationships involved. Still, I thought OpenJPA used Joins over Unions. Can you simplify the problem to a simple testcase that could be provided? Thanks, Kevin On Tue, Dec 1, 2009 at 7:15 AM, himadri [hidden email] wrote: Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com. View message @ http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4103283.html To unsubscribe from Open JPA generates incorrect Union All for Date and Boolean Datatype, click here. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4149331.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
) at weblogic.rmi.internal.BasicServerRef.postInvoke(BasicServerRef.java:650) at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:492) ... 3 more http://n2.nabble.com/file/n4138239/SampleEJBEar.ear SampleEJBEar.ear Ravi P Palacherla wrote: Looks like the SampleEJB.ear in the attachment is not a correct ear. I am unable to deploy it on Weblogic server. Also the configuration files (like persistence.xml) and entities are missing in the attachment without which I may not be able to tell what configuration you are using. Are you using openJPA configurations or kodo configurations ? There was a known issue with a CLOB data type when kodo configuration is used. The fix for this is in kodo files, it overrides openJPA's classes in order to fix it. Before going into details of the fix, Please attach a replicable testcase. That way I can see if the same fix applies for your scenario or not. It would be great if you are able to replicate the issue with openJPA configuration itself; we can work together on resolving the issue in openJPA itself. Also there were some workarounds of setting SubclassFetchMode=none;which I would not completely recommend with out understanding your complete environment. Regards, Ravi. himadri wrote: Hi, This is strange. The problem comes when I add Child Associations to the entity. If I remove Child Asscoiations , all work fine. I have attached my Sample here. Please see JPG for Entity Associations. SampleEJB.ear is the deployable ear on WL10.3. Let me know if you find out anything .. for now I have replace all em.find with em.createQuery(Select ...) , But will like to know the real reason behind it ! Lemme know if I can provide more simplified sample Thanks, Himadri From: Kevin Sutter [via OpenJPA] ml-node+4103283-1191879...@n2.nabble.com To: himadri hima_...@yahoo.co.in Sent: Thu, 3 December, 2009 5:39:17 AM Subject: Re: Open JPA generates incorrect Union All for Date and Boolean Datatype This sounds very strange. From your description, I have no idea why Date and Boolean attributes are having problems with Oracle. And, why a Union All is being generated for a simple find operation baffles me as well. Unless there is some inheritance or eagerly fetched relationships involved. Still, I thought OpenJPA used Joins over Unions. Can you simplify the problem to a simple testcase that could be provided? Thanks, Kevin On Tue, Dec 1, 2009 at 7:15 AM, himadri [hidden email] wrote: Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com. View message @ http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4103283.html To unsubscribe from Open JPA generates incorrect Union All for Date and Boolean Datatype, click here. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4149434.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
:1150) at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:326) at weblogic.rjvm.MsgAbbrevOutputStream.writeObject(MsgAbbrevOutputStream.java:618) at weblogic.rjvm.MsgAbbrevOutputStream.sendThrowable(MsgAbbrevOutputStream.java:471) at weblogic.rmi.internal.BasicServerRef.handleThrowable(BasicServerRef.java:694) at weblogic.rmi.internal.BasicServerRef.postInvoke(BasicServerRef.java:650) at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:492) ... 3 more http://n2.nabble.com/file/n4138239/SampleEJBEar.ear SampleEJBEar.ear Ravi P Palacherla wrote: Looks like the SampleEJB.ear in the attachment is not a correct ear. I am unable to deploy it on Weblogic server. Also the configuration files (like persistence.xml) and entities are missing in the attachment without which I may not be able to tell what configuration you are using. Are you using openJPA configurations or kodo configurations ? There was a known issue with a CLOB data type when kodo configuration is used. The fix for this is in kodo files, it overrides openJPA's classes in order to fix it. Before going into details of the fix, Please attach a replicable testcase. That way I can see if the same fix applies for your scenario or not. It would be great if you are able to replicate the issue with openJPA configuration itself; we can work together on resolving the issue in openJPA itself. Also there were some workarounds of setting SubclassFetchMode=none;which I would not completely recommend with out understanding your complete environment. Regards, Ravi. himadri wrote: Hi, This is strange. The problem comes when I add Child Associations to the entity. If I remove Child Asscoiations , all work fine. I have attached my Sample here. Please see JPG for Entity Associations. SampleEJB.ear is the deployable ear on WL10.3. Let me know if you find out anything .. for now I have replace all em.find with em.createQuery(Select ...) , But will like to know the real reason behind it ! Lemme know if I can provide more simplified sample Thanks, Himadri From: Kevin Sutter [via OpenJPA] ml-node+4103283-1191879...@n2.nabble.com To: himadri hima_...@yahoo.co.in Sent: Thu, 3 December, 2009 5:39:17 AM Subject: Re: Open JPA generates incorrect Union All for Date and Boolean Datatype This sounds very strange. From your description, I have no idea why Date and Boolean attributes are having problems with Oracle. And, why a Union All is being generated for a simple find operation baffles me as well. Unless there is some inheritance or eagerly fetched relationships involved. Still, I thought OpenJPA used Joins over Unions. Can you simplify the problem to a simple testcase that could be provided? Thanks, Kevin On Tue, Dec 1, 2009 at 7:15 AM, himadri [hidden email] wrote: Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com. View message @ http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4103283.html To unsubscribe from Open JPA generates incorrect Union All for Date and Boolean Datatype, click here. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- View this message in context: http://n2
RE: Open JPA generates incorrect Union All for Date and Boolean Datatype
Hi Himadri,
Good to know that it works.
I think kodo uses UNIONs with subclassFetchMode as join.
So, somehow the kodo configuration (which internally again uses openJPA) is
being picked up and hence you are seeing UNIONs in the sql statements.
I will see if there is a way to use only openJPA without kodo coming into the
picture at all on weblogic.
Regards,
Ravi.
-Original Message-
From: Himadri [mailto:hima_...@yahoo.co.in]
Sent: Thursday, December 10, 2009 11:11 PM
To: users@openjpa.apache.org
Subject: Re: Open JPA generates incorrect Union All for Date and Boolean
Datatype
Hi Ravi ,
Thanks ! It worked with none. I need to run all the test cases to see this
is not breaking others.
Yes , the default behaviour is JOIN not parallel , but User Guide mentions
parallel as default. We dont want JOIN. I have put it as none for now.
All Entities extends EntityBase which has common attribute like id and
version. Do you think this is not a good way to implement ?
Ravi P Palacherla wrote:
Hi Himadri,
Here are answers for some of your questions:
Why is it trying to get union all even for em.find(... ) ?
In your application all the entities ( E.g: AddOn.java, Typed.java) are
all subclasses of EntityBase.
If you use either of the following property:
property name=openjpa.jdbc.SubclassFetchMode value=parallel/
or
property name=openjpa.jdbc.SubclassFetchMode value=none/
then you will not see the error.
If you use parallel, then seperate select statements are used to eager
fetch data for subclasses
and if you use none then I guess it will no try to fetch the subclasses.
so, you can use any of the above properties to fix your issue.
The third type of SubclassFetchMode is join and even though the
documentation claims that parallel is default mode
http://openjpa.apache.org/builds/1.1.0/apache-openjpa-1.1.0/docs/manual/manual.html#openjpa.jdbc.SubclassFetchMode
The default looks like join mode.
So if using any of the above properties is acceptable to you then please
use it.
If you must use JOIN then please let me know and I will spend more time
to find the cause of the issue.
Regards,
Ravi.
Himadri wrote:
So sorry .. attached the wrong one .. Please see the correct one here.If
I run the junit , I get this exception javax.ejb.EJBException: EJB
Exception: ; nested exception is:
openjpa-1.1.0-r422266:657916 nonfatal general error
org.apache.openjpa.persistence.PersistenceException: [BEA][Oracle JDBC
Driver][Oracle]ORA-01790: expression must have same datatype as
corresponding expression
{prepstmnt 2625 SELECT 0, t0.ID, t0.version, t0.master_Ref, t0.NAME_F,
'', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID
= ? UNION ALL SELECT 1, t0.ID, t0.version, t0.addOn_Ref, t0.NAME_F, '',
0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM Master_T t0 WHERE t0.ID = ?
UNION ALL SELECT 2, t0.ID, t0.version, t0.NAME_F, t0.primaryMaster_Ref,
t0.secondaryMaster_Ref, 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM
Slave_T t0 WHERE t0.ID = ? UNION ALL SELECT 3, t0.ID, t0.version,
t0.addOn_Ref, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0
FROM SubAddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 4, t0.ID, t0.version,
t0.NAME_F, t0.slave_Ref, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0
FROM SubSlave_T t0 WHERE t0.ID = ? UNION ALL SELECT 5, t0.ID, t0.version,
'', '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F,
t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID
= ? [params=(String) 34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A, (String)
34B85860E33211DE984DF2390A53043A]} [code=1790, state=HY000]
at
weblogic.ejb.container.internal.RemoteBusinessIntfProxy.unwrapRemoteException(RemoteBusinessIntfProxy.java:105)
at
weblogic.ejb.container.internal.RemoteBusinessIntfProxy.invoke(RemoteBusinessIntfProxy.java:87)
at $Proxy0.testType(Unknown Source)
at com.tieto.magic.poc.test.TestSample.testP(TestSample.java:54)
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:597)
at junit.framework.TestCase.runTest(TestCase.java:168)
at junit.framework.TestCase.runBare(TestCase.java:134)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
SubclassFetchMode=none;which I would not completely recommend with out understanding your complete environment. Regards, Ravi. himadri wrote: Hi, This is strange. The problem comes when I add Child Associations to the entity. If I remove Child Asscoiations , all work fine. I have attached my Sample here. Please see JPG for Entity Associations. SampleEJB.ear is the deployable ear on WL10.3. Let me know if you find out anything .. for now I have replace all em.find with em.createQuery(Select ...) , But will like to know the real reason behind it ! Lemme know if I can provide more simplified sample Thanks, Himadri From: Kevin Sutter [via OpenJPA] ml-node+4103283-1191879...@n2.nabble.com To: himadri hima_...@yahoo.co.in Sent: Thu, 3 December, 2009 5:39:17 AM Subject: Re: Open JPA generates incorrect Union All for Date and Boolean Datatype This sounds very strange. From your description, I have no idea why Date and Boolean attributes are having problems with Oracle. And, why a Union All is being generated for a simple find operation baffles me as well. Unless there is some inheritance or eagerly fetched relationships involved. Still, I thought OpenJPA used Joins over Unions. Can you simplify the problem to a simple testcase that could be provided? Thanks, Kevin On Tue, Dec 1, 2009 at 7:15 AM, himadri [hidden email] wrote: Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com. View message @ http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4103283.html To unsubscribe from Open JPA generates incorrect Union All for Date and Boolean Datatype, click here. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4138239.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
Hi, This is strange. The problem comes when I add Child Associations to the entity. If I remove Child Asscoiations , all work fine. I have attached my Sample here. Please see JPG for Entity Associations. SampleEJB.ear is the deployable ear on WL10.3. Let me know if you find out anything .. for now I have replace all em.find with em.createQuery(Select ...) , But will like to know the real reason behind it ! Lemme know if I can provide more simplified sample Thanks, Himadri From: Kevin Sutter [via OpenJPA] ml-node+4103283-1191879...@n2.nabble.com To: himadri hima_...@yahoo.co.in Sent: Thu, 3 December, 2009 5:39:17 AM Subject: Re: Open JPA generates incorrect Union All for Date and Boolean Datatype This sounds very strange. From your description, I have no idea why Date and Boolean attributes are having problems with Oracle. And, why a Union All is being generated for a simple find operation baffles me as well. Unless there is some inheritance or eagerly fetched relationships involved. Still, I thought OpenJPA used Joins over Unions. Can you simplify the problem to a simple testcase that could be provided? Thanks, Kevin On Tue, Dec 1, 2009 at 7:15 AM, himadri [hidden email] wrote: Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com. View message @ http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4103283.html To unsubscribe from Open JPA generates incorrect Union All for Date and Boolean Datatype, click here. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4126266.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
Looks like the SampleEJB.ear in the attachment is not a correct ear. I am unable to deploy it on Weblogic server. Also the configuration files (like persistence.xml) and entities are missing in the attachment without which I may not be able to tell what configuration you are using. Are you using openJPA configurations or kodo configurations ? There was a known issue with a CLOB data type when kodo configuration is used. The fix for this is in kodo files, it overrides openJPA's classes in order to fix it. Before going into details of the fix, Please attach a replicable testcase. That way I can see if the same fix applies for your scenario or not. It would be great if you are able to replicate the issue with openJPA configuration itself; we can work together on resolving the issue in openJPA itself. Also there were some workarounds of setting SubclassFetchMode=none;which I would not completely recommend with out understanding your complete environment. Regards, Ravi. himadri wrote: Hi, This is strange. The problem comes when I add Child Associations to the entity. If I remove Child Asscoiations , all work fine. I have attached my Sample here. Please see JPG for Entity Associations. SampleEJB.ear is the deployable ear on WL10.3. Let me know if you find out anything .. for now I have replace all em.find with em.createQuery(Select ...) , But will like to know the real reason behind it ! Lemme know if I can provide more simplified sample Thanks, Himadri From: Kevin Sutter [via OpenJPA] ml-node+4103283-1191879...@n2.nabble.com To: himadri hima_...@yahoo.co.in Sent: Thu, 3 December, 2009 5:39:17 AM Subject: Re: Open JPA generates incorrect Union All for Date and Boolean Datatype This sounds very strange. From your description, I have no idea why Date and Boolean attributes are having problems with Oracle. And, why a Union All is being generated for a simple find operation baffles me as well. Unless there is some inheritance or eagerly fetched relationships involved. Still, I thought OpenJPA used Joins over Unions. Can you simplify the problem to a simple testcase that could be provided? Thanks, Kevin On Tue, Dec 1, 2009 at 7:15 AM, himadri [hidden email] wrote: Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com. View message @ http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4103283.html To unsubscribe from Open JPA generates incorrect Union All for Date and Boolean Datatype, click here. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4130072.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Open JPA generates incorrect Union All for Date and Boolean Datatype
This sounds very strange. From your description, I have no idea why Date and Boolean attributes are having problems with Oracle. And, why a Union All is being generated for a simple find operation baffles me as well. Unless there is some inheritance or eagerly fetched relationships involved. Still, I thought OpenJPA used Joins over Unions. Can you simplify the problem to a simple testcase that could be provided? Thanks, Kevin On Tue, Dec 1, 2009 at 7:15 AM, himadri hima_...@yahoo.co.in wrote: Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Open JPA generates incorrect Union All for Date and Boolean Datatype
Hi, I have an EJB3.0 application deployed on Weblogic 10.3 and Oracle 10g DB. I have an entity Typed which has Date and Boolean datatypes. em.persist is sucessful. But whereever there is em.find() on any entity in this application , I have observed that open JPA tries to get Union of all entities. This may be because its first time , But it fails with exception [BEA][Oracle JDBC Driver][Oracle]ORA-01790:expression must have same datatype as corresponding expression The SQL generated is union all of all entities in the application . For boolean FLAG_F , MOMENT_F wrong sql is generated. It should have to_date function for date. I have used DATE and boolean in Entity class. SELECT 0, t0.ID, t0.version, t0.NAME_F, '', 0, NULL, '', 0, 0, '1970-01-01', '', 0.0 FROM AddOn_T t0 WHERE t0.ID = ? UNION ALL SELECT 1, t0.ID, t0.version, '', '', t0.COUNTER_F, t0.DATA_F, t0.ENUMED1_F, t0.ENUMED2_F, t0.FLAG_F, t0.MOMENT_F, t0.NAME_F, t0.VALUE_F FROM TYPED_T t0 WHERE t0.ID = ? [params=(String) 30, (String) 30]} [code=1790, state=HY000] I guess openJPA fails to add to_date function for DATE and surrounding chars for boolean. Is this a bug or some settings available. Why is it trying to get union all even for em.find(... ) ? Thanks in advance.. - H -- View this message in context: http://n2.nabble.com/Open-JPA-generates-incorrect-Union-All-for-Date-and-Boolean-Datatype-tp4093559p4093559.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
