MySQL Bugs: #71105: DELETE .. ORDER BY .. LIMIT is safe in some cases

Bug #71105	DELETE .. ORDER BY .. LIMIT is safe in some cases
Submitted:	6 Dec 2013 16:00	Modified:	6 Dec 2013 16:55
Reporter:	Federico Razzoli	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.74, 5.5.35, 5.6.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
IMO, DELETE with ORDER BY and LIMIT should not produce the 1592 warning at least in 2 cases:

1)
mysql> CREATE TABLE t1 (a INT NOT NULL UNIQUE) ENGINE=InnoDB;
Query OK, 0 rows affected (0,46 sec)

mysql> DELETE FROM t1 ORDER BY a LIMIT 1;
Query OK, 0 rows affected, 1 warning (0,00 sec)

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

Why? ORDER BY <unique_field> LIMIT is deterministic.

2)
mysql> CREATE TABLE t2 (a INT, b INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0,31 sec)

mysql> DELETE FROM t2 ORDER BY a, b LIMIT 1;
Query OK, 0 rows affected, 1 warning (0,00 sec)

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

Why? ORDER BY <all_columns> LIMIT is deterministic.

How to repeat:
see above

http://bugs.mysql.com/bug.php?id=62537

Thank you for the report.

Verified as described. I agree this can be duplicate of bug #62537, but decided to keep it separate for now.