castor

Query of composite class fails after upgrading from 0.9.7 to 0.9.9(.1)

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Blocker Blocker
  • Resolution: Fixed
  • Affects Version/s: 0.9.9, 0.9.9.1
  • Fix Version/s: 1.0 M1
  • Component/s: JDO
  • Labels:
    None
  • Environment:
    MySQL 4.1.11
  • Testcase included:
    yes
  • Number of attachments :
    8

Description

Before upgrading to 0.9.9, we can create a virtual class in mapping to compose two and more real table. For example:
CALL SQL SELECT a.a1, a.a2, b.b1, b.b2 from tableA a left join tableB b on b.a = a.id AS model.Dummy
It throws the following exception for now on:
java.sql.SQLException: Column index out of range.
at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.fetchRaw(SQLEngine.java:2561)
at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.nextIdentity(SQLEngine.java:2249)
at org.exolab.castor.persist.QueryResults.nextIdentity(QueryResults.java:174)
at org.exolab.castor.jdo.engine.OQLQueryImpl$OQLEnumeration.hasMore(OQLQueryImpl.java:581)
at org.exolab.castor.jdo.engine.OQLQueryImpl$OQLEnumeration.hasMore(OQLQueryImpl.java:567)

When debugging, I'm surprised to find the SQLEngine use the nonexistent dummy table name in the query statment.

BTW, there is another bug I found before. The following mapping will make query fail:
<class identity="id" name="model.Item">
<map-to table="item"/>
<field name="id" type="integer">
<sql name="id" type="integer"/>
</field>
<field name="prop" type="model.Prop" collection="collection">
<sql many-key="item" />
</field>
<field name="name" type="string">
<sql name="name" type="varchar"/>
</field>
</class>
while, this doesn't:
<class identity="id" name="model.Item">
<map-to table="item"/>
<field name="id" type="integer">
<sql name="id" type="integer"/>
</field>
<field name="name" type="string">
<sql name="name" type="varchar"/>
</field>
<field name="prop" type="model.Prop" collection="collection">
<sql many-key="item" />
</field>
</class>
Someone also comment it out in line 2392, SQLEngine.java:
// TODO: wrong, as it could be that the first field is not part of the root class.
if (numberOfFields > 0) {
tableName = _engine._fields[0].tableName;

Testcase will be attached.

  1. File
    create.sql
    18/Nov/05 12:07 AM
    0.6 kB
    Sean
  2. Java Source File
    Item.java
    18/Nov/05 12:15 AM
    0.5 kB
    Sean
  3. XML File
    jdo-conf.xml
    18/Nov/05 12:07 AM
    0.5 kB
    Sean
  4. XML File
    mapping.xml
    18/Nov/05 12:15 AM
    1 kB
    Sean
  5. Text File
    patch.C1265.20051123.txt
    23/Nov/05 5:32 AM
    3 kB
    Werner Guttmann
  6. Java Source File
    Prop.java
    18/Nov/05 12:15 AM
    0.5 kB
    Sean
  7. Java Source File
    Sum.java
    18/Nov/05 12:16 AM
    0.6 kB
    Sean
  8. Java Source File
    TestTemplate.java
    18/Nov/05 12:16 AM
    2 kB
    Sean

Activity

Ascending order - Click to sort in descending order
Sean made changes -
Field Original Value New Value
Attachment mapping.xml [ 17633 ]
Attachment Prop.java [ 17635 ]
Attachment Item.java [ 17634 ]
Sean made changes -
Attachment Sum.java [ 17636 ]
Attachment TestTemplate.java [ 17637 ]
Hide
Permalink
Werner Guttmann added a comment -

Sean, is there any way you could join me on IRC ? Either connect to irc://irc.codehaus.org:6665/castor, or if you cannot connect due to security restrictions, pleae use http://irc.codehaus.org, and join the castor channel.

Show
Werner Guttmann added a comment - Sean, is there any way you could join me on IRC ? Either connect to irc://irc.codehaus.org:6665/castor , or if you cannot connect due to security restrictions, pleae use http://irc.codehaus.org , and join the castor channel.
Hide
Permalink
Werner Guttmann added a comment -

Initiall patch (tested against mySQL), pending confirmation from Ralf that this does not cause problems with other RDBMS such as Oracle.

Show
Werner Guttmann added a comment - Initiall patch (tested against mySQL), pending confirmation from Ralf that this does not cause problems with other RDBMS such as Oracle.
Werner Guttmann made changes -
Attachment patch.C1265.20051123.txt [ 17728 ]
Werner Guttmann made changes -
Comment [ Just an idea .... ;-)

Index: SQLEngine.java
===================================================================
RCS file: /home/projects/castor/scm/castor/src/main/org/exolab/castor/jdo/engine/SQLEngine.java,v
retrieving revision 1.38
diff -u -r1.38 SQLEngine.java
--- SQLEngine.java 13 Oct 2005 07:40:54 -0000 1.38
+++ SQLEngine.java 23 Nov 2005 11:10:35 -0000
@@ -419,7 +419,7 @@
             _log.debug( Messages.format( "jdo.createSql", sql ) );
         }
         
