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

[Alexey A. Efimov]

Title: Message

I think you must swap ORDER BY entries, becose REsultSet ordered in unknown format for Castor, but it very strange. I mean that:

If first ordered by ORDER_ID

ORDER_ID | BATCH_ID

1                  1

1                  2

1                  3

2                  1

3                  1

3                  2

3                  4

 

Castor look trough ResultSet and construct objects. It create 3 instance of Order, becose id follow in this nointerapteble order.

 

So, if you order by BATCH_ID

ORDER_ID | BATCH_ID

1                  1

2                  1

3                  1

1                  2

3                  2

1                  3

3                  4

 

Now Castor create 7 objects, but 3 will with id = 1, 1 with id = 2 and 3 with id = 3.

 

BUG in this thing!

 

Bruce take look at this, please!! This is actualy bug, i guess.

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

Alexey,

 

I thought that was what you meant; I was just having problems getting the Castor logging to work. I managed to get it right, and here's the SQL query:

SELECT "ORDERS"."ID","ORDERS"."NAME","ORDERS"."BATCHID","DETAIL"."ID","ITEM"."ID" FROM "ITEM","DETAIL","BATCH" "BATCH_0","ORDERS" WHERE "ORDERS"."ID"="DETAIL"."ORDERID"(+) AND "ORDERS"."ID"="ITEM"."ORDERID"(+) AND "ORDERS"."BATCHID"="BATCH_0"."ID" ORDER BY "BATCH_0"."ID" DESC

It should be clear that this is going to return a lot of tuples, since it will return each Order/Item combo. This is fine in terms of the query. Since I don't have my collections lazyloading, I'd like there to be just the one query to the database. The problem then, is that somewhere in the mapping layer, Castor is creating more than one Order with the same ID. It's not creating EVERY extra instance, because that would make some sense, but it is creating at least one extra.

 

When I uncomment the secondary ordering criterion, the SQL query generated by Castor looks like this:

SELECT "ORDERS"."ID","ORDERS"."NAME","ORDERS"."BATCHID","DETAIL"."ID","ITEM"."ID" FROM "ITEM","DETAIL","BATCH" "BATCH_0","ORDERS" WHERE "ORDERS"."ID"="DETAIL"."ORDERID"(+) AND "ORDERS"."ID"="ITEM"."ORDERID"(+) AND "ORDERS"."BATCHID"="BATCH_0"."ID" ORDER BY "BATCH_0"."ID" DESC , "ORDERS"."ID"

Same extra tuples when run straight against the database as is obvious, but NO extra Order instances returned by Castor. Clearly, the problem's *somewhere* in the Castor mapping layer; I just don't have the time or management backing to track it down right now.

 

Cheers!

Richard Porter

-----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>>