|
|
|
Martin, do you have any kind of a test case to prove this problem? If so, please
post it. The problem is, you can't reproduce the missing order reliably.
However you can try if you see the behaviour using this SQL script: drop table B create table A(NR_A number) create table B(NR_A number, NR_B number) insert into A(NR_A) values(1) insert into B(NR_A, NR_B) values(2, 1) insert into B(NR_A, NR_B) values(1, 1) insert into B(NR_A, NR_B) values(1, 2) commit select A.NR_A, B.NR_B What I get as result of the SELECT is for example: NR_A NR_B Note the marked When inserting an ORDER BY clause a Castor-compatible result set is fetched: select A.NR_A, B.NR_B NR_A NR_B If you compute statistics for table A the behaviour can be reproduced more
reliable: analyze table A compute statistics Created an attachment (id=640)
OrderTest.java The attached test program queries the tables A and B both using Castor and
plain JDBC. With the unpatched Castor version (that of 05.08.2004 in CVS), I can see the A-Sum [Castor]: 10 (should be the same as the following number) With my patch the last output lines are correct: A-Sum [Castor]: 8 (should be the same as the following number) Excuse my ignorance, but what if somebody specified an ORDER BY clause that
would prevent the (patched) code from performing successfully, iow an ORDER BY clause that isn't 'Castor-compatible' ? Thanks for the test. This is exaclty what I needed. I was never quite able to
figure out why results.size() was incorrect. I always knew that it was giving a total number of objects found in the graph but I didn't go far enough to see that the joins were affecting it in this way. My only question is exactly what Werner just asked. I tried to commit this a > Excuse my ignorance, but what if somebody specified an ORDER BY clause that
> would prevent the (patched) code from performing successfully, iow an ORDER BY > clause that isn't 'Castor-compatible' ? Yes, this is still a problem. If the user specifies an order clause, it has to Another problem, which is not handled quite satisfactory by my proposed patch
(It's more a work around than a fire and forget solution), is the handling of database locks:
I chose to ignore the lock request. Perhaps we should better fix SQLEngine algorithm (at least as a long term There would be at least two different ways to come up with a solution without
adding additional ORDER BY clauses to the generated SQL:
Any better ideas? I do not know whether you've all seen Martin's comments above, and I am sure we
have already seen similar threads at this very place .. but I'd like to see comments. And anyhow, does anybody know any bug numbers out there that we can link to this one ? I have an instinctive feeling that this may well be related to another problem I've seen here, but can't
recreate reliably, on MS SQL. I was even seeing it in other cases - but in most of those cases, I've since manually split the load operation so that the objects are no longer dependent. Gut instinct says this is actually a current issue on more than just oracle. As you state, Martin, one solution is to fetch all data using multiple queries
or using one great big query with some pretty awful joins. For a long time I've been curious to investigate offering users a configurable option of which of these strategies to use. Some people don't mind multiple queries while others do. And different situations influence this issue differently. What about offering this by way of a property in the castor.properties file? There is really no easy answer to this issue. It's similar to the LIMIT/OFFSET isssue with Oracle where we finally just agreed to support that feature only in Oracle 8.1.6 +. Comments? I think that's the right way to go about it, by making it configurable. Though I
do not agree that castor.properties is the right place to configure this. Imho, a more granular approach is required. Why not overload (again?) the various persistence-related methods of Database (as well) ? Iow, allow users to configure a default mode in castor.properties, and give them means to override this default on a per 'query' base. Hmm ? Yes, creating a new configuration switch is the way to make all users happy.
But of course it will increase the complexity even more. You will have to implement and maintain both possibilities. I also think to introduce this switch on a per query base is more convenient. This enables to optimize the performance for the particular cases. If we add such a switch I would vote to be the method "lazy loading of detail data" the default. This is the most general approach. Querying all data of a complete table at once can use an unpredictable amount of memory and can take quite long, depending on the available data. So what would be the allowed values of this new switch? enum DETAIL_DATA_METHOD { The "all at once" and "force order" methods could be combined. However we Let me just have a look at the way passing access modes to e.g. Database.load()
is handled. As far as I remember, there's two options with this. Either specify an access mode in the mapping file (access="read-only") or specify one at the method level. Bruce, correct me if I am wrong.
Martin, just wondering whether you would want to progress this any further ?
Update patch to current (Castor 1.1) source.
|
Patch for src/main/org/exolab/castor/jdo/drivers/OracleQueryExpression.java