- return new SQLQuery( this, sql, types );
+ return new SQLQuery( this, sql, types, false );
     }
 
 
@@ -441,7 +441,7 @@
                 _log.debug (Messages.format ("jdo.directSQL", sql));
             }
             
- return new SQLQuery( this, sql, types );
+ return new SQLQuery( this, sql, types, true );
  }
 
         if (_log.isDebugEnabled()) {
@@ -2027,8 +2027,21 @@
         private boolean _resultSetDone;
 
         private Object[] _fields;
+
+ /**
+ * Indicates whether the SQL query executed is issued as part of a SQL
+ * CALL statement or not.
+ */
+ private boolean _isCallSql = false;
 
- SQLQuery( SQLEngine engine, String sql, Class[] types )
+ /**
+ * Creates an instance of SQLQuery.
+ * @param engine SQLEngine instance
+ * @param sql The SQL statement to execute
+ * @param types Types of the class used.
+ * @param isCallSql true if the SQL is issued as part of a CALL SQL statement.
+ */
+ SQLQuery(SQLEngine engine, String sql, Class[] types, boolean isCallSql)
         {
             _engine = engine;
             _requestedEngine = engine;
@@ -2039,6 +2052,8 @@
             for (int i = 0; i < _identSqlType.length; i++) {
                 _identSqlType[i] = ((JDOFieldDescriptor) _engine._clsDesc.getIdentities()[i]).getSQLType()[0];
             }
+
+ _isCallSql = isCallSql;
         }
 
 
@@ -2313,11 +2328,16 @@
                 String metaTableName = metaData.getTableName(count);
                 String metaColumnName = metaData.getColumnName(count);
                if (fieldColumnName.equalsIgnoreCase(metaColumnName)) {
+ if (!_isCallSql) {
                    if (fieldTableName.equalsIgnoreCase(metaTableName)) {
- break;
- } else if ("".equals(metaTableName)) {
- break;
- }
+ break;
+ } else if ("".equals(metaTableName)) {
+ break;
+ }
+ } else {
+ // if we are running as a result of a CALL SQL statement, let's relax our checks.
+ break;
+ }
                } else if (fieldName.equalsIgnoreCase(metaColumnName)) {
                    break;
                }

Let me know what you think.

Werner ]
Hide
Permalink
Werner Guttmann added a comment -

Seam, moving on to the second issue (mentioned in the BTW above), what OQL query are you executing to see this behaviour ?

Show
Werner Guttmann added a comment - Seam, moving on to the second issue (mentioned in the BTW above), what OQL query are you executing to see this behaviour ?
Hide
Permalink
Sean added a comment -

Castor uses first field's tableName for reference when executing query. In above case, the table of collection (that is 'prop' table) will be used instead of real one (that is 'item' table), it causes the query failed.
Your patch for first issue might also solves this problem, will let you know after test.

Show
Sean added a comment - Castor uses first field's tableName for reference when executing query. In above case, the table of collection (that is 'prop' table) will be used instead of real one (that is 'item' table), it causes the query failed. Your patch for first issue might also solves this problem, will let you know after test.
Hide
Permalink
Werner Guttmann added a comment -

Sean, can you please do me a favour and move this issue to a separate bug report, and attach the relevant files as well. If you managed to provide me with samples that do not include the issue discussed here, even better .. .

