UPDATE/DELETE with LIMIT clause unsafe for SBR even when ORDER BY PK is present

[Ovais Tariq]

An update or delete when done using a limit generates a warning of the query being unsafe although an ORDER BY pk_column clause is used.

Test case is as follows:
master [localhost] {msandbox} (test) > show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `p` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

master [localhost] {msandbox} (test) > insert into a(id) values(null);
Query OK, 1 row affected (0.00 sec)

master [localhost] {msandbox} (test) > insert into a(id) select null from a;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

master [localhost] {msandbox} (test) > insert into a(id) select null from a;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

master [localhost] {msandbox} (test) > DELETE FROM a ORDER BY id LIMIT 1;
Query OK, 1 row affected, 1 warning (0.00 sec)

master [localhost] {msandbox} (test) > show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 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. |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You can see that the warning is generated even though there is an ORDER clause that orders by `id` which is a PK column.

[Sergei Glushchenko (sergei.glushchenko)]

The statements above generate following series of warnings:

==============================================================================

TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`p` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO a (id) VALUES (NULL);
INSERT INTO a (id) SELECT NULL FROM a;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
INSERT INTO a (id) SELECT NULL FROM a;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
DELETE FROM a ORDER BY id LIMIT 1;
Warnings:
Note 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.
SHOW WARNINGS;
Level Code Message
Note 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.
innodb.percona_bug1132194 [ fail ] Found warnings/errors in server log file!

This bug is about the last warning (using LIMIT).

[Laurynas Biveinis (laurynas-biveinis)]

There is another bug reported for the assert at the same location: bug 1086269, thus marking this one as its duplicate.

[Laurynas Biveinis (laurynas-biveinis)]

Please ignore comment #2, commented on the wrong bug by mistake.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-44