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. */