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;

EXECUTE stmt;

a

m

EXECUTE stmt;

a

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