SET @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off';
EXPLAIN SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
INSERT INTO t2 VALUES (2,'y');
EXPLAIN SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
Expanded test case:
CREATE TABLE t1 ( f1 varchar(32)) ; 'x'),(' c'),('x' );
INSERT INTO t1 VALUES ('b'),(
CREATE TABLE t2 ( f2 int, f3 varchar(32)) ;
INSERT INTO t2 VALUES (1,'x');
SET @@optimizer_ switch= 'materializatio n=off,in_ to_exists= on,subquery_ cache=off' ;
EXPLAIN SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
INSERT INTO t2 VALUES (2,'y');
EXPLAIN SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1;
drop table t1, t2;