DROP TABLE IF EXISTS test_a, test_b, test_result;

DROP PROCEDURE IF EXISTS sp_test;

CREATE TABLE test_a (num INT);

CREATE TABLE test_b (num INT);

CREATE TABLE test_result (num INT, cnt INT);

--delimiter |

CREATE PROCEDURE sp_test() 

BEGIN

DECLARE v_num  INT;

DECLARE v_done   INT DEFAULT 0;

/* Declare cursors */

DECLARE v_cursor CURSOR FOR

    SELECT A.num

    FROM   test_a A

    WHERE  ( SELECT COUNT(*)    FROM   test_b    WHERE  num = A.num ) = 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

START TRANSACTION;

OPEN v_cursor;

    DELETE FROM test_result;

v_loop: LOOP

    FETCH v_cursor INTO v_num;

    /* No more rows*/

    IF v_done = 1 THEN

        LEAVE v_loop;

    END IF;

    INSERT INTO test_result VALUES (v_num, (SELECT count(*) FROM test_b where num = v_num));

END LOOP v_loop;

CLOSE v_cursor;

COMMIT;

    SELECT * FROM test_result;

END |

--delimiter ;

INSERT INTO test_a VALUES (1),(2),(3),(4),(5);

INSERT INTO test_b VALUES (2),(3);

CALL sp_test();

CALL sp_test();

DROP TABLE IF EXISTS test_a, test_b, test_result;

DROP PROCEDURE IF EXISTS sp_test;

CALL sp_test();

num     cnt

1       0

4       0

5       0

CALL sp_test();

num     cnt

1       0

2       1

3       1

4       0

5       0

CALL sp_test();

num     cnt

1       0

4       0

5       0

CALL sp_test();

num     cnt

1       0

4       0

5       0

            revno: 3402.1.4

            revision-id: timour@askmonty.org-20120529211853-hww47vl7d4u4ae23

            parent: timour@askmonty.org-20120524110828-r0mm8sm1vn8a095e

            committer: timour@askmonty.org

            branch nick: 5.5-lpb944706

            timestamp: Wed 2012-05-30 00:18:53 +0300

            message:

              Patch for mdev-287: CHEAP SQ: A query with subquery in SELECT list, EXISTS, inner joins takes hundreds t

imes longer

              Analysis:

              The fix for lp:944706 introduces early subquery optimization.

              While a subquery is being optimized some of its predicates may be

              removed. In the test case, the EXISTS subquery is constant, and is

              evaluated to TRUE. As a result the whole OR is TRUE, and thus the

              correlated condition "b = alias1.b" is optimized away. The subquery

              becomes non-correlated.

              The subquery cache is designed to work only for correlated subqueries.

              If constant subquery optimization is disallowed, then the constant

              subquery is not evaluated, the subquery remains correlated, and its

              execution is cached. As a result execution is fast.

              However, when the constant subquery was optimized away, it was neither

              cached by the subquery cache, nor it was cached by the internal subquery

              caching. The latter was due to the fact that the subquery still appeared

              as correlated to the subselect_XYZ_engine::exec methods, and they

              re-executed the subquery on each call to Item_subselect::exec.

             Solution:

              The solution is to update the correlated status of the subquery after it has

              been optimized. This status consists of:

              - st_select_lex::is_correlated

              - Item_subselect::is_correlated

              - SELECT_LEX::uncacheable

              - SELECT_LEX_UNIT::uncacheable

              The status is updated by st_select_lex::update_correlated_cache(), and its

              caller st_select_lex::optimize_unflattened_subqueries. The solution relies

              on the fact that the optimizer already called

              st_select_lex::update_used_tables() for each subquery. This allows to

              efficiently update the correlated status of each subquery without walking

              the whole subquery tree.

              Notice that his patch is an improvement over MySQL 5.6 and older, where

              subqueries are not pre-optimized, and the above analysis is not possible.