I encountered an unexpected query result.
The OQL includes expressions on both the master objects
and the dependent/related objects.
The problem is described below.
I'd like to know whether the result is correct.
<<< Execution Environment >>>
* Castor 1.2
* MySQL 5.0.45
* JDK 1.5.0_14
* Windows XP, SP2
<<< Database Tables (state and city, 1:N) >>>
CREATE TABLE state
(
state_code char(2) NOT NULL,
state_name char(25) NOT NULL,
country_name char(25) NOT NULL,
PRIMARY KEY (state_code)
);
CREATE TABLE city
(
city_code char(4) NOT NULL,
city_name char(25) NOT NULL,
state_code char(2) NOT NULL, /* (FK) */
PRIMARY KEY (city_code)
);
<<< Database Records >>>
mysql> select * from state;
+------------+----------------------+--------------+
| state_code | state_name | country_name |
+------------+----------------------+--------------+
| DC | DISTRICT OF COLUMBIA | USA |
| WA | WASHINGTON | USA |
+------------+----------------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from city;
+-----------+------------+------------+
| city_code | city_name | state_code |
+-----------+------------+------------+
| WWAS | WASHINGTON | DC |
+-----------+------------+------------+
1 row in set (0.00 sec)
<<< Castor JDO Mapping (State and City) >>>
<class name="atis.State" identity="code">
<cache-type type="unlimited"/>
<map-to table="state"/>
<field name="code" type="string">
<sql name="state_code" type="char"/>
</field>
<field name="name" type="string">
<sql name="state_name" type="char"/>
</field>
<field name="countryName" type="string">
<sql name="country_name" type="char"/>
</field>
<field name="cities" type="atis.City" collection="collection">
<sql many-key="state_code"/>
</field>
</class>
<class name="atis.City" identity="code" depends="atis.State">
<cache-type type="unlimited"/>
<map-to table="city"/>
<field name="code" type="string">
<sql name="city_code" type="char"/>
</field>
<field name="name" type="string">
<sql name="city_name" type="char"/>
</field>
<field name="state" type="atis.State">
<sql name="state_code"/>
</field>
</class>
<<< select-all OQL (NO PROBLEM) >>>
*** OQL: SELECT State FROM atis.State State
*** Castor log messages (linebreaks and spaces added):
DEBUG (PreparedStatementProxy.<init>:77) -
Creating prepared statement proxy for SQL statement
SELECT
state.state_code,state.state_name,state.country_name,city.city_code
FROM state LEFT OUTER JOIN city ON state.state_code=city.state_code
DEBUG (DebuggingCacheProxy.remove:176) -
unlimited.remove(atis.State/<DC(2175)>) [atis.State]
DEBUG (DebuggingCacheProxy.remove:176) -
unlimited.remove(atis.City/<WWAS(2677522)>) [atis.City]
DEBUG (PreparedStatementProxy.<init>:77) -
Creating prepared statement proxy for SQL statement
SELECT city.city_code,city.city_name,city.state_code
FROM city
WHERE city.city_code=?
DEBUG (DebuggingCacheProxy.remove:176) -
unlimited.remove(atis.State/<WA(2762)>) [atis.State]
DEBUG (ConnectionProxy.invoke:71) - Committing JDBC Connection instance.
DEBUG (ConnectionProxy.invoke:69) - Closing JDBC Connection instance.
DEBUG (DebuggingCacheProxy.put:167) -
unlimited.put(atis.City/<WWAS(2677522)>,
[EMAIL PROTECTED]) [atis.City]
DEBUG (DebuggingCacheProxy.put:167) -
unlimited.put(atis.State/<DC(2175)>,
[EMAIL PROTECTED]) [atis.State]
DEBUG (DebuggingCacheProxy.put:167) -
unlimited.put(atis.State/<WA(2762)>,
[EMAIL PROTECTED]) [atis.State]
*** MySQL query log messages (line breaks and spaces added):
2 Query SELECT
state.state_code,state.state_name,state.country_name,city.city_code
FROM state LEFT OUTER JOIN city ON
state.state_code=city.state_code
2 Query SELECT city.city_code,city.city_name,city.state_code
FROM city
WHERE city.city_code='WWAS'
*** query result #objects=2
State[code=DC, name=DISTRICT OF COLUMBIA, countryName=USA]
State[code=WA, name=WASHINGTON, countryName=USA]
<<< selection OQL (UNEXPECTED RESULT !!!) >>>
*** OQL: SELECT DISTINCT State FROM atis.State State
WHERE (State.name = $1 OR State.cities.name = $2)
- $1="WASHINGTON"
- $2="WASHINGTON"
*** Castor log messages (linebreaks and spaces added):
DEBUG (PreparedStatementProxy.<init>:77) -
Creating prepared statement proxy for SQL statement
SELECT DISTINCT
state.state_code,state.state_name,state.country_name,city.city_code
FROM state LEFT OUTER JOIN city ON
state.state_code=city.state_code,city city_0
WHERE state.state_code=city_0.state_code
AND (( state.state_name = ? OR city_0.city_name = ? ))
DEBUG (DebuggingCacheProxy.remove:176) -
unlimited.remove(atis.State/<DC(2175)>) [atis.State]
DEBUG (DebuggingCacheProxy.remove:176) -
unlimited.remove(atis.City/<WWAS(2677522)>) [atis.City]
DEBUG (PreparedStatementProxy.<init>:77) -
Creating prepared statement proxy for SQL statement
SELECT city.city_code,city.city_name,city.state_code
FROM city
WHERE city.city_code=?
DEBUG (ConnectionProxy.invoke:71) - Committing JDBC Connection instance.
DEBUG (ConnectionProxy.invoke:69) - Closing JDBC Connection instance.
DEBUG (DebuggingCacheProxy.put:167) -
unlimited.put(atis.City/<WWAS(2677522)>, [EMAIL PROTECTED])
[atis.City]
DEBUG (DebuggingCacheProxy.put:167) -
unlimited.put(atis.State/<DC(2175)>, [EMAIL PROTECTED])
[atis.State]
*** MySQL query log messages (line breaks and spaces added):
2 Query SELECT DISTINCT
state.state_code,state.state_name,state.country_name,city.city_code
FROM state LEFT OUTER JOIN city ON
state.state_code=city.state_code,city city_0
WHERE state.state_code=city_0.state_code
AND (( state.state_name = 'WASHINGTON' OR city_0.city_name =
'WASHINGTON' ))
2 Query SELECT city.city_code,city.city_name,city.state_code
FROM city
WHERE city.city_code='WWAS'
*** result #objects=1
State[code=DC, name=DISTRICT OF COLUMBIA, countryName=USA]
*** expected result:
Since the state_name of 'WA' state is 'WASHINGTON'
and city_name of 'WWAS' city is 'WASHINGTON',
the result of the avobe OQL contains two State objects:
'DC' and 'WA'.
*** study:
The above SQL generated by Castor does not select State objects
that have no related City object because LEFT JOIN is spoiled
by "state.state_code=city_0.state_code" expression in the WHERE clause.
The SQL and result are not changed if the "depends" attribute
in the City mapping is removed, i.e. they are related objects.
Again, the SQL is:
SELECT DISTINCT
state.state_code,state.state_name,state.country_name,
city.city_code
FROM state LEFT OUTER JOIN city ON
state.state_code=city.state_code,city city_0
WHERE state.state_code=city_0.state_code
AND (( state.state_name = 'WASHINGTON' OR city_0.city_name =
'WASHINGTON' ))
On the other hand, the following SQL selects all the expected states
whether they have at least one city or not.
mysql> SELECT DISTINCT
state.state_code,state.state_name,state.country_name,
city.city_code
-> FROM state LEFT OUTER JOIN city ON state.state_code=city.state_code
-> WHERE state.state_name = 'WASHINGTON' OR city.city_name =
'WASHINGTON';
+------------+----------------------+--------------+-----------+
| state_code | state_name | country_name | city_code |
+------------+----------------------+--------------+-----------+
| DC | DISTRICT OF COLUMBIA | USA | WWAS |
| WA | WASHINGTON | USA | NULL |
+------------+----------------------+--------------+-----------+
2 rows in set (0.00 sec)
----
Akihito NAKAMURA
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
