MySQL Bugs: #25729: boolean full text search is confused by NULLs produced by LEFT JOIN

Bug #25729	boolean full text search is confused by NULLs produced by LEFT JOIN
Submitted:	20 Jan 2007 5:47	Modified:	9 Apr 2007 19:35
Reporter:	Alan Snyder	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.0.26, 5.0 BK, 5.1 BK	OS:	MacOS (OS X, linux)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
It seems that boolean full text searches, at least when there is no index, will match a NULL created by a LEFT JOIN, as demonstrated by these examples. In the database, the Tune Alternative Search Names table has very few rows, so that most columns are NULL in the LEFT JOIN.

These queries return 3678 rows:

SELECT DISTINCT `Fiddle Tunes`.`uid` FROM `Fiddle Tunes` LEFT JOIN `Tune Alternative Search Names` ON (`Fiddle Tunes`.`uid` = `Tune Alternative Search Names`.`uid`) WHERE MATCH (`Tune Alternative Search Names`.`Search Name`) AGAINST ('castle*' IN BOOLEAN MODE)

SELECT DISTINCT `Fiddle Tunes`.`uid` FROM `Fiddle Tunes` LEFT JOIN `Tune Alternative Search Names` ON (`Fiddle Tunes`.`uid` = `Tune Alternative Search Names`.`uid`) WHERE MATCH (`Tune Alternative Search Names`.`Search Name`) AGAINST ('castle' IN BOOLEAN MODE)

This query returns 2 rows:

SELECT DISTINCT `Fiddle Tunes`.`uid` FROM `Fiddle Tunes` LEFT JOIN `Tune Alternative Search Names` ON (`Fiddle Tunes`.`uid` = `Tune Alternative Search Names`.`uid`) WHERE `Tune Alternative Search Names`.`Search Name` LIKE '%castle%'

This query returns 2 rows:

SELECT DISTINCT `Fiddle Tunes`.`uid` FROM `Fiddle Tunes` LEFT JOIN `Tune Alternative Search Names` ON (`Fiddle Tunes`.`uid` = `Tune Alternative Search Names`.`uid`) WHERE MATCH (`Tune Alternative Search Names`.`Search Name`) AGAINST ('castle*' IN BOOLEAN MODE) AND `Tune Alternative Search Names`.`Search Name` IS NOT NULL

This last example demonstrates a workaround.

How to repeat:
No test case yet.

Thank you for the report.

Please provide output of statements SHOW CREATE TABLE `Fiddle Tunes` and SHOW CREATE TABLE `Tune Alternative Search Names`

test case

Attachment: bug25729.test (application/octet-stream, text), 830 bytes.

Thank you for the report.

Verified as described on Linux using last BK sources.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/23544

ChangeSet@1.2396, 2007-04-02 17:26:39+05:00, svoj@mysql.com +4 -0
  BUG#25729 - boolean full text search is confused by NULLs produced by
              LEFT JOIN
  Fixed that in certain situations MATCH ... AGAINST returns false hits
  for NULLs produced by LEFT JOIN when there is no fulltext index
  available.

Pushed into 5.1.18-beta

Pushed into 5.0.40

Noted in 5.0.40, 5.1.18 changelogs.

In certain situations, MATCH ... AGAINST returned false hits for NULL
values produced by LEFT JOIN when no full-text index was available.

I'm still having this problem in 5.0.45-community-nt. Please let me know if you need an example table to reproduce.