civicrm/civicrm-core — pull request #4367 by kenwest (2014-10-15 03:22:03+00:00)
Title: Don't join on deleted contacts at the other end of a relationship

Advanced Search does not filter relationships with deleted contacts.

The change to line 2551 fixes this. The changes to lines 2542 and 2547 have been added for symmetry but haven't been tested. As this bit of code is probably used in a number of places, it would be good to have some further testing.

Note that http://dev.mysql.com/doc/refman/5.5/en/join.html says "The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set".

So this SQL will work but perhaps the best change would be to adjust the WHERE clause. It just seemed too hard to track that.