Bug #941 Select with fulltext match in subselect returns no records
Submitted: 28 Jul 2003 23:05 Modified: 12 Aug 2003 11:05
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.0 OS:Linux (Linux/Mandrake 9.1)
Assigned to: Sergei Golubchik CPU Architecture:Any

[28 Jul 2003 23:05] [ name withheld ]
Description:
A select statement restricted by 'where <fld> IN (subselect)', and (subselect) constructed with a fulltext match using truncation operator, returns no records from the primary table in this example, even though (subselect), when executed separately, returns a list that contains valid entries for <fld>.

How to repeat:
DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;
CREATE TABLE T1 (
	T1RecordID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
	Field1 varchar(15)
);
CREATE TABLE T2 (
	T2RecordID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
	T1RecordID int NOT NULL,
	Field2 varchar(15),
	FULLTEXT (Field2)
);
INSERT INTO T1 (Field1) VALUES ('Joseph'),('Mary');
INSERT INTO T2 (T1RecordID,Field2) VALUES (1,'Josephxxx row1'),(1,'Josephyyy row2'),(1,'Josephzzz row3'),(2,'Maryxxx row1'),(2,'Maryyyy row2'),(2,'Maryzzz row3');

/* Expect next SELECT to return 'Maryxxx row1', 'Maryyyy row2' and 'Maryzzz row3' -- and it does. */
SELECT * FROM T2 WHERE MATCH(T2.Field2) AGAINST ('Mary*' IN BOOLEAN MODE);

/* Expect next SELECT to return T1RecordID = 2, 2, 2 - and it does. */
SELECT * FROM (SELECT T2.T1RecordID FROM T2 WHERE MATCH(T2.Field2) AGAINST ('Mary*' IN BOOLEAN MODE)) AS T2Extract;

/* Fails for Mary... */
/* Expect next SELECT to return 'Mary' - but it does not - why not??? */
SELECT T1.* FROM T1 WHERE T1.T1RecordID IN (SELECT T2.T1RecordID FROM T2 WHERE MATCH(T2.Field2) AGAINST ('Mary*' IN BOOLEAN MODE));

/* But it works for Joseph... */
/* Expect next SELECT to return 'Joseph' - and it does. */
SELECT T1.* FROM T1 WHERE T1.T1RecordID IN (SELECT T2.T1RecordID FROM T2 WHERE MATCH(T2.Field2) AGAINST ('Joseph*' IN BOOLEAN MODE));

/* Note: Reversing the order of insertion into T1 and T2 causes the example to work for Mary but fail for Joseph. */
[12 Aug 2003 11:05] Sergei Golubchik
cannot repeat on latest bk tree - most probably bug was fixed since 4.1.0