Mark Huijser wrote:
BasicQueryHandler.readNodes should trucate results to maxNumber, if the databaseHandler supports maxNumber, but shouldn't it always truncate the results if the maxNumber is set? And what happens if the DatabaseHandler doesn't support max at all?
Then it should fall back to the java code truncate.
In informix specifically, maxNumber-support is set to true, but in the case of a UNION query, max (SELECT FIRST n) cannot be used (stupid informix). So here we are dependent on the fallback in BasicQueryHandler.readNodes, which - poorly - never trucates the results :-(, because maxNumber-support is set to true.
That is indeed a probleem, that certain queries dissalow certain constructions, but is hardly the fault of Informix. What is it supposed to do with a first in union situation ?. The first clause only applies to the fist half of the union not the second half, but if you put it in both it just sums the both firsts or does it do something special like taking both firsts and halve the result ? Remember a union query is the concatenation of two queries, so clauses on the first half don't apply to the second. This includes first count,max and other clauses. For Example:
select first 10 number from persons where number>1000 union select number from persons where number<10;
This according to me should return first 10 persons combined with all persons with a number lower then 10. The database can't do something else,
if you want the database to just give 10 back something like do something like this:
select first 10 number from persons where number in (select number from persons where number>100 union select number from persons where number<10);
It is indeed a bit strange that Informix bans the use of first alltogether in unions, but that is probably because otherwise these kinds of problems crop up.
Possible solution can be to drop the "sqlHandlerSupportsMaxNumber" condition and extend the while a little in all the readNodes methods:
// Truncate results to provide weak support for maxnumber while (rs.next() && (maxNumber>results.size() || maxNumber==-1)) { ... }
Is it ok to check this in as a fix ?
Regards,
Mark
-- Rico Jansen ([EMAIL PROTECTED]) "You call it untidy, I call it LRU ordered" -- Daniel Barlow
