| Bug #25637 | LEFT JOIN with BOOLEAN FULLTEXT loses left table matches | ||
|---|---|---|---|
| Submitted: | 15 Jan 2007 22:25 | Modified: | 8 Feb 2007 22:51 |
| Reporter: | Richard Clayton | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 4.1.22 | OS: | Linux (Linux & Windows) |
| Assigned to: | Bugs System | Target Version: | |
| Tags: | left join boolean fulltext | ||
[15 Jan 2007 22: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 23:30]
Miguel Solorzano
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 23: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 3:04]
Igor Babaev
Pushed into 4.1.23, 5.0.36, 5.1.16-beta main trees.
[8 Feb 2007 19: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.
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!