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

SELECT from view, which was replaced in prepared statement inside cursor, gives wrong result

    XMLWordPrintable

    Details

    • Sprint:
      10.1.22

      Description

      Affects earlier versions as well.

      The following creates and replaces a view pointing to different tables within prepared statements inside a cursor. While still in the cursor a SELECT from the replaced view yields results from the table referenced previously, the view doesn't seem to be updated. SHOW CREATE VIEW gives correct results, though.

      Test case:

      DROP PROCEDURE IF EXISTS test.sp_testcase;
      DROP TABLE IF EXISTS test.tmp_list;
      DROP TABLE IF EXISTS test.a;
      DROP TABLE IF EXISTS test.b;
       
      CREATE TABLE test.tmp_list AS
      SELECT 'a' AS col
      UNION
      SELECT 'b' AS col
      ;
       
      CREATE TABLE test.a AS
      SELECT 'AAA';
       
      CREATE TABLE test.b AS
      SELECT 'BBB';
       
      DELIMITER #
       
      CREATE PROCEDURE test.sp_testcase ()
      BEGIN
       
        DECLARE done BOOLEAN DEFAULT FALSE;
        DECLARE var VARCHAR(255);
        DECLARE cur CURSOR FOR
        SELECT col FROM test.tmp_list;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
        
        OPEN cur;
        testLoop: LOOP
          FETCH cur INTO var;
          IF done THEN
            LEAVE testLoop;
          END IF;
          
          SELECT var;
          
          SET @stmt = CONCAT('CREATE OR REPLACE VIEW test.v AS SELECT * FROM ', var);
          SELECT @stmt;
          PREPARE _sql FROM @stmt;
          EXECUTE _sql;
          DROP PREPARE _sql;
          
          -- should return 'AAA' on the first iteration and 'BBB' on the second
          -- returns 'AAA' on both iterations
          SELECT * FROM test.v;
       
          SHOW CREATE VIEW test.v;
          
        END LOOP testLoop;
        CLOSE cur;
       
        -- returns 'BBB' as expected
        SELECT * FROM test.v;
        SHOW CREATE VIEW test.v;
          
      END#
       
      DELIMITER ;
       
      CALL test.sp_testcase();
       
      DROP PROCEDURE test.sp_testcase;
      DROP TABLE test.tmp_list;
      DROP TABLE test.a;
      DROP TABLE test.b;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              wollewalda Wolfgang Walther
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.