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
-
Activity
Repeatable as described:
CREATE OR REPLACE TABLE t1 ( |
id INT UNSIGNED NOT NULL AUTO_INCREMENT, |
col1 VARCHAR (2), |
col2 VARCHAR (2), |
PRIMARY KEY (id) |
);
|
INSERT INTO t1 (col1, col2) VALUES ('a', 'ba'); |
SELECT REPLACE('a', col1, col2) FROM t1; |
+--------------------------+
|
| REPLACE('a', col1, col2) |
|
+--------------------------+
|
| ba |
|
+--------------------------
|
SELECT 'a' |
UNION ALL |
SELECT REPLACE('a', col1, col2) FROM t1; |
+------+
|
| a |
|
+------+
|
| a |
|
| b |
|
+------+
|
Notice, data in the second SELECT query was truncated.
It seems REPLACE() does not calculate its max length well. This script demonstrates the problem:
SET sql_mode='TRADITIONAL'; |
CREATE OR REPLACE TABLE t1 ( |
id INT UNSIGNED NOT NULL AUTO_INCREMENT, |
col1 VARCHAR (2), |
col2 VARCHAR (2), |
PRIMARY KEY (id) |
);
|
INSERT INTO t1 (col1, col2) VALUES ('a', 'ba'); |
SELECT REPLACE('a', col1, col2) FROM t1; |
CREATE OR REPLACE TABLE t2 AS SELECT REPLACE('a', col1, col2) AS c1 FROM t1; |
ERROR 1406 (22001): Data too long for column 'c1' at row 1
|
CREATE OR REPLACE TABLE t2 AS SELECT REPLACE('a', col1, col2) AS c1 FROM t1 LIMIT 0; |
SHOW CREATE TABLE t2; |
+-------+-------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c1` varchar(1) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------+
|
This problem was fixed in 10.5 by:
commit 55b27888005083d30339d6f3a2aee034121d8693
|
Author: Monty <monty@mariadb.org>
|
Date: Mon Nov 9 20:57:27 2020 +0200
|
|
|
Fixed length estimate for REPLACE()
|
Thanks for the report.
Assigning to bar to decide what and in which version needs to be done (please feel free to modify Fix version if needed).