Description:
MySQL mistakenly produces warning when rolling back transactions on an UPDATE query that has JOIN to sub-query, but only under some circumstances.
How to repeat:
-- MySQL mistakenly produces warning when rolling
-- back transactions on an UPDATE query that has
-- JOIN to sub-query, but only under some circumstances.
--
-- Warning:
-- Some non-transactional changed tables couldn't be rolled back
CREATE DATABASE db_c;
USE db_c;
CREATE TABLE t_a (
c_1 int,
c_2 int
) ENGINE=InnoDB;
INSERT INTO t_a VALUES (1, 1);
INSERT INTO t_a VALUES (2, 2);
-- GOOD
BEGIN;
UPDATE t_a
JOIN
(
SELECT c_1
FROM t_a
) AS sub_q USING (c_1)
SET t_a.c_1 = 9
WHERE c_2 = 1;
-- Rows matched: 1 Changed: 1 Warnings: 0
ROLLBACK;
-- Query OK, 0 rows affected (0.00 sec)
-- BAD
-- Note: sub-query has no rows.
BEGIN;
UPDATE t_a
JOIN
(
SELECT c_1
FROM t_a
WHERE 0
) AS sub_q USING (c_1)
SET t_a.c_1 = 9
WHERE c_2 = 1;
-- Rows matched: 0 Changed: 0 Warnings: 0
ROLLBACK;
-- Query OK, 0 rows affected, 1 warning (0.00 sec)
-- BAD
-- Note: delete all but one row in table
-- then re-run query that was initially good.
-- Weird, huh?
DELETE FROM t_a WHERE c_2 = 2;
BEGIN;
UPDATE t_a
JOIN
(
SELECT c_1
FROM t_a
) AS sub_q USING (c_1)
SET t_a.c_1 = 9
WHERE c_2 = 1;
-- Rows matched: 1 Changed: 1 Warnings: 0
ROLLBACK;
-- Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS\G
-- Level: Warning
-- Code: 1196
-- Message: Some non-transactional changed tables couldn't be rolled back
SELECT VERSION();
-- Tested on:
-- 4.1.22-log
-- 5.0.41-community
DROP DATABASE db_c;
Description: MySQL mistakenly produces warning when rolling back transactions on an UPDATE query that has JOIN to sub-query, but only under some circumstances. How to repeat: -- MySQL mistakenly produces warning when rolling -- back transactions on an UPDATE query that has -- JOIN to sub-query, but only under some circumstances. -- -- Warning: -- Some non-transactional changed tables couldn't be rolled back CREATE DATABASE db_c; USE db_c; CREATE TABLE t_a ( c_1 int, c_2 int ) ENGINE=InnoDB; INSERT INTO t_a VALUES (1, 1); INSERT INTO t_a VALUES (2, 2); -- GOOD BEGIN; UPDATE t_a JOIN ( SELECT c_1 FROM t_a ) AS sub_q USING (c_1) SET t_a.c_1 = 9 WHERE c_2 = 1; -- Rows matched: 1 Changed: 1 Warnings: 0 ROLLBACK; -- Query OK, 0 rows affected (0.00 sec) -- BAD -- Note: sub-query has no rows. BEGIN; UPDATE t_a JOIN ( SELECT c_1 FROM t_a WHERE 0 ) AS sub_q USING (c_1) SET t_a.c_1 = 9 WHERE c_2 = 1; -- Rows matched: 0 Changed: 0 Warnings: 0 ROLLBACK; -- Query OK, 0 rows affected, 1 warning (0.00 sec) -- BAD -- Note: delete all but one row in table -- then re-run query that was initially good. -- Weird, huh? DELETE FROM t_a WHERE c_2 = 2; BEGIN; UPDATE t_a JOIN ( SELECT c_1 FROM t_a ) AS sub_q USING (c_1) SET t_a.c_1 = 9 WHERE c_2 = 1; -- Rows matched: 1 Changed: 1 Warnings: 0 ROLLBACK; -- Query OK, 0 rows affected, 1 warning (0.00 sec) SHOW WARNINGS\G -- Level: Warning -- Code: 1196 -- Message: Some non-transactional changed tables couldn't be rolled back SELECT VERSION(); -- Tested on: -- 4.1.22-log -- 5.0.41-community DROP DATABASE db_c;