wrong results with LEFT JOIN and maxdb 7.8.02.21

[Former Member]

We just migrated the db for our (non sap) application from 7.6 to 7.8.02.21

Now we ran into a problem because the new version returns different (wrong) results.

I debuged a while and now the problem can be reproduced with the following

very simple statements:

create table test (a int, b int)

insert into test values(1,1)

insert into test values(2,2)

insert into test values(3,3)

select t1.a, t2.a, t2.newcol

from test t1 left join (select a, 'X' as newcol from test where b = 2) t2 on t1.a = t2.a

This returns:

A;A;NEWCOL;

1; ?; X <-- !!

2; 2; X

3; ?; X <-- !!

With version 7.6. it returned the correct result:

A;A;NEWCOL;

1; ?; ?

2; 2; X

3; ?; ?

The problem is caused by the constant value in the select-clause

Is this a known bug? is there any workaround, or bug fix available ?

Regards

Bernhard

[steffen_schildberg · Accepted solution]

Hi Bernhard,

this is a new bug and can be circumvented adding a hint to the command which disables queryrewrite for this command:

select /+query_no/ t1.a, t2.a, t2.newcol

from test t1 left join (select a, 'X' as newcol from test where b = 2) t2 on t1.a = t2.a

An error message is created and can be found here for tracking purpose:

[http://maxdb.sap.com/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1239952]

Thank you for reporting!

Best regards,

Steffen

[Former Member · Accepted solution]

Hi Steffen,

schön mal wieder was von dir zu hören

Just tested the suggested work around in our application - and it works.

Thanks for this fast solution!

Bernhard