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 "Quinet Jérémie (JIRA)" <ji...@apache.org> on 2006/01/26 15:59:09 UTC
[jira] Created: (OJB-92) Unneeded query with Composite FK which has
null column(s), which also lead to not respecting constraint with some db2
drivers.
Unneeded query with Composite FK which has null column(s), which also lead to not respecting constraint with some db2 drivers.
------------------------------------------------------------------------------------------------------------------------------
Key: OJB-92
URL: http://issues.apache.org/jira/browse/OJB-92
Project: OJB
Type: Bug
Components: PB-API, RDBMS platform-specific support
Versions: 1.0.x CVS, 1.0.3, 1.0.4
Reporter: Quinet Jérémie
Priority: Minor
Hello,
i have a problem using composite FK, which may contain null column(s), here is a simple description :
table1
brand varchar(10) not null
code varchar(1) not null
the PK is composite : brand,code
table2
brand varchar(10) not null
country varchar(2) not null
code varchar(1)
the PK is composite : brand,country
and there is a FK (brand,code) pointing to table1
On db2 (and i think it's not the only rdbms) when you have a composite FK, the FK is considered 'null' (and integrity is ok) when at least one of the column is null, and so per example we can find in table2 a row with values : brand='brand1' country='fr' and a null code.
We have this kind of mapping :
<class-descriptor
class="test.bean.Object1"
table="TABLE1"
>
<field-descriptor
name="brand"
column="BRAND"
jdbc-type="VARCHAR"
length="10"
primarykey="true"
/>
<field-descriptor
name="code"
column="CODE"
jdbc-type="CHAR"
length="1"
primarykey="true"
/>
</class-descriptor>
<class-descriptor
class="test.bean.Object2"
table="TABLE2"
>
<field-descriptor
name="brand"
column="BRAND"
jdbc-type="VARCHAR"
length="10"
primarykey="true"
/>
<field-descriptor
name="country"
column="COUNTRY"
jdbc-type="VARCHAR"
length="2"
primarykey="true"
/>
<field-descriptor
name="code"
column="CODE"
jdbc-type="CHAR"
length="1"
/>
<reference-descriptor name="referenceToObject1" class-ref="test.bean.Object1">
<foreignkey field-ref="brand"/>
<foreignkey field-ref="code"/>
</reference-descriptor>
</class-descriptor>
When retrieving an instance of object2 using a QueryByIdentity (or QueryByCriteria) with brand='brand1' and country='fr' the generated SQL is like that :
SELECT BRAND,COUNTRY,CODE FROM TABLE2 WHERE BRAND = ? AND COUNTRY = ?
which point to the row : brand='brand1' country='fr' and a null code
then OJB try to retrieve the referenceToObject1 and generate this query :
SELECT BRAND,CODE FROM TABLE1 WHERE BRAND = ? AND CODE = ?
which is not needed as the FK for the row retrieved in table2 (brand,code) is 'null', on the PreparedStatement related to this query it tries a setNull on the second parameter but the problem for me is that the Universal DB2 Driver we use on Z/OS check constraints when executing setNull and throw an exception explaining the parameter can't be null.
i have quickly look at the source code use to retrieve the references, and see on the QueryReferenceBroker class that the getReferencedObjectIdentity method was used to get the Identity of the reference object and this method use the hasNullifiedFK method to check if the FK is null, couldn't it be possible to move the hasNullifiedFK to the Platform class or a mechanism to extend it ? (at this time i have made an extension of PersistenceBrokerImpl a use a new QueryReferenceBroker class).
i don't think the default behavior which is to tag a composite FK as null only if all the columns are null is correct for all the rdbms (at least for db2).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
[jira] Resolved: (OJB-92) Unneeded query with Composite FK which
has null column(s), which also lead to not respecting constraint with some
db2 drivers.
Posted by "Armin Waibel (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OJB-92?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Armin Waibel resolved OJB-92.
-----------------------------
Resolution: Fixed
Fix Version/s: 1.0.5
Hi,
I agree with you, if at least one FK value (of a compounded FK) is null the reference-query shouldn't be performed - fixed it in QueryReferenceBroker#hasNullifiedFK(...) method.
Additionally in OJB 1.0.5 the "null check of values" is now pluggable (see OJB-105).
regards,
Armin
> Unneeded query with Composite FK which has null column(s), which also lead to not respecting constraint with some db2 drivers.
> ------------------------------------------------------------------------------------------------------------------------------
>
> Key: OJB-92
> URL: https://issues.apache.org/jira/browse/OJB-92
> Project: OJB
> Issue Type: Bug
> Components: PB-API, RDBMS platform-specific support
> Affects Versions: 1.0.3, 1.0.4, 1.0.x CVS
> Reporter: Quinet Jérémie
> Priority: Minor
> Fix For: 1.0.5
>
>
> Hello,
> i have a problem using composite FK, which may contain null column(s), here is a simple description :
> table1
> brand varchar(10) not null
> code varchar(1) not null
> the PK is composite : brand,code
> table2
> brand varchar(10) not null
> country varchar(2) not null
> code varchar(1)
> the PK is composite : brand,country
> and there is a FK (brand,code) pointing to table1
> On db2 (and i think it's not the only rdbms) when you have a composite FK, the FK is considered 'null' (and integrity is ok) when at least one of the column is null, and so per example we can find in table2 a row with values : brand='brand1' country='fr' and a null code.
> We have this kind of mapping :
> <class-descriptor
> class="test.bean.Object1"
> table="TABLE1"
> >
> <field-descriptor
> name="brand"
> column="BRAND"
> jdbc-type="VARCHAR"
> length="10"
> primarykey="true"
> />
> <field-descriptor
> name="code"
> column="CODE"
> jdbc-type="CHAR"
> length="1"
> primarykey="true"
> />
> </class-descriptor>
> <class-descriptor
> class="test.bean.Object2"
> table="TABLE2"
> >
> <field-descriptor
> name="brand"
> column="BRAND"
> jdbc-type="VARCHAR"
> length="10"
> primarykey="true"
> />
> <field-descriptor
> name="country"
> column="COUNTRY"
> jdbc-type="VARCHAR"
> length="2"
> primarykey="true"
> />
> <field-descriptor
> name="code"
> column="CODE"
> jdbc-type="CHAR"
> length="1"
> />
> <reference-descriptor name="referenceToObject1" class-ref="test.bean.Object1">
> <foreignkey field-ref="brand"/>
> <foreignkey field-ref="code"/>
> </reference-descriptor>
> </class-descriptor>
> When retrieving an instance of object2 using a QueryByIdentity (or QueryByCriteria) with brand='brand1' and country='fr' the generated SQL is like that :
> SELECT BRAND,COUNTRY,CODE FROM TABLE2 WHERE BRAND = ? AND COUNTRY = ?
> which point to the row : brand='brand1' country='fr' and a null code
> then OJB try to retrieve the referenceToObject1 and generate this query :
> SELECT BRAND,CODE FROM TABLE1 WHERE BRAND = ? AND CODE = ?
> which is not needed as the FK for the row retrieved in table2 (brand,code) is 'null', on the PreparedStatement related to this query it tries a setNull on the second parameter but the problem for me is that the Universal DB2 Driver we use on Z/OS check constraints when executing setNull and throw an exception explaining the parameter can't be null.
> i have quickly look at the source code use to retrieve the references, and see on the QueryReferenceBroker class that the getReferencedObjectIdentity method was used to get the Identity of the reference object and this method use the hasNullifiedFK method to check if the FK is null, couldn't it be possible to move the hasNullifiedFK to the Platform class or a mechanism to extend it ? (at this time i have made an extension of PersistenceBrokerImpl a use a new QueryReferenceBroker class).
> i don't think the default behavior which is to tag a composite FK as null only if all the columns are null is correct for all the rdbms (at least for db2).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org