MySQL Bugs: #25637: LEFT JOIN with BOOLEAN FULLTEXT loses left table matches

Bug #25637	LEFT JOIN with BOOLEAN FULLTEXT loses left table matches
Submitted:	15 Jan 2007 21:25	Modified:	8 Feb 2007 21:51
Reporter:	Richard Clayton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.22	OS:	Linux (Linux & Windows)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	left join boolean fulltext

Description:
If you do a LEFT JOIN on a FULLTEXT query in BOOLEAN mode, only rows where the left and right tables are linked are returned. My understanding is that matches with the left table should be returned regardless of NULL results in the right table. See the example given.

NOTE: This works CORRECTLY in 4.0.21 but not in 4.1.22!

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

INSERT INTO test1 VALUES (1, 1, 'string');
INSERT INTO test1 VALUES (2, 0, 'string');

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

INSERT INTO test2 VALUES (1, 'string');

# Now to test...

SELECT test1.*, MATCH(test1.name) AGAINST('string') AS relevance FROM test1 LEFT JOIN test2 ON test1.link = test2.id WHERE MATCH(test1.name, test2.name) AGAINST('string' IN BOOLEAN MODE);

# This returns one row when two are matched in the first table. Returns two rows in 4.0.21.

SELECT test1.*, MATCH(test1.name) AGAINST('string') AS relevance FROM test1 LEFT JOIN test2 ON test1.link = test2.id WHERE MATCH(test1.name) AGAINST('string' IN BOOLEAN MODE);

# This returns two rows in both versions, but is no longer matching results in the second table (so is useless).

Suggested fix:
If this isn't a bug, then it's a drastic change of behaviour which has severely compromised my network of sites!

For fellow sufferers, you can get around this bug/change by altering:

WHERE MATCH(test1.name, test2.name) AGAINST('string' IN BOOLEAN MODE)

To read:

WHERE MATCH(test1.name) AGAINST('string' IN BOOLEAN MODE) OR MATCH(test2.name) AGAINST('string' IN BOOLEAN MODE)

Thank you for the bug report.

miguel@light:~/dbs/4.1$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> # Now to test...
mysql> 
mysql> SELECT test1.*, MATCH(test1.name) AGAINST('string') AS relevance FROM test1 LEFT
    -> JOIN test2 ON test1.link = test2.id WHERE MATCH(test1.name, test2.name)
    -> AGAINST('string' IN BOOLEAN MODE);
+----+------+--------+-----------+
| id | link | name   | relevance |
+----+------+--------+-----------+
|  1 |    1 | string |         0 |
+----+------+--------+-----------+
1 row in set (0.00 sec)

mysql> 

miguel@light:~/dbs/5.0$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> # Now to test...
mysql> 
mysql> SELECT test1.*, MATCH(test1.name) AGAINST('string') AS relevance FROM test1 LEFT
    -> JOIN test2 ON test1.link = test2.id WHERE MATCH(test1.name, test2.name)
    -> AGAINST('string' IN BOOLEAN MODE);
+----+------+--------+-----------+
| id | link | name   | relevance |
+----+------+--------+-----------+
|  1 |    1 | string |         0 | 
|  2 |    0 | string |         0 | 
+----+------+--------+-----------+
2 rows in set (0.00 sec)

mysql>

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/18588

ChangeSet@1.2598, 2007-01-22 14:23:53-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #25637: LEFT JOIN with BOOLEAN FULLTEXT loses left table matches.
  The bug is actually a duplicate of the bug 14708.
  Down-ported the fix for 14708 from 5.0.  
  Merged the test case for bug 14708 from 5.0.

Pushed into 4.1.23, 5.0.36, 5.1.16-beta main trees.

Copied the changelog entry for Bug#14708 to
4.1.23 changelog.

For BOOLEAN mode full-text searches on non-indexed columns, NULL rows 
generated by a LEFT JOIN caused incorrect query results.