Maybe a LEFT JOIN bug in MaxDB 7.7.06.09 ?

[Former Member]

This statement should return one row, but i get no result:

select *
from dual t1
left join dual t2
  on t2.dummy = 'b'

But this statement works:

select *
from dual t1
left join dual t2
  on t2.dummy != t1.dummy

Why?

Best Regards,

Stephan

[holger_becker]

Hi,

in MaxDB version <= 7.7 we have a problem at outer joins without real join transition.

We will fix this bug with one of our next patches.

You could check the proceeding with this link

http://sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1216351

Thank you for reporting this bug.

Kind regards

Holger

[Former Member]

Link don't work:

Error 1216351 not found

Best Regards,

Stephan

[lbreddemann]

>

> This statement should return one row, but i get no result:

>

>

select *
> from dual t1
> left join dual t2
>   on t2.dummy = 'b'

No - this statement should not deliver one row.

DUAL contains just one row with one column value 'a'.

There's no way this query can produce anything but the empty result set!

Make it "on t2.dummy ='a'" and you'll get your row!

> But this statement works:

>

select *
> from dual t1
> left join dual t2
>   on t2.dummy != t1.dummy

>

> Why?

Because "LEFT JOIN" is short for "LEFT OUTER JOIN".

For the database this means: take all matching rows from table t1 and try to match them with the join condition.

If you find matches, include them into the result set, if not, include a NULL value instead.

And that is what happens.

By the way: precisely this case is described in the documentation of MaxDB as well (MaxDB Documentation > Reference > SQL Reference Manual > Frequently Used Syntax Elements: Overview > Joined Table (joined_table) )...

regards,

Lars

[Former Member]

select *
from dual t1
left join dual t2
  on t2.dummy = 'b'

This is exactly what i mean.

I select one row from t1 and use a LEFT OUTER JOIN to join t2.

t2 joins no result to t1.

Therefore t1.dummy should be 'a' and t2.dummy should be NULL

select *
from dual t1
left join dual t2
  on t2.dummy != t1.dummy

This statement should return the same result like the first statement. One row from t1 and LEFT JOIN no row from t2. And it works. But if this works (i think it's the correct behaviour), why the first statement return nothing? The principle is the same.

[lbreddemann]

>

>

select *
> from dual t1
> left join dual t2
>   on t2.dummy = 'b'

>

> This is exactly what i mean.

> I select one row from t1 and use a LEFT OUTER JOIN to join t2.

> t2 joins no result to t1.

> Therefore t1.dummy should be 'a' and t2.dummy should be NULL

You're right - this is not working correctly.

In general it appears as if a condition like this:

select *
from t1
 join t2 on FALSE

Is not correclty evaluated and returns an empty result set.

I informed the responsible development collegues about this.

Thanks a lot for pointing out!

regards,

Lars