Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6231

Wrong result (extra rows) via cursor in a stored procedure

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 5.5.37, 10.0.11
    • Fix Version/s: 5.5.49
    • Component/s: OTHER
    • Labels:
      None
    • Sprint:
      5.5.44, 5.5.58

      Description

      Initially reported as http://bugs.skysql.com/show_bug.cgi?id=429

      Test case:

      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;

      Actual result:

      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

      Expected result:

      CALL sp_test();
      num     cnt
      1       0
      4       0
      5       0
      CALL sp_test();
      num     cnt
      1       0
      4       0
      5       0

      The problem appeared in 5.5 tree with the following revision:

                  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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration