MySQL Bugs: #12340: multitable delete deletes only one record

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	CPU Architecture:	Any

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;

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)

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.

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

Fixed in 5.0.12, cset 1.1963.1.1

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>