Recently, I discovered the amazing benefits of using MySQL's match against, but encountered some unexpected issues.
I noticed that when the joined table2 has no paired rows, MySQL's match against function returns a negative value for the join column with a NULL value. However, if I remove this NULL column from the match group, it returns a positive relevance value instead. Why does having one NULL column cause the entire match to return -2?
select *, match(t1.column1, t1.column2, t1.column3, t1.column4, t2.column5) against ('search string' in boolean mode) as relevance
from table1 t1
left join table2 t2 on (t2.id = t2.column_id)
/*having relevance > 0*/
order by relevance desc;