Problem with DISTINCT and FULLTEXT BOOLEAN

[Kevin Musker]

I have a representation of a tree in a MyISAM table (see (1)). Below is a subset of the data (apollogies for the formatting) (see (2)).

mysql> select * from temp_ingredient;
+----+------+-----------+---------+-----------+
| id | name | search_id | next_id | parent_id |
+----+------+-----------+---------+-----------+
| 52 | Meat | 48 | 587 | 1 |
| 61 | Pig | 49 | 150 | 52 |
| 63 | Pork | 50 | 93 | 61 |
+----+------+-----------+---------+-----------+

When trying to select all the children of a specific ingredient (e.g. meat) I use the following query.

SELECT DISTINCT
tree_1.id,
tree_1.parent_id,
tree_1.name
FROM
temp_ingredient tree_1
INNER JOIN temp_ingredient tree_2
ON tree_1.search_id BETWEEN tree_2.search_id AND tree_2.next_id-1
WHERE
MATCH(tree_2.name) AGAINST ('meat')
ORDER BY
tree_1.search_id

Returning the results:
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
| 52 | 1 | Meat |
| 61 | 52 | Pig |
| 63 | 61 | Pork |
+----+-----------+------+

Now, if boolean fulltext searching is used (e.g. ...AGAINST ('meat' IN BOOLEAN MODE)...), only two rows get returned as:
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
| 52 | 1 | Meat |
| 63 | 61 | Pork |
+----+-----------+------+

The actual fulltext match on it's own returns the same in both cases, e.g...

SELECT id, parent_id, name FROM temp_ingredient WHERE MATCH(name) AGAINST ('meat');
SELECT id, parent_id, name FROM temp_ingredient WHERE MATCH(name) AGAINST ('meat' IN BOOLEAN MODE);

... both return the one row.
Also, if the DISTINCT is removed from the tree query, all three rows are returned.

Am I missing some subtle difference between BOOLEAN and non-BOOLEAN FULLTEXT matching, or might this be a bug?

Thanks for any input.

Oh, nearly forgot. Using version 4.0.17 on FreeBSD 4.9.


====(1)====
CREATE TABLE `temp_ingredient` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`search_id` int(11) NOT NULL default '0',
`next_id` int(11) NOT NULL default '0',
`parent_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `search_id` (`search_id`),
KEY `next_id` (`next_id`),
FULLTEXT KEY `name` (`name`)
) TYPE=MyISAM;

====(2)====
INSERT INTO temp_ingredient VALUES (52,'Meat',48,587,1),(61,'Pig',49,150,52),(63,'Pork',50,93,61);