MySQL Bugs: #61769: Multi-table delete on unique index removes wrong rows in cluster

Bug #61769	Multi-table delete on unique index removes wrong rows in cluster
Submitted:	6 Jul 2011 10:06	Modified:	18 Nov 2011 11:45
Reporter:	Andrew Hutchings	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	ndb-7.1.14	OS:	Any
Assigned to:	Jonas Oreland	CPU Architecture:	Any
Tags:	regression

Description:
When deleting using multiple tables and a unique key on the where condition the wrong rows are deleted.

Workaround is to drop the unique index or turn it into a regular index.

How to repeat:
CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `c` (`c`),
  KEY `b` (`b`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` text,
  PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

INSERT INTO `t1` VALUES (1,3,'test3'),(2,2,'test2'),(3,1,'test1');
INSERT INTO `t2` VALUES (1,''),(2,''),(3,'');

delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test1';delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test2';delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test3';

Resulting output is random but almost always ends in:
ERROR 1032 (HY000): Can't find record in 't1'

Can't reproduce in 7.1.3, so looks like a regression.

After testing multiple versions found regression was introduced in 7.1.10

I have verified as described, however I have also found that if you select * from t1 before the second and third deletes, that those then succeed. 

12:38:29 root@localhost:test [139]> INSERT INTO `t1` VALUES (1,3,'test3'),(2,2,'test2'),(3,1,'test1');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

12:38:29 root@localhost:test [140]> INSERT INTO `t2` VALUES (1,''),(2,''),(3,'');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

12:38:29 root@localhost:test [141]> delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test1'; 
Query OK, 2 rows affected (0.02 sec)

12:38:30 root@localhost:test [142]> delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test2';
ERROR 1032 (HY000): Can't find record in 't1'
12:38:30 root@localhost:test [143]> select * from t1; select * from t2;
+---+------+-------+
| a | b    | c     |
+---+------+-------+
| 1 |    3 | test3 |
| 2 |    2 | test2 |
+---+------+-------+
2 rows in set (0.00 sec)

+---+------+
| a | b    |
+---+------+
| 3 |      |
| 2 |      |
+---+------+
2 rows in set (0.01 sec)

12:38:30 root@localhost:test [144]> delete t1,t2 from t1,t2 where t1.b=t2.a and t1.c='test3';
Query OK, 2 rows affected (0.01 sec)

Analysis:
1) also the first delete fails if you create tables, insert data and then   restart mysqld. It actually uses uninitialized data when performing the delete on t1.

2) The problem is due to this:
- server uses read_set to specify which columns an SE should read.
- in mysql_multi_delete, it correctly specifies that primary key for t1 needs to be read when reading using unique index.
- however, since this delete is using "const tables", (see what happens if you replace delete with select) the actual join is in fact performed *during join optimization*, and this is performed *before* the read_set is setup correctly.

Conclusion:
1) this is really a server bug
2) only SE's that don't read full rows are affected (i.e SE's that are in fact using readset)
3) possibly we could make a weird work-around
4) server should really add debug-code that initializes fields not being in readset to random-values. Then this would show-up on myisam/innodb too. This way ensuring that readset is correctly used by server!

I haven't investigated why this worked before...

/Jonas

this turned out to be the same as,
http://bugs.mysql.com/bug.php?id=61705

and has been fixed in upcoming 7.1.17