If change test suite a bit it (add subquery parameter to output) is clear that bug is bug of subquery (not subquery cache):
SET SESSION optimizer_switch = 'subquery_cache=off'; /* cache is OFF */ SELECT table1 .`col_int_nokey`,( SELECT MIN( SUBQUERY1_t1 .`col_int_key` ) FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 JOIN CC SUBQUERY1_t3 ON SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_varchar_nokey` = SUBQUERY1_t2 .`col_varchar_key` WHERE table1 .`col_int_nokey` ) FROM CC table1 ; col_int_nokey ( SELECT MIN( SUBQUERY1_t1 .`col_int_key` ) FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 JOIN CC SUBQUERY1_t3 ON SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_varchar_nokey` = SUBQUERY1_t2 .`col_varchar_key` WHERE table1 .`col_int_nokey` ) 3 0 2 0 0 NULL 8 NULL 5 NULL 2 NULL 33 NULL 5 NULL 1 NULL 9 NULL 1 NULL 3 NULL 8 NULL 231 NULL SET SESSION optimizer_switch = 'subquery_cache=on'; /* cache is ON */ SELECT table1 .`col_int_nokey`,( SELECT MIN( SUBQUERY1_t1 .`col_int_key` ) FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 JOIN CC SUBQUERY1_t3 ON SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_varchar_nokey` = SUBQUERY1_t2 .`col_varchar_key` WHERE table1 .`col_int_nokey` ) FROM CC table1 ; col_int_nokey ( SELECT MIN( SUBQUERY1_t1 .`col_int_key` ) FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 JOIN CC SUBQUERY1_t3 ON SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_varchar_nokey` = SUBQUERY1_t2 .`col_varchar_key` WHERE table1 .`col_int_nokey` ) 3 0 2 0 0 NULL 8 NULL 5 NULL 2 0 33 NULL 5 NULL 1 NULL 9 NULL 1 NULL 3 0 8 NULL 231 NULL
If change test suite a bit it (add subquery parameter to output) is clear that bug is bug of subquery (not subquery cache):
SET SESSION optimizer_switch = 'subquery_ cache=off' ; nokey` = SUBQUERY1_t2 .`col_varchar_key` nokey` = SUBQUERY1_t2 .`col_varchar_key` cache=on' ; nokey` = SUBQUERY1_t2 .`col_varchar_key` nokey` = SUBQUERY1_t2 .`col_varchar_key`
/* cache is OFF */ SELECT table1 .`col_int_nokey`,(
SELECT MIN( SUBQUERY1_t1 .`col_int_key` )
FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 JOIN CC SUBQUERY1_t3 ON SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_varchar_
WHERE table1 .`col_int_nokey` )
FROM CC table1 ;
col_int_nokey (
SELECT MIN( SUBQUERY1_t1 .`col_int_key` )
FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 JOIN CC SUBQUERY1_t3 ON SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_varchar_
WHERE table1 .`col_int_nokey` )
3 0
2 0
0 NULL
8 NULL
5 NULL
2 NULL
33 NULL
5 NULL
1 NULL
9 NULL
1 NULL
3 NULL
8 NULL
231 NULL
SET SESSION optimizer_switch = 'subquery_
/* cache is ON */
SELECT table1 .`col_int_nokey`,(
SELECT
MIN( SUBQUERY1_t1 .`col_int_key` )
FROM C SUBQUERY1_t1 JOIN (
C SUBQUERY1_t2 JOIN CC SUBQUERY1_t3 ON SUBQUERY1_t2 .`pk` )
ON SUBQUERY1_t3 .`col_varchar_
WHERE table1 .`col_int_nokey` )
FROM CC table1 ;
col_int_nokey (
SELECT
MIN( SUBQUERY1_t1 .`col_int_key` )
FROM C SUBQUERY1_t1 JOIN (
C SUBQUERY1_t2 JOIN CC SUBQUERY1_t3 ON SUBQUERY1_t2 .`pk` )
ON SUBQUERY1_t3 .`col_varchar_
WHERE table1 .`col_int_nokey` )
3 0
2 0
0 NULL
8 NULL
5 NULL
2 0
33 NULL
5 NULL
1 NULL
9 NULL
1 NULL
3 0
8 NULL
231 NULL