2015-11-20 17:01:00 UTC
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).