Bug #12340 multitable delete deletes only one record
Submitted: 3 Aug 2005 9:02 Modified: 16 Aug 2005 3:31
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.10a (Innodb) OS:Any (*)
Assigned to: Evgeny Potemkin

[3 Aug 2005 9:02] [ name withheld ]
Description:
While continuing testing with jakarta slide (see  Bug #12339) I've detected another problem.

In function "removeRevisionDescriptor" of the MySQLAdapter you'll find the following delete statement.

delete p from PROPERTIES p, VERSION_HISTORY vh, URI u where p.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = ? and vh.URI_ID = u.URI_ID AND u.URI_STRING = ?

This statement is deleting only one record from the properties table, even if there are several matching rows. 

This is reproducable as a prepared statement using jdbc or using the mysql console.

How to repeat:
Here's a small script showing the problem

drop table if exists PROPERTIES;
drop table if exists VERSION_HISTORY;
drop table if exists URI;

CREATE TABLE URI (
  URI_ID       bigint       NOT NULL auto_increment,
  URI_STRING   varchar(255)  CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY  (URI_ID),
  KEY URI_IX2  (URI_STRING)
) TYPE=InnoDB CHARACTER SET utf8;

CREATE TABLE VERSION_HISTORY (
  VERSION_ID   bigint      NOT NULL auto_increment,
  URI_ID       bigint      NOT NULL,
  BRANCH_ID    bigint      NOT NULL,
  REVISION_NO  varchar(20)  CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY  (VERSION_ID),
  UNIQUE KEY URI_ID (BRANCH_ID,URI_ID,REVISION_NO),
  KEY URI_ID_IX3 (URI_ID)
) TYPE=InnoDB CHARACTER SET utf8;

ALTER TABLE VERSION_HISTORY ADD CONSTRAINT VERSION_HISTORY_FK1 FOREIGN KEY
VERSION_HISTORY_FK1(URI_ID) REFERENCES URI (URI_ID);

CREATE TABLE PROPERTIES (
  VERSION_ID          bigint       NOT NULL,
  PROPERTY_NAMESPACE  varchar(50)   CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PROPERTY_NAME       varchar(50)   CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PROPERTY_VALUE      text			CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PROPERTY_TYPE       varchar(50)   CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  IS_PROTECTED        tinyint(1)    NOT NULL,
  PRIMARY KEY         (VERSION_ID,PROPERTY_NAMESPACE,PROPERTY_NAME)
) TYPE=InnoDB CHARACTER SET utf8;

ALTER TABLE PROPERTIES ADD CONSTRAINT PROPERTIES_FK1 FOREIGN KEY
PROPERTIES_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);

insert into URI (URI_ID, URI_STRING) values (1, '/');
insert into VERSION_HISTORY (URI_ID, BRANCH_ID, REVISION_NO)
select u.URI_ID, 2, '1.0' from URI u where u.URI_STRING = '/';

insert into PROPERTIES (VERSION_ID,PROPERTY_NAMESPACE,
PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE,IS_PROTECTED)
select vh.VERSION_ID, 'DAV:', 'modificationdate', '2005-08-03T08:05:16Z',
'', 1 from VERSION_HISTORY
vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = '/'
and vh.REVISION_NO = '1.0';

insert into PROPERTIES (VERSION_ID,PROPERTY_NAMESPACE,
PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE,IS_PROTECTED)
select vh.VERSION_ID, 'DAV:', 'creationdate', '2005-08-03T08:05:16Z',
'', 1 from VERSION_HISTORY
vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = '/'
and vh.REVISION_NO = '1.0';

delete p from PROPERTIES p, VERSION_HISTORY vh, URI u
where p.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = '1.0'
and vh.URI_ID = u.URI_ID AND u.URI_STRING = '/';

select * from PROPERTIES;
[3 Aug 2005 14:12] Aleksey Kishkin
mysql> select * from PROPERTIES \G
*************************** 1. row ***************************                                 VERSION_ID: 1                                                                  PROPERTY_NAMESPACE: DAV:
     PROPERTY_NAME: creationdate
    PROPERTY_VALUE: 2005-08-03T08:05:16Z
     PROPERTY_TYPE: 
      IS_PROTECTED: 1
*************************** 2. row ***************************
        VERSION_ID: 1
PROPERTY_NAMESPACE: DAV:
     PROPERTY_NAME: modificationdate
    PROPERTY_VALUE: 2005-08-03T08:05:16Z
     PROPERTY_TYPE: 
      IS_PROTECTED: 1
2 rows in set (0.00 sec)

mysql>  select p.* from PROPERTIES p, VERSION_HISTORY vh, URI u where p.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = '1.0' and vh.URI_ID = u.URI_ID AND u.URI_STRING = '/'\G
*************************** 1. row ***************************
        VERSION_ID: 1
PROPERTY_NAMESPACE: DAV:
     PROPERTY_NAME: creationdate
    PROPERTY_VALUE: 2005-08-03T08:05:16Z
     PROPERTY_TYPE: 
      IS_PROTECTED: 1
*************************** 2. row ***************************
        VERSION_ID: 1
PROPERTY_NAMESPACE: DAV:
     PROPERTY_NAME: modificationdate
    PROPERTY_VALUE: 2005-08-03T08:05:16Z
     PROPERTY_TYPE: 
      IS_PROTECTED: 1
2 rows in set (0.00 sec)

 delete p.* from PROPERTIES p, VERSION_HISTORY vh, URI u where p.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = '1.0' and vh.URI_ID = u.URI_ID AND u.URI_STRING = '/'\G 
Query OK, 1 row affected (0.05 sec)

mysql> select * from PROPERTIES \G                                                     *************************** 1. row ***************************
        VERSION_ID: 1
PROPERTY_NAMESPACE: DAV:
     PROPERTY_NAME: modificationdate
    PROPERTY_VALUE: 2005-08-03T08:05:16Z
     PROPERTY_TYPE: 
      IS_PROTECTED: 1
1 row in set (0.00 sec)
[9 Aug 2005 17:56] Evgeny Potemkin
When PRIMARY KEY is present ha_innobase::cmp_ref() uses it to compare refs.
After comparing part of key it moves pointers to compare next part.
For varchar parts pointers were moved only by length of parts, not including
bytes containig part length itself. This results in wrong comparision and
wrong number of deleted records.
[9 Aug 2005 18:03] 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/28081
[9 Aug 2005 20:43] Evgeny Potemkin
Fixed in 5.0.12, cset 1.1963.1.1
[16 Aug 2005 3:31] Mike Hillyer
Documented in 5.0.12 changelog:

<listitem><para>
 Comparison of InnoDB multi-part primary keys that include <literal>VARCHAR</literal> columns could produce incorrect results. (Bug #12340)
</para></listitem>