|
CREATE TABLE t1 (a varchar(1));
|
INSERT INTO t1 VALUES ('m'),('v');
|
|
CREATE TABLE t2 (b int, c varchar(1));
|
INSERT INTO t2 VALUES (2,'m'),(1,'x');
|
|
CREATE VIEW v2 AS SELECT * FROM t2;
|
|
PREPARE stmt FROM "SELECT STRAIGHT_JOIN * FROM t1 WHERE EXISTS ( SELECT b FROM v2 WHERE c = t1.a )";
|
EXECUTE stmt;
|
EXECUTE stmt;
|
|
# Cleanup
|
DROP VIEW v2;
|
DROP TABLE t1, t2;
|
|
10.3 0ca3aaa7
|
EXECUTE stmt;
|
a
|
m
|
EXECUTE stmt;
|
a
|
The query is expected to return the row.
If we change the query to EXPLAIN EXTENDED, we'll see
EXECUTE stmt;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
|
Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c`))))
|
EXECUTE stmt;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`b`))))
|
Note `c` changing to `b`.
|