Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
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
Issue Links
- relates to
-
-
- Open
-