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

MySql - Bug 28227 - Cursor fetches null values into variables

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.27
    • None
    • None
    • AWS RDS - 10.4.26-MariaDB-log

    Description

      Also reported as mysql bug => https://bugs.mysql.com/bug.php?id=28227

      When using cursor/fetch procedure, the variables became empty when they have same name as table column name.

      The code below can reproduce it:
      Does not work => CUSTOMER_NAME variable
      Works fine => V_CUSTOMER_NAME variable

      CREATE TEMPORARY TABLE TMP_TEST1 (CUSTOMER_NAME varchar(255));
      INSERT INTO TMP_TEST1 (CUSTOMER_NAME) 
      VALUES ('JOHN'),('MIKE'),('SMITH');
       
      DELIMITER //
      BEGIN NOT ATOMIC
       
        DECLARE done INT DEFAULT FALSE;
        -- DECLARE CUSTOMER_NAME varchar(255); -- not works
        DECLARE V_CUSTOMER_NAME varchar(255); -- works
        DECLARE CUSTOMER_NAME_LIST text DEFAULT '';
        DECLARE cur1 CURSOR FOR SELECT CUSTOMER_NAME FROM TMP_TEST1;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        
        OPEN cur1;
        read_loop: LOOP
          -- FETCH cur1 INTO CUSTOMER_NAME; -- not works
          FETCH cur1 INTO V_CUSTOMER_NAME; -- works
          IF done THEN
            LEAVE read_loop;
          END IF;
          -- set CUSTOMER_NAME_LIST = CONCAT_WS('|',CUSTOMER_NAME_LIST,CUSTOMER_NAME); -- not works
          set CUSTOMER_NAME_LIST = CONCAT_WS('|',CUSTOMER_NAME_LIST,V_CUSTOMER_NAME); -- works
        END LOOP read_loop;
        CLOSE cur1;
       
        SELECT CUSTOMER_NAME_LIST;
        
      END; //
      DELIMITER ;
       
      DROP TEMPORARY TABLE TMP_TEST1;
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            cwlo Carlos Oliveira
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.