Comment 3 for bug 612894

Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote : Re: Subquery cache returns extra rows on query with nested joins

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