[MDEV-17226] Column Data in Truncated on UNION to the length of the first value if using REPLACE Created: 2018-09-18  Updated: 2023-12-11  Resolved: 2023-12-11

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.5.9

Type: Bug Priority: Major
Reporter: Robert Humphries Assignee: Alexander Barkov
Resolution: Fixed Votes: 3
Labels: upstream
Environment:

Tested on:
Windows 10 64bit
Ubuntu 16.04.5 LTS

Issue Links:
Relates
relates to MDEV-22923 Data Truncation when using UNION SELECT Open

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



 Comments   
Comment by Elena Stepanova [ 2018-10-03 ]

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).

Comment by Alexander Barkov [ 2019-08-06 ]

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.

Comment by Alexander Barkov [ 2019-08-06 ]

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 |
 
+-------+-------------------------------------------------------------------------------------------+

Comment by Alexander Barkov [ 2023-12-08 ]

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()

Generated at Thu Feb 08 08:34:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.