MySQL Bugs: #14708: Inconsistent treatment of NULLs in LEFT JOINed FULLTEXT matching without index

Bug #14708	Inconsistent treatment of NULLs in LEFT JOINed FULLTEXT matching without index
Submitted:	7 Nov 2005 15:17	Modified:	23 Jun 2006 4:49
Reporter:	Felix Geerinckx (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.0.17-BK, 5.0.15	OS:	Linux (Linux, Windows)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
There seems to be an (undocumented?) inconsistency in the treatment of NULLs with fulltext matching in BOOLEAN mode on LEFT JOINED tables without FULLTEXT indexes:

NULLs from single table queries don't influence the result set
NULLs generated from (missing) LEFT JOINed records drop records from the result set even when a match can be found in the left table

How to repeat:
USE test;
DROP TABLE IF EXISTS master, detail;

CREATE TABLE master (
	id INT NOT NULL PRIMARY KEY,
	d CHAR(200) NOT NULL,
	e CHAR(200)
) ENGINE=MyISAM;

INSERT INTO master VALUES 
(1, 'aword', NULL), (2, 'aword', 'bword'), 
(3, 'bword', NULL), (4, 'bword', 'aword'), 
(5, 'aword and bword', NULL);

SELECT
	id, d, e
FROM master
WHERE
	MATCH(d, e) AGAINST ('+aword +bword' IN BOOLEAN MODE);

# As expected, 3 records are found (id=2, 4 and 5).
# Note the NULL for column e in the record with id=5, which doesn't seem to 
# influence the result

CREATE TABLE detail (
	id INT NOT NULL PRIMARY KEY,
	m_id INT NOT NULL,
	f CHAR(200),
	KEY (m_id)
) ENGINE = MyISAM;

INSERT INTO detail VALUES
(1, 1, 'bword'),
(2, 3, 'aword'),
(3, 5, '');

SELECT 
	m.id, m.d, m.e,
	d.id, d.f
FROM master m
LEFT JOIN detail d ON d.m_id = m.id
WHERE
	MATCH(m.d, m.e, d.f) AGAINST ('+aword +bword' IN BOOLEAN MODE);

# Unexpectedly, only 3 records are found (id = 1, 3 and 5)
# I would also expect id = 2, 4 in the result set, since they already match
# in the master only.
# The NULLs generated from the LEFT JOIN's missing records seem to make 
# MATCH(..) AGAINST(.. IN BOOLEAN MODE) return 0, even when there already
# is a match from the left side of the JOIN.

# If we explicitly insert NULL records in table detail:

INSERT INTO detail VALUES
(4, 2, NULL),
(5, 4, NULL);

# and rerun the SELECT
SELECT 
	m.id, m.d, m.e,
	d.id, d.f
FROM master m
LEFT JOIN detail d ON d.m_id = m.id
WHERE
	MATCH(m.d, m.e, d.f) AGAINST ('+aword +bword' IN BOOLEAN MODE);

# all five records *are* returned.

Suggested fix:
At least this should be mentioned in the Reference Manual as a (current?) limitation.
Also, there is no mention on multiple table fulltext matching (in boolean mode).

Of course, in 'normal mode' it won't work (ERROR 1210 (HY000): Incorrect arguments to MATCH), which can be deducted from the Reference Manual.

Thank you for a problem report. Verified just as described on 5.0.17-BK (ChangeSet@1.1967, 2005-11-11 13:39:46+04:00, ...) build on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,01 sec)

mysql> DROP TABLE IF EXISTS master, detail;
Query OK, 0 rows affected, 2 warnings (0,00 sec)

mysql> CREATE TABLE master (
    -> id INT NOT NULL PRIMARY KEY,
    -> d CHAR(200) NOT NULL,
    -> e CHAR(200)
    -> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO master VALUES
    -> (1, 'aword', NULL), (2, 'aword', 'bword'),
    -> (3, 'bword', NULL), (4, 'bword', 'aword'),
    -> (5, 'aword and bword', NULL);

Query OK, 5 rows affected (0,01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    -> id, d, e
    -> FROM master
    -> WHERE
    -> MATCH(d, e) AGAINST ('+aword +bword' IN BOOLEAN MODE);
+----+-----------------+-------+
| id | d               | e     |
+----+-----------------+-------+
|  2 | aword           | bword |
|  4 | bword           | aword |
|  5 | aword and bword | NULL  |
+----+-----------------+-------+
3 rows in set (0,00 sec)

mysql> CREATE TABLE detail (
    ->  id INT NOT NULL PRIMARY KEY,
    -> m_id INT NOT NULL,
    -> f CHAR(200),
    -> KEY (m_id)
    -> ) ENGINE = MyISAM;

Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO detail VALUES
    -> (1, 1, 'bword'),
    -> (2, 3, 'aword'),
    -> (3, 5, '');
Query OK, 3 rows affected (0,00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT
    -> m.id, m.d, m.e,
    -> d.id, d.f
    -> FROM master m
    -> LEFT JOIN detail d ON d.m_id = m.id
    -> WHERE
    -> MATCH(m.d, m.e, d.f) AGAINST ('+aword +bword' IN BOOLEAN MODE);
+----+-----------------+------+------+-------+
| id | d               | e    | id   | f     |
+----+-----------------+------+------+-------+
|  1 | aword           | NULL |    1 | bword |
|  3 | bword           | NULL |    2 | aword |
|  5 | aword and bword | NULL |    3 |       |
+----+-----------------+------+------+-------+
3 rows in set (0,00 sec)

mysql> INSERT INTO detail VALUES
    -> (4, 2, NULL),
    -> (5, 4, NULL);
Query OK, 2 rows affected (0,00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT  m.id, m.d, m.e, d.id, d.f FROM master m LEFT JOIN detail d ON d.m_id = m.id WHERE MATCH(m.d, m.e, d.f) AGAINST ('+aword +bword' IN BOOLEAN MODE);

+----+-----------------+-------+------+-------+
| id | d               | e     | id   | f     |
+----+-----------------+-------+------+-------+
|  1 | aword           | NULL  |    1 | bword |
|  2 | aword           | bword |    4 | NULL  |
|  3 | bword           | NULL  |    2 | aword |
|  4 | bword           | aword |    5 | NULL  |
|  5 | aword and bword | NULL  |    3 |       |
+----+-----------------+-------+------+-------+
5 rows in set (0,01 sec)

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

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

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

fixed in 5.1.12

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

fixed in 5.0.23

Noted in 5.0.23, 5.1.12 changelog.

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