From: Jorgen Loland
Date: June 9 2011 10:35am
Subject: bzr commit into mysql-trunk branch (jorgen.loland:3172) Bug#11882110
List-Archive: http://lists.mysql.com/commits/138937
X-Bug: 11882110
Message-Id: <20110609103535.584C479C@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1108975737490705489=="

--===============1108975737490705489==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///export/home/jl208045/mysql/mysql-trunk-11882110/ based on revid:alexander.nozdrin@stripped

 3172 Jorgen Loland	2011-06-09
      BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
                    UPDATED TWICE
      
      For multi update it is not allowed to update certain columns
      of a table if that table is accessed through multiple aliases
      if either
      1) one of the updated columns is used as partitioning key
      2) one of the updated columns is part of the primary key 
         and the primary key is clustered
      
      This check is done in unsafe_key_update().
      
      The bug was that for case 2), it was checked whether
      updated_column_number == table_share->primary_key 
      However, the primary_key variable is the index number of the 
      primary key, not a column number.
      
      The columns covered by an index is found in 
      table->key_info[idx_number]->key_part. The bugfix is to check
      if any of the columns in the keyparts of the primary key is 
      updated.
     @ mysql-test/r/multi_update.result
        Add test for bug#11882110
     @ mysql-test/r/multi_update_innodb.result
        Add test for bug#11882110
     @ mysql-test/t/multi_update.test
        Add test for bug#11882110
     @ mysql-test/t/multi_update_innodb.test
        Add test for bug#11882110
     @ sql/sql_update.cc
        unsafe_key_update() wrongly checked if the primary key index number was the same as updated column number. Now it is checked whether any of the columns making up the primary key is updated.
     @ sql/table.h
        Remove comment that has been introduced by a merge conflict (as per dlenev)

    modified:
      mysql-test/r/multi_update.result
      mysql-test/r/multi_update_innodb.result
      mysql-test/t/multi_update.test
      mysql-test/t/multi_update_innodb.test
      sql/sql_update.cc
      sql/table.h
=== modified file 'mysql-test/r/multi_update.result'
--- a/mysql-test/r/multi_update.result	2011-03-08 19:14:42 +0000
+++ b/mysql-test/r/multi_update.result	2011-06-09 10:35:26 +0000
@@ -697,4 +697,40 @@ SELECT * FROM t1;
 pk	a
 1	2
 DROP TABLE t1;
