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:Mac OS X (OS X, linux)
Assigned to: Sergey Vojtovich

[20 Jan 2007 5:47] Alan Snyder
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.
[20 Jan 2007 17:59] Sveta Smirnova
Thank you for the report.

Please provide output of statements SHOW CREATE TABLE `Fiddle Tunes` and SHOW CREATE TABLE `Tune Alternative Search Names`
[26 Jan 2007 8:28] Sveta Smirnova
test case

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

[26 Jan 2007 8:29] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources.
[2 Apr 2007 13:23] Bugs System
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.
[8 Apr 2007 19:01] Bugs System
Pushed into 5.1.18-beta
[8 Apr 2007 19:02] Bugs System
Pushed into 5.0.40
[9 Apr 2007 19:35] Paul Dubois
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.
[12 Dec 2007 23:26] Daniel Burckhardt
I'm still having this problem in 5.0.45-community-nt. Please let me know if you need an example table to reproduce.