Details
Description
The problem is repeatable with at least MyISAM and InnoDB.
SET @@optimizer_trace='enabled=on'; |
|
|
CREATE OR REPLACE TABLE t1 ( |
a CHAR(2) NOT NULL PRIMARY KEY, |
b VARCHAR(20) NOT NULL, |
KEY (b) |
) ENGINE=InnoDB CHARSET=utf8mb4;
|
|
|
CREATE OR REPLACE TABLE t2 ( |
a CHAR(2) NOT NULL PRIMARY KEY, |
b VARCHAR(20) NOT NULL, |
KEY (b) |
) ENGINE=InnoDB CHARSET=utf8mb4;
|
|
|
INSERT INTO t1 VALUES |
('AB','MySQLAB'), |
('JA','Sun Microsystems'), |
('MS','Microsoft'), |
('IB','IBM- Inc.'), |
('GO','Google Inc.'); |
|
|
INSERT IGNORE INTO t2 VALUES |
('AB','Sweden'), |
('JA','USA'), |
('MS','United States of America'), |
('IB','North America'), |
('GO','South America'); |
|
|
UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%'; |
ERROR 1406 (22001): Data too long for column 'b' at row 1
|
The above error is not expected.
Additionally, in some versions (at least 11.3 and higher), warnings are produced in a simpler script:
SET @@optimizer_trace='enabled=on'; |
|
|
CREATE OR REPLACE TABLE t1 ( |
a CHAR(2) NOT NULL PRIMARY KEY, |
b VARCHAR(20) NOT NULL, |
KEY (b) |
) ENGINE=InnoDB CHARSET=utf8mb4;
|
|
|
INSERT INTO t1 VALUES |
('AB','MySQLAB'), |
('JA','Sun Microsystems'), |
('MS','Microsoft'), |
('IB','IBM- Inc.'), |
('GO','Google Inc.'); |
|
|
UPDATE t1 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%'; |
+---------+------+----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+----------------------------------------+
|
| Warning | 1265 | Data truncated for column 'b' at row 1 |
|
| Warning | 1265 | Data truncated for column 'b' at row 1 |
|
| Warning | 1265 | Data truncated for column 'b' at row 1 |
|
| Warning | 1265 | Data truncated for column 'b' at row 1 |
|
+---------+------+----------------------------------------+
|
These warnings are not expected.
The problem seems to be in the fact that the call for like_range() in Item_func_like::get_mm_leaf() produces more characters than the size of the index.
The patch below fixes the problem, but the real patch should probably fix like_range() not to return more characters than the size of the index.
index 5793b19f695..108b4ac78ae 100644
|
--- a/sql/opt_range.cc
|
+++ b/sql/opt_range.cc
|
@@ -9174,6 +9174,19 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
|
&min_length, &max_length))
|
DBUG_RETURN(0); // Can't optimize with LIKE |
|
+ min_length= Well_formed_prefix(field->charset(),
|
+ (const char *) min_str + offset, min_length, |
+ field->char_length()).length();
|
+ max_length= Well_formed_prefix(field->charset(),
|
+ (const char *) max_str + offset, max_length, |
+ field->char_length()).length();
|
if (offset != maybe_null) // BLOB or VARCHAR |
{
|
int2store(min_str + maybe_null, min_length);
|
|
Attachments
Issue Links
- blocks
-
MDEV-19123 Change default charset from latin1 to utf8mb4
-
- Closed
-
Also repeatable with the TINYTEXT data type:
SET @@optimizer_trace='enabled=on';CREATE OR REPLACE TABLE t1 (a CHAR(2) NOT NULL PRIMARY KEY,b TINYTEXT NOT NULL,KEY (b(255))) ENGINE=InnoDB CHARSET=utf8mb4;CREATE OR REPLACE TABLE t2 (a CHAR(2) NOT NULL PRIMARY KEY,b TINYTEXT NOT NULL,KEY (b(255))) ENGINE=InnoDB CHARSET=utf8mb4;INSERT INTO t1 VALUES('AB','MySQLAB'),('JA','Sun Microsystems'),('MS','Microsoft'),('IB','IBM- Inc.'),('GO','Google Inc.');INSERT IGNORE INTO t2 VALUES('AB','Sweden'),('JA','USA'),('MS','United States of America'),('IB','North America'),('GO','South America');UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';ERROR 1406 (22001): Data too long for column 'b' at row 1