Filter api: systematic use JOIN when building SQL

Do not join tables using the where clause, for better readability and
avoiding risk of issues with operator precedence and 'any condition'
filtering mode.

This commit also removes an unnecessary LEFT JOIN between the bugnote
and bugnote_text tables; since this is a strict 1:1 relationship, an
inner join is sufficient and yields better performance.