From 0e72979040e9795ab4866a77d29fc96a3be620ed Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Sat, 1 Oct 2022 15:41:04 +0300 Subject: MDEV-29677: Wrong result with join query and innodb fulltext search 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. --- mysql-test/suite/innodb_fts/r/fulltext.result | 7 +++++-- mysql-test/suite/innodb_fts/t/fulltext.test | 4 +++- storage/innobase/handler/ha_innodb.cc | 5 +++++ 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(ft_handler))->ft_result; + if (result) + result->current= NULL; + DBUG_RETURN(rnd_init(false)); } -- cgit v1.2.1