Bug #33969 Updating a text field via a left join
Submitted: 21 Jan 2008 23:28 Modified: 6 Mar 2010 20:05
Reporter: Ryan Brothers Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.22-rc-community OS:Other (UBUNTU (64Bit), FreeBSD (64bit), RedHat)
Assigned to: Alexey Kopytov CPU Architecture:Any

[21 Jan 2008 23:28] Ryan Brothers
Description:
If I run a query to update a text field in a left join to more than 65,535 characters, MySQL does not throw an error or warning.

How to repeat:
SET @@session.sql_mode = 'TRADITIONAL';

DROP TABLE IF EXISTS test1;
DROP TABLE IF EXISTS test2;

CREATE TABLE test1 (
 id int not null
);

CREATE TABLE test2 (
 id int not null,
 description text not null
);

INSERT INTO test1 VALUES (1);
INSERT INTO test2 VALUES (1, '');

UPDATE test2 SET description = REPEAT('1', 70000) WHERE id = 1;
-- Error: ERROR 1406 (22001): Data too long for column 'description' at row 1

UPDATE test1
  LEFT JOIN test2 USING (id)
  SET test2.description = REPEAT('1', 70000)
  WHERE test1.id = 1;
-- No errors or warnings logged
[22 Jan 2008 4:16] Valeriy Kravchuk
Thank you for a problem report. For some reason I can not repeat the behaviour described:

C:\Program Files\MySQL\MySQL Server 5.0>bin\mysql -uroot -proot test -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.22-rc-community-debug MySQL Community Server - Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET @@session.sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.05 sec)

mysql> DROP TABLE IF EXISTS test1;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test1 (
    ->  id int not null
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE test2 (
    ->  id int not null,
    ->  description text not null
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO test1 VALUES (1);
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO test2 VALUES (1, '');
Query OK, 1 row affected (0.03 sec)

mysql> UPDATE test2 SET description = REPEAT('1', 70000) WHERE id = 1;
ERROR 1406 (22001): Data too long for column 'description' at row 1
mysql> UPDATE test1
    ->   LEFT JOIN test2 USING (id)
    ->   SET test2.description = REPEAT('1', 70000)
    ->   WHERE test1.id = 1;
ERROR 1406 (22001): Data too long for column 'description' at row 1
mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.1.22-rc-community-debug |
+---------------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E
RROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

Can you identify what is different in your case?
[22 Jan 2008 14:30] Ryan Brothers
Thanks for your help.  My @@sql_mode matches yours.  Could the character set be the reason for the difference in behavior?  My table was using latin1 since I didn't specify a character set in the create table statement.

I am running on CentOS 5 though I was also able to replicate this on a machine running CentOS 3 with MySQL 5.0.45.

Also, I noticed that if I select the length of the description field after my last UPDATE I only get 4464:

mysql> SELECT LENGTH(description) FROM test2;
+---------------------+
| LENGTH(description) |
+---------------------+
|                4464 |
+---------------------+
1 row in set (0.00 sec)
[28 Jan 2008 17:16] Susanne Ebrecht
It's not the character set.
I'll get the same result as Valeriy before, by using Latin1.

How did you install MySQL.

Did you use the packages/source from our download pages?
[28 Jan 2008 17:44] Ryan Brothers
Thanks for your help.  Yes, I am running the RPM's available for Red Hat 5 from http://dev.mysql.com/downloads/mysql/5.1.html:

MySQL-client-community-5.1.22-0.rhel5
MySQL-devel-community-5.1.22-0.rhel5
MySQL-server-community-5.1.22-0.rhel5
MySQL-shared-compat-5.1.22-0.rhel5

As a test, I just tried installing MySQL 5.1 for Windows and the 2nd UPDATE statement does cause an error as expected, so it seems that this problem is only occurring on Linux.  Were you running on Linux in your test?
[31 Jan 2008 14:17] Susanne Ebrecht
I can repeat this on FreeBSD with:

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-debug

mysql> CREATE TABLE test1 (id int not null);
mysql> CREATE TABLE test2 (id int not null, description text not null);
mysql> INSERT INTO test1 VALUES (1);
mysql> iNSERT INTO test2 VALUES (1, '');

mysql> UPDATE test2 SET description = REPEAT('1', 70000) WHERE id = 1;
ERROR 1406 (22001): Data too long for column 'description' at row 1

mysql> UPDATE test1 LEFT JOIN test2 USING (id) SET test2.description = REPEAT('1', 70000) WHERE test1.id = 1;
Query OK, 1 row affected (0.41 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select length(description) from test2\G
*************************** 1. row ***************************
length(description): 4464
[31 Jan 2008 14:20] Susanne Ebrecht
Also repeatable on FreeBSD 64bit with:

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.1.24-rc-debug

I'll try to get a deeper look.
[31 Jan 2008 14:41] Susanne Ebrecht
I also can repeat this on Ubuntu amd64.

I forgot, of course test mode was TRADITIONAL during all my tests:

mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

Maybe this is 64bit only.
[31 Jan 2008 15:27] Ryan Brothers
Thanks - I am running on all 32-bit machines, so it appears to affect both 32-bit and 64-bit.
[23 Apr 2008 5:53] 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/commits/45862

ChangeSet@1.2562, 2008-04-23 09:52:53+04:00, kaa@kaamos.(none) +4 -0
  Fix for bug #33969: Updating a text field via a left join
  
  When creating a temporary TEXT/BLOB field from an Item in
  Item::make_string_field(), the field's type was unconditionally set
  to the one corresponding to the maximum length (i.e. LONGTEXT/
  LONGBLOB). This resulted in problems when exactly the same TEXT/BLOB
  is type required in cases like CREATE ... SELECT or creating internal
  temporary tables for joins.
  
  Fixed by calling a different constructor for Field_blob so that an
  appropriate type is used depending on the Item's max_length value.
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[21 Nov 2009 17:31] 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/commits/91222

2898 Alexey Kopytov	2009-11-21
      Backport of the fix for bug #33969: Updating a text field via a
      left join 
      
      When creating a temporary TEXT/BLOB field from an Item in
      Item::make_string_field(), the field's type was unconditionally
      set to the one corresponding to the maximum length (i.e.
      LONGTEXT/ LONGBLOB). This resulted in problems when exactly the
      same TEXT/BLOB is type required in cases like CREATE ... SELECT
      or creating internal temporary tables for joins. 
      
      Fixed by calling a different constructor for Field_blob so that
      an appropriate type is used depending on the Item's max_length
      value.
[11 Dec 2009 6:02] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)
[11 Dec 2009 6:04] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[15 Dec 2009 3:17] Paul Dubois
Noted in 5.6.0, 6.0.6 changelogs.

Creation of a temporary BLOB or TEXT column could create a column 
with the wrong maximum length.
[6 Mar 2010 11:02] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[6 Mar 2010 20:05] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.