MySQL fulltext MATCH AGAINST IN BOOLEAN MODE query using a LEFT OUTER JOIN table's column unexpectedly returns no results

Question

In MySQL 5.6.51 using InnoDB, I have two tables:

users
user_aka_names

Table details:

• user_aka_names has a foreign key, user_id to users
  • One user can have many user_aka_names
  • Not all users have a user_aka_name
• users has user_name, internal_name, and name columns
  • internal_name and user_name have their own fulltext indices, and one joint fulltext index
  • name has a standard btree index
• user_aka_names has a name column
  • name has a fulltext index
• All name fields are varchar(255)

Data:

• I have several rows (~60) in users, each with their name-related columns filled out
  • I have much more data in production, this is just a dev environment
• I have more user_aka_names rows than users (~70)
• I have two user_aka_names with the value: "hellothere"

I'm trying to get all users where either the user_name, internal_name, or user_aka_names.name match a specific value, and I'm trying to do it efficiently, since I have many records. I've constructed a MATCH AGAINST query that should work, but it isn't returning the expected results.

ft_min_word_length is 4, so hellothere should meet that requirement. hellothere is also not on my stoplist.

I wrote the following query to retrieve results:

SELECT DISTINCT
  `users`.*
FROM `users`
LEFT OUTER JOIN
  user_aka_names ON users.id = user_aka_names.user_id
WHERE (
  MATCH(user_name, internal_name) AGAINST('+hellothere*' IN BOOLEAN MODE) OR
  MATCH(user_aka_names.name) AGAINST('+hellothere*' IN BOOLEAN MODE)
);

This works exactly as I intended to retrieve users having user_name and internal_name that include "hellothere". However, this query doesn't work if the user_aka_names.name matches "hellothere". It does not return any records.

It seems like the fulltext index on user_aka_names is working properly, as this query returns the two expected user_aka_name rows:

SELECT
  `user_aka_names`.*
FROM `user_aka_names`
WHERE (
  MATCH(user_aka_names.name) AGAINST('+hellothere*' IN BOOLEAN MODE)
);

Similarly, trying to put that in a subquery of the original query rather than having an OR correctly returns the two user record associated with the user_aka_names found:

SELECT DISTINCT
  `users`.*
FROM `users`
LEFT OUTER JOIN
  user_aka_names ON users.id = user_aka_names.user_id
WHERE (
  MATCH(user_name, internal_name) AGAINST('+hellothere*' IN BOOLEAN MODE) OR
    OR users.id IN (
    SELECT user_id
    FROM user_aka_names
    WHERE MATCH(name) AGAINST('+hellothere*' IN BOOLEAN MODE)
  )
);

I'm glad I have a working query, but I'd like to know why my original query using two MATCH AGAINST statements and a LEFT OUTER JOIN don't work. Is there something I'm misunderstanding about indices or joins here?

This testing was doe directly on mysql, but I' ultimately trying to get this working in Ruby on Rails, hence why I want to return users (User records).