Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Description
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(*)
|
0
|
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(*)
|
1
|