Hi Akihito, this sounds like a bug to me. Can you please open a new issue at jira and attach the content of this mail. We would appreciate if you could also attach a junit test for us to reproduce the problem.
Regards Ralf Akihito Nakamura schrieb: > 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 > -- Syscon Ingenieurbüro für Meß- und Datentechnik GmbH Ralf Joachim Raiffeisenstraße 11 72127 Kusterdingen Germany Tel. +49 7071 3690 52 Mobil: +49 173 9630135 Fax +49 7071 3690 98 Internet: www.syscon.eu E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: D-72127 Kusterdingen Registereintrag: Amtsgericht Stuttgart, HRB 382295 Geschäftsleitung: Jens Joachim, Ralf Joachim --------------------------------------------------------------------- To unsubscribe from this list, please visit: http://xircles.codehaus.org/manage_email
