MDEV-29677: Wrong result with join query and innodb fulltext searchbb-10.7-mdev29677

InnoDB FTS scan was used by a subquery. A subquery execution may start
a table read and continue until it finds the first matching record
combination. This can happen before the table read returns EOF.

The next time the subquery is executed, it will start another table read.
InnoDB FTS table read fails to re-initialize its data structures in this
scenario and will try to continue the scan started at the first execution.

Fixed by ha_innobase::ft_init() to stop the FTS scan if there is one.

Additional complication is the testcase: The original testcase used
the query plan with DEPENDENT SUBQUERY, but then Exists-to-IN optimization
was pushed which changed the test query's query plan to use MATERIALIZED.

The query plan with MATERIALIZED didn't hit the bug. I've adjusted the
testcase to use the QEP with DEPENDENT SUBQUERY instead.

3 files changed, 13 insertions, 3 deletions

diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result
index 9cf3083d827..90c5ce56720 100644
--- a/mysql-test/suite/innodb_fts/r/fulltext.result
+++ b/mysql-test/suite/innodb_fts/r/fulltext.result
@@ -537,7 +537,9 @@ CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)) ENGINE = InnoDB;
 INSERT INTO t2 VALUES (1,'Scargill');
 CREATE TABLE t3 (a int, b int) ENGINE = InnoDB;
 INSERT INTO t3 VALUES (1,1), (2,1);
+set @tmp=@@optimizer_switch, @@optimizer_switch='materialization=off';
 # t2 should use full text index
+# The subquery should be DEPENDENT SUBQUERY
 EXPLAIN
 SELECT count(*) FROM t1 WHERE 
 not exists(
@@ -546,8 +548,8 @@ WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	MATERIALIZED	t2	fulltext	b2	b2	0		1	Using where
-2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	fulltext	b2	b2	0		1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
 # should return 0
 SELECT count(*) FROM t1 WHERE 
 not exists(
@@ -562,6 +564,7 @@ SELECT 1 FROM t2 IGNORE INDEX (b2), t3
 WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
 );
 ERROR HY000: Can't find FULLTEXT index matching the column list
+set optimizer_switch=@tmp;
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)) ENGINE = InnoDB;
 INSERT INTO t1 VALUES
diff --git a/mysql-test/suite/innodb_fts/t/fulltext.test b/mysql-test/suite/innodb_fts/t/fulltext.test
index a45ca5a8d0d..66d4e288a27 100644
--- a/mysql-test/suite/innodb_fts/t/fulltext.test
+++ b/mysql-test/suite/innodb_fts/t/fulltext.test
@@ -570,7 +570,9 @@ INSERT INTO t2 VALUES (1,'Scargill');
 CREATE TABLE t3 (a int, b int) ENGINE = InnoDB;
 INSERT INTO t3 VALUES (1,1), (2,1);
 
+set @tmp=@@optimizer_switch, @@optimizer_switch='materialization=off';
 --echo # t2 should use full text index
+--echo # The subquery should be DEPENDENT SUBQUERY
 EXPLAIN
 SELECT count(*) FROM t1 WHERE 
   not exists(
@@ -578,7 +580,6 @@ SELECT count(*) FROM t1 WHERE
    WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
   );
 
-# INNODB_FTS: INVESTIGATE
 --echo # should return 0
 SELECT count(*) FROM t1 WHERE 
   not exists(
@@ -593,6 +594,7 @@ SELECT count(*) FROM t1 WHERE
    WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
   );
 
+set optimizer_switch=@tmp;
 DROP TABLE t1,t2,t3;
 
 #
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index ea521fb7798..a80c58f1304 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -9591,6 +9591,11 @@ ha_innobase::ft_init()
 		trx->will_lock = true;
 	}
 
+        /* If there is an FTS scan in progress, stop it */
+        fts_result_t* result =  (reinterpret_cast<NEW_FT_INFO*>(ft_handler))->ft_result;
+        if (result)
+                result->current= NULL;
+
 	DBUG_RETURN(rnd_init(false));
 }