Inconsistent results from ANSI join?

[Malc P]

Hi all
The development team here have asked me to run this by you;
They've found that the following query:

SELECT surname, firstname, a.clientid, a.clientref
FROM client a
LEFT JOIN planrole b on a.clientid = b.clientid
LEFT JOIN claimrole c on a.clientid = c.clientid
WHERE b.clientid IS NULL and c.clientid IS NULL

will return maybe 15 rows if you only include the first three fields (surname, firstname, a.clientid) in the "SELECT" line, and 61 rows if you include all four fields (surname, firstname, a.clientid, a.clientref).

They want to know if this is a bug - I'm inclined to the feeling that you can't expect to join on NULLs and get predictable results (but that still doesn't explain the different result sets - and this is consistent, every time they run it against this particular database - just by changing the list of columns to be returned).

Anyone?
Thanks

[s***@t-online.de]

Hello Malc,


i would vote for a bug.. (gut feeling)

however compare to NULL is undefined.


May be the difference is due to a different explain.

are the explain output different in both cases ?

if so try to use an optimizer hint to make the explain/ optimizer path equal, may be that way will produce the same results?


sorry not much, may be it helps.


See you

Superboer.

[s***@t-online.de]

Hello Malc


i would still vote for a bug and regarding the NULL i was way off here

( that will teach me again to read before being trigger happy...)

the resultset shoud give you all the rows which are in client but not in planrole and clainmrole i guess.

regardless of what is in the selected columnlist the result should always be the same i guess

tryed this on 11.7 and can not reproduce. Are you still on the 9.3?


hope it helps

Superboer

Post by s***@t-online.de
Hello Malc,
i would vote for a bug.. (gut feeling)
however compare to NULL is undefined.
May be the difference is due to a different explain.
are the explain output different in both cases ?
if so try to use an optimizer hint to make the explain/ optimizer path equal, may be that way will produce the same results?
sorry not much, may be it helps.
See you
Superboer.