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`;
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`;