Bug #9103 Erroneous data truncation warnings on multi-table updates
Submitted: 10 Mar 2005 20:00 Modified: 28 Apr 2005 15:59
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.11 OS:
Assigned to: Sergey Petrunya CPU Architecture:Any

[10 Mar 2005 20:00] Dean Ellis 
Description:
Multi-table updates which join across columns with prefix indexes can produce data truncation warnings though these columns are not modified by the update.

How to repeat:
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a INT, b VARCHAR(10), KEY b ( b(5) ) ) ENGINE=MyISAM;
CREATE TABLE t2 ( a INT, b VARCHAR(10) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES ( 1, 'abcd1e' );
INSERT INTO t1 VALUES ( 2, 'abcd2e' );
INSERT INTO t2 VALUES ( 1, 'abcd1e' );
INSERT INTO t2 VALUES ( 2, 'abcd2e' );
UPDATE t1, t2 SET t1.a = t2.a WHERE t2.b = t1.b;
REPAIR TABLE t1, t2;
UPDATE t1, t2 SET t1.a = t2.a WHERE t2.b = t1.b;
SHOW WARNINGS;
DROP TABLE t1, t2;

Suggested fix:
n/a
[18 Apr 2005 1:17] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24095
[20 Apr 2005 8:06] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24161
[20 Apr 2005 8:44] Sergey Petrunya 
Pushed into 4.1.12 and 5.0.5 trees.
[28 Apr 2005 15:59] Paul DuBois 
Noted in 4.1.12, 5.0.5 changelogs.