Bug #941 Select with fulltext match in subselect returns no records
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. */
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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. */