Re: [castor-dev] Bug with complex ORDER BY queries (in Oracle?)

[Richard Porter]

Title: Message

That would explain why adding my secondary ordering criterion resolves the issue for me. By forcing a sub-order on Order.id, the change in FK doesn't occur until we've really gotten to the next object. Without it, the indeterminate results from the database might have the FK change from, for example, 2 to 3 and back to 2.

 

Thanks Matthew.

 

-Richard Porter

-----Original Message-----
From: Matthew Baird [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2003 10:19 AM
To: [EMAIL PROTECTED]
Subject: Re: [castor-dev] Bug with complex ORDER BY queries (in Oracle?)

This is a limitation in Castor that doesn't really have a workaround.

 

Essentially Castor builds a complex joined sql statement to try and load a bunch of objects in one query, in the case of a 1:N relationship the query will return the 1 object as well as the N objects and then castor looks at the N side and detects a change in the FK reference in order to start building the next object.

 

That means if you do certain order operations, castor gets confused. Also the reason why count functionality doesn't quite work correctly.

 

hth,

Matthew

-----Original Message-----
From: Alexey A. Efimov [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2003 9:30 AM
To: [EMAIL PROTECTED]
Subject: Re: [castor-dev] Bug with complex ORDER BY queries (in Oracle?)

I mean, that you can interrupt SQL from Castor to database and look at this one.

-----Original Message-----
From: Richard Porter [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2003 8:22 PM
To: [EMAIL PROTECTED]
Subject: Re: [castor-dev] Bug with complex ORDER BY queries (in Oracle?)

I'm not sure I understand what you mean by "SQL result". If you mean, have I run a similar joined query with ordering in SQL, I have and it works. Otherwise, I'm at a loss as to what you want.

 

I have assumed that either the join is not correct, or the one-to-many table involved here is causing extra rows to appear because the Castor layer isn't getting only the distinct rows exclusive of the collection. I'd be more sure of that if I were seeing the same number of tuples I'd expect in a join across all these tables inclusive of the one-to-many relationship. I'm not. In the sample's case, just one extra Order.

 

If I had the SQL query that's been generated I'd have a better handle on the problem; however, I apparently haven't quite figured out how logging works in Castor, because even though I've set a log writer on a Mapping object and on the JDO instance I have, I'm not getting anything logged. And I just can't afford to spend the time on this bug (since I have a viable and agreeable workaround) that would help me track it down more completely.

Richard Porter

-----Original Message-----
From: Alexey A. Efimov [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2003 4:14 AM
To: [EMAIL PROTECTED]
Subject: Re: [castor-dev] Bug with complex ORDER BY queries (in Oracle?)

Sounds like that is the result of wrong join apllying. Did you have SQL result from this query?

 

-----Original Message-----
From: Richard Porter [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2003 2:00 AM
To: [EMAIL PROTECTED]
Subject: [castor-dev] Bug with complex ORDER BY queries (in Oracle?)

I have this query:
"SELECT o FROM castorbug.Order o ORDER BY o.batch.id  DESC";

It is run against a mapping that looks like this for the Order class:
    <class name="castorbug.Order" identity="id" key-generator="SEQ">
        <map-to table="ORDERS"/>
        <field name="id" type="long" required="true">
            <sql name="ID" type="bigint"/>
        </field>
        <field name="name" type="string">
            <sql name="NAME"/>
        </field>
        <field name="batch" type="castorbug.Batch">
            <sql name="BATCHID"/>
        </field>
        <field name="detail" type="castorbug.Detail">
            <sql many-key="ORDERID"/>
        </field>
        <field name="items" type="castorbug.Item" collection="collection">
            <sql many-key="ORDERID"/>
        </field>
    </class>

When the query is run, I get duplicate tuples returned.

The resultset can be fixed by either
a) removing the one-to-many mapping for the Item class
b) adding a secondary ordering criterion on "o.id"

For the purposes of our application, the latter solution is quite satisfactory. It sorts the results more cleanly anyway; however, this is clearly a bug with the generated SQL query.

I have attached a jar file containing a self-contained reproducible case. It includes a sqlscript for creating the database tables and associated artifacts and populating those tables.

Compiling and running the test as it stands returns (against my database) two copies of the Order with id = 2. I can not guarantee that the same results will occur against all occurrences of this test. Making the changes I've mentioned above will remove the duplicate tuple.

For completeness, I've been testing against Oracle 8.1.7.0.0 running on a Sun box. My clients have been Windows 2000 and Solaris.

Richard Porter
Software Engineer
Jel, Inc.

"Man is still the best computer...that can be mass produced with unskilled labor."
Werner Von Braun

<<orderbybug.jar>>