-end of tests
+#
+# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
+#               UPDATED TWICE
+#
+CREATE TABLE t1 ( 
+col_int_key int, 
+pk int, 
+col_int int, 
+key(col_int_key), 
+primary key (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,2,3);
+
+CREATE TABLE t2 ( 
+col_int_key int, 
+pk_1 int, 
+pk_2 int, 
+col_int int, 
+key(col_int_key), 
+primary key (pk_1,pk_2)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+
+SELECT * FROM t1;
+col_int_key	pk	col_int
+1	7	3
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+
+SELECT * FROM t2;
+col_int_key	pk_1	pk_2	col_int
+1	7	11	4
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/multi_update_innodb.result'
--- a/mysql-test/r/multi_update_innodb.result	2011-02-21 15:31:41 +0000
+++ b/mysql-test/r/multi_update_innodb.result	2011-06-09 10:35:26 +0000
@@ -27,3 +27,43 @@ pk	a	b
 0	1	2
 DROP VIEW v1;
 DROP TABLE t1;
+#
+# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
+#               UPDATED TWICE
+#
+CREATE TABLE t1 ( 
+col_int_key int, 
+pk int, 
+col_int int, 
+key(col_int_key), 
+primary key (pk)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2,3);
+
+CREATE TABLE t2 ( 
+col_int_key int, 
+pk_1 int, 
+pk_2 int, 
+col_int int, 
+key(col_int_key), 
+primary key (pk_1,pk_2)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+SELECT * FROM t1;
+col_int_key	pk	col_int
+1	2	3
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+SELECT * FROM t2;
+col_int_key	pk_1	pk_2	col_int
+1	2	3	4
+DROP TABLE t1,t2;

=== modified file 'mysql-test/t/multi_update.test'
--- a/mysql-test/t/multi_update.test	2011-03-08 19:14:42 +0000
+++ b/mysql-test/t/multi_update.test	2011-06-09 10:35:26 +0000
@@ -703,4 +703,49 @@ UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.
 SELECT * FROM t1;
 DROP TABLE t1;
 
---echo end of tests
+--echo #
+--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
+--echo #               UPDATED TWICE
+--echo #
+
+# Results differ between storage engines.
+# See multi_update_innodb.test for the InnoDB variant of this test
+CREATE TABLE t1 ( 
+  col_int_key int, 
+  pk int, 
+  col_int int, 
+  key(col_int_key), 
+  primary key (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,2,3);
+
+--echo
+CREATE TABLE t2 ( 
+  col_int_key int, 
+  pk_1 int, 
+  pk_2 int, 
+  col_int int, 
+  key(col_int_key), 
+  primary key (pk_1,pk_2)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+--echo
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+
+--echo
+SELECT * FROM t1;
+
+--echo
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+--echo
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+
+--echo
+SELECT * FROM t2;
+
+DROP TABLE t1,t2;
+
+
+
+

=== modified file 'mysql-test/t/multi_update_innodb.test'
--- a/mysql-test/t/multi_update_innodb.test	2011-02-21 15:31:41 +0000
+++ b/mysql-test/t/multi_update_innodb.test	2011-06-09 10:35:26 +0000
@@ -31,3 +31,47 @@ SELECT * FROM t1;
 DROP VIEW v1;
 DROP TABLE t1;
 
+--echo #
+--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
+--echo #               UPDATED TWICE
+--echo #
+
+# Results differ between storage engines.
+# See multi_update.test for the MyISAM variant of this test
+CREATE TABLE t1 ( 
+  col_int_key int, 
+  pk int, 
+  col_int int, 
+  key(col_int_key), 
+  primary key (pk)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2,3);
+
+--echo
+CREATE TABLE t2 ( 
+  col_int_key int, 
+  pk_1 int, 
+  pk_2 int, 
+  col_int int, 
+  key(col_int_key), 
+  primary key (pk_1,pk_2)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+--echo
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+--echo
+SELECT * FROM t1;
+
+--echo
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+--echo
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+
+--echo
+SELECT * FROM t2;
+
+DROP TABLE t1,t2;

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2011-06-09 08:58:41 +0000
+++ b/sql/sql_update.cc	2011-06-09 10:35:26 +0000
@@ -1069,17 +1069,27 @@ bool unsafe_key_update(TABLE_LIST *leave
             return true;
           }
 
-          if (primkey_clustered &&
-              (bitmap_is_set(table1->write_set, table1->s->primary_key) ||
-               bitmap_is_set(table2->write_set, table2->s->primary_key)))
-          {
-            // Clustered primary key is updated
-            my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0),
-                     tl->belong_to_view ? tl->belong_to_view->alias
-                                        : tl->alias,
-                     tl2->belong_to_view ? tl2->belong_to_view->alias
-                                         : tl2->alias);
-            return true;
+          if (primkey_clustered)
+          { 
+            // The primary key can cover multiple columns
+            KEY key_info= table1->key_info[table1->s->primary_key];
+            KEY_PART_INFO *key_part= key_info.key_part;
+            KEY_PART_INFO *key_part_end= key_part + key_info.key_parts;
+
+            for (;key_part != key_part_end; ++key_part)
+            {
+              if (bitmap_is_set(table1->write_set, key_part->fieldnr-1) ||
+                  bitmap_is_set(table2->write_set, key_part->fieldnr-1))
+              {
+                // Clustered primary key is updated
+                my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0),
+                         tl->belong_to_view ? tl->belong_to_view->alias
+                         : tl->alias,
+                         tl2->belong_to_view ? tl2->belong_to_view->alias
+                         : tl2->alias);
+                return true;
+              }
+            }
           }
         }
       }

=== modified file 'sql/table.h'
--- a/sql/table.h	2011-05-26 15:20:09 +0000
+++ b/sql/table.h	2011-06-09 10:35:26 +0000
@@ -669,7 +669,6 @@ struct TABLE_SHARE
   uint db_options_in_use;		/* Options in use */
   uint db_record_offset;		/* if HA_REC_IN_SEQ */
   uint rowid_field_offset;		/* Field_nr +1 to rowid field */
-  /* Index of auto-updated TIMESTAMP field in field array */
   uint primary_key;
   uint next_number_index;               /* autoincrement key number */
   uint next_number_key_offset;          /* autoinc keypart offset in a key */


--===============1108975737490705489==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/jorgen.loland@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jorgen.loland@stripped\
#   2j01hg1e5sg9k0pw
# target_branch: file:///export/home/jl208045/mysql/mysql-trunk-\
#   11882110/
# testament_sha1: 1be1c8e3e7d0ad36438f0a5e886f321f829d3b23
# timestamp: 2011-06-09 12:35:35 +0200
# base_revision_id: alexander.nozdrin@stripped\
#   06lrupk3f8wnz9ps
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSqz4FIACIJ/gFF9AgB4////
///fqr////5gD919vs1sY6O7QjSVVSSZ3AADouxtm60KVdMypmNamBXhohKe0mTU9qT0jymajynl
MjTQM1NDQAAAAANCFQyDEBoDRoaA0AaNDRiA0AAAADgaDTIaaNDCBkNDBGhpk0aAZBiAANBIUQQB
Typ+lPDTSYak9qnlN6kaNPUAAAAGh6hoEUiCYRoMpkAYgFPSah5T1PUMhoMh6hoA0xPUCpJBNAmC
BkCBTwpmVP0mp6nlP1TNR6aR+VHlP0o9QM1Pao+m3Eg5uM5I9dFPvb/sKFy2moYBv2Rtn1wwjpdU
VaeouYjBeeP91B1LSLgVPX+GCuqq1/JaRCjEsaIbIiF1+Jc4tbi1izTSqisXr3ZMnRP0P06uiT26
p292zJoMJasg8yeSAVKDGhv6mORyFWIxsIFgQEkKi0vlVPp6sqK4mYU2de7G+gLKJKWQYwqdnv6e
R7CklxdhcXlrJPJ/PReBpidZULUkceIwnhNjqUpzDbm/PDaLdg97Dt9qfmnKJtppsYafOl03WY2Z
tjoWVOY4b+apArLmo1srYVdz8PO6CcCazEUUOR9flGMZT6h8H6EtA8E2fvB28J/qB8/ma00n0Awf
sSYfAIDIi7TejUGoNnV1bc9Qr8C3W8XnWy/BUoqQHC+lN1lWrwPLdbxMg1LFe8NQMaXkw2UreqS9
2WmUuCgZipUqFQqWy2st9FVW+UsidOimv+LElgWcIHz/x/O2ZWWjhwudFvYmbE2NbDU24Z1noYZ0
wmxMaKOil8lWmd3D8j6Cx+RRLhcXG7XlN7U0GklsOTmUu12mP6cMNEwMO//Oqwuyce/pn0YxoUTW
twLgdEQi2ilvX3plBOW8Bm00woQhF9wE6vDBhsygE2Yib32KUUpSjpPxj904zBJ2votDspxFI6my
uz5nzqU7tgt3e6nHSL7/l19yqU8ewns5rtiSxSSj+yrNO/mGsaT3DYXjbJG6dthFhslPg73OPsH7
kHeAfcYPA1zIiKzp1fZLbMpT2r8AQ001cdjxK+ugXhDRgNNCosqL1qPMckm1bX+idmjo2ySY3Wj1
t8Reec85RRRRhnYMbaH3H3fZtFg1XCw5tZJVMoGqD8W/AkLi3MJ7qqLmE2/TUsgmJiiSXAU6d/+A
FANixkEc6ooAWHD6Kd3mgZX0l5BC2p9qfQsfgyGgoqgiWZS4UVE0ZkrAZW+qx2VsfYjL3cpnMWHC
HD5w6PWwp+rHnbuUl1Y20dxeuqxMxtyZ6dovMDZOE4/UefWnRsGtS5OIYXQlybJ/BcnieH1/645k
dJURSfN3prXMPuOqo1adXt3l6anMRwjr22Zad23MWFXHXQBwJDypaXDInw13mk0QRkluzZsW6XR9
O2ZkaPVxZkvO+lUpzp4DM2Ote2N6Xup5H9oeLAnb2Mlg8TW7dNOO04Z8/QvBMwHGv5gOhyuNo43S
VeruswE6zqxGcErjnHNzFRztlRMffnmFm+k6TSAVRo8TcuBaZ8NFBpaXhm04Q4jiMzgwm/flt37n
pjyuidQfO0uib5zMvR7HPVo1G46QOSYHdIrJsjfsGmfNOWx3lahCmnSzZZPDtaM+rHezuvawc05r
cNbJy5bvCYuLzPW06z3OBk2uhlykNXKG7YbR54OF0iuBzwWF3M20KeEtn7F7PWdXFVPq9N5ok7WL
bsXODOxdnZufJhlpuPMCptYcRudRVSAVzJlnrM3iaSsvyxmdhSVKrQ1Mr10001JwaotpyZpkvexi
0tCzqjrlnGYt04TgGTTrv8uGeDUv4EcDbZMcd25ocLvVm4HkbWmrUTz1zYXTKTcTDQ7TFuOsu79O
TK1cxovkTb74pDVSWlHp1abfJ+UntMTpN2ajCy+rVrNvNmrGdP7cmOrpzC/wGxnc7qdrmZPTNDgr
Bh1W1F17cw4nPMEZWUm5nuubDjdsVdMGdp260YO5bbXDUXRyF5oLt7a1TxlqiIlXnGbJBvKSVpBx
qLzDXSMrSgmbLRsK7jXbrNdmbCeF9WsKSZ4gHA+L5ZOMD9RmfhgZ0NsUM+o4BadDsarVKJUL63WM
YFRS5xKyWuyyqC652ynt7rWXQjqoMpShNu1IpQDRx75mZg0hYjpvEWY6r0t0ONPgIAMHGCbSDUpF
NI+gwKUqqrR949XuT0j6aRg0P62qLQXE/fKJ/CD3k/Uf6R8GBPhGwVEfAn9xeS7RP3k7Oqiv97Wl
UYH8oMsjR/SP8DIWlwq/VF0a0TvjE/nH4xzxKKKFDMOcbLxfHAaRhGUuFw8n6i4n6kzGyPfqTdGc
aM8Xi6XfKNZdH5E+IyYi+J90ai3pKJ1fhmhTiT5oOQ+JuLxlzC0ZFzQcCWJjFhiUyGMWDDJqHyJ1
jkVJ2x0xjGk0NSJqjTL5cTOLoxHcaJmkZo2LQ35myLheTZF0c45hik1SMxNAtdBkg6TEYRcS+NMX
MrqqsRwj2GMYRhE/obzmJeKjX8plaR4n28vYQ7zKTE2wuk5zH0e1bzJ6qXJRdFSPvB8V5fQqhV8j
D8bpF77PZTFclUP4mDPH4JHA+PVK/vZ2xb3taT7nvffH2vg3s7+U1XsGhgpmZ3v+Dob6SbuvwRNj
4Mf0OWcpLqySwhb9TQ/0c3rD7A0YQFvWNvJRfD0REh4GThL2prnT24ST4yoMEjMzTD42+2y17XWO
MrsJGSW6Xjn9WEjx04vi8S92HvWU+DneDOrU3O+zF5Knwvzs344M7yy3V5DoSzM5f+6W2DCW1Mhr
7mR7VdncQ1Go7zDx2wTNJjyGRkSLDQTPrC96Zl611EudF0URkQi50aghU9m38woGoBijkLMKk1Il
cam7ToemamspFVGBax4VvSn4Oblhx51mM804uWxxV43KWbB6JZy0Rw2jgzXsrM0SpC7namLzkj1y
OlsXrOn3G5oOQhtOxozhnh59C8xW2ErH0czxyKdnEIxNTKzz6mX4IetSLt3qiWmssTBy8qRI1plS
zO4hpKVIpMVTR0M/Wv4uS88T0HPPq0W4Kuxd7PpD1u9gverJOpMt1b6i24l0i5lCy7yujS7dXB0H
MCVJttyh3Tk5TnxaYoJ0nrYKoOUi87zfe5fUSCzQpi2G4wMGUUZapSz6BTBdWQ4dRE4fApmURkQF
a6NWg49a97AG0ujI8qaCINKd/g9lHiyqgupMN7p430djpykwzxFqbpOlmN67XNjRxvhpN86DTE7i
IZ6TZDARCZZPYD4HYUQQ+Pi2VdJMpKXyfA8msyMzXUQtxeyKzpcxTgh0G8+8fV6hgeI9uD0+YQv7
qUKo9rzs6TwTiAUHaAUBw1rBCkAlRN7Tachwek1PK8HH0DwmMOCuMx97CRUjnwjG6eh3hjcwI2rc
iVGukjnNsVwl5GyEDxl8d5tLLwv/9BiWIeBa9zhagwKWkVnRBINz0oTeYv1F0OyqSpUCwCR7OPRm
ztyV+ydssyeOoUoaHTC02nLE9D1c86P3SpzJ07gDt/CXPU5mGxyPrPyLAy3YFLmOt2u44EBEHd6w
uukkblp0ORxVSKFSVR2lhYHSLtO+fyym7vo5cU1ryFEGnwLqXaZFyXkXKc6anWWl8PFxXz5fSDNF
EfXPDB2NSd8ldiJaLJRRK2TTvKlR5q7aPNxkNJuPrl8m4nJR1CaPpjIvPSkghB3MYPRUbBTxbht9
Na8+bcuY91GBPW53QXnMQB7Dkz3rpKDR56XznObMlVJHKyQ4jvsRaGcxPETKtBCuR1okFCVM8FWw
JXEKiuzDBuILSJIitBlJEDCDwRT+lKVPkAOpsTAOYzmg7nBbi0HAwkCwrBDTGBtxA6qqZtXKce8E
bCLEPQhlAimgh4XodYAfTwUJCeogFwBxqMaKgtqgvhhP4QSz/ERhT+f5lClg86HUnm9fgW5ssD+O
kfDdNJ3+zKkO507p+yyUPBah6Jb9t16ZyRnkw4cGufeveD7DKecYMGDAvF69e/4LxmM2dYMZeXtt
tsT8seLxmZ3HSt4BwHcuY611nGINy7DSdS7DkbTTQ2zghG8ZrIUYbBi6l4yVQg8UV7BpLlS38OvW
larwRdGY+3OAfimEgg2FbqOeR7zlKbK9gbrHOGdSCBtsrjwZ0KDGrWWzoom22S0hAYR0GNtobGc9
heveRyZnbJz6mWizfhbu6sN57QuTtq6TROaFi2K0zgcPq+wLwvDJJJJMRuRClFWoaAA0LKZoXQrg
cHLTW/ri8tSsBwYWXVdxYM7HAksvl8vc7C3MvNQT0FHuaGLTTJEzPMMFab8cW+j5/IlbmuVLYUvI
Q00ES3Tax7lvCoJEwZYtYzFM2JJp9xTVMUv2mYJkwr/7BEVFJI2hXnUydxBThcXNAFmgfwPyc55v
F+V7C2t9RUOyijUvegwaBwEmVVbNa4osAdZQ2bTmpte0vaHLDF1uZwrYqjzbT2mkKUPAye1/a8/k
VF05rMgdL4tGDvaw2FcqDJk9bc6XzKs90GBrZOqdOBhOqbJ4pfBfUjgrPytEqQ4rGFQGDQScSj4J
M6oSlhHc6mp524prQpbnbwdwDcArtRGMMESwJEsoJYuhyIYvp5BH8XckU4UJAqs+BSA=


--===============1108975737490705489==--