Show
Werner Guttmann added a comment - Sean, can you please do me a favour and move this issue to a separate bug report, and attach the relevant files as well. If you managed to provide me with samples that do not include the issue discussed here, even better .. .
Werner Guttmann made changes -
Assignee Werner Guttmann [ wguttmn ]
Werner Guttmann made changes -
Fix Version/s 1.0 [ 11700 ]
Original Estimate 1 hour [ 3600 ]
Remaining Estimate 1 hour [ 3600 ]
Hide
Permalink
Ralf Joachim added a comment -

I could run CTF with your patch applied without any problem.

Show
Ralf Joachim added a comment - I could run CTF with your patch applied without any problem.
Hide
Permalink
Werner Guttmann added a comment -

Great. I will commit this patch later on ....

Show
Werner Guttmann added a comment - Great. I will commit this patch later on ....
Hide
Permalink
Sean added a comment -

Werner, this patch works for the testcase attached.
Unfortunately, if there is an aggregative function in select, the problem is still there. For example, if you change the statement in TestTemplate.java to:
CALL SQL SELECT i.id, i.name, max(p.value) FROM item i left join prop p on p.item = i.id GROUP BY i.id AS jdo.test1265.Sum
the exception will be thrown.

Show
Sean added a comment - Werner, this patch works for the testcase attached. Unfortunately, if there is an aggregative function in select, the problem is still there. For example, if you change the statement in TestTemplate.java to: CALL SQL SELECT i.id, i.name, max(p.value) FROM item i left join prop p on p.item = i.id GROUP BY i.id AS jdo.test1265.Sum the exception will be thrown.
Hide
Permalink
Werner Guttmann added a comment -

I think using an alias for the max(p.value) should do the trick, as long as the column alias "meets" the name of the column as defined in the class mapping of "Sum".

Show
Werner Guttmann added a comment - I think using an alias for the max(p.value) should do the trick, as long as the column alias "meets" the name of the column as defined in the class mapping of "Sum".
Hide
Permalink
Sean added a comment -

GOOOD..My program works proper again. Thanks for effort!

Show
Sean added a comment - GOOOD..My program works proper again. Thanks for effort!
Hide
Permalink
Werner Guttmann added a comment -

Great. Can I ask you a favour ? We have recently started to add a HOW-TO section to Castor JDO. Would you mind coming up with half a page of information about your particular task ? E.g. how to run a SQL call statement that uses a virtual table and a function ?

Other than this, I will commit the patch above as is later on today.

Show
Werner Guttmann added a comment - Great. Can I ask you a favour ? We have recently started to add a HOW-TO section to Castor JDO. Would you mind coming up with half a page of information about your particular task ? E.g. how to run a SQL call statement that uses a virtual table and a function ? Other than this, I will commit the patch above as is later on today.
Hide
Permalink
Werner Guttmann added a comment -

Speaking of which, is the second issue mentioned above still an issue ? If yes, can you please rause a new issue and attach all relevant files ?

Show
Werner Guttmann added a comment - Speaking of which, is the second issue mentioned above still an issue ? If yes, can you please rause a new issue and attach all relevant files ?
Hide
Permalink
Sean added a comment -

Yes, the problem is still there and I will submit a new issue soon.

Show
Sean added a comment - Yes, the problem is still there and I will submit a new issue soon.
Hide
Permalink
Sean added a comment -

Werner, the new issue is here: http://jira.codehaus.org/browse/CASTOR-1271
It is my honour if I can make something for Castor and its users. Would you please send me a sample chapter to follow?

Show
Sean added a comment - Werner, the new issue is here: http://jira.codehaus.org/browse/CASTOR-1271 It is my honour if I can make something for Castor and its users. Would you please send me a sample chapter to follow?
Hide
Permalink
Werner Guttmann added a comment -

Great. Simply have a look at any of the documents in src/how-to.

Show
Werner Guttmann added a comment - Great. Simply have a look at any of the documents in src/how-to.
Werner Guttmann made changes -
Status Open [ 1 ] Resolved [ 5 ]
Resolution Fixed [ 1 ]
Werner Guttmann made changes -
Fix Version/s 1.0 [ 11700 ]
Fix Version/s 1.0 M1 [ 12121 ]
Ralf Joachim made changes -
Status Resolved [ 5 ] Closed [ 6 ]

People

  • Assignee:
    Werner Guttmann
    Reporter:
    Sean
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved:

Time Tracking

Estimated:
1h
Original Estimate - 1 hour
Remaining:
1h
Remaining Estimate - 1 hour
Logged:
Not Specified
Time Spent - Not Specified