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

[15 Jan 2007 21:25] Richard Clayton
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!
[15 Jan 2007 21:55] Richard Clayton
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)
[15 Jan 2007 22:30] MySQL Verification Team
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>
[22 Jan 2007 22:20] 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/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.
[28 Jan 2007 2:04] Igor Babaev
Pushed into 4.1.23, 5.0.36, 5.1.16-beta main trees.
[8 Feb 2007 18:31] Paul DuBois
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.