[#MDEV-29677] Wrong result with join query and innodb fulltext search

Testcase:

--source include/have_innodb.inc

CREATE TABLE t1 (a int) ENGINE = InnoDB;

INSERT INTO t1 VALUES (1), (2);

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);

SELECT * FROM t2 where MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE);

set optimizer_switch='materialization=on';

EXPLAIN

SELECT count(*) FROM t1 WHERE

not exists(

SELECT 1 FROM t2, t3

WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)

);

SELECT count(*) FROM t1 WHERE

not exists(

SELECT 1 FROM t2, t3

WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)

);

Produces a correct result:

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

...

count(*)

Note that this query plan executes the full text scan once.

Now, try a query plan executes the full text scan twice:

set optimizer_switch='materialization=off';

EXPLAIN

SELECT count(*) FROM t1 WHERE

not exists(

SELECT 1 FROM t2, t3

WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)

);

SELECT count(*) FROM t1 WHERE

not exists(

SELECT 1 FROM t2, t3

WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)

);

Produces:

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       DEPENDENT SUBQUERY      t2      fulltext        b2      b2      0               1       Using where

2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where

...

count(*)

[MDEV-29677] Wrong result with join query and innodb fulltext search Created: 2022-10-01 Updated: 2023-02-02
Status:	Open
Project:	MariaDB Server
Component/s:	Full-text Search, Storage Engine - InnoDB
Affects Version/s:	10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s:	10.3