You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Andy Malakov <an...@transdecisions.com> on 2004/01/06 02:59:36 UTC
Another issue
Hello Armin at all,
I use Oracle 9i and OJB_BRANCH_1_0. What do you guys think about the following problem (I will describe it step-by-step):
ReportQueryRsIterator uses ResultSetMetaData to determine column types (cannot not use class registry as others).
Oracle maps INTEGER, INT, SMALLINT to NUMBER(38) datatype (JDBC: java.sql.Types.NUMBER)
ReportQueryRsIterator uses JdbcTypesHelper and maps java.sql.Types.NUMBER into JdbcTypesHelper.T_Numeric data handler.
JdbcTypesHelper.T_Numeric.readValueFromStatement() will produce java.lang.BigDecimal.
Problem is: java.lang.BigDecimal and java.lang.Integer have different hash code algothims (1.4.2_02)- hash code values will be different even for the same internal values.
Side effect: MtoNCollectionPrefetcher.associateBatched(Collection owners, Collection children, Collection mToNImplementors) fails because it tries to match Identity that uses java.lang.BigDecimal PKs with Identity that uses java.lang.Integer PKs.
User-visible result: NullPointerException during complex object retrieval in line: list.add(child);
RC5 and previous build that I got about two weeks ago didn't had this problem.
Thanks a lot,
Andy
Re: Another issue
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi andy,
i used the distinct in the prefetch query because the same child may be
referenced from many parents (see person-project testcases). when looking for
all person working for a list of projects i'll get some persons who are on
multiple projects. this isn't really a problem because in associateBatched
parents and children are put in hashtables.
the query to retrieve mn-implementors works perfectly without distinct.
i'll do further testings before i change the queries ;)
jakob
Jakob Braeuchi wrote:
> hi andy,
>
> sorry i forgot about the DISTINCT. i'll have a look at it tomorrow.
>
> jakob
>
> Andy Malakov wrote:
>
>> Hello Jacob,
>>
>> Thank you very much! I believe it will be helpful feature.
>>
>>
>> What about not-using DISTINCT (or making it optional) while selecting
>> link tables (MtoNCollectionPrefetcher)?
>>
>> protected Query buildPrefetchQuery(Collection ids)
>> {
>> ... return new
>> QueryByMtoNCriteria(cds.getItemClass(),
>> cds.getIndirectionTable(), crit,
>> true); // <=true causes "select DISTINCT ..." and problems
>> in Oracle
>> }
>>
>> If disable usage of distinct in this context all my tests seem to be
>> just fine.
>>
>> ===================================================================
>> Using SELECT DISTINCT in
>> MtoNCollectionPrefetcher.buildPrefetchQuery(Collection ids) may cause
>> problem in Oracle.
>>
>> Problem: If 'child' table in M:N association has columns of type LOB,
>> VARRAY, etc (in my case it is SDO geometry type MDSYS.SDO_GEOMETRY)
>> prefetch query will fail because Oracle does not allow using such
>> columns in SELECT DISTINCT queries (it cannot compare such types
>> easily). Here is what error message looks like:
>>
>> ORA-22901 cannot compare nested table or VARRAY or LOB attributes of
>> an object type
>> Cause: Comparison of nested table or VARRAY or LOB attributes of an
>> object type was attempted in the absence of a MAP or ORDER method.
>> Action: Define a MAP or ORDER method for the object type.
>>
>> Can you please explain why OJB uses DISTINCT select in association
>> prefetching? It seems that queries like
>>
>> SELECT [DISTINCT] CHILD_TABLE.* FROM CHILD_TABLE, LINK_TABLE
>> WHERE
>> LINK_TABLE.PARENT_FK = '12345' AND
>> CHILD_TABLE_PK = LINK_TABLE.CHILD_FK
>>
>> should not produce duplicate rows (unless there are duplicate records
>> in LINK_TABLE)
>> ===================================================================
>>
>> All the Best,
>> Andy
>>
>> ----- Original Message -----
>> From: "Jakob Braeuchi" <jbraeuchi@gmx.ch <ma...@gmx.ch>>
>> To: "OJB Developers List" <ojb-dev@db.apache.org
>> <ma...@db.apache.org>>
>> Cc: "Armin Waibel" <armin@code-au-lait.de <ma...@code-au-lait.de>>
>> Sent: Thursday, January 29, 2004 3:55 PM
>> Subject: Re: Another issue
>>
>> > hi andy,
>> >
>> > i just commited the patch to solve this problem.
>> > the types of the columns can now be defined in the report-query:
>> >
>> > Criteria crit = new Criteria();
>> > Collection results = new Vector();
>> > int types[] = new int[]{Types.DECIMAL, Types.VARCHAR, Types.BIGINT};
>> >
>> > crit.addLike("firstname", "%o%");
>> > ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class,
>> crit);
>> > q.setColumns(new String[] { "id", "firstname", "count(*)" });
>> > q.addGroupBy(new String[] { "id", "firstname" });
>> > q.setJdbcTypes(types);
>> >
>> > see QueryTest#testReportQueryWithJdbcTypes()
>> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Another issue
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi andy,
sorry i forgot about the DISTINCT. i'll have a look at it tomorrow.
jakob
Andy Malakov wrote:
> Hello Jacob,
>
> Thank you very much! I believe it will be helpful feature.
>
>
> What about not-using DISTINCT (or making it optional) while selecting
> link tables (MtoNCollectionPrefetcher)?
>
> protected Query buildPrefetchQuery(Collection ids)
> {
> ...
> return new QueryByMtoNCriteria(cds.getItemClass(),
> cds.getIndirectionTable(), crit,
> true); // <=true causes "select DISTINCT ..." and problems in
> Oracle
> }
>
> If disable usage of distinct in this context all my tests seem to be
> just fine.
>
> ===================================================================
> Using SELECT DISTINCT in
> MtoNCollectionPrefetcher.buildPrefetchQuery(Collection ids) may cause
> problem in Oracle.
>
> Problem: If 'child' table in M:N association has columns of type LOB,
> VARRAY, etc (in my case it is SDO geometry type MDSYS.SDO_GEOMETRY)
> prefetch query will fail because Oracle does not allow using such
> columns in SELECT DISTINCT queries (it cannot compare such types
> easily). Here is what error message looks like:
>
> ORA-22901 cannot compare nested table or VARRAY or LOB attributes of an
> object type
> Cause: Comparison of nested table or VARRAY or LOB attributes of an
> object type was attempted in the absence of a MAP or ORDER method.
> Action: Define a MAP or ORDER method for the object type.
>
> Can you please explain why OJB uses DISTINCT select in association
> prefetching? It seems that queries like
>
> SELECT [DISTINCT] CHILD_TABLE.* FROM CHILD_TABLE, LINK_TABLE
> WHERE
> LINK_TABLE.PARENT_FK = '12345' AND
> CHILD_TABLE_PK = LINK_TABLE.CHILD_FK
>
> should not produce duplicate rows (unless there are duplicate records in
> LINK_TABLE)
> ===================================================================
>
> All the Best,
> Andy
>
> ----- Original Message -----
> From: "Jakob Braeuchi" <jbraeuchi@gmx.ch <ma...@gmx.ch>>
> To: "OJB Developers List" <ojb-dev@db.apache.org
> <ma...@db.apache.org>>
> Cc: "Armin Waibel" <armin@code-au-lait.de <ma...@code-au-lait.de>>
> Sent: Thursday, January 29, 2004 3:55 PM
> Subject: Re: Another issue
>
> > hi andy,
> >
> > i just commited the patch to solve this problem.
> > the types of the columns can now be defined in the report-query:
> >
> > Criteria crit = new Criteria();
> > Collection results = new Vector();
> > int types[] = new int[]{Types.DECIMAL, Types.VARCHAR, Types.BIGINT};
> >
> > crit.addLike("firstname", "%o%");
> > ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class,
> crit);
> > q.setColumns(new String[] { "id", "firstname", "count(*)" });
> > q.addGroupBy(new String[] { "id", "firstname" });
> > q.setJdbcTypes(types);
> >
> > see QueryTest#testReportQueryWithJdbcTypes()
> >
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Another issue
Posted by Andy Malakov <an...@transdecisions.com>.
Hello Jacob,
Thank you very much! I believe it will be helpful feature.
What about not-using DISTINCT (or making it optional) while selecting link tables (MtoNCollectionPrefetcher)?
protected Query buildPrefetchQuery(Collection ids)
{
...
return new QueryByMtoNCriteria(cds.getItemClass(),
cds.getIndirectionTable(), crit,
true); // <=true causes "select DISTINCT ..." and problems in Oracle
}
If disable usage of distinct in this context all my tests seem to be just fine.
===================================================================
Using SELECT DISTINCT in MtoNCollectionPrefetcher.buildPrefetchQuery(Collection ids) may cause problem in Oracle.
Problem: If 'child' table in M:N association has columns of type LOB, VARRAY, etc (in my case it is SDO geometry type MDSYS.SDO_GEOMETRY) prefetch query will fail because Oracle does not allow using such columns in SELECT DISTINCT queries (it cannot compare such types easily). Here is what error message looks like:
ORA-22901 cannot compare nested table or VARRAY or LOB attributes of an object type
Cause: Comparison of nested table or VARRAY or LOB attributes of an object type was attempted in the absence of a MAP or ORDER method.
Action: Define a MAP or ORDER method for the object type.
Can you please explain why OJB uses DISTINCT select in association prefetching? It seems that queries like
SELECT [DISTINCT] CHILD_TABLE.* FROM CHILD_TABLE, LINK_TABLE
WHERE
LINK_TABLE.PARENT_FK = '12345' AND
CHILD_TABLE_PK = LINK_TABLE.CHILD_FK
should not produce duplicate rows (unless there are duplicate records in LINK_TABLE)
===================================================================
All the Best,
Andy
----- Original Message -----
From: "Jakob Braeuchi" <jb...@gmx.ch>
To: "OJB Developers List" <oj...@db.apache.org>
Cc: "Armin Waibel" <ar...@code-au-lait.de>
Sent: Thursday, January 29, 2004 3:55 PM
Subject: Re: Another issue
> hi andy,
>
> i just commited the patch to solve this problem.
> the types of the columns can now be defined in the report-query:
>
> Criteria crit = new Criteria();
> Collection results = new Vector();
> int types[] = new int[]{Types.DECIMAL, Types.VARCHAR, Types.BIGINT};
>
> crit.addLike("firstname", "%o%");
> ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class, crit);
> q.setColumns(new String[] { "id", "firstname", "count(*)" });
> q.addGroupBy(new String[] { "id", "firstname" });
> q.setJdbcTypes(types);
>
> see QueryTest#testReportQueryWithJdbcTypes()
>
Re: Another issue
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi andy,
i just commited the patch to solve this problem.
the types of the columns can now be defined in the report-query:
Criteria crit = new Criteria();
Collection results = new Vector();
int types[] = new int[]{Types.DECIMAL, Types.VARCHAR, Types.BIGINT};
crit.addLike("firstname", "%o%");
ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class, crit);
q.setColumns(new String[] { "id", "firstname", "count(*)" });
q.addGroupBy(new String[] { "id", "firstname" });
q.setJdbcTypes(types);
see QueryTest#testReportQueryWithJdbcTypes()
hth
jakob
Jakob Braeuchi wrote:
> hi andy,
>
> i was digging a little deeper and imo we need to convert the ownerPk and
> childPk to the types defined in the respective field-descriptors of the
> pk-fields:
>
>
> ...
> int ownerPkLen = getOwnerClassDescriptor().getPkFields().length;
> int childPkLen = getItemClassDescriptor().getPkFields().length;
> Object[] ownerPk = new Object[ownerPkLen];
> Object[] childPk = new Object[childPkLen];
>
> ...
> Object[] mToN = (Object[])it.next();
> System.arraycopy(mToN,0,ownerPk,0,ownerPkLen);
> System.arraycopy(mToN,ownerPkLen,childPk,0,childPkLen);
>
> //conversion needed here!
>
> Identity ownerId = new Identity(null, ownerTopLevelClass,
> ownerPk);
> Identity childId = new Identity(null, childTopLevelClass,
> childPk);
> ...
>
> the field-descriptor know their jdbcTypes. the only thing we now need is
> an easy way to convert between the types. the interface JdbcType already
> provides a method called sequenceKeyConversion. what about adding an
> additional conversion method ?
>
> jakob
>
> Jakob Braeuchi wrote:
>
>> hi andy,
>>
>> this problem did not show up in previous builds because there was no
>> prefetching of m:n-relationships ;)
>>
>> what about converting all numbers to Integer in
>> MtoNCollectionPrefetcher#associateBatched ?
>>
>> jakob
>>
>> Andy Malakov wrote:
>>
>>> Hello Armin at all,
>>>
>>> I use Oracle 9i and OJB_BRANCH_1_0. What do you guys think about the
>>> following problem (I will describe it step-by-step):
>>>
>>> ReportQueryRsIterator uses ResultSetMetaData to determine column
>>> types (cannot not use class registry as others).
>>>
>>> Oracle maps INTEGER, INT, SMALLINT to NUMBER(38) datatype (JDBC:
>>> java.sql.Types.NUMBER)
>>>
>>> ReportQueryRsIterator uses JdbcTypesHelper and maps
>>> java.sql.Types.NUMBER into JdbcTypesHelper.T_Numeric data handler.
>>>
>>> JdbcTypesHelper.T_Numeric.readValueFromStatement() will produce
>>> java.lang.BigDecimal.
>>>
>>> Problem is: java.lang.BigDecimal and java.lang.Integer have different
>>> hash code algothims (1.4.2_02)- hash code values will be different
>>> even for the same internal values.
>>>
>>> Side effect: MtoNCollectionPrefetcher.associateBatched(Collection
>>> owners, Collection children, Collection mToNImplementors) fails
>>> because it tries to match Identity that uses java.lang.BigDecimal PKs
>>> with Identity that uses java.lang.Integer PKs.
>>>
>>> User-visible result: NullPointerException during complex object
>>> retrieval in line: list.add(child);
>>>
>>> RC5 and previous build that I got about two weeks ago didn't had this
>>> problem.
>>>
>>> Thanks a lot,
>>> Andy
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Another issue
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi andy,
i was digging a little deeper and imo we need to convert the ownerPk and childPk
to the types defined in the respective field-descriptors of the pk-fields:
...
int ownerPkLen = getOwnerClassDescriptor().getPkFields().length;
int childPkLen = getItemClassDescriptor().getPkFields().length;
Object[] ownerPk = new Object[ownerPkLen];
Object[] childPk = new Object[childPkLen];
...
Object[] mToN = (Object[])it.next();
System.arraycopy(mToN,0,ownerPk,0,ownerPkLen);
System.arraycopy(mToN,ownerPkLen,childPk,0,childPkLen);
//conversion needed here!
Identity ownerId = new Identity(null, ownerTopLevelClass, ownerPk);
Identity childId = new Identity(null, childTopLevelClass, childPk);
...
the field-descriptor know their jdbcTypes. the only thing we now need is an easy
way to convert between the types. the interface JdbcType already provides a
method called sequenceKeyConversion. what about adding an additional conversion
method ?
jakob
Jakob Braeuchi wrote:
> hi andy,
>
> this problem did not show up in previous builds because there was no
> prefetching of m:n-relationships ;)
>
> what about converting all numbers to Integer in
> MtoNCollectionPrefetcher#associateBatched ?
>
> jakob
>
> Andy Malakov wrote:
>
>> Hello Armin at all,
>>
>> I use Oracle 9i and OJB_BRANCH_1_0. What do you guys think about the
>> following problem (I will describe it step-by-step):
>>
>> ReportQueryRsIterator uses ResultSetMetaData to determine column types
>> (cannot not use class registry as others).
>>
>> Oracle maps INTEGER, INT, SMALLINT to NUMBER(38) datatype (JDBC:
>> java.sql.Types.NUMBER)
>>
>> ReportQueryRsIterator uses JdbcTypesHelper and maps
>> java.sql.Types.NUMBER into JdbcTypesHelper.T_Numeric data handler.
>>
>> JdbcTypesHelper.T_Numeric.readValueFromStatement() will produce
>> java.lang.BigDecimal.
>>
>> Problem is: java.lang.BigDecimal and java.lang.Integer have different
>> hash code algothims (1.4.2_02)- hash code values will be different
>> even for the same internal values.
>>
>> Side effect: MtoNCollectionPrefetcher.associateBatched(Collection
>> owners, Collection children, Collection mToNImplementors) fails
>> because it tries to match Identity that uses java.lang.BigDecimal PKs
>> with Identity that uses java.lang.Integer PKs.
>>
>> User-visible result: NullPointerException during complex object
>> retrieval in line: list.add(child);
>>
>> RC5 and previous build that I got about two weeks ago didn't had this
>> problem.
>>
>> Thanks a lot,
>> Andy
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Another issue
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi andy,
this problem did not show up in previous builds because there was no prefetching
of m:n-relationships ;)
what about converting all numbers to Integer in
MtoNCollectionPrefetcher#associateBatched ?
jakob
Andy Malakov wrote:
> Hello Armin at all,
>
> I use Oracle 9i and OJB_BRANCH_1_0. What do you guys think about the following problem (I will describe it step-by-step):
>
> ReportQueryRsIterator uses ResultSetMetaData to determine column types (cannot not use class registry as others).
>
> Oracle maps INTEGER, INT, SMALLINT to NUMBER(38) datatype (JDBC: java.sql.Types.NUMBER)
>
> ReportQueryRsIterator uses JdbcTypesHelper and maps java.sql.Types.NUMBER into JdbcTypesHelper.T_Numeric data handler.
>
> JdbcTypesHelper.T_Numeric.readValueFromStatement() will produce java.lang.BigDecimal.
>
> Problem is: java.lang.BigDecimal and java.lang.Integer have different hash code algothims (1.4.2_02)- hash code values will be different even for the same internal values.
>
> Side effect: MtoNCollectionPrefetcher.associateBatched(Collection owners, Collection children, Collection mToNImplementors) fails because it tries to match Identity that uses java.lang.BigDecimal PKs with Identity that uses java.lang.Integer PKs.
>
> User-visible result: NullPointerException during complex object retrieval in line: list.add(child);
>
> RC5 and previous build that I got about two weeks ago didn't had this problem.
>
> Thanks a lot,
> Andy
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org