From b5d39ff5e7c4fb7d767f0d10f653e40fceca6e7d Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 5 Mar 2022 13:50:31 -0800 Subject: [PATCH] Fix bug in REPLACE() in UNION truncating result Item_func_replace::resolve_type() incorrectly calculates char_length, which propagates to max_length and truncates the result. Conditions: UNION query Longest field (whether physical field, string literal, or the forumla "char_length * (replace_length - 1ULL)" in the "resolve_type()" function ) determines the max_size for all values in the result column. So if a REPLACE() extends string length beyond that size, it will be truncated. e.g. by the old formula, using the query below, the char_length for the REPLACE = 20, and the result string is 19 characters long, so it is not truncated. char_length = char_length * (replace_length - 1ULL) 20 = 4 * (6-1) SELECT REPLACE('aaab','a','123456') as 'L19' UNION SELECT REPEAT('a',12); +---------------------+ | L19 | +---------------------+ | 123456123456123456b | | aaaaaaaaaaaa | +---------------------+ // NOT TRUNCATED But with the following, the result string is 24 characters long and will be truncated to 20. SELECT REPLACE('aaaa','a','123456') as 'L24' UNION SELECT REPEAT('a',12); +----------------------+ | L24 | +----------------------+ | 12345612345612345612 | | aaaaaaaaaaaa | +----------------------+ // 4 CHARS TRUNCATED And, finally, if we increase the length of the other part of the UNION to 24, then we will not see any truncation. SELECT REPLACE('aaaa','a','123456') as 'L24' UNION SELECT REPEAT('a',24); +--------------------------+ | L24 | +--------------------------+ | 123456123456123456123456 | | aaaaaaaaaaaaaaaaaaaaaaaa | +--------------------------+ // REPEAT() EXTENDS SIZE to 24, REPLACE() NOT TRUNCATED ************* **SOLUTIONS** ************* **1.)** match exact length of output string, using division: char_length = char_length / search_length * (replace_length - search_length) + char_length; 2.) Just remove "- 1ULL" from original formula: char_length = char_length * replace_length 3.) adjust args[0]->max_char_length somehow --- mysql-test/r/func_str.result | 22 ++++++++++++++++++++++ mysql-test/t/func_str.test | 13 +++++++++++++ sql/item_strfunc.cc | 9 +++++++-- 3 files changed, 42 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 3d25f48cf00b..653b300979b5 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -5617,3 +5617,25 @@ LPAD(c, 3, 'x') NULL pad DROP TABLE t1; +# Bug#XXXXXX: REPLACE in UNION - result truncated +# when short string on either side of union +# 'short string' can be literal +SELECT 'X' UNION ALL SELECT REPLACE('a','a','ZzZ'); +X +X +ZzZ +# 'short string' can be first argument of REPLACE (both truncated) +SELECT REPLACE('a','a','Zz') AS 'Zz' UNION ALL SELECT REPLACE('X','X','XX'); +Zz +Zz +XX +# other strfuncs +SELECT REPLACE('a','a','Zz') AS 'Zz' UNION ALL SELECT CONCAT('a'); +Zz +Zz +a +SELECT REPLACE('aaa','a','999') AS '9x9' UNION ALL SELECT CONCAT('a','a'); +9x9 +999999999 +aa +# (also affects column data types varchar, char, enum, ?) diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index b9de7b9867fe..d4f4d2d2b9a9 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -2429,3 +2429,16 @@ SELECT LPAD(c, 4, 'x') FROM t1; SELECT LPAD(c, 3, 'x') FROM t1; DROP TABLE t1; + + +--echo # Bug#XXXXXX: REPLACE in UNION - result truncated +--echo # when short string on either side of union + +--echo # 'short string' can be literal +SELECT 'X' UNION ALL SELECT REPLACE('a','a','ZzZ'); +--echo # 'short string' can be first argument of REPLACE (both truncated) +SELECT REPLACE('a','a','Zz') AS 'Zz' UNION ALL SELECT REPLACE('X','X','XX'); +--echo # other strfuncs +SELECT REPLACE('a','a','Zz') AS 'Zz' UNION ALL SELECT CONCAT('a'); +SELECT REPLACE('aaa','a','999') AS '9x9' UNION ALL SELECT CONCAT('a','a'); +--echo # (also affects column data types varchar, char, enum, ?) diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index 2b681e86f08f..1f6557989a78 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -1165,10 +1165,15 @@ bool Item_func_replace::resolve_type(THD *thd) { if (simplify_string_args(thd, collation, args + 1, 1)) return true; ulonglong char_length = args[0]->max_char_length(collation.collation); + ulonglong search_length = args[1]->max_char_length(collation.collation); ulonglong replace_length = args[2]->max_char_length(collation.collation); - if (replace_length > 1ULL) { - char_length = char_length * (replace_length - 1ULL); + if (replace_length > 1ULL && search_length >= 1ULL) { + // instead of calc only from replace_length, use search_length as well + // no "/": "char_length = char_length * replace_length" + // with "/" less memory, slower processor? // nick belyavski + char_length = char_length / search_length * (replace_length - search_length) + + char_length; } set_data_type_string(char_length);