Bug #92482 Column Data in Truncated on UNION to the len of the first value if using REPLACE
Submitted: 18 Sep 2018 13:33 Modified: 19 Sep 2018 15:02
Reporter: Robert Humphries Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.12 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:x86

[18 Sep 2018 13:33] Robert Humphries
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.

How to repeat:
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`;
[19 Sep 2018 15:02] MySQL Verification Team
thanks for the report
Bogdan
[7 Nov 2018 19:06] Dag Wanvik
Posted by developer:
 
This issue can be seen also in 5.7.