Bug #62731 DELETE doesn't use composite keys in some case
Submitted: 14 Oct 2011 6:29 Modified: 14 Oct 2011 6:50
Reporter: Hailin Hu Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1.58 OS:Linux
Assigned to:
Tags: composite primary keys, delete

[14 Oct 2011 6:29] Hailin Hu
Description:
For a table using composite primary keys, delete locks the whole table even if keys of one row is given in some case.
I am afraid the same where clause is parsed in different way is not expected.

How to repeat:
create a table as the below:

CREATE  TABLE IF NOT EXISTS `foo` (
  `col1` INT NOT NULL,
  `col2` INT NOT NULL,
  PRIMARY KEY (`col1`,`col2`) )
ENGINE = InnoDB;
INSERT IGNORE INTO `foo` VALUES (1,1), (2,2), (3,3), (4,4);

start transaction;
# 1 row locked
delete from foo where col1 = 1 and col2 = 1;
# 5 rows locked
delete from foo where ( col1, col2 ) = ( 1, 1 );
# 1 row
explain select * from foo where ( col1, col2 ) = ( 1, 1 );
show engine innodb status;
rollback;
[14 Oct 2011 6:50] Valerii Kravchuk
This is a duplicate of Bug #43187.