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

Local variable TEXT has mangled data in stored procedure

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.22, 10.0, 10.1
    • Fix Version/s: 10.0.26
    • Labels:
      None
    • Environment:
      CentOS release 6.4
      Server version: 5.5.5-10.1.8-MariaDB MariaDB Server
    • Sprint:
      10.0.24

      Description

      I created a small test case to reproduce the problem, which seems to be this: When I have a table with a TEXT column, and when I have a stored procedure that SELECTs that TEXT column into a local TEXT variable, the content of the field is mangled. In the example below, the LOCAL_TEXT variable should contain "quick brown fox". When I select the field into a VARCHAR(45), it retains the value exactly. This problem did not exist prior to this version of MariaDB.

      -Thank you

      Here is the result of invoking RunTest():

      mysql> call RunTest();
      +-----------------+-----------------+-----------------+
      | LOCAL TEXT      | LOCAL VARCHAR   | A1_TEXT         |
      +-----------------+-----------------+-----------------+
      |      brown fox | quick brown fox | quick brown fox |
      +-----------------+-----------------+-----------------+
      1 row in set (0.04 sec)
       
      Query OK, 0 rows affected (0.04 sec)}}

      The test.sql code is below. Into the table, I inserted the value "quick brown fox" to column A1_TEXT.

      cat test.sql
      -- -----------------------------------------------------
      -- Table `table1`
      -- -----------------------------------------------------
      CREATE TABLE IF NOT EXISTS `table1` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `A1_TEXT` TEXT NULL,
        PRIMARY KEY (`id`))
      ENGINE = InnoDB;
       
      DELIMITER $$
      DROP PROCEDURE IF EXISTS `RunTest`;
      CREATE PROCEDURE `RunTest`()
      BEGIN
          DECLARE L_A1_TEXT TEXT DEFAULT NULL;
          DECLARE L_A1_VARC VARCHAR(45) DEFAULT NULL;
       
          SELECT table1.A1_TEXT INTO L_A1_TEXT FROM table1 where id=1;
          SELECT table1.A1_TEXT INTO L_A1_VARC FROM table1 where id=1;
       
          SELECT
              L_A1_TEXT as 'LOCAL TEXT',
              L_A1_VARC as 'LOCAL VARCHAR',
              table1.A1_TEXT
          FROM table1;
      END
      $$
      DELIMITER ;

        Attachments

        1. my.cnf
          0.2 kB
        2. test.sql
          0.8 kB

          Issue Links

            Activity

              People

              • Assignee:
                cvicentiu Vicentiu Ciorbaru
                Reporter:
                pompidou K WALKER
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: