MySQL Bugs: #36676: UPDATE on TEXT column using LEFT JOIN doesn't update all rows

Bug #36676	UPDATE on TEXT column using LEFT JOIN doesn't update all rows
Submitted:	12 May 2008 22:05	Modified:	22 May 2008 12:41
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.58	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
When performing a multi-table UPDATE of TEXT columns with a LEFT JOIN, not all rows are updated.

mysql> drop table if exists one;
Query OK, 0 rows affected (0.00 sec)

mysql> create table one ( id int ) engine = myisam;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into one values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> drop table if exists two;
Query OK, 0 rows affected (0.00 sec)

mysql> create table two ( id int, s1 text, s2 text, s3 varchar(10), s4 text, s5
varchar(10) ) engine = myisam;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into two values (1,'test', 'test', 'test', 'test', 'test'),(2,'tes
t', 'test', 'test', 'test', 'test');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from one left join two using(id);
+------+------+------+------+------+------+
| id   | s1   | s2   | s3   | s4   | s5   |
+------+------+------+------+------+------+
|    1 | test | test | test | test | test |
|    2 | test | test | test | test | test |
+------+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> update one left join two using(id) set s1 = 'changed';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update one join two using(id) set s2 = 'changed';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update one left join two using(id) set s3 = 'changed';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update one left join two using(id) set s4 = 'changed', s5 = 'changed';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from one left join two using(id);
+------+---------+---------+---------+---------+---------+
| id   | s1      | s2      | s3      | s4      | s5      |
+------+---------+---------+---------+---------+---------+
|    1 | changed | changed | changed | changed | changed |
|    2 | test    | changed | changed | test    | test    |
+------+---------+---------+---------+---------+---------+
2 rows in set (0.00 sec)

How to repeat:
drop table if exists one;
create table one ( id int ) engine = myisam;
insert into one values (1),(2);

drop table if exists two;
create table two ( id int, s1 text, s2 text, s3 varchar(10), s4 text, s5 varchar(10) ) engine = myisam;
insert into two values (1,'test', 'test', 'test', 'test', 'test'),(2,'test', 'test', 'test', 'test', 'test');

select * from one left join two using(id);

update one left join two using(id) set s1 = 'changed';
update one join two using(id) set s2 = 'changed';
update one left join two using(id) set s3 = 'changed';
update one left join two using(id) set s4 = 'changed', s5 = 'changed';

select * from one left join two using(id); 

Suggested fix:
Update rows correctly.

This bug also can appear in a weird error message:

  ERROR 1022 (23000): Can't write; duplicate key in table ''

even if updating table has no blobs (TEXTs).

How to repeat:

1. set max hash table size to small value (to experiment with not very large tables): --max_heap_table_size=1

  ../sql/mysqld --no-defaults --basedir=. --datadir=./var/master-data
     --language=../sql/share/english/
     --character-sets-dir=../sql/share/charsets/ --gdb  --skip-grant-tables
     --max_heap_table_size=1

2.
CREATE TABLE one (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE two (id INT, s1 CHAR(255));

# insert [1..64] into table `one`
INSERT INTO one VALUES (0), (0), (0), (0), (0), (0), (0), (0);
INSERT INTO one (SELECT 0 FROM one);
INSERT INTO one (SELECT 0 FROM one);
INSERT INTO one (SELECT 0 FROM one);

INSERT INTO two (SELECT ID, 'a' FROM one);

UPDATE one LEFT JOIN two USING(id) SET s1 = 'b';

========================================================

Server version: 5.0.62-debug Source distribution

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

mysql> CREATE TABLE one (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE two (id INT, s1 CHAR(255));
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> # insert [1..64] into table `one`
mysql> INSERT INTO one VALUES (0), (0), (0), (0), (0), (0), (0), (0);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

[skip]

mysql> INSERT INTO one (SELECT 0 FROM one);
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO two (SELECT ID, 'a' FROM one);
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> UPDATE one LEFT JOIN two USING(id) SET s1 = 'b';
ERROR 1022 (23000): Can't write; duplicate key in table ''

##########################################################
# At the same time:
##########################################################

mysql> DELETE FROM two WHERE id>50;
Query OK, 13 rows affected (0.00 sec)

mysql> UPDATE one LEFT JOIN two USING(id) SET s1 = 'b';
Query OK, 50 rows affected (0.01 sec)
Rows matched: 50  Changed: 50  Warnings: 0

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/46808

ChangeSet@1.2625, 2008-05-18 05:36:20+05:00, gshchepa@host.loc +4 -0
  Fixed bug#36676: multiupdate using LEFT JOIN updates only
                   first row or fails with an error:
    ERROR 1022 (23000): Can't write; duplicate key in table ''
  
  The server uses intermediate temporary table to store updated
  row data.  The first column of this table contains rowid.
  Current server implementation doesn't reset NULL flag of that
  column even if the server fills a column with rowid.
  To keep each rowid unique, there is an unique index.
  An insertion into an unique index takes into account NULL
  flag of key value and ignores real data if NULL flag is set.
  So, insertion of actually different rowids may lead to two
  kind of problems.  Visible effect of each of these problems
  depends on an initial engine type of temporary table:
  
  1. If multiupdate initially creates temporary table as
  a MyISAM table (a table contains blob columns, and the
  create_tmp_table function assumes, that this table is
  large), it inserts only one single row and updates
  only rows with one corresponding rowid. Other rows are
  silently ignored. 
  
  2. If multiupdate initially creates MEMORY temporary
  table, fills it with data and reaches size limit for
  MEMORY tables (max_heap_table_size), multiupdate
  converts MEMORY table into MyISAM table and fails
  with and error;
    ERROR 1022 (23000): Can't write; duplicate key in table ''
  
  
  Multiupdate has been fixed to update the NULL flag of a
  temporary table rowid columns.

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/46811

ChangeSet@1.2625, 2008-05-18 13:32:50+05:00, gshchepa@host.loc +4 -0
  Fixed bug#36676: multiupdate using LEFT JOIN updates only
                   first row or fails with an error:
    ERROR 1022 (23000): Can't write; duplicate key in table ''
  
  The server uses intermediate temporary table to store updated
  row data.  The first column of this table contains rowid.
  Current server implementation doesn't reset NULL flag of that
  column even if the server fills a column with rowid.
  To keep each rowid unique, there is an unique index.
  An insertion into an unique index takes into account NULL
  flag of key value and ignores real data if NULL flag is set.
  So, insertion of actually different rowids may lead to two
  kind of problems.  Visible effect of each of these problems
  depends on an initial engine type of temporary table:
  
  1. If multiupdate initially creates temporary table as
  a MyISAM table (a table contains blob columns, and the
  create_tmp_table function assumes, that this table is
  large), it inserts only one single row and updates
  only rows with one corresponding rowid. Other rows are
  silently ignored. 
  
  2. If multiupdate initially creates MEMORY temporary
  table, fills it with data and reaches size limit for
  MEMORY tables (max_heap_table_size), multiupdate
  converts MEMORY table into MyISAM table and fails
  with an error:
    ERROR 1022 (23000): Can't write; duplicate key in table ''
  
  
  Multiupdate has been fixed to update the NULL flag of
  temporary table rowid columns.

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/46812

ChangeSet@1.2625, 2008-05-18 14:21:25+05:00, gshchepa@host.loc +4 -0
  Fixed bug#36676: multiupdate using LEFT JOIN updates only
                   first row or fails with an error:
    ERROR 1022 (23000): Can't write; duplicate key in table ''
  
  The server uses intermediate temporary table to store updated
  row data.  The first column of this table contains rowid.
  Current server implementation doesn't reset NULL flag of that
  column even if the server fills a column with rowid.
  To keep each rowid unique, there is an unique index.
  An insertion into an unique index takes into account NULL
  flag of key value and ignores real data if NULL flag is set.
  So, insertion of actually different rowids may lead to two
  kind of problems.  Visible effect of each of these problems
  depends on an initial engine type of temporary table:
  
  1. If multiupdate initially creates temporary table as
  a MyISAM table (a table contains blob columns, and the
  create_tmp_table function assumes, that this table is
  large), it inserts only one single row and updates
  only rows with one corresponding rowid. Other rows are
  silently ignored. 
  
  2. If multiupdate initially creates MEMORY temporary
  table, fills it with data and reaches size limit for
  MEMORY tables (max_heap_table_size), multiupdate
  converts MEMORY table into MyISAM table and fails
  with an error:
    ERROR 1022 (23000): Can't write; duplicate key in table ''
  
  
  Multiupdate has been fixed to update the NULL flag of
  temporary table rowid columns.

pushed in -bugteam

Pushed into 5.0.64

Pushed into 5.1.25-rc

See also BUG#31379

Also UPDATE of VIEW with OUTER-joined tables is affected by this bug.

Noted in 5.0.64, 5.1.25 changelogs.

Multiple-table UPDATE statements that used a temporary table could
fail to update all qualifying rows or fail with a spurious
duplicate-key error. 

Setting report to Need Doc Info pending push into 6.0.x.

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

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/47145

Pushed into 5.0.64  (revid:sp1r-gshchepa/uchum@host.loc-20080518092717-10710) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)

Pushed into 5.1.25-rc  (revid:sp1r-gshchepa/uchum@host.loc-20080518092744-14339) (version source revid:sp1r-gshchepa/uchum@host.loc-20080518092744-14339) (pib:3)