Tom,
>>>> (1) More than one result in single result query
>>>> Testcase: testReadTransactionIsolation_CustomUpdate_Refresh took
>>>> 0,046
>
I have debugged this test case with FINEST logging level as you told me,
and I think I found the problem -- but once more, I need your help to
get it fixed! :-)
As you correctly assumed, the problem is that the DELETE using temporary
table is not working. I have written a test program that does the same
SQL commands that are found in the debug log:
CREATE TABLE TEMP.$CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, PRIMARY KEY
(EMP_ID))
INSERT INTO TEMP.$CMP3_EMPLOYEE (EMP_ID) SELECT t0.EMP_ID FROM
CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME =
'testReadTransactionIsolation') AND (t1.EMP_ID = t0.EMP_ID))
DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT EMP_ID FROM
TEMP.$CMP3_EMPLOYEE WHERE EMP_ID = CMP3_SALARY.EMP_ID)
DELETE FROM CMP3_EMPLOYEE WHERE EXISTS(SELECT EMP_ID FROM
TEMP.$CMP3_EMPLOYEE WHERE EMP_ID = CMP3_EMPLOYEE.EMP_ID)
DROP TABLE TEMP.$CMP3_EMPLOYEE
The test programs shows the same behaviour, it just doesn't delete
anything from CMP3_EMPLOYEE. In fact I was a bit upset about that. I
checked several times the same few lines, then I saw the problem: The
test case produces different SQL types for EMP_ID in the temporary table
TEMP.$CMP3_EMPLOYEE (INTEGER, see above) and EMP_ID in the base table
CMP3_EMPLOYEE (FIXED(15)). In fact, MaxDB is unable to compare INTEGER
types of different width! While I found out that FIXED(15) is used
because the test suite specified java.lang.Long for the base table's
column and MaxDBPlatform is mapping java.lang.Long to FIXED(n), I did
not find out why INTEGER is used for the temporary table. Actually I am
quite certain that this is a bug in the temp table generation code
(that, BTW, is NOT part of MaxDBPlatform but to be found somewhere in
the super classes).
So the solution would be find and fix that bug, but as I said before, I
did not find it after one day of work. :-( It would be very, very kind
of you, if you could fix that. Maybe you remember that we skipped that
bug some weeks ago when I reported it (the problem was that the FOREIGN
KEY could not get established because of the same discrepancy -- you
just told me to switch off using foreign key constraints)? So the fix is
needed anyways because in the next days I will switch ON foreign key
constrains again and THEN you will get it back on you desk anyways. ;-)
I tried to find a workaround and in fact it would be as easy as casting
both fields to the same width using "...FIXED(x, 20)=FIXED(y, 20)..."
instead of "...x=y...". At least in my test program that worked around
the bug. But then I stuck with implementing that workound in
MaxDBPlatform, so I am giving up here.
I really beg for your help. Please fix that bug.
Thanks a lot, I would be lost without you! :-)
Markus
P.S.: One more side information. Despite the fact that you are using
NUMERIC (= FIXED(20) in MaxDB) in one place and INTEGER in the other,
what MaxDB thinks to be not comparable by default, you also are mixing
provision of decimals. Sometimes you have not provided them, so MaxDB
gets told a type of "FIXED(20)" while sometimes you explicitely specify
a value of 0, so MaxDB gets told a type of "FIXED(20,0)". In fact,
FIXED(20) and FIXED(20,0) is not the same for MaxDB! So please also
check for either setting 0 explicitely EVERYWHERE or NOWHERE.