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

Column Data in Truncated on UNION to the length of the first value if using REPLACE

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
    • Fix Version/s: 10.3, 10.4
    • Component/s: Data types
    • Labels:
    • Environment:
      Tested on:
      Windows 10 64bit
      Ubuntu 16.04.5 LTS

      Description

      When you do a UNION, where at least one of the queries has a replace on the value SELECTed, then the returned values are truncated to the length of the smallest value. This only seems to occur when the length of the columns being selected from the table are equal.

      Please see the test script for some examples. For each query, if you run the query that is truncated as a simple query (instead of in the union) it returns the correct result.

      This is an issue in MySQL version 8 as well.

      CREATE TABLE `t1` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `col1` VARCHAR (2),
        `col2` VARCHAR (2),
        PRIMARY KEY (`id`)
      );
       
      CREATE TABLE `t2` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `col1` VARCHAR (1),
        `col2` VARCHAR (2),
        PRIMARY KEY (`id`)
      );
       
      INSERT INTO `t1` (`col1`, `col2`) VALUES ("a", "ba");
      INSERT INTO `t2` (`col1`, `col2`) VALUES ("a", "ba");
       
      SELECT "a"
      UNION ALL
      SELECT REPLACE("a", `col1`, `col2`) FROM `t1`;
      /* Result:
      a
      -
      a
      b
      */
       
      SELECT "a"
      UNION ALL
      SELECT REPLACE("a", `col1`, `col2`) FROM `t2`;
      /* Result:
      a
      -
      a
      ba
      */
       
      SELECT REPLACE("z", `col1`, `col2`) FROM `t1`
      UNION ALL
      SELECT REPLACE("a", `col1`, `col2`) FROM `t1`;
      /* Result:
      REPLACE("z", `col1`, `col2`)
      -
      z
      b
      */
       
      SELECT REPLACE("z", `col1`, `col2`) FROM `t2`
      UNION ALL
      SELECT REPLACE("a", `col1`, `col2`) FROM `t2`;
      /* Result:
      REPLACE("z", `col1`, `col2`)
      -
      z
      ba
      */
       
      DROP TABLE `t1`, `t2`;
      

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              rmhumphries Robert Humphries
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: