MySQL Bugs: #44397: Negative relevancy value for boolean fulltext query using a left join

Bug #44397	Negative relevancy value for boolean fulltext query using a left join
Submitted:	22 Apr 2009 2:46	Modified:	22 Apr 2009 12:29
Reporter:	Tim McLaughlin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	5.0.79, 5.1.33, 5.1.34, 6.0.9	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	fulltext, left join, NOT NULL, null

Description:
If you execute a SELECT containing a LEFT JOIN and a FULLTEXT search in BOOLEAN mode, any row in the left table that has a non-NULL value for the link column in the LEFT JOIN expression but with no matching row in the right table results in an incorrect relevancy value (the relevancy always seems to be "-2").  However, if the row in the left table has a matching row in the right table or the link column in the left table has a value of NULL the correct relevancy is produced.  See the example given.

I encountered this problem in version 5.1.33.

This problem appears to be somewhat similar to bug numbers 14708 and 25637

How to repeat:
DROP TABLE IF EXISTS men;
CREATE TABLE men
(
    id int(10) NOT NULL auto_increment,
    link int(10) default NULL,
    name mediumtext default NULL,
    PRIMARY KEY (id)
) TYPE = MyISAM;

INSERT INTO men VALUES(1, 10, 'Homer Simpson');
INSERT INTO men VALUES(2, NULL, 'Moe Syzlak');
INSERT INTO men VALUES(3, 0, 'Groundskeeper Willie');

DROP TABLE IF EXISTS women;
CREATE TABLE women
(
    id int(10) NOT NULL auto_increment,
    name mediumtext default NULL,
    PRIMARY KEY (id)
) TYPE = MyISAM;

INSERT INTO women VALUES(10, 'Marge Simpson');

SELECT men.name AS mans_name, men.link, women.name AS womans_name, MATCH(men.name, women.name) AGAINST('Simpson Syzlak Willie' IN BOOLEAN MODE) AS relevance
FROM men
LEFT JOIN women ON men.link = women.id;

# I get the following results:
# mans_name             link    womans_name    relevance
# Homer Simpson         10      Marge Simpson  1
# Moe Syzlak            NULL    NULL           1
# Groundskeeper Willie  0       NULL           -2

# The relevancy value of "1" for Homer and Moe is what I would expect 
# but the relevancy value of "-2" for Willie appears to be incorrect.  
# I would expect it to be "1" as well.

Negative relevance is definitely a bug. Verified with 5.0.79, 5.1.34 and 6.0.9 also.

I discovered a temporary workaround for this bug:

SELECT men.name AS mans_name, men.link, women.name AS womans_name, MATCH(men.name, women.name) AGAINST('Simpson Syzlak Willie' IN BOOLEAN MODE) AS relevance
FROM men
LEFT JOIN (SELECT * FROM women) AS women ON men.link = women.id

# This produces the result I would expect:
# mans_name            link  womans_name    relevance
# Homer Simpson        10    Marge Simpson  1
# Moe Syzlak           NULL  NULL           1
# Groundskeeper Willie 0     NULL           1

up

the bug is still present on 5.0.90 and the workaround is slow...