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

Local variable TEXT has mangled data in stored procedure

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.22, 10.0, 10.1
    • 10.0.26
    • None
    • CentOS release 6.4
      Server version: 5.5.5-10.1.8-MariaDB MariaDB Server
    • 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

              cvicentiu Vicențiu Ciorbaru
              pompidou K WALKER
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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