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
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.1.58
• OS: Linux
• CPU Architecture: Any
• 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] Valeriy Kravchuk

This is a duplicate of Bug #43187.